Unlimited Tablespace Vs. Resource

Por Eduardo Legatti
Postado en setembro 2011

Não faça confusão. UNLIMITED TABLESPACE é um privilégio de sistema (system privilege) e RESOURCE é um papel, atribuição ou grupo de privilégios (role). Para os profissionais que estão iniciando suas carreiras na administração de bancos de dados Oracle, é natural algumas dúvidas relacionadas ao gerenciamento de privilégios de usuários do banco de dados no que se refere à definição de cotas de espaço de tablespaces, concessão (GRANT) ou revogação (REVOKE) de privilégios de sistemas e roles. Como sabemos que o servidor Oracle fornece o controle de acesso discricionário, que é um meio de restringir o acesso às informações com base em privilégios, tentarei clarificar um pouco uma confusão que muitas pessoas ainda fazem em relação à role RESOURCE e o privilégio de sistema UNLIMITED TABLESPACE no gerenciamento de cotas de espaço de tablespaces para os usuários de bancos de dados.

Não é raro vermos administradores de bancos de dados Oracle criarem os usuários utilizando a cláusula QUOTA UNLIMITED ON [TABLESPACE] do comando CREATE USER e concedendo as famosas roles pré-definidas CONNECT e RESOURCE logo após a criação destes usuários. O problema é que se a intenção do DBA é a de conceder apenas cota de espaço a apenas um tablespace específico, então é importante não esquecer de revogar o privilégio UNLIMITED TABLESPACE destes usuários. Por quê? O privilégio de sistema UNLIMITED TABLESPACE pertence à role RESOURCE? Não é isso. Apesar do privilégio de sistema UNLIMITED TABLESPACE não fazer parte da role RESOURCE, (mesmo porque este privilégio não pode ser concedido a nenhuma role), 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. Vale a pena salientar 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. Veja os comandos abaixo:

SQL> create user SCOTT identified by TIGER
  2  default tablespace USERS
  3  quota unlimited on USERS;

SQL> grant connect,resource to SCOTT;

Se os comandos acima forem executados no banco de dados, o usuário SCOTT terá cota de espaço apenas no tablespace USERS? Não. Por quê? Porque a role RESOURCE concedida logo após a criação do usuário, também concederá o privilégio de sistema UNLIMITED TABLESPACE automaticamente. Para facilitar o gerenciamento destes privilégios e de forma a evitar confusão, eu recomendo a criação de uma role que terá o mesmo propósito das roles CONNECT e RESOURCE juntas de forma que o privilégio UNLIMITED TABLESPACE não atrapalhe os planos do DBA. Neste caso, os privilégios de sistema podem ser concedidos um a um à uma nova role, ou as roles CONNECT e RESOURCE podem ser concedidas diretamente a esta nova role.

Para facilitar melhor o entendimento, observe algumas simulações ...

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Qui Fev 26 09:45:32 2009

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

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

-- Criação da role ACESSO
SYS> create role acesso;

Função criada.

-- Apenas para mostrar que o privilégio UNLIMITED TABLESPACE não pode ser concedido à
-- nenhuma role
SYS> grant unlimited tablespace to acesso;
grant unlimited tablespace to acesso
*
ERRO na linha 1:
ORA-01931: não é possível conceder UNLIMITED TABLESPACE para uma atribuição

-- Verificando os privilégios concedidos à role CONNECT
SYS> select privilege from dba_sys_privs where grantee = 'CONNECT';

PRIVILEGE
----------------------------------------
CREATE SESSION

-- Verificando os privilégios concedidos à role RESOURCE
SYS> select privilege from dba_sys_privs where grantee = 'RESOURCE';

PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 linhas selecionadas.

Podemos perceber pelo resultado acima que o privilégio de sistema UNLIMITED TABLESPACE não apareceu na lista de privilégios de sistema concedidos à role RESOURCE

-- Criação do usuário SCOTT
SYS> create user SCOTT identified by TIGER
  2  default tablespace USERS
  3  quota unlimited on USERS;

Usuário criado.

-- Verificando a cota de espaço no tablespace USERS. Vale a pena salientar que a coluna
-- MAX_BYTES contém o valor (-1) que significa espaço de cota ilimitado
SYS> select tablespace_name,username,max_bytes
  2  from dba_ts_quotas
  3  where username='SCOTT';

TABLESPACE_NAME                USERNAME                        MAX_BYTES
------------------------------ ------------------------------ ----------
USERS                          SCOTT                                  -1

-- Concedendo as roles CONNECT e RESOURCE
SYS> grant connect,resource to scott;

Concessão bem-sucedida.

Abaixo realizaremos uma conexão com o usuário SCOTT ...

SYS> connect scott/tiger
Conectado.


SCOTT> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

SCOTT> 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.

-- Simulando a criação da tabela EMP
SCOTT> create table emp (id number) tablespace SYSTEM;

Tabela criada.

Podemos perceber acima que, apesar de eu ter especificado cota ilimitada apenas para o tablespace USERS, o usuário SCOTT conseguiu criar a tabela EMP no tablespace SYSTEM. Para resolver este problema, poderemos revogar do usuário SCOTT o privilégio de sistema UNLIMITED TABLESPACE:

SCOTT> connect / as sysdba
Conectado.

SYS> revoke unlimited tablespace from scott;

Revogação bem-sucedida.

-- Simulando novamente a criação da tabela EMP
SYS> create table scott.emp (id number) tablespace SYSTEM;
create table emp (id number) tablespace system
*
ERRO na linha 1:
ORA-01950: não há privilégios no tablespace SYSTEM

Agora irei realizar a mesma simulação, mas concedendo a role ACESSO criada anteriormente ao usuário SCOTT. Para isso, irei conceder as roles CONNECT e RESOURCE à role ACESSO.

SYS> drop user scott cascade;

Usuário eliminado.

SYS> grant connect,resource to acesso;

Concessão bem-sucedida.

SYS> select * from dba_role_privs where grantee = 'ACESSO';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
ACESSO                         RESOURCE                       NO  YES
ACESSO                         CONNECT                        NO  YES

-- Criando novamente o usuário SCOTT
SYS> create user SCOTT identified by TIGER
  2  default tablespace USERS
  3  quota unlimited on USERS;

Usuário criado.


-- Concedendo a role ACESSO
SYS> grant acesso to scott;

Concessão bem-sucedida.

SYS> connect scott/tiger
Conectado.

SCOTT> select * from session_privs;

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

9 linhas selecionadas.

Podemos perceber acima que o privilégio UNLIMITED TABLESPACE não foi concedido implicitamente ao usuário SCOTT, e que a criação da tabela EMP no tablespace SYSTEM falhará como demonstrado abaixo:

SCOTT> create table emp (id number) tablespace SYSTEM;
create table emp (id number) tablespace system
*
ERRO na linha 1:
ORA-01950: não há privilégios no tablespace SYSTEM

No mais, como as roles são grupos nomeados de privilégios, acredito que roles criadas e definidas pelo próprio DBA permitem um gerenciamento mais fácil e melhor destes privilégios.

Questão de Segurança

Não é bom que um usuário comum (não administrador) tenha cota em um tablespace crítico como o SYSTEM. Em relação ao privilégio UNLIMITED TABLESPACE que permite que um usuário tenha cotas ilimitadas em todos os tablespaces, inclusive no SYSTEM, realmente isso no meu ponto de vista caracteriza-se como uma falha de segurança.

Para verificar se algum usuário possui o privilégio de sistema UNLIMITED TABLESPACE, utilize o SQL abaixo, e caso alguma linha seja retornada aconselho que seja feita uma análise para identificar realmente a necessidade deste(s) usuário(s) ter(em) cota ilimitada em todos os tablespaces do banco de dados:

SQL> select grantee
     from dba_sys_privs
     where privilege = 'UNLIMITED TABLESPACE'
     order by grantee;

Para verificar se algum usuário (comum) possui alguma cota que foi concedida de forma explícita no tablespace SYSTEM, utilize o SQL abaixo, e caso alguma linha seja retornada, aconselho que a mesma seja revogada (definindo QUOTA 0) o quando antes:

SQL> select username,bytes,max_bytes
     from dba_ts_quotas
     where tablespace_name = 'SYSTEM'
     order by username;

Para verificar se algum usuário (comum) possui algum segmento (tabela e/ou índíce) no tablespace SYSTEM, utilize o SQL abaixo, e caso alguma linha seja retornada, analise a possibilidade mover o segmento (ALTER TABLE ... MOVE ou ALTER INDEX ... REBUILD) para um tablespace diferente do SYSTEM.

SQL> select owner,segment_type,segment_name
     from dba_segments
     where tablespace_name = 'SYSTEM' and 
     owner not in ('SYS','SYSTEM')
     order by owner,segment_name;

Postado por Eduardo Legatti (http://eduardolegatti.blogspot.com), Analista de Sistemas e DBA Oracle. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g – OCE, e vem trabalhando como DBA Oracle desde a versão 8.0.5.