DEMO: HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE

HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL
HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER

The DBT_OPFAU script 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.

------------------------------------------------------------------------
-- 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;

Written At

21 NOV 202113:20

OEL Release

7.9 x64

Database Release

19.12.0.0.0