How to grant access to oracle dynamic performance views (v$ views)

Last Updated on :October 25, 2019

Oracle dynamic performance views are used to view current database activity and performance in real time. These dynamic views are starts with v$ and also called v$ views

If any user has granted ‘SELECT ANY DICTIONARY’ or ‘select_catalog_role’ privilege, then same user can query data dictionary and dynamic performance views

grant select any dictionary to USER;
grant select_catalog_role to USER ;

As most of v$views are used by DBA , In many cases granting all dynamic performance view to developer / application user  might violate security rule.

If DBA wish to grant specific  v$views (v$session, v$lock, v$sql, and v$database etc) to developer or application user to monitor database session or lock   then DBA can create new role and grant privilege to specific v$ views such as v$session, v$lock, v$sql, and v$database etc

Here is the Script to create new role ‘SELECT_VDOLLAR_ROLE’

DROP   ROLE SELECT_VDOLLAR_ROLE;
CREATE ROLE SELECT_VDOLLAR_ROLE;

BEGIN
    FOR x IN ( SELECT object_name
                 FROM dba_objects
                WHERE object_type = 'VIEW' 
                  AND object_name LIKE 'V\_$%' ESCAPE '\' )
    LOOP
        DBMS_OUTPUT.PUT_line(x.object_name);
       EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
    END LOOP;
END;
/

Once role is created, you can grant this role to any user those require access to v$views

grant SELECT_VDOLLAR_ROLE to [USER];
grant SELECT_VDOLLAR_ROLE to hr;