roles - Roles, Privileges, assigned users
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
SQL> @role10
Enter value for amp1: CONNECT
********** ROLE INFORMATION ********** Nov 27, 2011 07:24 AM
*--------------------------------------------------------------------------*
Role Name : CONNECT
.
********** CONNECT - NO OTHER ROLES GRANTED *********
.
********** CONNECT - SYSTEM PRIVILEGES GRANTED *********
. System Privilege Admin
. *---------------------------------------------------------------------*
. CREATE SESSION NO
.
.
********** CONNECT - NO OBJECT PRIVILEGES GRANTED *********
.
********** CONNECT - NO COLUMN PRIVILEGES GRANTED *********
.
********** CONNECT - USERS/ROLES ASSIGNED *********
. User / Role Name Admin Default
. *---------------------------------------------------------------------*
. ABC NO YES
. ANUJ NO YES
. ANUJREP NO YES
. ANUJTEST NO YES
. APEX_030200 YES YES
. APEX_040000 NO YES
. GGATE NO YES
. IX NO YES
. MDDATA NO YES
. MDSYS NO YES
. OWBSYS YES YES
. PM NO YES
. SCOTT NO YES
. SPATIAL_CSW_ADMIN_USR NO YES
. SPATIAL_WFS_ADMIN_USR NO YES
. SYS YES YES
. TEST_USER YES YES
. VIHAAN NO YES
. WMSYS NO YES
. XYZ NO YES
.
*--------------------------------------------------------------------------*
Output saved at /tmp/roleinfo.lst
SQL> @role10
Enter value for amp1: RESOURCE
********** ROLE INFORMATION ********** Nov 27, 2011 07:25 AM
*--------------------------------------------------------------------------*
Role Name : RESOURCE
.
********** RESOURCE - NO OTHER ROLES GRANTED *********
.
********** RESOURCE - SYSTEM PRIVILEGES GRANTED *********
. System Privilege Admin
. *---------------------------------------------------------------------*
. CREATE CLUSTER NO
. CREATE INDEXTYPE NO
. CREATE OPERATOR NO
. CREATE PROCEDURE NO
. CREATE SEQUENCE NO
. CREATE TABLE NO
. CREATE TRIGGER NO
. CREATE TYPE NO
.
.
********** RESOURCE - NO OBJECT PRIVILEGES GRANTED *********
.
********** RESOURCE - NO COLUMN PRIVILEGES GRANTED *********
.
********** RESOURCE - USERS/ROLES ASSIGNED *********
. User / Role Name Admin Default
. *---------------------------------------------------------------------*
. ABC NO YES
. ANUJ NO YES
. ANUJREP NO YES
. ANUJTEST NO YES
. APEX_030200 YES YES
. APEX_040000 YES YES
. BI NO YES
. CTXSYS NO YES
. EXFSYS NO YES
. GGATE NO YES
. HR NO YES
. IX NO YES
. LOGSTDBY_ADMINISTRATOR NO YES
. MDDATA NO YES
. MDSYS NO YES
. OE NO YES
. OLAPSYS NO YES
. OUTLN NO YES
. PM NO YES
. SCOTT NO YES
. SH NO YES
. SPATIAL_CSW_ADMIN_USR NO YES
. SPATIAL_WFS_ADMIN_USR NO YES
. SYS YES YES
. TEST_USER YES YES
. VIHAAN NO YES
. WMSYS NO YES
. XDB NO YES
. XYZ NO YES
.
*--------------------------------------------------------------------------*
Output saved at /tmp/roleinfo.lst
No comments:
Post a Comment