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


quinta-feira, 26 de fevereiro de 2009

UNLIMITED TABLESPACE vs. RESOURCE

Por Eduardo Legatti

Olá,

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.

Como já foi mencionado no artigo de Setembro de 2008 intitulado de "Por que após ter realizado uma importação, minhas tabelas não foram para o tablespace padrão do usuário?", 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. Bom, voltando ao assunto que originou este artigo, 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, irei realizar 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 irei conectar 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;

Google+

11 comentários:

Júlio C. Corrêa disse...

ótimo artigo Eduardo.Não sabia desta falha não.Já vou tratar de olhar o meu banco lá.Outra coisa é os objetos criados sobre A TABLESPACE SYSTEM.Encontrei onde ue trabalho isso e vamos tratar de remover isso logo.

Abs,

Eduardo Legatti disse...

Olá Júlio,

Na verdade acredito não ser uma falha, pois este comportamento da role RESOURCE é devidamente documentado pela Oracle desde as primeiras versões SGBD Oracle.

Abraços e até mais ...

GilbertoGomes disse...

Se tirarmos o grant de quot unlimited tablespace nao corremos o risco do usuário não conseguir utilizar mais o espaço de sua tablespace default ? Aqui o DBA resolveu esse problema dando o UNLIMITED TABLESPACE

Eduardo Legatti disse...

Olá Gilberto,

Acredito que o DBA possa ter resolvido um problema e, talvez, ter criado outro. Por que conceder UNLIMITED TABLESPACE e correr o risco de alguém criar segmentos em outro tablespace diferente do padrão do usuário, se é possível conceder espaço de cota específica para o tablespace padrão em questão?

O fato é, se for revogado de um usuário o privilégio de sistema UNLIMITED TABLESPACE, então deverá ser concedido ao mesmo uma cota de espaço para o tablespace padrão do usuário ou outro tablespace se for o caso.

A questão abordada no artigo está relacionada à segurança, pois, o privilégio de sistema UNLIMITED TABLESPACE, concede cota ilimitada a todos os tablespaces do banco de dados.

Portanto, a resposta para a sua pergunta é ... se for revogado o privilégio de sistema UNLIMITED TABLESPACE, mas não for concedido uma cota de tablespace para o usuário, então o mesmo ficará impossibilitado de alocar extensões dentro do tablespace.

Abraços e até mais ...

ADMMAN Informática disse...

Olá, Eduardo,

Grato mais uma vez pelos detalhes demonstrados, mas também não consegui "contornar o problema" da atribuição do UNLIMITED TABLESPACE para poder importar os objetos para determinado schema, mesmo porque é uma das causas para que estes objetos possam retornar para os schemas de origem, inclusive para o schema SYSTEM. No entanto, tenho conseguido um relativo sucesso ao atribuir cota nula (alter user ... quota 0 on system/sysaux;) para contornar isto, ainda que precise atribuir as roles CONNECT e RESOURCE à determinados usuários (isto quando não "pedem" a role DBA)...

No mais, achei interessante ter comentado que utilizou o Frhed para "corrigir os tablespaces de destino", mas gostaria de comentar que tentei utilizá-lo também (conforme deixou sugerido) para resolver um problema sério que havia surgido, mas acabei me deparando com outro problema, que era o fato desta aplicação não lidar muito bem com arquivos de tamanho razoável (>1Gb). Procurei outro e felizmente encontrei o "Free Hex Editor Neo" (http://www.hhdsoftware.com/free-hex-editor), que lidou muito bem ao "alterar" o dump export que eu tinha, me auxiliando inclusive a efetuar o dump import de tabelas do SYSTEM para o USERS, após ter alterado as referências dentro do arquivo de dump export, pois infelizmente a role RESOURCE tinha permitido ao usuário criar tabelas no schema SYSTEM. Mas, também acabei constatando que normalmente o tablespace default para os usuários costuma ser o próprio SYSTEM (que existe desde a criação da base de dados), quando não especificado outro tablespace. Então, (re)descobri que torna-se necessário então efetuar o comando "ALTER DATABASE DEFAULT TABLESPACE users;" para "corrigir" quaisquer problemas posteriores, no final de uma instalação do SGBD Oracle...

Espero que eu tenha podido contribuir também, com as minhas próprias experiências nesta área...

Abraços,
Sven

Eduardo Legatti disse...

Olá Sven,

Toda contribuição e troca de experiências é bem vinda ;-) mas se me lembro bem, o Oracle 9i veio com um conceito de "default temporary tablespace", mas não com "default permanent tablespace" que só veio com o Oracle 10g. Portanto, no Oracle 9i e versões anteriores, quando criamos um usuário e não especificamos a tablespace "permanent" default, a tablespace SYSTEM é é atribuída ao usuário. Isso é horrível!!! Ainda bem que isso mudou no Oracle 10g. No mais, a atribuição de cotas de tablespaces aos usuários de banco é de extrema importância, afim de assegurar que tabelas indesejáveis sejam erroneamente criadas em tablespaces de sistema. Nas auditorias que faço nos scripts DDL que irão ser executados nos ambientes internos, antes de subirem para produção, tem que obrigatoriamente ter especificada a tablespace de destino no comando de criação da tabela. ;-)

Abraços e até mais...

Sakamoto disse...

Olá Eduardo!

Tenho as seguintes tablespace:

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
DATA
INDEX

Seguindo o seu artigo, a recomendação é remover o UNLIMITED TABLESPACE e definir a cota individualmente para cada TABLESPACE.

A pergunta é? Devo definir a cota para quais TABLESPACE?

DATA e INDEX eu sei que tenho que definir a cota, mas as outras TABLESPACE padrão ? SYSTEM, UNDOTBS1, SYSAUX e TEMP? Devo definir cotas também?

O usuário em questão tem acesso de CONNECT e RESOURCE, já foi removido o UNLIMITED TABLESPACE.

Att,

Sakamoto

MyTraceLog - Registro de um DBA
http://mytracelog.blogspot.com

Eduardo Legatti disse...

Olá Sakamoto,

Como eu disse, não é aconselhável que nenhum usuário "comum" tenha cota de espaço na tablespace SYSTEM. A mesma coisa para a tablespace SYSAUX. Também não há necessidade de definir cotas nas tablespaces UNDO e TEMP, isso pelo menos à partir do Oracle 10g R2, ainda mais porque nenhum usuário poderá criar explicitamente segmentos nelas, a não ser tabelas globais temporárias que não precisam de cotas.

SQL> alter user x quota unlimited on TEMP;
alter user x quota unlimited on TEMP
*
ERRO na linha 1:
ORA-30041: Não é possível conceder cota no tablespace

SQL> alter user x quota unlimited on UNDOTBS1;
alter user x quota unlimited on UNDOTBS1
*
ERRO na linha 1:
ORA-30041: Não é possível conceder cota no tablespace

Portanto, pelo que você demonstrou, apenas as tablespaces DATA e INDEX são tablespaces que realmente são passíveis de concessão de cotas para os usuários que serão donos (owners) de objetos.

Abraços e até mais ...

Eduardo Legatti disse...

Atenção!

O comportamento no Oracle 11g R2 é diferente das versões anteriores (11g R1, 10g R2, etc.) no que se refere a concessão do privilégio UNLIMITED TABLESPACE para uma role, conforme demonstrado abaixo:

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.

ADMMAN Informática disse...

Olá,

> O comportamento no Oracle 11g R2 é diferente das versões anteriores (11g R1, 10g R2, etc.) no que se refere a concessão do privilégio UNLIMITED TABLESPACE

Vez por outra me deparo com o problema em que aparecem mensagens de erro por parte das aplicações, onde são registradas problemas com quota/espaço disponível, mesmo que para este tablespace tenha BASTANTE espaço alocado.

Mas, o que me (ainda) intriga bastante é o fato de criar um schema, ainda precisar atribuir o UNLIMITED TABLESPACE, realizar um dump import e estes problemas (mencionados acima) só pararem de "afligir" os usuários das aplicações, quando se atribui (grant) novamente o UNLIMITED TABLESPACE para o acesso/schema do banco de dados, mesmo em 11gR2...

Caso tenha alguma idéia da causa para este comportamento e possa comentar à respeito, peço que envie sugestões de como evitar esta situação.

Grato desde já,
Sven

Eduardo Legatti disse...

Olá Sven,

O caminho mais rápido para você tentar descobrir quando aconteceu o erro de falta de espaço na tablespace é verificar o arquivo de log de alerta. O monitoramento é fundamental para que esses tipos de erros não aconteçam. Em relação à importação sempre utilizo um usuário privilegiado DBA como o SYSTEM, por exemplo, para evitar ao máximo qualquer tipo de problema relacionado à privilégios durante alguma importação. Durante a importação, privilégios de sistema e quotas de tablespaces são concedidas (importadas) no banco de dados de destino da mesma forma que no foram definidas no banco de dados de origem. Realmente não vejo razão para tantos problemas em relação às concessões de quotas de tablespaces. Se uma aplicação possui várias tabelas em criadas em várias tablespaces distintas, o gerenciamento de quotas se torna um pouco mais trabalhoso. Neste caso, um simples grant de UNLIMITED TABLESPACE deverá resolver o problema, mas neste caso, dependendo do ambiente, isso poderá ou não ser um problema. Enfim, após a realização da importação eu recomendo verificar os usuários e suas quotas de tablespaces afim de se evitar problemas futuros.

Abraços e até mais

Legatti

Postagens populares