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)