SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 1000
SET LINES 132

REM
REM
REM || Title : show_users_privs.sql
REM ||
REM || Purpose : Script which display Privs for All Users.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM

COLUMN uname FORMAT A25 HEADING 'Username'
COLUMN rname FORMAT A25 HEADING 'Rolename'
COLUMN priv FORMAT A30 HEADING 'Privilege'

SELECT DECODE(sa1.grantee#, 1, 'public', u1.name) uname,
SUBSTR(u2.name,1,20) rname,
SUBSTR(spm.name,1,27) priv
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

SPOOL show_user_privs.lst
/
SPOOL OFF

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 60

CLEAR BREAKS
CLEAR COL