script to list all privileges for user

script to list all privileges for user

/* Setting formatting options */
SET TERMOUT OFF
SET SERVEROUTPUT ON
SET VERIFY OFF;
SET FEEDBACK OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE
/* Define bind variables */
VAR i_depth NUMBER;
VAR i_user VARCHAR2(32);
/* Retrieve user input */
SET TERMOUT ON
ACCEPT USER  PROMPT ‘User: ‘
ACCEPT DEPTH  DEFAULT 3 PROMPT ‘Depth [3]: ‘
SET TERMOUT OFF
/* Bind input */
exec :i_depth := &DEPTH;
exec :i_user := ‘&USER’;
SET TERMOUT ON
COLUMN PRIVS FORMAT A150
SELECT (
CASE WHEN LEVEL > 1
THEN LPAD(‘ ‘, 4*(LEVEL-1)) || CHR(212) || CHR(205) || ‘> ‘
END
) || GRANTED_ROLE “PRIVS”
FROM
(
/* THE USERS */
SELECT NULL  AS GRANTEE
, USERNAME  AS GRANTED_ROLE
FROM  DBA_USERS
WHERE UPPER(USERNAME) = UPPER(:i_user)
/* THE ROLES TO ROLES RELATIONS */
UNION
SELECT GRANTEE
, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
/* THE ROLES TO SYS PRIVILEGE RELATIONS */
UNION
SELECT GRANTEE
, PRIVILEGE
FROM DBA_SYS_PRIVS
UNION
/* THE ROLES TO OBJECT PRIVILEGE RELATIONS */
SELECT GRANTEE
, RPAD(DBA_TAB_PRIVS.OWNER || ‘.’ || TABLE_NAME,62,’ ‘) ||  ‘ (‘ || DBA_OBJECTS.OBJECT_TYPE || ‘ -> ‘ || PRIVILEGE || ‘)’
FROM  DBA_TAB_PRIVS
JOIN DBA_OBJECTS ON DBA_OBJECTS.OBJECT_NAME = DBA_TAB_PRIVS.TABLE_NAME
WHERE  OBJECT_TYPE NOT IN (‘PACKAGE BODY’,'SYNONYM’)
)
START WITH GRANTEE IS NULL
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE AND LEVEL <= :i_depth;