DDL语句审计触发器

创建oracle ddl触发器记录所有ddl操作

---- 存储DDL语句的表         
create table sys.my_audit_ddl
(
opertime timestamp PRIMARY KEY,
ip varchar2(20),
hostname varchar2(30),
operation varchar2(30),
object_type varchar2(30),
object_name varchar2(30),
sql_stmt clob,
db_schema varchar2(30)
);

---- 捕获DDL语句的触发器
CREATE OR REPLACE TRIGGER sys.trg_my_audit_ddl
  after ddl ON DATABASE
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  n        NUMBER;
  stmt     clob := NULL;
  sql_text ora_name_list_t;
BEGIN
  n := ora_sql_txt(sql_text);
  FOR i IN 1 .. n LOOP
    stmt := stmt || sql_text(i);
  END LOOP;

  INSERT INTO sys.my_audit_ddl
    (opertime, ip, hostname, operation, object_type, object_name, sql_stmt,db_schema)
  VALUES
    (systimestamp,
     sys_context('userenv', 'ip_address'),
     sys_context('userenv', 'terminal'),
     ora_sysevent,
     ora_dict_obj_type,
     ora_dict_obj_name,
     stmt,
     user  
   );

  COMMIT;
END;
/
 
-- drop  procedure test_proc;
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
  dbms_output.put_line('这是一个测试中文DDL过程');
END;
/

CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
  dbms_output.put_line('这是一个测试中文DDL过程');
END;
/

-----查看结果----
select * from sys.my_audit_ddl;