Pavan DBA's Blog

The DBA Knowledge Store

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;

 

Advertisements

2 Responses to “script to list all privileges for user”

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: