Cheeky Cockatoo

rem role.sql <role_name> - Shows all privs with the role and all users w role.
set pause off;
set echo off;
set termout on;
set linesize 80;
set pagesize 999;

column c1  heading "Role";
column c2  format a40 heading "Table Name";
column c3  heading "Priv.";
break on c1 skip 0 on c2 skip 0 on c3 skip 0

ttitle "Role &&1 in Database dbname";
select  substr(granted_role,1,29) c1
from sys.role_role_privs
where  role = (upper('&&1'))
;
 
select  substr(role,1,30) c1,
        ltrim(rtrim(owner))||'.'||substr(table_name,1,29) c2,
        substr(privilege,1,6) c3
from sys.role_tab_privs
where  role = (upper('&&1'))
order by 1,2,3;
  
column c1  heading "Role";
column c2  heading "Grantee";
ttitle "Users with &&1 role";
select  substr(granted_role,1,10) c1,
substr(grantee,1,20) c2
from dba_role_privs
where   granted_role = (upper('&&1'))
order by 1,2;