Learn about the ORACLE resource role

What permissions does the ORACLE resource role have? The following describes how to view the permissions of the ORACLE resource role.

 
 

  
  
SQL> select * from v$version where rownum<=1;  
  
  
 
  
  
BANNER  
  
  
--------------------------------------------------------------------------------  
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi  
 
 

View the permissions of the ORACLE resource role from role_sys_privs

 
 

  
  
SQL> select PRIVILEGE from role_sys_privs where role='RESOURCE';  
  
  
 
  
  
PRIVILEGE  
  
  
--------------------------------------------------------------------------------  
  
  
CREATE CLUSTER  
  
  
CREATE SEQUENCE  
  
  
CREATE TRIGGER  
  
  
CREATE TABLE  
  
  
CREATE PROCEDURE  
  
  
CREATE TYPE  
  
  
CREATE OPERATOR  
  
  
CREATE INDEXTYPE  
  
  
 
  
  
8 rows selected.  
 
 

When you grant the ORACLE resource role a user, it not only grants the permissions of the ORACLE resource role itself, but also has the unlimited tablespace permission.

 
 

  
  
SQL> conn desk  
  
  
Enter password:  
  
  
Connected.  
  
  
SQL> select PRIVILEGE from user_sys_privs;  
  
  
no rows selected  
  
  
SQL> conn / as sysdba  
  
  
Connected.  
  
  
SQL> grant resource to desk;  
  
  
Grant succeeded.  
  
  
SQL> conn desk  
  
  
Enter password:  
  
  
Connected.  
  
  
SQL> select PRIVILEGE from user_sys_privs;  
  
  
PRIVILEGE  
  
  
----------------------------------------  
  
  
UNLIMITED TABLESPACE  
  
  
SQL> 
  
  
 
 
 

When a resource is granted a role, the unlimited tablespace permission is not granted.

 
 

  
  
SQL> show user  
  
  
USER is "SYS"  
  
  
SQL> create role testrole identified using testrole;  
  
  
Role created.  
  
  
SQL> revoke resource from desk;  
  
  
Revoke succeeded.  
  
  
SQL> grant resource to testrole;  
  
  
Grant succeeded.  
  
  
SQL> grant testrole to desk;  
  
  
Grant succeeded.  
  
  
SQL> conn desk  
  
  
Enter password:  
  
  
Connected.  
  
  
SQL> select privilege from user_sys_privs;  
  
  
no rows selected  
  
  
SQL> 
 
 

Oracle data export and import Permissions

Oracle read-only user role creation

View oracle user permissions

Introduction to oracle object permissions

How to store images in oracle
 

Oracle data export and import Permissions

Oracle read-only user role creation

View oracle user permissions