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;