Oracle Roles - Unlimited Tablespace Privilege

Two most popular roles that come with Oracle are CONNECT and RESOURCE role.
Specified roles provide various system privileges.

Oracle 9i

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO

8 rows selected.

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO

8 rows selected.

Oracle 10g

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

1 row selected.

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.

Oracle 11g

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='CONNECT';

GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
CONNECT                   CREATE SESSION                           NO

1 row selected.

SQL> select grantee, privilege, admin_option from dba_sys_privs
where grantee='RESOURCE';

GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
RESOURCE                  CREATE TRIGGER                           NO
RESOURCE                  CREATE SEQUENCE                          NO
RESOURCE                  CREATE TYPE                              NO
RESOURCE                  CREATE PROCEDURE                         NO
RESOURCE                  CREATE CLUSTER                           NO
RESOURCE                  CREATE OPERATOR                          NO
RESOURCE                  CREATE INDEXTYPE                         NO
RESOURCE                  CREATE TABLE                             NO

8 rows selected.

This roles had interesting privileges back in version 9i of database, as they have now. It is very often that dba grants this roles to application, script or user and it is a wise to check privileges of given roles and revoke unneccessary ones.


Let's talk a little about RESOURCE ROLE.

I'll create test user, grant him resource role and show his privileges.

system@TEST11> create user testusr identified by testusr;

User created.

system@TEST11> grant resource to testusr;

Grant succeeded.

system@TEST11> select grantee, granted_role, admin_option, default_role
from dba_role_privs
2  where grantee = 'TESTUSR';

GRANTEE                   GRANTED_ROLE                   ADM DEF
------------------------- ------------------------------ --- ---
TESTUSR                   RESOURCE                       NO  YES

1 row selected.

system@TEST11> select grantee, privilege, admin_option from dba_sys_privs
where grantee='TESTUSR';

GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
TESTUSR                   UNLIMITED TABLESPACE                     NO

1 row selected.

If I query dba_sys_privs I'll get "UNLIMITED TABLESPACE" privilege granted to user. "UNLIMITED TABLESPACE" privilege was assigned to user with RESOURCE role. This is very dangerous privilege.
From documentation:

Advantage:

You can grant a user unlimited access to all tablespaces of a database with one statement.

Disadvantages:
*
The privilege overrides all explicit tablespace quotas for the user.
*
You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.

As you can see, when you grant RESOURCE role to user, that user/application gets "UNLIMITED TABLESPACE" privilege and automatically has rights to write in any tablespace that he chooses to - even in SYSTEM tablespce.

So after granting RESOURCE role you should immediately execute:

system@TEST11> revoke unlimited tablespace from testusr;

Revoke succeeded.

and grant quotas on tablespaces that you desire.

NOTE!
It is very dangerous to grant RESOURCE role without revoking UNLIMITED TABLESAPCE privilege before Import. With UNLIMITED TABLESPACE privilege given to user import data could be imported in any tablespace and you could load data to unwanted locations.



RELATED DOCUMENTS:
Administering user privileges, roles and profiles