Print Friendly, PDF & Email
How to find the privileges that have been granted to a user by querying data dictionary views.
SOLUTION

Three data dictionary views provide access to information about privileges granted to users or roles

The DBA_SYS_PRIVS describes system privileges granted to users and roles.
The ROLE_SYS_PRIVS describes system privileges granted to roles.
The DBA_TAB_PRIVS describes all object grants in the database.
DEMO

With this demo, you grant some privileges to the DEMO user, and then by querying three mentioned data dictionary views, you retrieve all privileges you've granted to that user.

Create a user for the demo

-- Create the DEMO user
-- DROP USER DEMO CASCADE;

CREATE USER DEMO IDENTIFIED BY DEMO DEFAULT TABLESPACE USERS;

Provide some grant to the DEMO user

-- Granting some system roles to the DEMO user

GRANT CONNECT, RESOURCE TO DEMO;

-- Granting some system privileges to the <span class="hlw">DEMO</span> user

GRANT CREATE ANY DIRECTORY TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO;

-- Granting SELECT privileges on some system views to the <span class="hlw">DEMO</span> user

GRANT SELECT ON V_$SESSION TO DEMO;
GRANT SELECT ON V_$SESSION_EVENT TO DEMO;

Now query mentioned data dictionary views to retrieve all granted privileges by executing the DBT_SROPRIVS script

SQL> @dbt_sroprivs.sql

USERNAME: demo


-- System privileges: DBA_SYS_PRIVS

GRANTEE    PRIVILEGE
---------- ----------------------------------------
DEMO       UNLIMITED TABLESPACE
DEMO       CREATE ANY DIRECTORY

-- Role privileges: DBA_ROLE_PRIVS

ROLE       PRIVILEGE
---------- --------------------
CONNECT    SET CONTAINER
           CREATE SESSION
RESOURCE   CREATE OPERATOR
           CREATE CLUSTER
           CREATE INDEXTYPE
           CREATE TABLE
           CREATE TRIGGER
           CREATE TYPE
           CREATE PROCEDURE
           CREATE SEQUENCE

-- Object privileges: DBA_TAB_PRIVS

GRANTEE    PRIVILEGE  OBJECT_NAME
---------- ---------- --------------------
DEMO       SELECT     SYS.V_$SESSION
DEMO       SELECT     SYS.V_$SESSION_EVENT

As can be seen from the output, the privileges returned by the querying mentioned views are identical to privileges granted to the DEMO user at the beginning of the demo.

NOTE

By querying the same views, you can easily reconstruct GRANT statements for each granted privilege.

-------------------------------------
-- Defining a variable for keeping a username
-------------------------------------
SET LINES 200
SET VERIFY OFF
SET SQLNUMBER OFF
SET FEEDBACK OFF

ACCEPT USERNAME PROMPT 'USERNAME: '


------------------------------------
-- System privileges: DBA_SYS_PRIVS
------------------------------------

COL SYSTEM_GRANTS FOR A50 
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || 
       DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' SYSTEM_GRANTS 
       FROM DBA_SYS_PRIVS WHERE GRANTEE=UPPER('&USERNAME');

------------------------------------
-- Role privileges: DBA_ROLE_PRIVS
------------------------------------

COL ROLE_GRANTS FOR A50 
SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || 
       DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' ROLE_GRANTS 
       FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER('&USERNAME');
       

------------------------------------
-- Object privileges: DBA_TAB_PRIVS
------------------------------------

COL OBJECT_GRANTS FOR A50 
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || 
       DECODE(GRANTABLE, 'YES', ' WITH GRANT OPTION', NULL) || ';' OBJECT_GRANTS 
       FROM DBA_TAB_PRIVS WHERE GRANTEE=UPPER('&USERNAME');
EXAMPLE
SQL> ACCEPT USERNAME PROMPT 'USERNAME: '
USERNAME: demo


SYSTEM_GRANTS
--------------------------------------------------
GRANT UNLIMITED TABLESPACE TO DEMO;
GRANT CREATE ANY DIRECTORY TO DEMO;


ROLE_GRANTS
------------------------------
GRANT RESOURCE TO DEMO;
GRANT CONNECT TO DEMO;


OBJECT_GRANTS
--------------------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO DEMO;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO DEMO;

That’s it. The demo is completed. Drop the DEMO user

SQL> DROP USER DEMO CASCADE;

Written At
18 NOV 202123:00
OEL Release
7.9 x64
Database Release
19.12.0.0.0