-- #############################################################################################
 
 --
 
 -- %Purpose: Show Roles granted to Users and Roles
 
 --
 
 -- #############################################################################################
 
 --
 
spool show_roles.lst
 --
 
ttitle left  'Display Roles granted to Users and Roles' -
skip  2 
set feed off
set pagesize  10000 
break on grantee skip  1 
column datum new_value datum noprint
column grantee format A27 heading 'User or Role|receiving the Grant'
column granted_role format A30 heading 'Granted|Role'
column default_role format A10 heading 'Default|Role'
column admin_option format A10 heading 'Admin|Option'

select  to_char(sysdate, 'MM/DD/YY') datum,
  substr(grantee, 1 , 27 ) grantee,
  substr(granted_role, 1 , 30 ) granted_role,
  substr(default_role, 1 , 20 ) default_role,
  substr(admin_option, 1 , 10 ) admin_option
from dba_role_privs
where grantee not in ('SYS','SYSTEM')
order by  1 , 2 , 3 ;

 -- #############################################################################################
 
 --
 
 -- %Purpose: Show Object Privileges for Schema Owner which can be choosen
 
 --
 
 -- Use:      Needs Oracle DBA Access
 
 --
 
 -- #############################################################################################
 
 --
 
set feed off;
set pagesize  10000 ;
set wrap off;
set linesize  200 ;
set heading on;
set tab on;
set scan on;
set verify off;

spool show_grants.lst
ttitle off

ACCEPT user_namen CHAR PROMPT 'Schema Owner: '

column owner format a10 heading 'Object|Owner'
column grantor format a22 heading 'User who|performed the Grant'
column grantee format a24 heading 'User/Role to whom|Access was granted'
column privilege format a12 heading 'Object|Privilege'
column table_name noprint new_value tab

ttitle left 'Object Grants on: 'tab skip  2 

break on table_name skip page -
on owner skip  2  -
on grantor skip -
on grantee -
on privileges

select  substr(table_name, 1 , 20 ) table_name,
  substr(owner, 1 , 16 ) owner,
  substr(grantor, 1 , 24 ) grantor,
  substr(grantee, 1 , 24 ) grantee,
  substr(privilege, 1 , 12 ) privilege
from dba_tab_privs
where upper(owner) LIKE UPPER('&user_namen')
order by  1 , 2 , 3 , 4 , 5 ;
spool off;