Unlimited Tablespace vs Resource
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 SYSTEMAgora 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 SYSTEMNo 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 quanto 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;Abraço