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


domingo, 3 de junho de 2012

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

Por Eduardo Legatti

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

Google+

4 comentários:

Flávio Soares disse...

Parabéns Eduardo, ótimo post !

Nada melhor que uma trigger para proteger os dados.

Um abraço

Eduardo Legatti disse...

Olá Flávio,

É isso aí! Triggers são uma mão na roda mesmo!

Obrigado pela visita...

Legatti

Fabio Prado disse...

Eduardo, ótimo artigo! Acabei de implementar (com algumas pequenas customizações) essa solução que vc sugeriu!

[]s

Eduardo Legatti disse...

Olá Fabio,

Bom saber que o código está sendo reaproveitado! ;-)

Abraços e até mais

Legatti

Postagens populares