rem
rem Information about roles - Roles, Privileges, assigned users, etc.
rem
rem Biju Thomas
rem
rem Provide the role name along with the scriptname
rem
set serveroutput on feedback off verify off pages 0
spool /tmp/roleinfo.lst
declare
     wrole varchar2 (30) := '&1';
     /*  Users */
     cursor crole is select role
     from dba_roles where
     role like upper(wrole);
     /* Roles granted */
     cursor crg (r in varchar2) is
     select granted_role, admin_option, default_role
     from dba_role_privs where
     grantee = upper(r)
     order by granted_role;
     /* System privileges granted */
     cursor csg (r in varchar2) is
     select privilege, admin_option
     from dba_sys_privs where
     grantee = upper(r)
     order by privilege;
     /* Object privileges granted */
     cursor cog (r in varchar2) is
     select (owner ||'.'|| table_name) object, privilege
     from dba_tab_privs where
     grantee = upper(r)
     order by owner, table_name;
     /* Column privileges granted */
     cursor ccg (r in varchar2) is
     select (owner ||'.'|| table_name ||'.'|| column_name) wcolumn, privilege
     from dba_col_privs where
     grantee = upper(r)
     order by  owner, table_name, column_name;
     /* Users / roles granted this role */
     cursor cug (r in varchar2) is 
     select grantee, admin_option, default_role
     from dba_role_privs where
     granted_role = upper(r)
     order by grantee;
     wcount number := 0;
     wdate varchar2 (25) := to_char(sysdate,'Mon DD, YYYY  HH:MI AM');
     w5space char(5) := '.    ';
     wdum1 varchar2 (255);
     wdum2 varchar2 (255);
     wdum3 varchar2 (255);
     wdum4 varchar2 (255);
     wdum5 varchar2 (255);
     wdum6 varchar2 (255);
     wdum7 varchar2 (255);
  begin
    dbms_output.enable(100000);
    for rrole in crole loop
      dbms_output.put_line('********** ROLE INFORMATION **********                ' || wdate);
      dbms_output.put_line('*--------------------------------------------------------------------------*');
      wcount := wcount + 1;
      dbms_output.put_line('Role Name : ' || rrole.role);
      dbms_output.put_line(w5space);
      open crg (rrole.role);
      fetch crg into wdum1, wdum2, wdum3;
      if crg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO OTHER ROLES GRANTED  *********');
         close crg;
      else
         close crg;
         dbms_output.put_line('********** ' || rrole.role || ' - OTHER ROLES GRANTED  *********');
         dbms_output.put_line(w5space || 'Role name                                         Admin     Default');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rrg  in crg (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rrg.granted_role,50) || rpad(rrg.admin_option,10) || rpad(rrg.default_role,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open csg (rrole.role);
      fetch csg into wdum1, wdum2;
      if csg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO SYSTEM PRIVILEGES GRANTED  *********');
         close csg;
      else
         close csg;
         dbms_output.put_line('********** ' || rrole.role || ' - SYSTEM PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'System Privilege                                  Admin');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rsg  in csg (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rsg.privilege,50) || rpad(rsg.admin_option,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open cog (rrole.role);
      fetch cog into wdum1, wdum2;
      if cog%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO OBJECT PRIVILEGES GRANTED  *********');
         close cog;
      else
         close cog;
         dbms_output.put_line('********** ' || rrole.role || ' - OBJECT PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'Object Name                             Privilege');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rog  in cog (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rog.object,40) || rpad(rog.privilege,30));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open ccg (rrole.role);
      fetch ccg into wdum1, wdum2;
      if ccg%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO COLUMN PRIVILEGES GRANTED  *********');
         close ccg;
      else
         close ccg;
         dbms_output.put_line('********** ' || rrole.role || ' - COLUMN PRIVILEGES GRANTED  *********');

         dbms_output.put_line(w5space || 'Column Name                                       Privilege');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rcg  in ccg (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rcg.wcolumn,50) || rpad(rcg.privilege,20));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line(w5space);
      open cug (rrole.role);
      fetch cug into wdum1, wdum2, wdum3;
      if cug%notfound then
         dbms_output.put_line('********** ' || rrole.role || ' - NO USERS/ROLES ASSIGNED  *********');
         close cug;
      else
         close cug;
         dbms_output.put_line('********** ' || rrole.role || ' - USERS/ROLES ASSIGNED  *********');
         dbms_output.put_line(w5space || 'User / Role Name                                  Admin     Default');
         dbms_output.put_line(w5space || '*---------------------------------------------------------------------*');
         for rug  in cug (rrole.role) loop
          dbms_output.put_line(w5space || rpad(rug.grantee,50) || rpad(rug.admin_option,10) || rpad(rug.default_role,10));
         end loop;
         dbms_output.put_line(w5space);
      end if;
      dbms_output.put_line('*--------------------------------------------------------------------------*');
    end loop;
    if wcount =0 then
      dbms_output.put_line('******************************************************');
      dbms_output.put_line('*                                                    *');
      dbms_output.put_line('* Plese Verify Input Parameters... No Matches Found! *');
      dbms_output.put_line('*                                                    *');
      dbms_output.put_line('******************************************************');
    end if;
  end;
/
set serveroutput off feedback on verify on pages 999
spool off
prompt
prompt Output saved at /tmp/roleinfo.lst