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_SROPRIVS script is a set of queries to data dictionary views that retrieve all privileges granted to a user.

-------------------------------------
-- 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 GRANTEE FOR A10 
COL PRIVILEGE FOR A40 

SELECT GRANTEE,PRIVILEGE
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE=UPPER('&USERNAME');

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

BREAK ON ROLE 
COL ROLE FOR A10 
COL PRIVILEGE FOR A20 

SELECT ROLE,PRIVILEGE
FROM   ROLE_SYS_PRIVS
WHERE  ROLE IN (
       SELECT GRANTED_ROLE
       FROM DBA_ROLE_PRIVS
       WHERE GRANTEE=UPPER('&USERNAME'))
ORDER BY ROLE;

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

COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A20

SELECT GRANTEE, PRIVILEGE,
       OWNER || '.' || TABLE_NAME OBJECT_NAME
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE=UPPER('&USERNAME');

Written At

18 NOV 202122:30

OEL Release

7.9 x64

Database Release

19.12.0.0.0