Oracle uses Roles to define the privileges and rights a user has in an Oracle database. By defining a Role with specific system and object privileges, we greatly reduce the complexity of creating a new user account within any system. This is accomplished by assigning all specific rights to a single Role then granting that Role to a new user, instead of granting all the rights individually to each new account. In this article we will review a script which will regenerate the DDL to recreate a specific role or just view the privileges which that role contains.
1. Connect to your Oracle database server and logon to SQL*PLUS.
MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 05 17:42:43 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create the role VIEW_ALL_SCOTT to demonstrate the generation of DDL.
Create role VIEW_ALL_SCOTT identified by password;
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;
3. Execute the following PL/SQL block to regenerate the DDL of the role VIEW_ALL_SCOTT.
set serveroutput on
spool ./create_role.sql
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.
SQL>
4. Now you can use the DDL generated to recreate the role in another database or examine the DDL to find the specific rights a user will receive once they are granted the role.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com