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 - ProductionI'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 MARCOVI'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 0Connecting 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 MARCOVAfter 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 .13Let'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 selectedHere 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 selectedThis 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 - ProductionCreation 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 MARCOVAssigning 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 0MARCOV 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 MARCOVGrant 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 MARCOVRESOURCE 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 MARCOVMARCOV 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 .13What 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 .25So 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.