Menu de navegação

[SusuParticipante]

Gostaria de uma ajuda para identificar o problema da minha trigger
A trigger de Schema ela consegue inedtificar as palavras reservadas
ora_sysevent,ORA_DICT_OBJ_TYPE.
Eu consigo criar a trigger porem ela está com o status de invalido

CREATE OR REPLACE TRIGGER admdados.TR_B_DDL_DATABASE
BEFORE CREATE OR DROP OR ALTER on admdados.SCHEMA
DECLARE
sql_text ora_name_list_t; /* Variável que recebe a linha de comando em formato binário */
v_stmt CLOB; /* Variável com a linha de comando após conversão */
n number(8); /* variável que recebe o tamanho da linha de comando (para o comando FOR) */
v_erro VARCHAR2(4000); /* Variável que recebe o erro ocorrido */
valor number(8); /* variável que recebe o número de comandos ALTER. */
oper_invalida exception;
VALIDA_COMANDO number(8); /* variável que recebe o texto para o comando ALTER */
BEGIN
IF ora_login_user NOT IN (SYS, SYSTEM, DBSNMP,CONTROLEACESSO_PRODUCAO,SAT,USER_COPO,CARGA,SYSMAN,ADMDADOS)
THEN
IF ora_sysevent <> ‘ANALYZE’
THEN /* Esta rotina só funciona a partir da versao 9i */
n := ora_sql_txt(sql_text);
FOR i IN 1..n
LOOP
v_stmt := (v_stmt || sql_text(i));
END LOOP;
v_stmt := TRIM(SUBSTR(v_stmt,1,3998)); /* Campo varchar – Limitado em 4000 caracteres */
END IF;
IF ora_sysevent IN (‘CREATE’, ‘DROP’)
THEN
IF ora_dict_obj_type NOT IN (‘ROLLBACK SEGMENT’, ‘TABLESPACE’,’PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’SUMMARY’)/*’PACKAGE’, ‘PROCEDURES’, ‘FUNCTION’, ‘VIEW’*/
THEN
raise oper_invalida;
END IF;
ELSIF ora_sysevent = ‘ALTER’
THEN
IF ora_dict_obj_type NOT IN (‘ROLLBACK SEGMENT’, ‘TABLESPACE’,’PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’SUMMARY’)
THEN /* Para não registrar alterações de segmento de rollback e tablespace e etc… */
IF ORA_DICT_OBJ_TYPE = ‘TABLE’
THEN
VALIDA_COMANDO:=INSTR(v_stmt,’CACHE’);
IF VALIDA_COMANDO = 0
THEN
VALIDA_COMANDO:= INSTR(v_stmt,’CONSTRAINT’);
IF VALIDA_COMANDO = 0
THEN
VALIDA_COMANDO:= INSTR(v_stmt,’TRIGGER’);
IF VALIDA_COMANDO = 0
THEN
raise oper_invalida;
END IF;
END IF;
END IF;
ELSIF ORA_DICT_OBJ_TYPE = ‘VIEW’
THEN
raise oper_invalida;
ELSIF ORA_DICT_OBJ_TYPE = ‘TRIGGER’
THEN
VALIDA_COMANDO:= INSTR(v_stmt,’TR_B_DDL_ALTER_TRIGGER’);
IF VALIDA_COMANDO = 1
THEN
raise oper_invalida;
END IF;
END IF;
END IF;
END IF;
END IF;
— Incluir esta parte para caso dê algum problema, será registrado o log sem a linha de comando.
COMMIT;

EXCEPTION
WHEN oper_invalida
THEN
RAISE_APPLICATION_ERROR(-20500, ‘Acao nao permitida! Abra uma Solicitação na Arquitetura’);
WHEN OTHERS
THEN
v_erro := ‘Erro ocorrido: ‘ || sqlerrm;
v_erro := TRIM(SUBSTR(v_erro,1,4000));

— Insere o log sem a linha de comando
INSERT INTO admdados.HISTORICO_DDLS (AUHD_NM_USUARIO_ORACLE,AUHD_NM_USUARIO_SO,AUHD_DT_EVENTO,AUHD_DS_EVENTO,AUHD_DS_OBJETO,AUHD_NM_OBJETO,AUHD_NM_OWNER_OBJETO,AUHD_DS_COMANDO)
VALUES (ora_login_user,sys_context(‘USERENV’,’OS_USER’),sysdate,ora_sysevent,ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner,v_erro);
END;

[IshiiParticipante]

Olá,

Qual exatamente é o erro?

No prompt do SQL digite

SQL> show errors trigger admdados.TR_B_DDL_DATABASE;

E coloque o resultado aqui.

[]s Ishii

[SusuParticipante]

show errors trigger admdados.TR_B_DDL_DATABASE
LINE/COL ERROR

---

10/2 PL/SQL: Statement ignored
10/28 PLS-00201: identifier ‘SYS’ must be declared

[SusuParticipante]

Ishii,
valeu pela dica!!! Ficou faltando as aspas simples nos usuários.
Obrigada pela ajuda!!
o comando show errors trigger nomeTriggers já foi para a minha biblioteca!
vlw 😀

[IshiiParticipante]

Olá,

Melhor ir para a biblioteca o comando completo:

show errors ;

Pois ele serve para triggers, views, procedures, functions, packages etc.

[]s Ishii

[SusuParticipante]

Ishii,
ve se vc consegue me ajudar. Eu criei as minhas triggers de auditoria porém elas não executam. Não tem erros, não estão invalidas.

SQL> show errors trigger admdados.TR_B_DDL_DATABASE;
NÒo hß erros.

Porém qdo eu faço um CREATE OR DROP OR ALTER. Essa trigger nao dispara a mensagem e nem loga as informações na minha tabela.

Eu criei uma outra trigger muito simples so para teste, que dispara uma mensagem e essa tb não funciona e não contem nenhum erro.

CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON admdados.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => ‘Cannot drop object’);
END;
/

Eu tenho role de dba isso pode interferir alguma coisa. Eu criei essas triggers em outro esquema e depois conectei com um outro usuário fiz um create table, insert, e drop e não realizou o gatilho.

Obrigada,
Suzana

[IshiiParticipante]

Olá,

As triggers tipo on .SCHEMA tem um problema quando usadas dessa forma, somente serão disparadas quando o for o mesmo conectado, por exemplo:

Se você conectar com o user admdados, todos os comandos CREATE, ALTER e DROP serão registrados, porém….

Se você usar um usuário system por exemplo e efetuar o comando:

alter table admdados.tabela disable all triggers;

Esse comando não ficará nas suas tabelas, pois quem efetivamente está conectado é o user SYSTEM e ele faz a alteração na tabela do owner.

O ideal para essa sua situação é uma trigger on database, seria mais ou menos assim:


create or replace trigger admdados.tr_b_ddl_database
before create or drop or alter on database


E para controlar apenas o owner admdados use a opção ora_dict_obj_owner dentro do primeiro IF

IF ora_login_user NOT IN ('SYS', 'SYSTEM', 'DBSNMP','CONTROLEACESSO_PRODUCAO','SAT','USER_COPO','CARGA','SYSMAN','ADMDADOS') and
ora_dict_obj_owner = 'ADMDADOS' then ....

Com isso, qualquer usuário Oracle que conectar e tentar criar,alterar ou dropar já dispara a trigger (dentro das condições dela é claro).

Tente e qualquer coisa coloque aqui.

[]s Ishii
ps: se precisar me add no msn

[SusuParticipante]

Ishii,
EU CRIEI A TRIGGER NO MEU ESQUEMA ADMDADOS. E TAMBÉM CRIEI UMA TABELA E DEI UM INSERT SO PARA VE SE FUNCIONA. MESMO ASSIM NADA ACONTECE. AQUI O MSN É BLOQUEADO!

create table admdados.TESTE2(ide integer);
COMMIT;

CREATE OR REPLACE TRIGGER ADMDADOS.drop_trigger
before create or drop or alter on database
BEGIN
INSERT INTO ADMDADOS.TESTE2 VALUES(1000);
RAISE_APPLICATION_ERROR (
num => -20000,
msg => ‘Cannot drop object’);
END;
/
COMMIT;
VLW PELA AJUDA,
SUZANA 🙂

[SusuParticipante]

ACHO QUE ACHEI O PROBLEMA.
http://download.oracle.com/docs/cd/B141 … m#i2153503

TENHO Q CRIAR CONECTADO COMO SYSTEM. EU SÓ TENHO ROLE DE DBA. PARA MIM A ROLE DE DBA DAVA ACESSO A TUDO.

Prerequisites
Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

•To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.

•To create a trigger in any schema on a table in any schema, or on another user’s schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.

•In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

[IshiiParticipante]

Olá,

O seu usuário admdados tem o grant administer database trigger ?

Comando:

grant administer database trigger to ;

E depois de criar a trigger, mesmo que faça um novo create table não vai nada para a tabela de auditoria?

[]s Ishii
ps: skype fabio.ishii
gtalk: ishii.fabio(a)gmail.com

[SusuParticipante]

EU ME DEI O GRANT DE ADMINISTRADOR DE TRIGGER!! MESMO ASSIM NAO CONSIGO EXECUTAR A TRIGGER! SE EU TIVESSE A SENHA DE SYSTEM EU PODERIA ATE TESTAR, SO POSSO FAZER ISSO EM CASA.

SQL> SELECT * FROM DBA_SYS_PRIVS
2 WHERE GRANTEE LIKE ‘ADMDADOS%’;

GRANTEE PRIVILEGE ADM

---

ADMDADOS ADMINISTER DATABASE TRIGGER NO
ADMDADOS CREATE TABLE NO
ADMDADOS UNLIMITED TABLESPACE NO
ADMDADOS CREATE SESSION NO

create table admdados.TESTE2(ide integer);
COMMIT;

CREATE OR REPLACE TRIGGER drop_trigger
before create or drop or alter on database
BEGIN
INSERT INTO ADMDADOS.TESTE2 VALUES(1000);
RAISE_APPLICATION_ERROR (
num => -20000,
msg => ‘Cannot drop object’);
END;
/
COMMIT;

create table admdados.TESTE3(ide integer);
COMMIT;
DROP TABLE admdados.TESTE3;
COMMIT;

SELECT * FROM admdados.TESTE2

MESMO ASSIM NADA! EU TE MANDEI UM CONVITE PELO GTALK.

[IshiiParticipante]

Olá,

Acho que é grants mesmo…

Faltou

grant create any trigger

[]s Ishii

[SusuParticipante]

Segue o motivo:
alter system set “_system_trig_enabled” = true scope=spfile;

[Autor]

Posts