Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘script to list all privileges for user’

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;

 

Posted in User management | Tagged: , , , | 2 Comments »

 
%d bloggers like this: