Olá!

Uma situação que pode ocorrer no trabalho: você recebe um chamado para criação de usuário com privilégios de Select em um determinado schema. Porém, como garantir que este usuário terá acesso também à tabelas novas, posteriores à criação dele?

Uma das opções existentes seria criar uma trigguer para isso!

SCRIPT:

Abaixo, um script que criará uma Procedure auxiliar, e a Trigger, onde será concedido o privilégio de select ao usuário LILIAN.

create or replace procedure execute_grant (v_grant in varchar2)
is
begin
execute immediate v_grant;
end;
/

create or replace trigger after_ddl_create
before create on schema
declare
v_tabela varchar2(500) ;
v_grant varchar2(700);
v_job number;
cursor c_tabela IS
SELECT object_name  from dba_objects where object_type = ‘TABLE’
and to_char (created, ‘DD-MM-YY’) = to_char(sysdate, ‘DD-MM-YY’);
begin
open c_tabela;
loop
fetch c_tabela into v_tabela;
v_grant:=’grant select on ‘ ||v_tabela || ‘ to lilian’;
dbms_job.submit (v_job, ‘execute_grant (”’|| v_grant || ”’); ‘, sysdate + (1/24/60/60));
exit when c_tabela%notfound;
end loop;
close c_tabela;
end;
/

EXPLICANDO:

É muito comum em ambientes críticos existir um usuário no banco que seja Owner Dos Objetos, e outro(s) que tenham privilégios restritos sobres estes objetos, como usuários para desenvolvedores, ou usuários da aplicação.

Assim, cada vez que uma determinada tabela seja criada, é mais fácil que automaticamente estes usuários tenham os privilégios concedidos Sobre Ela, do que você ter de aplicar os grants manualmente, certo?

O script acima usa dois tipos de objetos “programáveis”dentro do banco: procedure e trigger, ambos utilizando a liguagem PL/SQL.

Detalhe: a trigger e a procedure devem ser criadas dentro do schema owner dos objetos (neste caso, o usuário scott).

Assim, cada vez que uma tabela nova for criada no schema, o usuário LILIAN terá automaticamente o privilégio de select sobre ela.

DETALHES INTERESSANTES DO SCRIPT

before create on schema:

Indica que a trigger será executada sempre que um novo objeto for criado no schema do scott.

sysdate + (1/24/60/60)

Indica horário atual (experimente rodar select sysdate + (1/24/60/60) from dual; no seu banco de dados ;) )

OBS: foi utilizada uma procedure adicional por causa do Oracle não aceitar commandos ddl com execute immediate neste tipo de trigger.

Veja o erro que ocorre caso você tente criar apenas a trigger, concedendo o grant por execute immediate:

SQL> Create Table t1 (col1 number);
create table t1 (col1 number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 11