-- #############################################################################################
--
-- %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;