No artigo UNLIMITED TABLESPACE vs. RESOURCE, eu procurei não só demonstrar as diferenças e as conseqüências entre usar o privilégio de sistema e a role, como também algumas boas práticas no que se refere às questões de segurança. Ficou claro que apesar do privilégio de sistema UNLIMITED TABLESPACE não fazer parte da role RESOURCE, o Oracle automaticamente e de forma implícita, concede este privilégio de sistema a todo usuário que tiver a concessão da role RESOURCE diretamente, ou seja, este privilégio será concedido ao usuário sempre que a role RESOURCE também for concedida. Eu também demonstrei que, uma vez que o privilégio de sistema UNLIMITED TABLESPACE seja concedido a um usuário, todas as cotas de espaço de tablespace porventura concedidas a este usuário serão explicitamente desprezadas. Bom, mas se revogarmos o privilégio de sistema UNLIMITED TABLESPACE, o que acontece com a cotas que foram definidas anteriormente?
Após uma leitura na documentação oficial do Oracle 11g R2, percebi que houve uma alteração expressiva nesse comportamento em relação às versões/releases anteriores (11g R1, 10g, 9i, etc...).
10g
---
Granting Users the UNLIMITED TABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.
11g R1
------
Granting Users the UNLIMITED TABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.
11g R2
------
Granting Users the UNLIMITED TABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces. You can grant this privilege only to users, not to roles.
Perceberam a diferença? No Oracle 11g R2, após revogarmos o privilégio de sistema UNLIMITED TABLESPACE, as cotas que foram previamente concedidas serão perdidas e terão que ser novamente concedidas explicitamente. Na release anterior (11g R1), e versões anteriores (10g, etc...) as cotas concedidas entrarão em vigor novamente.
Essa diferença de comportamento pode gerar algumas dores de cabeça se não ficarmos atentos quando estamos administrando vários bancos de dados de diferentes versões e releases. Portanto, seja atencioso.
No mais, neste artigo irei demonstrar essa diferença de comportamento utilizando o Oracle 10g R2 e o Oracle 11g R2 conforme simulação abaixo:
SQL*Plus: Release 10.2.0.1.0 - Production on Seg Abr 2 19:02:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user scott identified by tiger default tablespace users;
Usuário criado.
SQL> grant create session,create table to scott;
Concessão bem-sucedida.
SQL> alter user scott quota unlimited on TBS_01;
Usuário alterado.
SQL> alter user scott quota unlimited on TBS_02;
Usuário alterado.
SQL> alter user scott quota unlimited on TBS_03;
Usuário alterado.
SQL> select * from dba_ts_quotas where username='SCOTT';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ---------- ---------- ---------- ---------- ---------- ---
TBS_01 SCOTT 0 -1 0 -1 NO
TBS_02 SCOTT 0 -1 0 -1 NO
TBS_03 SCOTT 0 -1 0 -1 NO
Acima, após criar o usuário SCOTT, foi concedido ao mesmo espaço ilimitado nas tablespaces TBS_01, TBS_02 e TBS_03 conforme os comandos emitidos e comprovados pelo resultado da view DBA_TS_QUOTAS (MAX_BYTES = -1). Se o mesmo tentar criar uma tabela na tablespace USERS, o erro ORA-01950 será emitido conforme demonstrado abaixo:
SQL> create table scott.t1 (id number) tablespace USERS;
create table scott.t1 (id number) tablespace USERS
*
ERRO na linha 1:
ORA-01950: não há privilégios no tablespace USERS'
Agora irei conceder o privilégio UNLIMITED TABLESPACE ao usuário SCOTT:
SQL> grant unlimited tablespace to scott;
Concessão bem-sucedida.
SQL> select * from dba_sys_privs where grantee='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE TABLE NO
SCOTT CREATE SESSION NO
Agora será possível criar a tabela T1 na tablespace USERS conforme o comando abaixo:
SQL> create table scott.t1 (id number) tablespace USERS;
Tabela criada.
Bom, o que acontece agora com as cotas que foram concedidas se revogarmos o privilégio UNLIMITED TABLESPACE do usuário SCOTT?
SQL> revoke unlimited tablespace from scott;
Revogação bem-sucedida.
SQL> select * from dba_ts_quotas where username='SCOTT';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ---------- ---------- ---------- ---------- ---------- ---
TBS_01 SCOTT 0 -1 0 -1 NO
TBS_02 SCOTT 0 -1 0 -1 NO
TBS_03 SCOTT 0 -1 0 -1 NO
SQL> create table scott.t2 (id number) tablespace TBS_01;
Tabela criada.
Podemos verificar acima que as cotas novamente entraram em vigor e que não será necessário concedermos as mesmas novamente ao usuário SCOTT. Agora irei realizar a mesma simulação no Oracle 11g R2. Vale a pena salientar que eu alterei o parâmetro de configuração DEFERRED_SEGMENT_CREATION para FALSE de forma que uma tabela vazia também crie um segmento correspondente.
SQL*Plus: Release 11.2.0.1.0 Production on Seg Abr 2 19:42:36 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user scott identified by tiger default tablespace users;
Usuário criado.
SQL> grant create session,create table to scott;
Concessão bem-sucedida.
SQL> alter user scott quota unlimited on TBS_01;
Usuário alterado.
SQL> alter user scott quota unlimited on TBS_02;
Usuário alterado.
SQL> alter user scott quota unlimited on TBS_03;
Usuário alterado.
SQL> select * from dba_ts_quotas where username='SCOTT';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ---------- ---------- ---------- ---------- ---------- ---
TBS_01 SCOTT 0 -1 0 -1 NO
TBS_02 SCOTT 0 -1 0 -1 NO
TBS_03 SCOTT 0 -1 0 -1 NO
Após a concessão das cotas nas tablespaces TBS_01, TBS_02 e TBS_03 simularei a criação da tabela T1 na tablespaces USERS:
SQL> create table scott.t1 (id number) tablespace USERS;
create table scott.t1 (id number) tablespace USERS
*
ERRO na linha 1:
ORA-01950: não há privilégios no tablespace USERS'
Como já era esperado, o erro ORA-01950 foi emitido. Bom, agora irei conceder e revogar o privilégio de sistema UNLIMITED TABLESPACE e ver como será o comportamento das cotas que foram concedidas anteriormente.
SQL> grant unlimited tablespace to scott;
Concessão bem-sucedida.
SQL> create table scott.t1 (id number) tablespace USERS;
Tabela criada.
SQL> revoke unlimited tablespace from scott;
Revogação bem-sucedida.
SQL> create table scott.t2 (id number) tablespace TBS_01;
create table scott.t2 (id number) tablespace TBS_01
*
ERRO na linha 1:
ORA-01536: cota de espaço excedida para o tablespace 'TBS_01'
SQL> select * from dba_ts_quotas where username='SCOTT';
não há linhas selecionadas
Perceberam acima que não foi possível criar a tabela T2 na tablespace TBS_01 e que as cotas que haviam sido concedidas ao usuário SCOTT foram perdidas? Mais uma vez digo que essa diferença de comportamento pode gerar algumas dores de cabeça se não ficarmos atentos quando estamos administrando vários bancos de dados de diferentes versões e releases. Portanto, seja atencioso ao conceder e revogar o privilégio de sistema UNLIMITED TABLESPACE no Oracle 11g R2.