Standard Database Auditing

轉自：http://hi.baidu.com/dbaeyes/blog/item/ad1da21b9f2616d6ad6e75bd.html

Enabled through the AUDIT_TRAIL parameter

* NONE: Disables collection of audit records
* DB: Enables auditing with records stored in the database
* OS: Enables auditing with records stored in the operating system audit trail

Can audit:

* Login events
* Exercise of system privileges
* Exercise of object privileges
* Use of SQL statements

Standard Database Auditing

To use database auditing you must first set the non-dynamic parameter AUDIT_TRAIL to point to a storage location for audit records. The normal setting for this parameter is DB, which causes audit records to be stored in the DBA_AUDIT_TRAIL table.

Database auditing can capture information about login events, the exercise of system privileges, and the exercise of object privileges. Audit information can be focused by the user generating the audit event or by the status of the event (successful or not). The following audit command that generates information is probably not required because it is not well focused. This option captures any operation that affects any table:

SQL> AUDIT TABLE;
Audit succeeded.

A better example of an audit command (because it is more narrowly focused) is:

SQL> AUDIT DELETE ON hr.employees WHENEVER SUCCESSFUL;
Audit succeeded.

Specifying Audited Options

* System privilege auditing (nonfocused and focused)
AUDIT select any table, create any trigger;
AUDIT select any table BY hr BY SESSION;

* Object privilege auditing (nonfocused and focused)
AUDIT ALL on hr.employees;
AUDIT UPDATE,DELETE on hr.employees BY ACCESS;

* Session auditing
AUDIT session whenever not successful;

SQL statement auditing: The statement shown in the slide will audit any DDL statement that affects a table including CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and so on. SQL Statement auditing can be focused by username or by success/failure.

SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;

SQL statement auditing: The statement shown in the slide will audit any DDL statement that affects a table including CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and so on. SQL Statement auditing can be focused by username or by success/failure.

SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;

System privilege auditing can be used to audit the exercise of any system privilege (such as DROP ANY TABLE). It can be focused by username or success/failure. By default, each time an audited system privilege is exercised an audit record is generated. You can choose to group those records so that only one record is generated per session (that way if a user updates 100,000 records in a table belonging to another user, you only gather one audit record). If the BY SESSION clause is not specified, the default is BY ACCESS. Consider using the BY SESSION clause to limit the performance and storage impact of system privilege auditing.

Object privilege auditing can be used to audit actions on tables, views, procedures, sequences, directories and user-defined data types. This type of auditing can be focused by success/failure and grouped by session or access. Unlike system privilege auditing, the default grouping is by session so you must implicitly specify BY ACCESS if you want a separate audit trail record generated for each action. （因為測試的時候沒有加 by access, 使得select後的insert沒有被audit，在這裡迷惑了很久，重新仔細的看文件，才發現這段，E文還是不行啊）

The AUDIT SESSION option audits the creation of user sessions. It can be focused by username or by success/failure. This option is unique because it generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session. In many databases it is common to use the AUDIT SESSION (nonfocused) command. In almost all databases you should AUDIT SESSION WHENEVER NOT SUCCESSFUL because this allows you to detect attempts to break into your database

Note: Often your audit options start as nonfocused because you are not sure what type of activity you are looking for. The AUDIT ALL option is a convenient shortcut to audit a broad range of activity.

Viewing Auditing Options

To see which audit options have been selected, use the views listed above.

DBA_STMT_AUDIT_OPTSand DBA_PRIV_AUDIT_OPTScontain only records of statement or privilege audit options that have been specified. DBA_OBJ_AUDIT_OPTS contains one record per auditable object regardless of what object audit options have been specified. The view shows a column for each auditable option. For example, INSERTaudit options are shown in the INScolumn. Audit options are displayed as SUCCESSFUL/NOT SUCCESSFULwith three possible values for each status:

? - Not audited
? S Collect audit records by session
? A Collect audit records by access

SQL> SELECT object_name, object_type, ins, upd
FROM dba_obj_audit_opts WHERE object_name = 'EMPLOYEES'
OBJECT_NAME OBJECT_TY INS UPD

------------ --------- --- ---
EMPLOYEES    TABLE     A/S -/-

簡言之：查詢
select * from dba_obj_audit_opts --object的audit

Viewing Auditing Results

Access to audit records should be tightly controlled because they may contain sensitive information. Usually the task of managing the audit trail is handled by the administrator but if it needs to be delegated the DELETE_CATALOG_ROLE grants permission to delete from the audit trail.

Value-Based Auditing

Database auditing records that inserts, updates, and deletes have occurred on audited objects, but does not capture the actual values that were changed. Value-based auditing extends database auditing, capturing the actual values that have been changed. Value-based auditing leverages database triggers (event-driven PL/SQL constructs).

網轉中文：

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
1、審計初始化引數：在init檔案中新增 AUDIT_TRAIL = DB

2、設定審計
物件審計：AUDIT DELETE,INSERT,UPDATE ON TABLE (BY USER);
SESSION審計：
AUDIT SESSION;
-- 指定使用者
AUDIT SESSION BY (USER1,USER2)
許可權審計：
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOTSUCCESSFUL;
AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;

3、關閉審計NOAUDIT

4、清除審計
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name = 。。。

5、相關檢視查詢,從中可以查詢到相關的資料
STMT_AUDIT_OPTION_MAP-審計選項型別程式碼
AUDIT_ACTIONS-action程式碼
ALL_DEF_AUDIT_OPTS-物件建立時預設的物件審計選項
DBA_STMT_AUDIT_OPTS-當前資料庫系統審計選項
DBA_PRIV_AUDIT_OPTS-許可權審計選項
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS-物件審計選項
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL-審計記錄
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT-審計物件列表
DBA_AUDIT_SESSION
USER_AUDIT_SESSION-session審計
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT-語句審計
DBA_AUDIT_EXISTS-使用BY AUDIT NOT EXISTS選項的審計
DBA_AUDIT_POLICIES-審計POLICIES
DBA_COMMON_AUDIT_TRAIL-標準審計+精細審計

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

審計是對選定的使用者動作的監控和記錄，通常用於：

u          審查可疑的活動。例如：資料被非授權使用者所刪除，此時安全管理員可決定對該 資料庫的所有連線進行審計，以及對資料庫的所有表的成功地或不成功地刪除進行審計。

u          監視和收集關於指定資料庫活動的資料。例如：DBA可收集哪些被修改、執行了多少次邏輯的I/O等統計資料。

ORACLE支援三種審計型別：

u          語句審計，對某種型別的SQL語句審計，不指定結構或物件。

u          特權審計，對執行相應動作的系統特權的使用審計。

u          物件審計，對一特殊模式物件上的指定語句的審計。

ORACLE所允許的審計選擇限於下列方面：

u          審計語句的成功執行、不成功執行，或者其兩者。

u          對每一使用者會話審計語句執行一次或者對語句每次執行審計一次。

u          對全部使用者或指定使用者的活動的審計。

當資料庫的審計是使能的，在語句執行階段產生審計記錄。審計記錄包含有審計的操作、使用者執行的操作、操作的日期和時間等資訊。審計記錄可存在資料字典表（稱為審計記錄）或作業系統審計記錄中。資料庫審計記錄是在SYS模式的AUD$表中。

設定ORACLE審計

下列步驟可以設定ORACLE的審計功能：

1. 修改引數檔案（init.ora，如果使用伺服器引數檔案使用alter system set = scope=spfile|both，詳情參照1.1節中關於引數檔案的介紹），設定 AUDIT_TRAIL引數，並且重啟資料庫。AUDIT_TRAIL的取值如下：

l         DB/TRUE：啟動審計功能，並且把審計結果存放在資料庫的 SYS.AUD$ 表中

l         OS：啟動審計功能，並把審計結果存放在作業系統的審計資訊中  

l         DB_EXTENDED：具有DB/TRUE的功能，另外填寫AUD$的SQLBIND和SQLTEXT欄位

l         NONE/FALSE：關閉審計功能

         

2.如果設定 AUDIT_TRAIL = OS, 還需要修改引數AUDIT_FILE_DEST。

    如果作業系統支援設定AUDIT_TRAIL=OS，檔案會自動存放在AUDIT_FILE

_DEST所指定的目錄下，並且檔名包含程式的PID。

   比如：

    AUDIT_FILE_DEST = $ORACLE_HOME/rdbms/audit  

    $ ls -l $ORACLE_HOME/rdbms/audit  

   -rw-rw---- 1 ora92    dba        881 Mar 17 09:57 ora_13264.aud

    $ ps -ef|grep 13264

    ora92 13264 13235 0 09:56:43 ? 0:00 oracleV92 (DESCRIPTION=(LOCAL=Y)

    SQL> select spid, program, username from v$process;

    SPID PROGRAM                                 USERNAME

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

   ...

   13264        oracle@frhp11 (TNS V1-V3)                    ora92

    注意：WINDOWS NT不使用AUDIT_FILE_DEST引數，如果使用OS設定，那麼審計資訊會存放在WINDOWS NT的事件日誌裡。下面的章節會有專門的介紹。

   

3. 確認審計相關的表是否已經安裝

SQLPLUS> connect / AS SYSDBA

SQLPLUS> select * from sys.aud$; -- 沒有記錄返回  

SQLPLUS> select * from dba_audit_trail; -- 沒有記錄返回

如果做上述查詢的時候發現表不存在，說明審計相關的表還沒有安裝，需要安裝。

   SQLPLUS> connect / as sysdba

   SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql

審計表安裝在SYSTEM表空間。所以要確保SYSTEM表空間又足夠的空間存放審計資訊。

     

4. 關閉並重啟資料庫

  

5. 設定所需要的審計資訊

下面是一個例子

   SQL> connect system/manager

   SQL> grant audit system to scott;     

   SQL> connect scott/tiger

   SQL> audit session;

停止審計：

   SQL> noaudit session;

將審計相關的表移動到其他表空間

由於AUD$表等審計相關的表存放在SYSTEM表空間，因此為了不影響系統的效能，保護SYSTEM表空間，最好把AUD$移動到其他的表空間上。可以使用下面的語句來進行移動：

sql>connect / as sysdba;

sql>alter table aud$ move tablespace ;

sql>alter index I_aud1 rebuild online tablespace ;

SQL> alter table audit$ move tablespace ;

SQL> alter index i_audit rebuild online tablespace ;

SQL> alter table audit_actions move tablespace ;

SQL> alter index i_audit_actions rebuild online tablespace ;

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/81912/viewspace-600639/，如需轉載，請註明出處，否則將追究法律責任。