Script for Viewing Privileges
========================================

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
/