Oracle Roles and Users audit report

I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing.

-- Package Name : users_granted_role
-- This package was created to spool user and their respective Privs from oracle data Dictionary.
CREATE OR REPLACE PACKAGE users_granted_role
IS
 procedure write_op (pv_str in varchar2);
 function user_or_role(pv_grantee in dba_users.username%type) return varchar2;
 function role_pwd(pv_role in dba_roles.role%type)   return varchar2;
 procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number);
 procedure get_role (pv_role in varchar2);
 Procedure extract_user_role_details;
END users_granted_role;
/
create or replace package body users_granted_role
IS
 output_method varchar2(1) :='S';
 skip_user varchar2(1) := 'N';
 user_to_skip varchar2(20) :='TEST%';
 --
 lg_fptr utl_file.file_type;
 lv_file_or_screen varchar2(1):='S';
 v_tag      VARCHAR2(8);
 v_filename VARCHAR2(30);
 v_today    VARCHAR2(8);
 --
 cursor find_all_roles is
 Select    role
 from    dba_roles;
 --
procedure write_op (pv_str in varchar2) is
 begin
 v_today    := TO_CHAR(sysdate,'YYYYMMDD');
 v_tag      := 'UserPriv';
 v_filename := 'User_Privileges'|| v_today;

 if lv_file_or_screen='S' then
 dbms_output.put_line(v_tag || v_filename||' '||pv_str);
 else
 utl_file.put_line(lg_fptr,pv_str);
 end if;
 exception
 when utl_file.invalid_path  then
 dbms_output.put_line('invalid path');
 when utl_file.invalid_mode  then
 dbms_output.put_line('invalid mode');
 when utl_file.invalid_filehandle  then
 dbms_output.put_line('invalid filehandle');
 when utl_file.invalid_operation  then
 dbms_output.put_line('invalid operation');
 when utl_file.read_error  then
 dbms_output.put_line('read error');
 when utl_file.write_error  then
 dbms_output.put_line('write error');
 when utl_file.internal_error  then
 dbms_output.put_line('internal error');
 when others then
 dbms_output.put_line('ERROR (write_op) => '||sqlcode);
 dbms_output.put_line('MSG (write_op) => '||sqlerrm);

end write_op;
 --
function user_or_role(pv_grantee in dba_users.username%type) return varchar2 is
 --
 cursor c_use (cp_grantee in dba_users.username%type) is
 select  'USER' userrole
 from    dba_users u
 where   u.username=cp_grantee
 union
 select  'ROLE' userrole
 from    dba_roles r
 where   r.role=cp_grantee;
 --
 lv_use c_use%rowtype;
 --
 begin
 open c_use(pv_grantee);
 fetch c_use into lv_use;
 close c_use;
 return lv_use.userrole;
 exception
 when others then
 dbms_output.put_line('ERROR (user_or_role) => '||sqlcode);
 dbms_output.put_line('MSG (user_or_role) => '||sqlerrm);
end user_or_role;
 -----------------
function role_pwd(pv_role in dba_roles.role%type)   return varchar2 is
 --
 cursor c_role(cp_role in dba_roles.role%type) is
 select    r.password_required
 from    dba_roles r
 where    r.role=cp_role;
 --
 lv_role c_role%rowtype;
 --
 begin
 open c_role(pv_role);
 fetch c_role into lv_role;
 close c_role;
 return lv_role.password_required;
 exception       
 when others then null;
 --dbms_output.put_line('ERROR (role_pwd) => '||sqlcode);
 --dbms_output.put_line('MSG (role_pwd) => '||sqlerrm);
end role_pwd;
 --
procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is
 --
 lv_tab varchar2(50):='';
 lv_loop number;
 lv_user_or_role dba_users.username%type;
 --
 cursor c_user (cp_username in dba_role_privs.grantee%type) is
 select  d.grantee,
 d.admin_option
 from    dba_role_privs d
 where   d.granted_role=cp_username;
 --
 begin
 pv_tabstop:=pv_tabstop+1;
 for lv_loop in 1..pv_tabstop loop
 lv_tab:=lv_tab||chr(9);
 end loop;

 for lv_user in c_user(pv_grantee) loop
 lv_user_or_role:=user_or_role(lv_user.grantee);
 if lv_user_or_role = 'ROLE' then
 if lv_user.grantee = 'PUBLIC' then
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')');
 else
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')'
 ||' which is granted to =>');
 end if;
 get_users(lv_user.grantee,pv_tabstop);
 else
 if upper(skip_user) = 'Y' and lv_user.grantee like upper(user_to_skip) then
 null;
 else
 write_op(lv_tab||'User => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option||')');
 end if;
 end if;
 end loop;
 pv_tabstop:=pv_tabstop-1;
 lv_tab:='';
 exception
 when others then
 dbms_output.put_line('ERROR (get_users) => '||sqlcode);
 dbms_output.put_line('MSG (get_users) => '||sqlerrm);       
end get_users;
----
procedure get_role (pv_role in varchar2) is
 --
 cursor c_main (cp_role in varchar2) is
 select    p.grantee,
 p.admin_option
 from    dba_role_privs p
 where    p.granted_role=cp_role;
 --
 lv_userrole dba_users.username%type;
 lv_tabstop number;
 --
 --
begin
 lv_tabstop:=1;
 for lv_main in c_main(pv_role) loop   
 lv_userrole:=user_or_role(lv_main.grantee);
 if lv_userrole='USER' then
 if upper(skip_user) = 'Y' and lv_main.grantee like upper(user_to_skip) then
 null;
 else
 write_op(chr(9)||'User => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option||')');
 end if;
 else
 if lv_main.grantee='PUBLIC' then
 write_op(chr(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')');
 else
 write_op(chr(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')'
 ||' which is granted to =>');
 end if;
 get_users(lv_main.grantee,lv_tabstop);
 end if;
 end loop;
 exception
 when others then
 dbms_output.put_line('ERROR (get_role) => '||sqlcode);
 dbms_output.put_line('MSG (get_role) => '||sqlerrm);
end get_role;

Procedure extract_user_role_details is
begin
 write_op('Users_granted_role: Release 1.0 - Author : Sagar PATIL on '|| sysdate);
 for role_to_find in find_all_roles loop
 lv_file_or_screen:= upper(output_method);
 write_op(chr(10));
 write_op('Investigating Role => '||upper(role_to_find.role)||' (PWD = '
 ||role_pwd(upper(role_to_find.role))||') which is granted to =>');
 write_op('====================================================================');
 get_role(upper(role_to_find.role));
 end loop;
exception
 when others then
 dbms_output.put_line('ERROR (main) => '||sqlcode);
 dbms_output.put_line('MSG (main) => '||sqlerrm);
end extract_user_role_details;
end;
/

Run it as below

SQL> spool list_of_users.lst
SQL> set serveroutput on size 20000;
SQL> exec users_granted_role.extract_user_role_details;

UserPrivUser_Privileges20101026 Investigating Role => CONNECT (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => GRIDCONTROL (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)
UserPrivUser_Privileges20101026         User => OWBSYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026

UserPrivUser_Privileges20101026 Investigating Role => RESOURCE (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         Role => LOGSTDBY_ADMINISTRATOR (ADM = NO|PWD =NO) which is granted to =>
UserPrivUser_Privileges20101026                 User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => EXFSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => CTXSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => OLAPSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => XDB (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => OUTLN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)

PL/SQL procedure successfully completed.
SQL> spool off;