Oracle 10g审计(audit)实验(五)
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
SQL> COL SQL_TEXT FOR A45
SQL> SET LINESIZE 120
SQL> COL DB_USER FOR A15
SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20
--------------------精细审计结果存放到FGA_LOG$的基表里，通过DBA_FGA_AUDIT_TRAIL 查看。
--------------------清除精细审计结果
SQL> SELECT COUNT(*) FROM FGA_LOG$;
COUNT(*)
----------
3
SQL> DELETE FROM FGA_LOG$;
3 ROWS DELETED.
--------------------禁止精细审计
SQL> EXEC DBMS_FGA.DISABLE_POLICY(-
> OBJECT_SCHEMA=>'SCOTT',OBJECT_NAME=>'EMP1',-
> POLICY_NAME=>'CHK_EMP');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
CONN SCOTT/TIGER
SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;
3 ROWS UPDATED.
SQL> COMMIT;
COMMIT COMPLETE.
CONN / AS SYSDBA
SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20
------------------启用精细审计
SQL> EXEC DBMS_FGA.ENABLE_POLICY(-
> OBJECT_SCHEMA=>'SCOTT',OBJECT_NAME=>'EMP1',-
> POLICY_NAME=>'CHK_EMP');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
CONN SCOTT/TIGER
SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;
3 ROWS UPDATED.
SQL> COMMIT;
COMMIT COMPLETE.
SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20
SCOTT 2012-09-15 11: