Subject:            SQL: SCRIPT FOR VIEWING PRIVILEGES
Author:            DHAYTER
Modified:           09 Jan 96 12:46:21
-------------------------------------------------------------------------------


REM **********************************************************************
REM 
REM   View  DBA_USER_PRIVS 
REM 
REM   To show what privileges a user has been granted
REM   either directly OR via roles.
REM 
REM   Can also be used to query which users have a given privilege
REM 
REM   
REM   Example 1
REM   ~~~~~~~~~
REM   To view all privileges enjoyed by user SCOTT..
REM   
REM   
REM   SQL> SELECT * FROM DBA_USER_PRIVS WHERE USERNAME='SCOTT';
REM   
REM   USERNAME                 ROLENAME             PRIVILEGE                   
REM   ------------------------ -------------------- --------------------------- 
REM   SCOTT                    CONNECT              ALTER SESSION               
REM   SCOTT                    CONNECT              CREATE CLUSTER              
REM   SCOTT                    CONNECT              CREATE DATABASE LINK        
REM   SCOTT                    CONNECT              CREATE SEQUENCE             
REM   SCOTT                    CONNECT              CREATE SESSION              
REM   SCOTT                    CONNECT              CREATE SYNONYM              
REM   SCOTT                    CONNECT              CREATE TABLE                
REM   SCOTT                    CONNECT              CREATE VIEW                 
REM   SCOTT                    RESOURCE             CREATE CLUSTER              
REM   SCOTT                    RESOURCE             CREATE PROCEDURE            
REM   SCOTT                    RESOURCE             CREATE SEQUENCE             
REM   SCOTT                    RESOURCE             CREATE TABLE                
REM   SCOTT                    RESOURCE             CREATE TRIGGER              
REM   SCOTT                                         SELECT ANY TABLE            
REM   SCOTT                                         UNLIMITED TABLESPACE        
REM   
REM   15 rows selected.
REM   
REM   
REM   
REM   Example 2
REM   ~~~~~~~~~
REM   To view all users who have CREATE ANY TABLE privilege...
REM   
REM   
REM   SQL> SELECT * FROM DBA_USER_PRIVS WHERE PRIVILEGE = 'CREATE ANY TABLE';
REM   
REM   USERNAME                 ROLENAME             PRIVILEGE                   
REM   ------------------------ -------------------- --------------------------- 
REM   DBA                      IMP_FULL_DATABASE    CREATE ANY TABLE            
REM   DBA                                           CREATE ANY TABLE            
REM   IMP_FULL_DATABASE                             CREATE ANY TABLE            
REM   OPS$SWELSH               DBA                  CREATE ANY TABLE            
REM   RBERRY                   DBA                  CREATE ANY TABLE            
REM   SWELSH                   DBA                  CREATE ANY TABLE            
REM   SYS                      DBA                  CREATE ANY TABLE            
REM   SYS                      IMP_FULL_DATABASE    CREATE ANY TABLE            
REM   SYSCASE                  DBA                  CREATE ANY TABLE            
REM   SYSTEM                   DBA                  CREATE ANY TABLE            
REM   
REM   10 rows selected.
REM   
REM **********************************************************************
REM
REM
REM  The view DBA_USER_PRIVS should be created in the SYS account...
REM

CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS
SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20), 
   SUBSTR(SPM.NAME,1,27) 
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1, 
     SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
  AND SA1.PRIVILEGE# = U2.USER#
  AND U2.USER# = SA2.GRANTEE#
  AND SA2.PRIVILEGE# = SPM.PRIVILEGE
UNION
SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
WHERE SA.GRANTEE#=U.USER# 
  AND SA.PRIVILEGE#=SPM.PRIVILEGE
/