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 DEMO user

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

-- Granting SELECT privileges on some system views to the DEMO user

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

Now let's query data dictionary views

For directly and indirectly granted privileges

DBT_UPRIVSI

 @dbt_uprivsi.sql

USERNAME: DEMO


-- All user roles

GRANTEE         GRANTED_ROLE
--------------- ----------------------------------------
DEMO            CONNECT
DEMO            RESOURCE
RESOURCE        SODA_APP

-- System privileges: DBA_SYS_PRIVS

GRANTEE         PRIVILEGE
--------------- ----------------------------------------
CONNECT         CREATE SESSION
CONNECT         SET CONTAINER
DEMO            CREATE ANY DIRECTORY
DEMO            UNLIMITED TABLESPACE
RESOURCE        CREATE CLUSTER
RESOURCE        CREATE INDEXTYPE
RESOURCE        CREATE OPERATOR
RESOURCE        CREATE PROCEDURE
RESOURCE        CREATE SEQUENCE
RESOURCE        CREATE TABLE
RESOURCE        CREATE TRIGGER
RESOURCE        CREATE TYPE

-- Object privileges: DBA_TAB_PRIVS

GRANTEE         PRIVILEGE  OBJECT_NAME
--------------- ---------- ----------------------------------------
DEMO            SELECT     SYS.V_$SESSION
DEMO            SELECT     SYS.V_$SESSION_EVENT
SODA_APP        EXECUTE    XDB.DBMS_SODA_ADMIN
SODA_APP        EXECUTE    XDB.DBMS_SODA_USER_ADMIN
SODA_APP        READ       XDB.JSON$USER_COLLECTION_METADATA

For directly granted privileges and roles
DBT_UPRIVSD

SQL> @dbt_uprivsd.sql

USERNAME: DEMO


-- System privileges: DBA_SYS_PRIVS

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



-- Granted Roles: DBA_ROLE_PRIVS

GRANTEE         GRANTED_ROLE     DEFAULT ROLE_CREATED      ROLE_TYPE
--------------- ---------------- ------- ----------------- ------------
DEMO            CONNECT          YES     17-APR-2019 00:56 ORACLE ROLE
DEMO            RESOURCE         YES     17-APR-2019 00:56 ORACLE ROLE



-- 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.13.0.0.0