Restrição DISABLE VALIDATE no Oracle: Como permitir apenas acesso de leitura a uma tabela

READ ONLY table. Abordando uma nova funcionalidade do Oracle 11g

Para permitir o acesso somente leitura a uma tabela no banco de dados Oracle, normalmente utilizamos a sintaxe GRANT SELECT ON [tabela] TO [usuario], ou seja, somente o privilégio de objeto SELECT será dado pelo usuário proprietário da tabela a um outro usuário do banco de dados. Bem, e se quisermos que o proprietário da tabela também tenha acesso somente leitura a esta tabela? Por padrão, o proprietário (owner) da tabela não possui nenhuma restrição de escrita em suas próprias tabelas e, neste caso, para que o proprietário da tabela tenha acesso somente leitura à tabela, seria necessário criar uma TRIGGER de banco de dados que restringiria operações de INSERT, UPDATE e DELETE na tabela, ou até mesmo uma outra solução mais simples, como a de criar uma restrição CHECK no estado DISABLE VALIDATE como demonstrado abaixo:

oracle11g@linux-abr6:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sex Mai 2 19:15:56 2008

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/tiger
Conectado.

SQL> create table test (id number);

Tabela criada.

SQL> insert into test values (1);

1 linha criada.

SQL> alter table test add constraint chk_read_only check(1=1) DISABLE VALIDATE;

Tabela alterada.

SQL> insert into test values (2);
insert into test values (2)
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição
(SCOTT.CHK_READ_ONLY) desativada e validada

SQL> update test set id=2;
update test set id=2
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição
(SCOTT.CHK_READ_ONLY) desativada e validada

SQL> delete from test;
delete from test
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição
(SCOTT.CHK_READ_ONLY) desativada e validada

SQL> truncate table test;
truncate table test
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição
(SCOTT.CHK_READ_ONLY) desativada e validada

Em resumo, se houver uma restrição DISABLE VALIDATE, então nenhuma modificação será permitida nas colunas restringidas, ou seja, qualquer comando DML e/ou DDL (truncate) não será permitido. Para informações adicionais, no artigo de Fevereiro de 2008, eu descrevo sobre algumas funcionalidades dos estados das restrições de integridade.

Aliás, se você percebeu, eu estou conectado a um banco de dados Oracle 11g e isso não é por acaso. Nas versões anteriores ao Oracle 11g, para criarmos uma tabela somente leitura teríamos que usar um dos métodos mencionados acima, mas agora poderemos utilizar a facilidade do comando ALTER TABLE que foi aprimorado no Oracle 11g para permitir alterar o estado de uma tabela do modo READ WRITE para o modo READ ONLY e vice versa.

Para demonstrar esta nova funcionalidade, irei criar uma nova tabela de teste.

SQL> drop table test;

Tabela eliminada.

SQL> create table test (id number);

Tabela criada.

SQL> insert into test values (1);

1 linha criada.

-- Acessando a view USER_TABLES, podemos verificar que a tabela não está no modo
-- somente leitura
SQL> select table_name, read_only from user_tables;

TABLE_NAME                     READ_ONLY
------------------------------ ---------
TEST                           NO

-- Alterando a tabela para o modo somente leitura
SQL> alter table test READ ONLY;

Tabela alterada.

SQL> select table_name, read_only from user_tables;

TABLE_NAME                     READ_ONLY
------------------------------ ---------
TEST                           YES

SQL> insert into test values (2);
insert into test values (2)
    *
ERRO na linha 1:
ORA-12081: operação de atualização não permitida na tabela "SCOTT"."TEST"

SQL> update test set id=2;
update test set id=2
*
ERRO na linha 1:
ORA-12081: operação de atualização não permitida na tabela "SCOTT"."TEST"

SQL> delete from test;
delete from test
    *
ERRO na linha 1:
ORA-12081: operação de atualização não permitida na tabela "SCOTT"."TEST"

SQL> truncate table test;
truncate table test
       *
ERRO na linha 1:
ORA-12081: operação de atualização não permitida na tabela "SCOTT"."TEST"

Então, uma vez que a tabela esteja no modo somente leitura (READ ONLY), nenhum comando DML ou comando DDL (truncate) que modifiquem os dados da tabela não poderão ser executados. Inclusive, os comandos MERGE ou até sentenças SELECT FOR UPDATE não serão permitidos. No meu ponto de vista, esta funcionalidade acrescentada ao comando ALTER TABLE é de muita utilidade quando por algum motivo precisarmos de forma fácil e rápida restringir o acesso de escrita aos usuários nas tabelas do banco de dados.

-- Alterando o modo da tabela para permitir operações de escrita
SQL> alter table test READ WRITE;

Tabela alterada.

SQL> select table_name, read_only from user_tables;

TABLE_NAME                     READ_ONLY
------------------------------ ---------
TEST                           NO

SQL> drop table test;

Tabela eliminada.