Oracle開啟審計，記錄登錄用戶的所有操作

1、以DBA登錄Oracle
 
# su - oracle
$ sqlplus /nolog
SQL> conn / as sysdba
 
2、查看當前審計設置
 
SQL> show parameter audit;
 
參數說明

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.
The AUDIT_SYS_OPERATIONS static parameter
enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges,
including the SYS user. All audit records are written to the OS audit trail.
The AUDIT_FILE_DEST parameter
specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used.
It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
3、修改audit參數，開啟審計

SQL> alter system set audit_trail=db_extended scope=spfile;
注意，如果audit_trail=db，不記錄SQL_BIND和SQL_TEXT
 
4、重啟數據庫

SQL> shutdown immediate;
SQL> startup;

################### 

###################

###################
5、測試基于用戶的審計打開
 
創建用戶AUDIT_TEST
 
$ sqlplus /nolog
SQL> conn / as sysdba
SQL> audit all by audit_test by access;
SQL> audit select table, update table, insert table, delete table by audit_test by access;
SQL> audit execute procedure by audit_test by access;
 
分別對應以下三種：
DDL (CREATE, ALTER & DROP of objects)
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
SYSTEM EVENTS (LOGON, LOGOFF etc.)
SQL> conn audit_test/password
SQL> create table test(id  number);
SQL> insert into test(id) values (1);
SQL> insert into test(id) values (2);
SQL> update test set id = 3 where id = 1;
SQL> select * from test;
SQL> delete from test;
SQL> commit;
SQL> drop table test;
SQL> select view_name from dba_views where  view_name like 'dba%audit%' order by view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
視圖說明:
1. SYS.AUD$
審計功能的底層視圖,如果需要對數據進行刪除,只需要對aud$視圖進行刪除既可,其他視圖里的數據都是由aud$所得.
2. DBA_AUDIT_EXISTS
列出audit not exists和audit exists產生的審計跟蹤,我們默認的都是audit exists.
3. DBA_AUDIT_TRAIL
可以在里面查處所有審計所跟蹤的信息.
4. DBA_AUDIT_OBJECT
可以查詢所有對象跟蹤信息.(例如,對grant,revoke等不記錄),信息完全包含于dba_audit_trail
5. DBA_AUDIT_SESSION
所得到的數據都是有關logon或者logoff的信息.
6. DBA_AUDIT_STATEMENT
列出grant ,revoke ,audit ,noaudit ,alter system語句的審計跟蹤信息.
7. DBA_PRIV_AUDIT_OPTS
通過系統和由用戶審計的當前系統特權
8. DBA_OBJ_AUDIT_OPTS
可以查詢到所有用戶所有對象的設計選項
9. ALL_DEF_AUDIT_OPTS
10. AUDIT_ACTIONS
可以查詢出在aud$等視圖中actions列的含義
11. SYSTEM_PRIVILEGE_MAP
可以查詢出aud$等視圖中priv$used列的含義(注意前面加'-')
常用視圖：
DBA_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
查看審計內容，主要字段：os_username, userhost, timestamp, owner，sql_bind, sql_text
SQL> select * from dba_audit_trail where  owner = 'AUDIT_TEST' order by timestamp;
注意：owner的值必須大寫，例如 owner = 'AUDIT_TEST'
-------------------------------------------------------------------------------
關閉審計
-------------------------------------------------------------------------------
SQL> alter system set audit_trail=none scope=spfile;

########################

########################

###對該表做各種DML操作###
用scott用戶登錄

SQL> conn scott/123     

SQL> create table t_test as select * from emp;

SQL> update t_test set emp1='111';

                  *

第 1 行出現錯誤:

ORA-00904: "EMP1": 標識符無效    

SQL> delete from t_test where rownum=1;

已刪除 1 行。

SQL> commit;

####查詢審計信息

SQL> select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT

SQL> from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;

或者

SQL> select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL

SQL> where OBJECT_NAME='T_TEST'

SQL> and STATEMENT_TYPE in ('INSERT','UPDATE','DELETE');

---結果：

SQL> /

EXTENDED_TIMESTAMP                       SESSION_ID SQL_TEXT

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

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

14-8月 -12 04.14.45.187000 下午 +08:00       190125 update t_test set emp1='111'

14-8月 -12 04.26.02.968000 下午 +08:00       190125 delete from t_test where rownum=1

注意：審計一般只用于對普通用戶操作，一般不審計SYS用戶
對于windows系統，對sys用戶的審計信息并不存在AUDIT_FILE_DEST參數指定的目錄里，而是在windows的事件管理器中。
---------------------------------------------------------------------------------------------------------------------
###另外通過細粒度審計FGA也可以實現上述審計：
###用法創建審計策略：

Syntax

DBMS_FGA.ADD_POLICY(

   object_schema   VARCHAR2,

   object_name     VARCHAR2,

   policy_name     VARCHAR2,

   audit_condition VARCHAR2,

   audit_column    VARCHAR2,

   handler_schema  VARCHAR2,

   handler_module  VARCHAR2, 

   enable          BOOLEAN  );
###刪除審計策略：

DBMS_FGA.DROP_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

###啟用審計策略：

DBMS_FGA.ENABLE_POLICY(

   object_schema  VARCHAR2 := NULL,

   object_name    VARCHAR2,

   policy_name    VARCHAR2,

   enable         BOOLEAN := TRUE);

###禁用審計策略：

DBMS_FGA.DISABLE_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

 
###首先，創建審計策略

SQL> conn /as sysdba

已連接。

SQL> begin

  2  dbms_fga.add_policy

  3  (

  4  object_schema=>'SCOTT',object_name=>'T_TEST',

  5  policy_name=>'Test_audit'

  6  );

  7  end;

  8  /

PL/SQL 過程已成功完成。

SQL> conn scott/tigger

###進行查詢
SQL> select ename from t_test;

###使用SYS登錄進行查詢，

SQL> select statement_type,SQL_TEXT from dba_fga_audit_trail;

STATEME SQL_TEXT

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

SELECT  select ename from t_test

 ------------
注意： 經過測試發現審計到的SQL語句存在著大小寫2種格式。
直接執行的SQL語句，是什么樣的語句，審計到的也就是什么樣。
在存儲里執行的語句，審計到的全是大寫的語句。
存儲里動態執行的語句，是什么樣的語句，審計到的也就是什么樣的語句。

BEGIN

EXECUTE immediate 'delete FrOm emp WHERE ROWNUM=1';

END;

###審計到的就是
 DELETE delete FrOm emp WHERE ROWNUM=1;