oracle 10g 细粒度审计

ORACLE 10g在细粒度审计（FGA）方面有了很大的扩展。9i的细粒度审计只支持SELECT语句，而10g提供了对DML的支持（支持Insert、Update和Delete，Merge不是一个独立的DML语句）。

10g的细粒度审计不仅仅能够对一个相关字段进行审计，可以对多个字段进行审计。在audit_column_opts中可以设置dbms_fga.all_columns或者dbms_fga.any_columns。

审计日志可以把SQL文本和绑定变量写入LOB字段。如果审计的开销过大，可以关闭写入LOB的信息。

以下是一个10G DBMS_FGA.ADD_POLICY的例子，可以看出和9i的细粒度审计有很大的不同：

      dbms_fga.add_policy ( object_schema => 'PIET',  object_name => 'EMP', 

                            policy_name => 'MYPOLICY1', audit_condition => NULL,

                            audit_column => 'SALARY,COMMISSION_PCT',

                            audit_column_opts => DBMS_FGA.ALL_COLUMNS,

                            audit_trail => DBMS_FGA.DB_EXTENDED,

                            statement_types => 'INSERT, UPDATE');

     下面一个例子演示了对INSERT的审计过程：

 

     SQL> connect piet/piet

     Connected.

     SQL> CREATE TABLE EMP (

          EMPNO         NUMBER(4) NOT NULL,

          ENAME         VARCHAR2(10),

          JOB           VARCHAR2(9),

          MGR           NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),

          HIREDATE      DATE,

          SAL           NUMBER(7,2),

          COMM          NUMBER(7,2),

          DEPTNO        NUMBER(2) NOT NULL,

          CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

 

     Table created.

 

     SQL> INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NU;

     1 row created.

     SQL> grant all on emp to miller;

     Grant succeeded.

      SQL> conn system/manager   

      SQL> execute sys.DBMS_FGA.ADD_POLICY(-

             object_schema   => 'PIET', -

             object_name     => 'EMP', -

             policy_name     => 'mypolicy1', -

             audit_condition => 'sal < 1000', -

             audit_column    => 'comm', -

             enable          => TRUE,  -

             statement_types => 'INSERT');

 

     PL/SQL procedure successfully completed.

 

     SQL> select * from DBA_AUDIT_POLICY_COLUMNS ;

 

     OBJECT_SCHEMA                  OBJECT_NAME                                       

     ------------------------------ ------------------------------                   

     POLICY_NAME                    POLICY_COLUMN                                    

     ------------------------------ ------------------------------                    

     PIET                           EMP                                              

     MYPOLICY1                      COMM             

 

     SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,

                 POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL

          from DBA_AUDIT_POLICIES ;

 

     OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA SEL INS UPD DEL

     ------------- ----------- ----------- ----------- ------------- --- --- --- --- ---                                                                    

     PIET          EMP         MYPOLICY1   sal < 1000  COMM          YES NO  YES NO  NO

 

     SQL> conn miller/miller

     Connected.

    以下的例子是一个插入一条sal小于1000的记录，符合审计的条件：

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

          VALUES(1000, 'SAM', 800, 15, 10);

     1 row created.

    以下的例子插入一条sal大于1000的记录，不符合审计的条件：

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO) 

          VALUES(3000, 'TOM', 20000, 1000, 20);

     1 row created.

 以下的插入语句不包含审计的字段，不进行审计：

     SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL,DEPTNO) 

          VALUES (1111, 'RAMA', 98,30);

     1 row created.

     SQL> commit;

     Commit complete.

    下面来验证一下审计的结果：

 conn system/manager

     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,

                 POLICY_NAME,SQL_TEXT

          from dba_fga_audit_trail ;

 

 

     DB_USER  SCHEMA   OBJECT POLICY_NAME

     -------- -------- ------ ------------------------------

     SQL_TEXT

     -------------------------------------------------------

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(1000, 'SAM', 800, 15, 10)

 以下的例子演示一个包含函数（sysdate,uid,user,round等）的审计：

         execute dbms_fga.drop_policy( object_schema => 'PIET',-

                              object_name => 'EMP',-

                              policy_name => 'mypolicy1');

      SQL> execute sys.DBMS_FGA.ADD_POLICY(-

             object_schema   => 'PIET', -

             object_name     => 'EMP', -

             policy_name     => 'mypolicy1', -

             audit_condition => 'round(sal, -2) >= 3000 ', -

             audit_column    => 'comm', -

             enable          => TRUE,  -

             statement_types => 'INSERT');

      PL/SQL procedure successfully completed.

 

     SQL> connect miller/miller

     Connected.

 

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

          VALUES(5000, 'RUDY', 2979, 15, 10);

     1 row created.

 

     SQL> commit;

     Commit complete.

    现在来验证一下审计结果： 

         conn system/manager

      SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,

                 POLICY_NAME,SQL_TEXT

          from dba_fga_audit_trail ;

      DB_USER  SCHEMA   OBJECT POLICY_NAME

     -------- -------- ------ ------------------------------

     SQL_TEXT

     -------------------------------------------------------

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(1000, 'SAM', 800, 15, 10)

 

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(5000, 'RUDY', 2979, 15, 10)

 

 

以下是一个使用user函数的审计：

 

          execute dbms_fga.drop_policy( object_schema => 'PIET',-

                              object_name => 'EMP',-

                              policy_name => 'mypolicy1');

 

 

     SQL> execute sys.DBMS_FGA.ADD_POLICY(-

             object_schema   => 'PIET', -

             object_name     => 'EMP', -

             policy_name     => 'mypolicy1', -

             audit_condition => 'ename=USER ', -

             audit_column    => 'comm', -

             enable          => TRUE,  -

             statement_types => 'INSERT');

      PL/SQL procedure successfully completed.

      SQL> connect miller/miller

     Connected.

     SQL> conn piet/piet

     Connected.

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

          VALUES(89, 'MILLER', 800, 1000, 10);

      1 row created.

      SQL> commit;

     Commit complete.

 

     SQL> conn miller/miller

     Connected.

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

          VALUES(69, 'MILLER', 700, 700, 10);

     1 row created.

      SQL> commit;

     Commit complete.

    验证一下刚才的审计结果：

          con system/manager

      SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,

                 POLICY_NAME,SQL_TEXT

          from dba_fga_audit_trail ;

      DB_USER  SCHEMA   OBJECT POLICY_NAME

     -------- -------- ------ ------------------------------

     SQL_TEXT

     -------------------------------------------------------

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(1000, 'SAM', 800, 15, 10)

 

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(5000, 'RUDY', 2979, 15, 10)

 

     MILLER   PIET     EMP    MYPOLICY1

     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)

     VALUES(69, 'MILLER', 800, 1000, 10)