-- This script will report on user role grants and also
-- report privileges granted to users and roles.
-- Mark Reineck 1-14-1998

SET ECHO OFF
SET PAGES 55
SET LINES 120
COL GRANTEE HEADING User_or_Role FORMAT A20
COL GRANTED_ROLE HEADING Role FORMAT A20
COL DEFAULT_ROLE HEADING Default FORMAT A7
COL ADMIN_OPTION HEADING Admin FORMAT A5
COL OBJECT HEADING SCHEMA.TABLE.COLUMN FORMAT A50
COL PRIVILEGE FORMAT A30

ttitle 'List system privileges granted to each user or role'

select grantee, privilege
from sys.dba_sys_privs where privilege<>'CREATE SESSION';
order by grantee;

ttitle 'List users or roles who may grant system privileges'

select grantee, privilege
from sys.dba_sys_privs
where admin_option='YES'
order by grantee;

ttitle 'System privilege grants to users directly or by role'

select grantee, to_char(null) granted_role, privilege
from sys.dba_sys_privs, dba_users
where grantee=username and grantee not in ('SYS','SYSTEM')
union all
select r.grantee, granted_role, privilege
from sys.dba_sys_privs p, sys.dba_role_privs r
where p.grantee=r.granted_role and r.grantee not in ('SYS','SYSTEM')
order by grantee, privilege;