script to list all privileges for user
Posted by Pavan DBA on October 16, 2011
/* 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;
Pavan DBA's Blog 
123 said
Hello Please mention the imp scripts with the links..
I know the above script is copy and pasted from https://forums.oracle.com/forums/thread.jspa?messageID=3863731
http://bhaskarrampalli.blogspot.in/2011/11/user-management-category.html..share the information in better way..if you can otherwise leave it. your not the only dba master in the industry. you know yourself as you have to improve lot of areas in dba concepts. you know only basic part..I just want to say dont fool the members, share the knowledge in better way.
Thanks in advanced.
Pavan DBA said
thanks for making the point to add the link. surely will do that from next time. BTW I never said that I prepared these scripts, I am just sharing it so that for some dba’s it would be handy.
thanks once again