客户提出了一个需求。想对一个表做audit。 本来这是非常简单的一个case,因为oracle自带的 audit功能就可以非常方便的实现。 实现的方式如下:
BEGIN DBMS_FGA.ADD_POLICY( object_schema => 'SCOTT', object_name => 'TEST', policy_name => 'chk3', statement_types => 'insert,update,delete'); END;
这种方式激活的是FGA(fine granulate audit),可以对一个表进行非常精细的审计,审计结果存在 dba_fga_audit_trail这个表中。表结构如下:
SQL> desc dba_fga_audit_trail Name Null? Type ----------------------------------------- -------- ---------------------------- SESSION_ID NOT NULL NUMBER TIMESTAMP DATE DB_USER VARCHAR2(30) OS_USER VARCHAR2(255) USERHOST VARCHAR2(128) CLIENT_ID VARCHAR2(64) ECONTEXT_ID VARCHAR2(64) EXT_NAME VARCHAR2(4000) OBJECT_SCHEMA VARCHAR2(30) OBJECT_NAME VARCHAR2(128) POLICY_NAME VARCHAR2(30) SCN NUMBER SQL_TEXT NVARCHAR2(2000) SQL_BIND NVARCHAR2(2000) COMMENT$TEXT VARCHAR2(4000) STATEMENT_TYPE VARCHAR2(7) EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE PROXY_SESSIONID NUMBER GLOBAL_UID VARCHAR2(32) INSTANCE_NUMBER NUMBER OS_PROCESS VARCHAR2(16) TRANSACTIONID RAW(8) STATEMENTID NUMBER ENTRYID NUMBER DBID NUMBER
可以看到这里收集到的auditi信息其实已经非常丰富了。但是客户的需求里多了一条,需要client 机器上 client进程的pid。 但是现在的dba_fga_audit_trial这个表中并没有client pid这一列。所以我们需要自己获得。同时我们需要创建一个新的表来存放 audit 结果。
具体过程是先创建 temp_audit表:
SQL> desc temp_audit Name Null? Type ----------------------------------------- -------- ---------------------------- TIMESTAMP DATE DB_USER VARCHAR2(30) OS_USER VARCHAR2(30) USERHOST VARCHAR2(30) CLIENTPID VARCHAR2(10) OBJECT_SCHEMA VARCHAR2(30) OBJECT_NAME VARCHAR2(128) POLICY_NAME VARCHAR2(30) SQL_TEXT NVARCHAR2(2000) OS_PROCESS VARCHAR2(16)
这个先创建的用来存放audit 结果的表里面有一条clientpid是需要自己写SQL去查询的,其它的都可以直接从dba_fga_audit_trial中获得。 我们要考虑的是,怎么让目标表在发生变化的时候会把audit信息写入这个表。很显然,trigger是一个好办法。我们创建一个trigger在目标表上,当目标表被DML语句操作的时候,trigger就去读取dba_fga_audit_trial并且通过一系列SQL来写temp_audit这个表。
我们首先写一个procedure 该procedure会读取dba_fga_audit_trial这个表并且通过一系列SQL得到client pid然后写入temp_audit。 然后我们再写一个trigger来调用这个procedure
create or replace procedure pump_audit_CITOSADMIN as cursor audit_cur is select * from dba_fga_audit_trail where OBJECT_SCHEMA='CITOSADMIN' and OBJECT_NAME='BERTH_APPLICATION' order by TIMESTAMP ; v_clientpid varchar2(12); v_my_audit_count number; v_count number; begin select count(*) into v_my_audit_count from temp_audit where OBJECT_SCHEMA='CITOSADMIN' and OBJECT_NAME='BERTH_APPLICATION' ; v_count :=1; for record_name in audit_cur loop if v_count <= v_my_audit_count then v_count := v_count+1; else select b.process into v_clientpid from v$process a, v$session b where a.addr = b.paddr and a.spid=record_name.OS_PROCESS; insert into temp_audit(TIMESTAMP,DB_USER,OS_USER,USERHOST,CLIENTPID,OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,SQL_TEXT,OS_PROCESS) values(record_name.TIMESTAMP,record_name.DB_USER,record_name.OS_USER,record_name.USERHOST,v_clientpid,record_name.OBJECT_SCHEMA,record_name.OBJECT_NAME,record_name.POLICY_NAME,record_name.SQL_TEXT,record_name.OS_PROCESS); end if; end loop; end;
create or replace trigger audit_obj_CITOSADMIN after insert or update or delete on CITOSADMIN.BERTH_APPLICATION begin pump_audit_CITOSADMIN; end;