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

Last updated on September 2nd, 2019 at 05:52 pm

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

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.

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

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

Leave a Reply