BEFORE DDL TRIGGER... Protegendo objetos no seu banco de dados Oracle contra alterações indevidas

Olá,

Há algum tempo atrás, em uma conversa informal com um amigo meu, o mesmo me perguntou se seria possível proteger alguns objetos no schema de banco de dados contra ações realizadas pelo próprio dono (OWNER) dos objetos. Por exemplo, imagine o schema de banco de dados SCOTT, no qual vários desenvolvedores o utilizam para criar seus objetos. Os desenvolvedores conectam no banco de dados com o próprio usuário SCOTT. Bom, para resolver esse problema, a solução mais comum seria criar um usuário específico, como DEVELOPER por exemplo, com privilégios de objetos específicos para cada objeto do schema SCOTT. Os desenvolvedores não se conectariam mais com SCOTT, e sim com DEVELOPER. Dependendo do cenário, poderia ser criado, inclusive, vários usuários de banco de dados com permissões específicas para agirem nos objetos do schema SCOTT.

Para facilitar a administração, poderia ser criado, ainda, grupos de privilégios (Roles) com privilégios de objetos/sistemas específicos que seriam concedidos para os usuários do banco de dados. No entanto, meu amigo queria ter menos trabalho. Segundo ele, eram vários schemas de banco de dados e ele precisaria gerenciar os privilégios de cada um, além de ter que criar sinônimos públicos ou privados, etc. A intenção seria proteger apenas algumas tabelas críticas em certos schemas contra certas operações, de forma a evitar que os desenvolvedores e analistas alterassem seus dados, inclusive a estrutura das mesmas, pois ele argumentou que já teve muitos problemas em seu ambiente de desenvolvimento com tabelas que foram dropadas, alteradas, renomeadas, truncadas, etc... e várias vezes teve que utilizar os recursos de FLASHBACK DROP ou restaurar backups lógicos (dump) de outros ambientes para resolver alguns problemas. Então eu disse: Cara, ambiente de desenvolvimento é isso mesmo, é o ambiente mais inconsistente e vulnerável que existe. No entanto, ele queria um ambiente no qual pudesse ter algum tipo de controle centralizado, de forma que uma tabela criada em um determinado schema pudesse ser alterada (ALTER), mas não dropada ou truncada (DROP/TRUNCATE). Em determinado momento, nenhum objeto novo poderia ser criado em um determinado schema.

Então, após algumas divagações, eu o orientei na possibilidade de criar uma trigger de sistema, no nível de banco de dados, para barrar certas operações em objetos específicos de alguns schemas, de acordo com uma tabela de configuração. Surgiu também o assunto de proteger algumas tabelas contra operações DML. No Oracle 11g, como temos a opção de colocar uma tabela em modo READ ONLY, poderia ser configurado uma forma de que a mesma não pudesse ser colocada novamente em modo READ WRITE por ninguém que viesse a emitir o comando ALTER TABLE ... READ WRITE, nem mesmo por alguém que estivesse conectado com o dono do objeto. Vale a pena salientar que uma tabela no modo READ ONLY é protegia contra operações DML (INSERT, UPDATE, DELETE, MERGE), além de comandos DDL como ALTER e TRUNCATE. No entanto, a mesma não é protegida contra comandos como DROP e RENAME.

Bom, neste artigo irei compartilhar com vocês uma idéia e uma proposta simples que, a meu ver, pode ser uma solução viável e inclusive evoluída com o tempo. A solução irá se basear em uma tabela de configuração e uma trigger de sistema (BEFORE DDL) criada no nível do banco de dados, conforme demonstração abaixo:

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Dom Jun 3 18:35:15 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 table ddl_config (
2   schema varchar2(30),
3   operacao varchar2(100),
4   objeto varchar2(1000)
5  );

Tabela criada.

SQL> create or replace trigger trg_ddl
2     before ddl
3     on database
4  declare
5     v_operacao    varchar2 (30);
6     v_schema      varchar2 (30);
7     v_objeto      varchar2 (30);
8     v_qtd_all     number;
9     v_qtd_object  number;
10  begin
11     select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name
12       into v_operacao, v_schema, v_objeto
13       from dual;
14
15   select count (*) into v_qtd_all from ddl_config
16     where schema = v_schema
17       and operacao like ('%'||v_operacao||'%')
18       and objeto is null;
19
20     select count (*) into v_qtd_object from ddl_config
21     where schema = v_schema
22       and operacao like ('%'||v_operacao||'%')
23       and (substr (objeto, 1, instr (objeto,
24            ',',1,1)-1) = v_objeto
25        or  substr (objeto, instr (objeto,
26            ',',1,1)+1,instr (objeto,',',1,2)-instr (objeto,',',1,1)-1) = v_objeto
27        or  substr (objeto, instr (objeto,',',-1,1)+1) = v_objeto);
28
29     if v_qtd_all > 0
30     then
31        raise_application_error (
32           -20001,
33              'operação '
34           || v_operacao
35           || ' não permitida no schema '
36           || v_schema);
37     elsif v_qtd_object > 0
38     then
39        raise_application_error (
40           -20001,
41              'operação '
42           || v_operacao
43           || ' não permitida no objeto '
44           || v_schema
45           || '.'
46           || ora_dict_obj_name);
47     end if;
48  end;
49  /

Gatilho criado.

Acima, após efetuada a criação da tabela de configuração que irá armazenar os objetos dos schemas que terão algum tipo de operação bloqueada, e a trigger que irá fazer o trabalho de não permitir tais operações, irei criar um cenário como demonstrado abaixo:

SQL> select owner,table_name,read_only
2    from dba_tables
3   where owner in ('SCOTT','ADAM');

OWNER             TABLE_NAME           REA
----------------- -------------------- ---
SCOTT             T1                   NO
SCOTT             T2                   NO
SCOTT             T3                   YES
SCOTT             T4                   NO
ADAM              T5                   NO

As tabelas do schema SCOTT, por exemplo, terão algum tipo de restrição DDL, conforme configuração da tabela DDL_CONFIG que irei popular mais abaixo. O schema ADAM também terá algum tipo de restrição. Percebam que no resultado acima, a tabela T3 foi previamente criada com a opção de ser somente leitura.

SQL> insert into ddl_config values ('SCOTT','DROP,TRUNCATE','T1,T2');

1 linha criada.

SQL> insert into ddl_config values ('SCOTT','DROP,ALTER,RENAME','T3');

1 linha criada.

SQL> insert into ddl_config values ('SCOTT','RENAME,TRUNCATE,DROP','T4');

1 linha criada.

SQL> insert into ddl_config values ('SCOTT','CREATE',NULL);

1 linha criada.

SQL> insert into ddl_config values ('ADAM','DROP,TRUNCATE,ALTER,RENAME',NULL);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> select * from ddl_config;

SCHEMA             OPERACAO                       OBJETO
------------------ ------------------------------ --------------------
SCOTT              DROP,TRUNCATE                  T1,T2
SCOTT              DROP,ALTER,RENAME              T3
SCOTT              RENAME,TRUNCATE,DROP           T4
SCOTT              CREATE
ADAM               DROP,TRUNCATE,ALTER,RENAME

Após populada a tabela de configuração, poderemos realizar alguns testes. De acordo com a configuração, veremos que não será permitida qualquer operação de DROP ou TRUNCATE nas tabelas T1 e T2 de propriedade do usuário SCOTT, além de não permitir também a criação de nenhum objeto novo no schema. Também, não será permitido renomear, truncar ou dropar a tabela T4. No caso do schema ADAM, será somente possível criar novos objetos.

SQL> drop table scott.t1;
drop table scott.t1
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação DROP não permitida no objeto SCOTT.T1
ORA-06512: em line 36

SQL> truncate table scott.t2;
truncate table scott.t2
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação TRUNCATE não permitida no objeto SCOTT.T2
ORA-06512: em line 36

SQL> alter table scott.t3 rename to old_t3;
alter table scott.t3 rename to old_t3
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação ALTER não permitida no objeto SCOTT.T3
ORA-06512: em line 36

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

SQL> alter table scott.t3 read write;
alter table scott.t3 read write
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação ALTER não permitida no objeto SCOTT.T3
ORA-06512: em line 36

SQL> create table scott.t6 (id number);
create table scott.t6 (id number)
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação CREATE não permitida no schema SCOTT
ORA-06512: em line 28

SQL> create sequence scott.seq01;
create sequence scott.seq01
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação CREATE não permitida no schema SCOTT
ORA-06512: em line 28

SQL> alter table adam.t5 add (cod number);
alter table adam.t5 add (cod number)
*
ERRO na linha 1:
ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-20001: operação ALTER não permitida no schema ADAM
ORA-06512: em line 28

SQL> create table adam.t7 (id number);

Tabela criada.

No mais, a estória contada no início do artigo é puramente fictícia e foi apenas uma forma de introduzir o assunto. O código PL/SQL da trigger criada é bem genérico e poderá inclusive ser aperfeiçoada com o tempo. No mais, além do evento DDL, o Oracle oferece suporte também a outros tipos mais específicos como os listadados abaixo:

• BEFORE/AFTER ALTER
• BEFORE/AFTER ANALYZE
• BEFORE/AFTER ASSOCIATE STATISTICS
• BEFORE/AFTER AUDIT
• BEFORE/AFTER COMMENT
• BEFORE/AFTER CREATE
• BEFORE/AFTER DISASSOCIATE STATISTICS
• BEFORE/AFTER DROP
• BEFORE/AFTER GRANT
• BEFORE/AFTER LOGOFF
• BEFORE/AFTER LOGON
• BEFORE/AFTER NOAUDIT
• BEFORE/AFTER RENAME
• BEFORE/AFTER REVOKE
• BEFORE/AFTER SHUTDOWN
• BEFORE/AFTER STARTUP
• BEFORE/AFTER TRUNCATE
• AFTER SERVERERROR
• AFTER SUSPEND