Database administrator workshop: Revoking RESOURCE role on 11gR2 resets all QUOTA previously granted

Thursday, January 3, 2013

Revoking RESOURCE role on 11gR2 resets all QUOTA previously granted

This post is related to a different behaviour on revoking RESOURCE role between Oracle version 11gR2 and 10gR2. It could happen that revoking the RESOURCE role from a user on 11gR2 generates several errors from user's prospective, especially when he tries to perform his usual DML operations: this situation doesn't happen instead on 10gR2.

Let's go through an example and connect to an Oracle 11gR2 instance.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I'm going to create MARCOV user and granting him two privileges (CREATE SESSION and CREATE TABLE):

SQL> CREATE USER MARCOV
  2    IDENTIFIED BY "MARCOV"
  3    DEFAULT TABLESPACE USERS
  4    TEMPORARY TABLESPACE TEMP
  5    PROFILE DEFAULT
  6    ACCOUNT UNLOCK;

User created.

SQL>   GRANT CREATE SESSION, CREATE TABLE TO MARCOV;

Grant succeeded.

The following query shows privileges granted to the user:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION                           NO  MARCOV
CREATE TABLE     NO  MARCOV

I'm going to give some quota to my user on tablespace "USERS":

SQL> ALTER USER MARCOV QUOTA 50M ON "USERS";

User altered.

I can query dba_ts_quotas to show if my user has quota on some tablespaces:

SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME                       TABLESPACE_NAME                  MAXMBYTE
------------------------------ ------------------------------ ----------
     BYTES PERCENT_USED
---------- ------------
MARCOV                         USERS                                  50
         0            0

Connecting using MARCOV user, I'm able to create T1 table

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T1 (
  2    COL1  NUMBER,
  3    COL2  VARCHAR2(1 BYTE)
  4  ) segment creation immediate TABLESPACE USERS;

Table created.

As sysdba I grant now RESOURCE role to MARCOV user

SQL> connect / as sysdba
Connected.
SQL> GRANT "RESOURCE" TO MARCOV;

Grant succeeded.

RESOURCE role is now granted to MARCOV...

SQL>  Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

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

... and that role is formed by the following privileges...

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'RESOURCE';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
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  RESOURCE

8 rows selected.

Granting RESOURCE role to a user means automatically to add also UNLIMITED TABLESPACE privilege.

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE                     NO  MARCOV
CREATE SESSION                           NO  MARCOV
CREATE TABLE     NO  MARCOV

After the creation of just one table (using "segment creation immediate" to force Oracle to allocate an extent without waiting to insert the first record) this is the situation of MARCOV's quota:

SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         65536  .13

Let's see what it happens when I revoke the RESOURCE role:

SQL> REVOKE "RESOURCE" FROM MARCOV;

Revoke succeeded.

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T2 (
  2    COL1  NUMBER,
  3    COL2  VARCHAR2(1 BYTE)
  4  )
  5  TABLESPACE USERS;

Table created.

When Oracle try to allocate an extent for T2 (I didn't use the "segment creation immediate" option) I receive the "ORA-01536: space quota exceeded for tablespace 'USERS'" error. Why ?

SQL> insert into T2 values (1, 'A');
insert into T2 values (1, 'A')
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Same behaviour if I try to create a T3 table allocating just one extent. Again... why ?

SQL> create table t3 (a number) segment creation immediate tablespace users;
create table t3 (a number) segment creation immediate tablespace users
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

As you can see the RESOURCE role is revoked as well as the UNLIMITED TABLESPACE (as expected).

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee  FROM dba_sys_privs dsp
  2  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION                           NO  MARCOV
CREATE TABLE                             NO  MARCOV

SQL> Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

no rows selected

Here is the answer. When you revoke RESOURCE role, all quotas assigned to the user are reset!!!

SQL> connect / as sysdba
Connected.
SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

no rows selected

This behaviour is different compared with Oracle Database Server 10gR2. Let's see it with an example.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Creation of MARCOV user with only two privileges.

SQL> CREATE USER MARCOV
  2  IDENTIFIED BY "MARCOV"
  3  DEFAULT TABLESPACE USERS
  4  TEMPORARY TABLESPACE TEMP01
  5  PROFILE DEFAULT
  6  ACCOUNT UNLOCK;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO MARCOV;

Grant succeeded.

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2  FROM dba_sys_privs dsp
  3  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

Assigning some quota on USERS tablespace to MARCOV.

SQL> ALTER USER MARCOV QUOTA 50M ON "USERS";

User altered.

Quota is assigned, but still any byte is used.

SQL> SELECT USERNAME,
  2  tablespace_name, 
  3  MAX_BYTES/1024/1024 MAXMBYTE,
  4  BYTES, 
  5  DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6  from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         0      0

MARCOV user is able to connect and create T1 table on USERS tablespace:

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T1 (
  2  COL1  NUMBER,
  3  COL2  VARCHAR2(1 BYTE)
  4  ) TABLESPACE USERS;

Table created.

Let's see the privileges currently assigned to MARCOV user:

SQL> connect / as sysdba
Connected.
SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee FROM dba_sys_privs dsp
  2  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

Grant RESOURCE role to the user:

SQL> GRANT "RESOURCE" TO MARCOV;

Grant succeeded.

You can verify RESOURCE role is granted with the following query:

SQL> Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

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

RESOURCE role is always formed by 8 privileges:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2  FROM dba_sys_privs dsp
  3  WHERE dsp.grantee = 'RESOURCE';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
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  RESOURCE

8 rows selected.

Also in this case UNLIMITED TABLESPACE is generously granted to MARCOV user:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee FROM dba_sys_privs dsp  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE    NO  MARCOV
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

MARCOV user is consuming some space after the creation of T1 table.

SQL> SELECT USERNAME, tablespace_name, MAX_BYTES/1024/1024 MAXMBYTE,
  2  BYTES, DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  3  from   sys.dba_ts_quotas WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         65536  .13

What does it happen on 10gR2 if I revoke RESOURCE role ?

SQL> REVOKE "RESOURCE" FROM MARCOV;

Revoke succeeded.

The user is always able to create its table...

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T2 ( COL1  NUMBER,COL2  VARCHAR2(1 BYTE)) TABLESPACE USERS;

Table created.

... and quota previously assigned are not revoked.

SQL> connect / as sysdba
Connected.
SQL> SELECT USERNAME, tablespace_name, MAX_BYTES/1024/1024 MAXMBYTE, BYTES, DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used   from   sys.dba_ts_quotas WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         131072 .25

So this is a big difference: in Oracle 11gR2 revoking RESOURCE role means Oracle will revoke all quotas previously assigned to the user and this doesn't happen in 10gR2.
So under Oracle 11gR2 you have to remember to give the quotas back after the revoke of the RESOURCE role.

That's all.