第17期 Oracle通过触发器监控审计执行的ddl语句

我们可以在不同的层级上进行审计，比如在数据库级别，审计数据库上所有ddl操作，我们也可以在schema（这里是'YOUR_SCHEMA_NAME'）上审计每个DDL操作。1.数据库级别DDL审计2.schema级别DDL审计以下是创建在schema级别DDL触发器的示例代码：

CREATE OR REPLACE TRIGGER ddl_audit_trigger
AFTER CREATE ON SCHEMA
BEGIN
  IF ORA_DICT_OBJ_OWNER = 'YOUR_SCHEMA_NAME' THEN
    INSERT INTO ddl_audit_table (ddl_type, ddl_timestamp, ddl_sql)
    VALUES (ORA_DICT_OBJ_NAME, SYSTIMESTAMP, ORA_DICT_OBJ_SQL);
  END IF;
END;
/

在这个示例中，ddl_audit_trigger 是触发器的名称，它在指定的schema（这里是'YOUR_SCHEMA_NAME'）上的每个DDL操作后激活。ddl_audit_table 是用来记录审计信息的表，你需要预先创建这个表，它至少包含 ddl_type（DDL操作的类型）、ddl_timestamp（操作的时间戳）和 ddl_sql（执行的DDL语句）这三个字段。请注意，这个触发器只能用于监控指定schema内的DDL操作。如果你需要监控整个数据库的DDL操作，你可能需要修改触发器的定义，使其在更高的数据库层面上触发。确保你有足够的权限来创建触发器和审计表，并且在运行这些代码之前，替换'YOUR_SCHEMA_NAME'和ddl_audit_table为实际的schema名称和表名称。具体案例：1.创建记录存储DDL语句的表

create table 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)
);

2.创建捕获DDL语句的触发器

create or replace trigger trg_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 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;
/

3.检查创建审计DDL的触发器是否成功，并且是生效状态。

col object_name for a20
select t.object_name, t.object_type, t.status, t.last_ddl_time
  from dba_objects t
 where t.object_type = 'TRIGGER'
   and t.object_name = 'TRG_AUDIT_DDL';

4.查看并确认隐藏参数_system_trig_enabled 为 true：

col KSPPINM for a30
col KSPPSTVL for a20
col KSPPDESC for a50
SELECT ksppinm, ksppstvl, ksppdesc
  FROM x$ksppi x, x$ksppcv y
 WHERE x.indx = y.indx
   AND ksppinm = '_system_trig_enabled';

5.创建创建测试账户pxboracle 进行测试

create user pxboracle identified by 123456;
grant connect,resource to pxboracle;
alter user pxboracle quota unlimited on users;
conn pxboracle/123456;
create table audit_ddl_test(id number,name varchar2(20));
insert into audit_ddl_test values(1,'pxboracle');
commit;
truncate table audit_ddl_test;
alter table audit_ddl_test add (insert_date date default(sysdate));
desc audit_ddl_test;
drop table audit_ddl_test;
conn / as sysdba
col OPERTIME for a30
col ip for a10
col hostname for a10
col OPERATION for a10
col OBJECT_TYPE for a10
col OBJECT_NAME for a15
select * from audit_ddl where db_schema='PXBORACLE';

6.验证ddl审计情况