oracle数据库审计(三)

SQL>
eg：

SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(
3 OBJECT_SCHEMA=>'SCOTT',
4 OBJECT_NAME=>'DEPT',
5 POLICY_NAME=>'EMP_AUDIT',
6 AUDIT_COLUMN=>'DEPTNO',
7 STATEMENT_TYPES=>'UPDATE');
8 END;
9 /

PL/SQL procedure successfully completed.

SQL>
SQL> CONN SCOTT/root
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> update dept set deptno=50 where deptno=40;

1 row updated.
SQL> select sql_text from dba_fga_audit_trail;

SQL_TEXT
------------------------------
update dept set deptno=50 wher
e deptno=40

SQL>
SQL>
另外查看fga信息可以通过dba_fga_audit_trail进行查看，查看权限策略信息可以查看dba_audit_policies;
SQL> begin
2 dbms_fga.disable_policy(
3 object_schema=>'SCOTT',
4 object_name=>'DEPT',
5 policy_name=>'EMP_AUDIT');
6 END;
7
8 /

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,ENABLED FROM DBA_AUDIT_POLICIES;

OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME ENA
------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ ---
SCOTT DEPT SYS EMP_AUDIT NO

SQL> BEGIN
2 DBMS_FGA.ENABLE_POLICY(
3 object_schema=>'SCOTT',
4 object_name=>'DEPT',
5 policy_name=>'EMP_AUDIT');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,POLICY_COLUMN,ENABLED FROM DBA_AUDIT_POLICIES;

OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_COLUMN ENA
------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---
SCOTT DEPT SYS EMP_AUDIT DEPTNO YES

SQL>

SQL> BEGIN
2 DBMS_FGA.DROP_POLICY(
3 object_schema=>'SCOTT',
4 object_name=>'DEPT',
5 policy_name=>'EMP_AUDIT');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> COL OBJECT_NAME FOR A20
SQL> SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_OWNER,POLICY_NAME,POLICY_COLUMN,ENABLED FROM DBA_AUDIT_POLICIES;

no rows selected

SQL>

另外对于audit_trail设置为os，需要在操作系统上进行日志设置。参考：http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams016.htm#REFRN10263

That's all;