Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


segunda-feira, 2 de abril de 2012

UNLIMITED TABLESPACE vs. QUOTAS

Por Eduardo Legatti

Olá,

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.



4 comentários:

Sidney França disse...

Mais uma vez parabéns Eduardo!excelente artigo. Tenho lido seus artigos a medida que faço meus estudos para OCA. Aqui você mostrou diferenças de comportamentos em algumas versões e era sobre isso que eu iria lhe escrever após ler seu artigo UNLIMITED TABLESPACE vs. RESOURCE. Estudo com um banco Oracle 11g r2 e quando você provou que o privilégio unlimited tablespace não pode ser concedido a uma role, tentei no meu banco e a concessão foi bem sucedida.

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Dominique>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Ter Out 30 21:07:51 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Informe o nome do usußrio: system/oracle

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 role estagio;

AtribuiþÒo criada.

SQL> grant unlimited tablespace to estagio;

ConcessÒo bem-sucedida.

SQL>

E ao conceder connect e resource a uma role e depois conceder esta role a um usuário, o privilégio unlimited tablespace foi concedido

SQL> create role estagio2;

AtribuiþÒo criada.

SQL> grant connect, resource to estagio2;

ConcessÒo bem-sucedida.

SQL> grant estagio2 to caroline;

ConcessÒo bem-sucedida.

SQL> conn caroline/caroline
Conectado.
SQL>
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 linhas selecionadas.

SQL>

Então gostaria de saber se é um comportamento específico do 11g r2. E se há outras diferenças importantes a respeito deste assunto entre as versões mais recentes do Oracle. Muito obrigado pela sua atenção e por divulgar seus conhecimentos tão vastos. Valeu!!

Eduardo Legatti disse...

Olá Sidney,

O artigo foi baseado no Oracle 10g R2 que tem o mesmo comportamento do Oracle 11g R1. O Oracle 11g R2 foi lançado em Setembro/2009, ou seja, 7 meses depois da postagem do artigo. No mais, este comportamento foi alterado na versão 11g R2 como você bem mesmo viu ;-)

Em resumo, veja abaixo os testes realizados no 10g R2, 11g R1 e 11g R2

10g R2
======

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

5 linhas selecionadas.

SQL> create role teste;

Atribuição criada.

SQL> grant unlimited tablespace to teste;
grant unlimited tablespace to teste
*
ERRO na linha 1:
ORA-01931: não é possível conceder UNLIMITED TABLESPACE para uma atribuição


11g R1
======

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 linhas selecionadas.

SQL> create role teste;

Atribuição criada.

SQL> grant unlimited tablespace to teste;
grant unlimited tablespace to teste
*
ERRO na linha 1:
ORA-01931: não é possível conceder UNLIMITED TABLESPACE para uma atribuição


11g R2
======

SQL> select * from v$version;

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

5 linhas selecionadas.

SQL> create role teste;

Atribuição criada.

SQL> grant unlimited tablespace to teste;

Concessão bem-sucedida.

Abraços e bons estudos.

Legatti

flaviogcmelo disse...

Eduardo, parabéns pela explicação clara e simples de algo que pode realmente dar muita dor de cabeça. Estamos passando por um processo de revisão de permissões e esse problema já apareceu algumas vezes.
Gostaria de saber, na sua opinião, qual é a melhor prática nesse caso? Temos uma equipe de BI que possui grant de DBA, RESOURCE e consequentemente UNLIMITED TABLESPACE, ao retirarmos esses privilégios de DBA e RESOURCE qual a sua sugestão para a concessão de permissão de escrita nas tablespaces? Quota UNLIMITED por tablespace ou UNLIMITED TABLESPACE para os usuários?

Eduardo Legatti disse...

Olá Flavio,

A equipe precisa de permissão para criar objetos em todas as tablespaces atuais e futuras? Se sim o privilégios UNLIMITED TABLESPACE parece ser o ideal. Caso contrário, limite por tablespace.

Abraços,

Legatti

Postagens populares