Oracle 10g审计(audit)实验(四)
3 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 ROWS SELECTED.
SQL> UPDATE EMP1 SET ENAME='MAOMI' WHERE EMPNO=7788;
1 ROW UPDATED.
SQL> DELETE FROM EMP1 WHERE ROWNUM <2;
1 ROW DELETED.
SQL> COMMIT;
COMMIT COMPLETE.
SQL> CONN / AS SYSDBA
CONNECTED.
SQL> COL SES_ACTIONS FOR A20
SQL> SELECT USERNAME,USERHOST,TIMESTAMP,SES_ACTIONS,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;
USERNAME USERHOST TIMESTAMP SES_ACTIONS OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------
SCOTT SUN10G 2012-09-15 10:09:56 DEPT1 CREATE TABLE
SCOTT SUN10G 2012-09-15 10:20:36 ---S-----SS----- EMP1 SESSION REC
CONN SCOTT/TIGER
SQL> UPDATE EMP1 SET ENAME='SCOTTMAOMIMAOMI' WHERE EMPNO=7788;
UPDATE EMP1 SET ENAME='SCOTTMAOMIMAOMI' WHERE EMPNO=7788
*
ERROR AT LINE 1:
ORA-12899: VALUE TOO LARGE FOR COLUMN "SCOTT"."EMP1"."ENAME" (ACTUAL: 15, MAXIMUM: 10)
CONN / AS SYSDBA
SQL> SELECT USERNAME,USERHOST,TIMESTAMP,SES_ACTIONS,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;
USERNAME USERHOST TIMESTAMP SES_ACTIONS OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------
SCOTT SUN10G 2012-09-15 10:09:56 DEPT1 CREATE TABLE
SCOTT SUN10G 2012-09-15 10:20:36 ---S-----SS----- EMP1 SESSION REC
SCOTT SUN10G 2012-09-15 10:30:10 ----------F----- EMP1 SESSION REC
SCOTT SUN10G 2012-09-15 10:26:42 ---------SB----- EMP1 SESSION REC
-----------S 表示会话操作成功，F 表示会话中的操作失败，B 表示会话中的操作既有成功，也有失败。
5、精细审计FINE GRAINED AUDITING (FGA)
根据内容监控数据访问
对 SELECT、INSERT、UPDATE、DELETE 和 MERGE 操作进行审计
可以对表或视图甚至是一个活多个列进行审计
可能会调用存储过程
使用 DBMS_FGA 包进行管理
建立审计策略
SQL> EXEC DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA=>'SCOTT',-
> OBJECT_NAME=>'EMP1',POLICY_NAME=>'CHK_EMP',-
> AUDIT_CONDITION=>'DEPTNO=20',AUDIT_COLUMN=>'SAL',-
> STATEMENT_TYPES=>'UPDATE,SELECT');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
CONN SCOTT/TIGER
SQL> UPDATE EMP1 SET DEPTNO=10 WHERE EMPNO=7788;
1 ROW UPDATED.
SQL> UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788;
1 ROW UPDATED.
SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;
3 ROWS UPDATED.
SQL> COMMIT;
COMMIT COMPLETE.
SQL> SET LINESIZE 80
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