data object audit

客户提出了一个需求。想对一个表做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;