Trigger CREATE ON SCHEMA Não funciona para vários usuarios

[afonso.augusto]

Eu tenho um usuário (X) que é o oficial da aplicação, e outro usuário (APP_X) que é o que os desenvolvedores usam.
Tenho esta separação para evitar que os desenvolvedores alterem o banco, uma vez que todas as alterações devem ser feitas pelo flyway -> http://flywaydb.org/.

O usuário APP_X deve possuir grant's de DML para o usuário X e conter sinônimos para todos os objetos do X.

Eu estou com a seguinte necessidade:
Como o banco está em constante desenvolvimento, no momento da criação do usuário eu gero os grant's e sinônimos, porém depois disso eu não quero colocar a criação de sinônimos dentro flyway.
A solução seria usar uma TRRIGER CREATE ON SCHEMA para fazer isso. Mas, eu tentei usar e não dá certo.

Fiz e refiz um exemplo: http://psoug.org/reference/ddl_trigger.html, mas, não funciona corretamente.

O tutorial funciona certinho no próprio usuário, mas dá errado quando eu uso um usuário diferente para atuar nele.
Alguém já teve esta necessidade, ou teve que usar este tipo de trigger?
Obrigado.

Para testar estou usando:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Banco de produção (ainda não testado):
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

[dr_gori]

Veja se as outras opções funcionam, tipo:
BEFORE CREATE ON DATABASE
ou
BEFORE CREATE OR ALTER OR DROP ON SCHEMA (ou DATABASE).

Pois se uma dessas funcionar certinho, basta colocar uma condição na trigger.

[stcoutinho]

Oi afonso.augusto,

Acho que você não precisaria criar "dois schemas distintos" para proteger os objetos do sistema.

Você poderia fazer o seguinte:

A) Se você não tiver um usuário com permissão de DBA, crie um (ex: DBA_X).
B) No DBA_X, crie uma trigger para bloqueio de comandos DDL no schema "X"

Selecionar tudo

CREATE OR REPLACE TRIGGER TGR_SCHEMA_X_DDL
BEFORE CREATE OR ALTER OR DROP OR RENAME ON X.SCHEMA
DECLARE
BEGIN
     RAISE_APPLICATION_ERROR (-20905,'COMANDOS DDL não PERMITIDOS AQUI');
END TGR_SCHEMA_X_DDL;

C) Nesta condição, nenhum usuário que se conecte como "X" poderá alterar objetos neste schema (comandos DDL);
D) Mas você - como usuário DBA_X - poderá efetuar alterações no schema X sem problemas, bastando para isso informar o OWNER dos objetos que está modificando/criando/excluindo. E esta trigger estaria protegida dos desenvolvedores, pois estaria em seu schema DBA_X.

Agora .. este tipo de trigger pode ser "muito radical" para seu dia-a-dia.

No entanto, você pode tornar este controle mais flexível. Para isso, você poderia criar uma tabela de objetos no schema DBA_X, onde seriam listados todos os objetos que os desenvolvedores poderiam alterar via comandos DDL. A trigger anterior poderia então ser alterada, para verificar se o comando DDL - que está sendo executado no schema X - está na "lista de exceções" nesta tabela.

Acho que fazendo assim, você facilita a administração de seus sistemas, pois tem um único usuário no DESENVOLVIMENTO e não precisa ficar se preocupando em bloquear senhas, discutir com desenvolvedores, etc.

Na sua ideia anterior, eu não sei se você conseguiria executar comandos de SINONIMOS e GRANTS dentro deste tipo de trigger, pois elas envolvem COMMITs explícitos. Posso estar enganado quanto à isso, é claro.

Espero ter podido ajudar você.

Abs

Sergio Coutinho