Home » Oracle » Oracle Database » How to grant access to v$ views (v$session ,v$instance)

How to grant access to v$ views (v$session ,v$instance)

One can’t grant direct access V$session as v$session is a synonym. Same is true for v$instance

In fact this is true for all the v$ views are they are all synonym’s

SQL> GRANT SELECT ON v$session TO test_user;
grant select on v$session to test_user
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> GRANT SELECT ON v$instance TO test_user;
grant select on v$instance to test_user
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Query to shows the V$SESSION is a public synonym

SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;
OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$INSTANCE’; 
OWNER OBJECT_TYPE 
———————— ——————- 
PUBLIC SYNONYM

Query to shows the object (table/view) the synonym points to

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;
TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’; 
TABLE_OWNER TABLE_NAME 
————– —————————— 
SYS V_$INSTANCE

How to grant access to v$ views

We can grant access  to these views   by giving  the grant access on the  underlying table/view.
so for V$session, we need grant select on v_$session. Similarly for other views, we have to do the same things

SQL> GRANT SELECT ON V_$SESSION TO test_user;
Grant succeeded.

SQL> GRANT SELECT ON V_$INSTANCE TO test_user; 
Grant succeeded.

Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation

Here are some of the v$ views with the synonym name and table name. we need to use the table for granting access to the user, if we try with synonym name, we will get this same failure for all these views

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ACCESS                       V_$ACCESS
PUBLIC     V$ACTIVE_INSTANCES             V_$ACTIVE_INSTANCES
PUBLIC     V$ACTIVE_SERVICES              V_$ACTIVE_SERVICES
PUBLIC     V$ACTIVE_SESSION_HISTORY       V_$ACTIVE_SESSION_HISTORY
PUBLIC     V$ACTIVE_SESS_POOL_MTH         V_$ACTIVE_SESS_POOL_MTH
PUBLIC     V$ADVISOR_CURRENT_SQLPLAN      V_$ADVISOR_CURRENT_SQLPLAN
PUBLIC     V$ADVISOR_PROGRESS             V_$ADVISOR_PROGRESS
PUBLIC     V$ALERT_TYPES                  V_$ALERT_TYPES
PUBLIC     V$AQ                           V_$AQ
PUBLIC     V$AQ1                          V_$AQ1
PUBLIC     V$AQ_BACKGROUND_COORDINATOR    V_$AQ_BACKGROUND_COORDINATOR

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$AQ_BMAP_NONDUR_SUBSCRIBERS   V_$AQ_BMAP_NONDUR_SUBSCRIBERS
PUBLIC     V$AQ_CACHED_SUBSHARDS          V_$AQ_CACHED_SUBSHARDS
PUBLIC     V$AQ_CROSS_INSTANCE_JOBS       V_$AQ_CROSS_INSTANCE_JOBS
PUBLIC     V$AQ_INACTIVE_SUBSHARDS        V_$AQ_INACTIVE_SUBSHARDS
PUBLIC     V$AQ_JOB_COORDINATOR           V_$AQ_JOB_COORDINATOR
PUBLIC     V$AQ_MESSAGE_CACHE             V_$AQ_MESSAGE_CACHE
PUBLIC     V$AQ_MESSAGE_CACHE_ADVICE      V_$AQ_MESSAGE_CACHE_ADVICE
PUBLIC     V$AQ_MESSAGE_CACHE_STAT        V_$AQ_MESSAGE_CACHE_STAT
PUBLIC     V$AQ_MSGBM                     V_$AQ_MSGBM
PUBLIC     V$AQ_NONDUR_REGISTRATIONS      V_$AQ_NONDUR_REGISTRATIONS
PUBLIC     V$AQ_NONDUR_SUBSCRIBER         V_$AQ_NONDUR_SUBSCRIBER

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$AQ_NONDUR_SUBSCRIBER_LWM     V_$AQ_NONDUR_SUBSCRIBER_LWM
PUBLIC     V$AQ_NOTIFICATION_CLIENTS      V_$AQ_NOTIFICATION_CLIENTS
PUBLIC     V$AQ_REMOTE_DEQUEUE_AFFINITY   V_$AQ_REMOTE_DEQUEUE_AFFINITY
PUBLIC     V$AQ_SERVER_POOL               V_$AQ_SERVER_POOL
PUBLIC     V$AQ_SHARDED_SUBSCRIBER_STAT   V_$AQ_SHARDED_SUBSCRIBER_STAT
PUBLIC     V$AQ_SUBSCRIBER_LOAD           V_$AQ_SUBSCRIBER_LOAD
PUBLIC     V$AQ_UNCACHED_SUBSHARDS        V_$AQ_UNCACHED_SUBSHARDS
PUBLIC     V$ARCHIVE                      V_$ARCHIVE
PUBLIC     V$ARCHIVED_LOG                 V_$ARCHIVED_LOG
PUBLIC     V$ARCHIVE_DEST                 V_$ARCHIVE_DEST
PUBLIC     V$ARCHIVE_DEST_STATUS          V_$ARCHIVE_DEST_STATUS

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ARCHIVE_GAP                  V_$ARCHIVE_GAP
PUBLIC     V$ARCHIVE_PROCESSES            V_$ARCHIVE_PROCESSES
PUBLIC     V$ASH_INFO                     V_$ASH_INFO
PUBLIC     V$ASM_ACFSREPL                 V_$ASM_ACFSREPL
PUBLIC     V$ASM_ACFSREPLTAG              V_$ASM_ACFSREPLTAG
PUBLIC     V$ASM_ACFSSNAPSHOTS            V_$ASM_ACFSSNAPSHOTS
PUBLIC     V$ASM_ACFSTAG                  V_$ASM_ACFSTAG
PUBLIC     V$ASM_ACFSVOLUMES              V_$ASM_ACFSVOLUMES
PUBLIC     V$ASM_ACFS_ENCRYPTION_INFO     V_$ASM_ACFS_ENCRYPTION_INFO
PUBLIC     V$ASM_ACFS_SECURITY_INFO       V_$ASM_ACFS_SECURITY_INFO
PUBLIC     V$ASM_ACFS_SEC_ADMIN           V_$ASM_ACFS_SEC_ADMIN

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ASM_ACFS_SEC_CMDRULE         V_$ASM_ACFS_SEC_CMDRULE
PUBLIC     V$ASM_ACFS_SEC_REALM           V_$ASM_ACFS_SEC_REALM
PUBLIC     V$ASM_ACFS_SEC_REALM_FILTER    V_$ASM_ACFS_SEC_REALM_FILTER
PUBLIC     V$ASM_ACFS_SEC_REALM_GROUP     V_$ASM_ACFS_SEC_REALM_GROUP
PUBLIC     V$ASM_ACFS_SEC_REALM_USER      V_$ASM_ACFS_SEC_REALM_USER
PUBLIC     V$ASM_ACFS_SEC_RULE            V_$ASM_ACFS_SEC_RULE
PUBLIC     V$ASM_ACFS_SEC_RULESET         V_$ASM_ACFS_SEC_RULESET
PUBLIC     V$ASM_ACFS_SEC_RULESET_RULE    V_$ASM_ACFS_SEC_RULESET_RULE
PUBLIC     V$ASM_ALIAS                    V_$ASM_ALIAS
PUBLIC     V$ASM_ATTRIBUTE                V_$ASM_ATTRIBUTE
PUBLIC     V$ASM_AUDIT_CLEANUP_JOBS       V_$ASM_AUDIT_CLEANUP_JOBS

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ASM_AUDIT_CLEAN_EVENTS       V_$ASM_AUDIT_CLEAN_EVENTS
PUBLIC     V$ASM_AUDIT_CONFIG_PARAMS      V_$ASM_AUDIT_CONFIG_PARAMS
PUBLIC     V$ASM_AUDIT_LAST_ARCH_TS       V_$ASM_AUDIT_LAST_ARCH_TS
PUBLIC     V$ASM_CLIENT                   V_$ASM_CLIENT
PUBLIC     V$ASM_DBCLONE_INFO             V_$ASM_DBCLONE_INFO
PUBLIC     V$ASM_DISK                     V_$ASM_DISK
PUBLIC     V$ASM_DISKGROUP                V_$ASM_DISKGROUP
PUBLIC     V$ASM_DISKGROUP_SPARSE         V_$ASM_DISKGROUP_SPARSE
PUBLIC     V$ASM_DISKGROUP_STAT           V_$ASM_DISKGROUP_STAT
PUBLIC     V$ASM_DISK_IOSTAT              V_$ASM_DISK_IOSTAT
PUBLIC     V$ASM_DISK_IOSTAT_SPARSE       V_$ASM_DISK_IOSTAT_SPARSE

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ASM_DISK_SPARSE              V_$ASM_DISK_SPARSE
PUBLIC     V$ASM_DISK_SPARSE_STAT         V_$ASM_DISK_SPARSE_STAT
PUBLIC     V$ASM_DISK_STAT                V_$ASM_DISK_STAT
PUBLIC     V$ASM_ESTIMATE                 V_$ASM_ESTIMATE
PUBLIC     V$ASM_FILE                     V_$ASM_FILE
PUBLIC     V$ASM_FILEGROUP                V_$ASM_FILEGROUP
PUBLIC     V$ASM_FILEGROUP_FILE           V_$ASM_FILEGROUP_FILE
PUBLIC     V$ASM_FILEGROUP_PROPERTY       V_$ASM_FILEGROUP_PROPERTY
PUBLIC     V$ASM_FILESYSTEM               V_$ASM_FILESYSTEM
PUBLIC     V$ASM_OPERATION                V_$ASM_OPERATION
PUBLIC     V$ASM_QUOTAGROUP               V_$ASM_QUOTAGROUP

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$ASM_TEMPLATE                 V_$ASM_TEMPLATE
PUBLIC     V$ASM_USER                     V_$ASM_USER
PUBLIC     V$ASM_USERGROUP                V_$ASM_USERGROUP
PUBLIC     V$ASM_USERGROUP_MEMBER         V_$ASM_USERGROUP_MEMBER
PUBLIC     V$ASM_VOLUME                   V_$ASM_VOLUME
PUBLIC     V$ASM_VOLUME_STAT              V_$ASM_VOLUME_STAT
PUBLIC     V$AW_AGGREGATE_OP              V_$AW_AGGREGATE_OP
PUBLIC     V$AW_ALLOCATE_OP               V_$AW_ALLOCATE_OP
PUBLIC     V$AW_CALC                      V_$AW_CALC
PUBLIC     V$AW_LONGOPS                   V_$AW_LONGOPS
PUBLIC     V$AW_OLAP                      V_$AW_OLAP

OWNER      SYNONYM_NAME                   TABLE_NAME
---------- ------------------------------ ------------------------------
PUBLIC     V$AW_SESSION_INFO              V_$AW_SESSION_INFO
PUBLIC     V$BACKUP                       V_$BACKUP
PUBLIC     V$BACKUP_ARCHIVELOG_DETAILS    V_$BACKUP_ARCHIVELOG_DETAILS
PUBLIC     V$BACKUP_ARCHIVELOG_SUMMARY    V_$BACKUP_ARCHIVELOG_SUMMARY
PUBLIC     V$BACKUP_ASYNC_IO              V_$BACKUP_ASYNC_IO
PUBLIC     V$BACKUP_CONTROLFILE_DETAILS   V_$BACKUP_CONTROLFILE_DETAILS
PUBLIC     V$BACKUP_CONTROLFILE_SUMMARY   V_$BACKUP_CONTROLFILE_SUMMARY
PUBLIC     V$BACKUP_COPY_DETAILS          V_$BACKUP_COPY_DETAILS
PUBLIC     V$BACKUP_COPY_SUMMARY          V_$BACKUP_COPY_SUMMARY
PUBLIC     V$BACKUP_CORRUPTION            V_$BACKUP_CORRUPTION
PUBLIC     V$BACKUP_DATAFILE              V_$BACKUP_DATAFILE

Hope you like short article on grant select on v_$session. This same can be executed for granting access on any v$views . Hope you like this post. Please do provide the feedback

See also  How ASM Failure Groups and CSS provide high availability

Also Reads
alter system switch logfile : Check the difference between alter system switch log file and alter system archive log current
V$system_parameter : Check out V$parameter,v$system_parameter and v$spparameter view and how we can use them in oracle database
alter system kill session : Check how to kill the oracle session in case of locking and hangs
alter user identified by values :
https://docs.oracle.com/database/121/REFRN/GUID-28E2DC75-E157-4C0A-94AB-117C205789B9.htm
ora-00942: table or view does not exist

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top