OCP Blog
Super DBA
Script generate all user privileges in one report
by Vazha Mantua Monday, May 14, 2012 12:09 PM

Good Day All.

Our task is get all privileges granted for user and create report based on this information. As you know privileges gives on user directly or on his role. Privileges divided by 2 part: system and object.

Let see example, create report for user Scott

1.Create repository table for store data:

create table vm_user_privs(privilege varchar2(100),user_name varchar2(100),object_name varchar2(100));

2. Main script

declare
cursor c_user is
select a.username from dba_users a
where a.username='SCOTT';
p_user varchar2(100);

begin
open c_user;
loop
fetch c_user into p_user;
insert into vm_user_privs
select /*+ rule */ a.privilege,p_user,a.table_name from dba_tab_privs a where a.grantee=p_user
union all
select /*+rule */ b.privilege,p_user,b.table_name from dba_tab_privs b where b.grantee in (select b1.granted_role from dba_role_privs b1 where b1.grantee=p_user)
union all
select /*+ rule */ c.privilege,p_user,null from dba_sys_privs c where c.grantee=p_user
union all
select /*+rule */ d.privilege,p_user,null from dba_sys_privs d where d.grantee in (select b2.granted_role from dba_role_privs b2 where b2.grantee=p_user);
commit;
EXIT WHEN c_user%NOTFOUND;
end loop;
close c_user;
end;

3. See result:

select * from vm_user_privs

Filter by APML

Calendar

<<  October 2017  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

TextBox