Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role RESOURCE.
The RESOURCE role is used to give an individual the right to create any object within his/her own schema. This role is normally used to define a developer’s privilege within a test and development system. It allows a developer to build objects necessary to create a system without impacting other developers on the same system. The following instructions define how to create a user account, assign the RESOURCE role to that user and lists the privileges granted by RESOURCE role.
1. Logon to you Oracle database as sysdba.
mylinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Wed May 20 17:16:49 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 a permanent tablespace for your new user.
SQL> create tablespace devl datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/d
evl01.dbf’ size 5000m;
Tablespace created.
SQL>
3. Create a temporary tablespace for you new user.
SQL> create temporary tablespace devl_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/OR
ADATA/ORCL10G/devl_temp01.dbf’ size 2500m;
Tablespace created.
SQL>
4. Create you development user.
SQL> Create user developer01 identified by developer
2 Default tablespace devl
3 Quota unlimited on devl
4 Temporary tablespace devl_temp;
User created.
SQL>
5. You have to grant create session directly to the user developer01. NOTE: “create session†is not part of the RESOURCE role.
SQL> grant create session to developer01;
Grant succeeded.
SQL>
6. Grant the role RESOURCE to the user developer01.
SQL> grant resource to developer01;
Grant succeeded.
SQL>
7. Now you can logon and test the new user account by creating an object.
SQL> connect developer01/developer
Connected.
SQL> create table test1(testing varchar2(30));
Table created.
SQL> select tablespace_name from user_tables where table_name=’TEST1′;
TABLESPACE_NAME
——————————
DEVL
SQL>
8. The following PL/SQL block gives you all of the privileges granted to a specific role where the variable V_USER defines the role.
set serveroutput on
declare
v_ct number;
v_user varchar2(30):=’RESOURCE’;
begin
for role in(select * from dba_role_privs where grantee=v_user)
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;
for sys_priv in(select * from dba_sys_privs where grantee=v_user)
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;
for tab_priv in(select * from dba_tab_privs where grantee=v_user)
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> set serveroutput on
declare
v_ct number;
v_user varchar2(30):=’RESOURCE’;
begin
for role in(select * from dba_role_privs where grantee=v_user)
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;
for sys_priv in(select * from dba_sys_privs where grantee=v_user)
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;
for tab_priv in(select * from dba_tab_privs where grantee=v_user)
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> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
31 32 33 34 35 36 37 38 39 40 41 42 grant CREATE TYPE to
RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;
grant CREATE TYPE to RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;
PL/SQL procedure successfully completed.
SQL>
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com