------------------------------------------------------------------------ -- Victor Khalturin -- -- Version 13:12 21-Nov-2021 (001) -- -- dbt_opfau.sql - Objects Privileges For A User. -- -- The DBT_OPFAU fetches all objects, including objects permissions -- granted to a specific user. In addition, it provides information on how -- the privileges are transferred to a user: directly or through a role. ------------------------------------------------------------------------ SET PAGES 999 SET LINES 300 COL OBJECT_TYPE FOR A15 COL OWNER FOR A15 COL OBJECT_NAME FOR A15 COL GRANTED_TO FOR A15 COL GRANTED_DIRECTLY FOR A16 COL GRANTED_THROUGH FOR A15 COL PRIVILEGE FOR A10 COL GRANTABLE FOR A9 SET VERIFY OFF SET SQLNUMBER OFF SET COLSEP " | " UNDEFINE USERNAME WITH ROLES AS ( -- FETCHING ALL ROLES ASSIGNED TO A USER THROUGH A HIERARCHICAL QUERY SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS START WITH GRANTEE=UPPER('&&USERNAME') CONNECT BY PRIOR GRANTED_ROLE=GRANTEE ) SELECT OWNER, TABLE_NAME OBJECT_NAME, TYPE OBJECT_TYPE, '&USERNAME' GRANTED_TO, DECODE(GRANTEE,'&USERNAME','YES','NO') GRANTED_DIRECTLY, DECODE(GRANTEE,'&USERNAME','DIRECT GRANT',GRANTEE) GRANTED_THROUGH, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE='&USERNAME' OR GRANTEE IN (SELECT GRANTED_ROLE FROM ROLES) ORDER BY OWNER,GRANTED_THROUGH;