Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

declare

v_role varchar2(30):= ‘VIEW_ALL_SCOTT’;
v_ct number;

begin

— Create original role definition

dbms_output.put_line(‘create role ‘||v_role||’ identified by
‘||v_role||’;’);

— Grant all roles defined for the role.

for role in(select * from dba_role_privs where grantee=v_role)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

— Grant all system privileges for the role.

for sys_priv in(select * from dba_sys_privs where grantee=v_role)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

— Grant all object privileges for the role.

for tab_priv in(select * from dba_tab_privs where grantee=v_role)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

SQL> spool ./create_role.sql
SQL>
SQL> declare
2
3 v_role varchar2(30):= ‘VIEW_ALL_SCOTT’;
4 v_ct number;
5
6 begin
7
8 — Create original role definition
9
10 dbms_output.put_line(‘create role ‘||v_role||’ identified by
11 ‘||v_role||’;’);
12
13
14
15 — Grant all roles defined for the role.
16
17 for role in(select * from dba_role_privs where grantee=v_role)
18 loop
19 if role.admin_option = ‘YES’
20 then
21 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ w
ith
22 admin option’||’;’);
23 else
24 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’
);
25 end if;
26 end loop;
27
28
29 — Grant all system privileges for the role.
30
31 for sys_priv in(select * from dba_sys_privs where grantee=v_role)
32 loop
33 if sys_priv.admin_option = ‘YES’
34 then
35 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’
36 with admin option’||’;’);
37 else
38 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
39 ‘||sys_priv.grantee||’;’);
40 end if;
41 end loop;
42
43 — Grant all object privileges for the role.
44
45 for tab_priv in(select * from dba_tab_privs where grantee=v_role)
46 loop
47 if tab_priv.grantable = ‘YES’
48 then
49 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
50 ‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ w
ith
51 grant option;’);
52 else
53 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
54 ‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’)
;
55 end if;
56 end loop;
57 end;
58 /
create role VIEW_ALL_SCOTT identified by VIEW_ALL_SCOTT;
grant CONNECT to VIEW_ALL_SCOTT;
grant CREATE SESSION to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.DEPT to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.EMP to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.BONUS to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.SALGRADE to VIEW_ALL_SCOTT;

PL/SQL procedure successfully completed.