DEMO: HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER

HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL
HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE

The DBT_UPRIVSI script is a set of queries to data dictionary views that retrieve all DIRECTLY and INDIRECTLY granted privileges to a user.
The DBT_UPRIVSD script is a set of queries to data dictionary views that retrieve only DIRECTLY granted privileges to a user.

-- DBT_UPRIVSI

-------------------------------------
-- A variable for keeping a username
-------------------------------------
SET LINES 200
SET VERIFY OFF
SET SQLNUMBER OFF
SET FEEDBACK OFF
SET PAGES 999
SET LINES 200

PROMPT
ACCEPT USERNAME PROMPT 'USERNAME: '
PROMPT

------------------------------------
-- All user roles
------------------------------------

PROMPT
PROMPT -- All user roles

COL GRANTEE FOR A20
COL GRANTED_ROLE FOR A40

SELECT DISTINCT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
ORDER BY 1,2;
  
------------------------------------
-- System privileges: DBA_SYS_PRIVS
------------------------------------

PROMPT
PROMPT -- System privileges: DBA_SYS_PRIVS 

COL PRIVILEGE FOR A40 

WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE,PRIVILEGE
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE=UPPER('&USERNAME') OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2;

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

PROMPT
PROMPT -- Object privileges: DBA_TAB_PRIVS

COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A40

WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE, PRIVILEGE,
       OWNER || '.' || TABLE_NAME OBJECT_NAME
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE=UPPER('&USERNAME') OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2,3;

Written At

18 NOV 202122:30

OEL Release

7.9 x64

Database Release

19.12.0.0.0