-- 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 A20

ttitle 'Roles assigned to each user'

select grantee, granted_role, default_role, admin_option
from sys.dba_role_privs
where grantee not in ('SYS','SYSTEM')
order by grantee, granted_role; ttitle 'Users assigned to each role' select grantee, granted_role, default_role, admin_option
from sys.dba_role_privs
where grantee not in ('SYS','SYSTEM')
order by granted_role, grantee; ttitle 'Table grants to roles or users' select grantee, owner||'.'||table_name||'.'||column_name object, privilege
from sys.dba_col_privs
union all
select grantee, owner||'.'||table_name object, privilege
from sys.dba_tab_privs
order by grantee, object;

ttitle 'Table grants to users directly or by role'

select grantee, to_char(null) granted_role, owner||'.'||table_name||'.'||column_name object, privilege
from sys.dba_col_privs, dba_users
where grantee=username and grantee not in ('SYS','SYSTEM')
union all
select grantee, to_char(null) granted_role, owner||'.'||table_name object, privilege
from sys.dba_tab_privs, dba_users
where grantee=username and grantee not in ('SYS','SYSTEM')
union all
select r.grantee, granted_role, owner||'.'||table_name||'.'||column_name object, privilege
from sys.dba_col_privs p, sys.dba_role_privs r
where p.grantee=r.granted_role and r.grantee not in ('SYS','SYSTEM')
union all
select r.grantee, granted_role, owner||'.'||table_name object, privilege
from sys.dba_tab_privs p, sys.dba_role_privs r
where p.grantee=r.granted_role and r.grantee not in ('SYS','SYSTEM')
order by grantee, object;

ttitle 'Table grants to public'

select owner||'.'||table_name||'.'||column_name object, privilege
from sys.dba_col_privs where grantee='PUBLIC'
union all
select owner||'.'||table_name object, privilege
from sys.dba_tab_privs where grantee='PUBLIC'
order by object;