Oracle Audit 學習與測試

參考文件

<>P643

<>P781

1 Overview of Audit

2 Audit Records and the Audit Trail

3 Mechanisms for Auditing

4 Focus Auditing

5 Managing the Audit Trail

6 Viewing Database Audit Trail Information

7 test

8 Notes

1 Overview of Audit

1.1 Auditing is the monitoring and recording of selectedd user database actions.

1.2 Oracle supports three general types of auditing:

Statement auditing: AUDIT DDL statement and DML statement. .(for example, AUDIT TABLE audits all CREATE and DROP TABLE statements不針對具體的Schema Object).

Privilege auditing: AUDIT CREATE TABLE

Schema object auditing: AUDIT SELECT ON employee

Fine-grained auditing: allows the monitoring of data access based on content.

2 Audit Records and the Audit Trail

Audit records include information such as the operation that was audited, and the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or an operating system audit trail.

The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database’s data dictionary.Several predefined views are provided to help to view information.

The operating system audit trail is encoded and not readable, but it is decoded in data dictionary files and error messages.

ACTION CODE resides in dictionary table AUDIT_ACTIONS.

PRIVILEGES USED resides in dictionary table SYSTEM_PRIVILEGE_MAP.

COMPLETION CODE describes the result of an attempted operation.

3 Mechanisms for Auditing

3.1 when are audit records generated

The recording of audit information can be enabled or disabled. An audit record is generated during the execute phase of statement execution. SQL statements inside PL/SQL program units are individually audited, when the program unit is executed.The generation and insertion of an audit trail record is independent of a user’s transaction. Therefore, even if a user’s transaction is rolled back, the audit trail record remains committed.(審計記錄的時機)

3.2 Events always audited to the Operating System Audit Trail

Regardless of whether database auditing is enabled, Oracle always records some database-related actions into the operating system audit trail: (AIX, $ORACLE_HOME/rdbms/audit/)

1) At instance startup.

2) At instance shutdown

3) During connections with administrator privileges.

4 Focus Auditing

4.1 Successful and Unsuccessful statement execution auditing

Using either form. of the AUDIT statement, you can include:

The WHENEVER SUCCESSFUL clause, to audit only successful executions of the audited statement

The WHENEVER NOT SUCCESSFUL clause, to audit only unsuccessful executions of the audited statement

Neither of the previous clauses, to audit both successful and unsuccessful executions of the audited statement

4.2 By Session and By Access

1) BY SESSION inserts only one audit record in the audit trail, for each user and schema object, during the session that includes an audited action.(只插入一條審計記錄,default)

A session is the time between when a user connects to and disconnects from an Oracle database.

2) Setting audit BY ACCESS inserts one audit record into the audit trail for each execution of an auditable operation within a cursor.(一次執行插入一條審計記錄)

4.3 Audit by user

By focusing on specific users, you can minimize the number of audit records generated.

AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake;

5 Managing the Audit Trail

5.1 Enable and Disable Auditing

1) Setting the AUDIT_TRAIL Initialization Parameter,the parameter can be set to the following values:

DB: Enable database auditing and directs all audit records to the database audit trial,except for records that are always written to the operating system audit trail.

OS: Enable database auditing and directs all audit records to an operating system file.

None: Disable auditing(Default)

idle> show parameter AUDIT_TRAIL

NAME TYPE VALUE

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

audit_trail string NONE

2) setting the AUDIT_FILE_DEST Initialization Parameter, the parameter specifies an operating system directory into which the audit trail is written when AUDIT_TRAIL=OS is specified.it is also the location to which mandatory auditing information is written.(預設OS路徑)

idle> show parameter AUDIT_FILE_DEST

NAME TYPE VALUE

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

audit_file_dest string ?/rdbms/audit

3) Audit_sys_operations：

idle> show parameter Audit_sys_operations

NAME TYPE VALUE

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

audit_sys_operations boolean FALSE

預設為false，當設定為true時，所有sys使用者（包括以sysdba,sysoper身份登入的使用者）的操作都會被記錄，audit trail不會寫在aud$表中，這個很好理解，如果資料庫還未啟動aud$不可用，那麼像conn /as sysdba這樣的連線資訊，只能記錄在其它地方。如果是windows平臺，audti trail會記錄在windows的事件管理中，如果是linux/unix平臺則會記錄在audit_file_dest引數指定的檔案中。

5.2 Setting Audit Level

Specify auditing options using the AUDIT statement.The AUDIT statement allows you to set audit options at three level: Statement, Privilege, Object.

5.3 Turn off audit options

將對應審計語句的audit改為noaudit即可，如audit session whenever successful對應的取消審計語句為noaudit session whenever successful;

To disable statement or privilege auditing options, you must have the AUDIT

SYSTEM system privilege

5.4 Controlling the Growth and Size of the Audit Trail

DELETE FROM SYS.AUD$;

DELETE FROM SYS.AUD$ WHERE obj$name='EMP';

6 Viewing Database Audit Trail Information

6.1 Creating the audit trail views

views (except STMT_AUDIT_OPTION_MAP) are created by the CATALOG.SQL and CATAUDIT.SQL scripts:

6.2 Deleting the audit trail views

running the script. file CATNOAUD.SQL.

6.3和審計相關的檢視

dba_audit_trail：儲存所有的audit trail，實際上它只是一個基於aud$的檢視。其它的檢視dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一個子集。

dba_stmt_audit_opts：可以用來檢視statement審計級別的audit options，即資料庫設定過哪些statement級別的審計。dba_obj_audit_opts,dba_priv_audit_opts檢視功能與之類似

all_def_audit_opts：用來檢視資料庫用on default子句設定了哪些預設物件審計。

7 test

7.1、啟用審計

SQL> conn /as sysdba

SQL> show parameter audit

NAME TYPE VALUE

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

audit_file_dest string ?/rdbms/audit

audit_sys_operations boolean FALSE

audit_trail string NONE

transaction_auditing boolean TRUE

SQL> alter system set audit_sys_operations=TRUE scope=spfile; --審計管理使用者(以sysdba/sysoper角色登陸)

SQL> alter system set audit_trail=db scope=spfile;

SQL> shutdown immediate

SQL> startup

idle> show parameter audit

NAME TYPE VALUE

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

audit_file_dest string ?/rdbms/audit

audit_sys_operations boolean TRUE

audit_trail string DB

transaction_auditing boolean TRUE

7.2、開始審計

col DEST_NAME format a30

col OS_USERNAME format a15

col USERNAME format a15

col USERHOST format a15

col TERMINAL format a15

col OBJ_NAME format a30

select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid from dba_audit_trail;

OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP

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

OWNER OBJ_NAME ACTION_NAME SESSIONID

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

oracle TEST 17-JAN-10

TEST DEPART SESSION REC 299

audit select table by test by access;

如果在命令後面新增by user則只對user的操作進行審計,如果省去by使用者,則對系統中所有的使用者進行審計(不包含sys使用者).

例：

AUDIT DELETE ANY TABLE; --審計刪除表的操作

AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL; --只審計刪除失敗的情況

AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL; --只審計刪除成功的情況

AUDIT DELETE,UPDATE,INSERT ON depart by test; --審計test使用者對錶user.table的delete,update,insert操作

7.3、撤銷審計

SQL> noaudit all on DEPART;

7.4 Fine-grained auditing

參考dbms_fga　Package．

8 Notes

1 Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded

Oracle Audit 學習與測試

相關文章