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

SQL> @DBT_UPRIVSI.sql

USERNAME or ROLE: demo

*************************
** Account details
*************************


Database                  TST19C
Systimestamp              11-SEP-2022 15:52
Account Name              DEMO
Account Type              USER
Created                   11-SEP-2022 15:42
Expiry Date               10-MAR-2023 15:42
Account Status            OPEN
User Profile              DEFAULT
Default Tablespace        USERS
Temporary Tablespace      TEMP


*************************
** Granted Roles
** Directly and Indirectly
*************************

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


*************************
** System privileges
** Directly and Indirectly
*************************

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
** Directly and Indirectly
*************************

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 or ROLE: demo

*************************
** Account details
*************************


Database                  TST19C
Systimestamp              11-SEP-2022 15:54
Account Name              DEMO
Account Type              USER
Created                   11-SEP-2022 15:42
Expiry Date               10-MAR-2023 15:42
Account Status            OPEN
User Profile              DEFAULT
Default Tablespace        USERS
Temporary Tablespace      TEMP


*************************
** System privileges
*************************

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


*************************
** Granted Roles
*************************

GRANTEE                   GRANTED_ROLE                   SET_AS_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
*************************

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