審計（Audit) 用於監視使用者所執行的資料庫操作，審計記錄可存在資料字典表（稱為審計記錄：儲存在system表空間中的 SYS.AUD$ 表中，可透過檢視dba_audit_trail檢視）或作業系統審計記錄中（預設位置為$ORACLE_BASE/admin/$ORACLE_SID/adump/）.。預設情況下審計是沒有開啟的。

不管你是否開啟資料庫的審計功能，以下這些作業系統會強制記錄：用管理員許可權連線Instance；啟動資料庫；關閉資料庫。

和審計相關的兩個主要引數

1、audit_sys_operations

AUDIT_SYS_OPERATIONS enables or disables the auditing of top-level operations, which are SQL statements directly issued by users when connecting with SYSDBA or SYSOPER privileges.(SQL statements run from within PL/SQL procedures or functions are not considered top-level.) The audit records are written to the operating system'saudit trail. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to xml or xml, extended.

On UNIX platforms, if the AUDIT_SYSLOG_LEVEL parameter has also been set, then it overrides the AUDIT_TRAIL parameter and SYS audit records are written to the system audit log using the SYSLOG utility.

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

2、audit_trail

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$). 將audit trail 記錄在資料庫的審計相關表中，如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. 10g裡新增的。
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated. 10g裡新增的。
os- Auditing is enabled, with all audit records directed to the operating system's audit trail. 將audit trail 記錄在作業系統檔案中，檔名由audit_file_dest引數指定；

注：這兩個引數是static引數，需要重新啟動資料庫才能生效。

當開啟審計功能後，可在三個級別對資料庫進行審計：Statement(語句)、Privilege（許可權）、object（物件）。

1、 Statement（語句審計）

對某種型別的SQL語句審計，不指定結構或物件。比如audit table 會審計資料庫中所有的create table、drop table、truncate table語句，alter session by cmy會審計cmy使用者所有的資料庫連線。

2、 Privilege（許可權審計）

當使用者使用了該許可權則被審計，如執行grant select any table to a，當執行了audit select any table語句後，當使用者a 訪問了使用者b的表時（如select * from b.t）會用到select any table許可權，故會被審計。

注意：使用者是自己表的所有者，所以使用者訪問自己的表不會被審計。

3 、 Object（物件審計）

對一特殊模式物件上的指定語句的審計。如審計on關鍵字指定物件的相關操作，如aduit alter, delete, drop, insert on cmy.t by scott; 這裡會對cmy使用者的t表進行審計，但同時使用了by子句，所以只會對scott使用者發起的操作進行審計。

注意：Oracle沒有提供對schema中所有物件的審計功能，只能一個一個物件審計，對於後面建立的物件，Oracle則提供on default子句來實現自動審計，比如執行audit drop on default by access;後，對於隨後建立的物件的drop操作都會審計。但這個default會對之後建立的所有資料庫物件有效，似乎沒辦法指定只對某個使用者建立的物件有效，相比 trigger 可以對schema的DDL進行“審計”，這個功能稍顯不足。

審計的一些其他選項：

1、by access / by session

by access 每一個被審計的操作都會生成一條audit trail。

by session 一個會話裡面同型別的操作只會生成一條audit trail，預設為by session。

2、whenever [not] successful

whenever successful 操作成功(dba_audit_trail中returncode欄位為0) 才審計,

whenever not successful 反之。省略該子句的話，不管操作成功與否都會審計。

和審計相關的檢視

1、dba_audit_trail

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

2、dba_stmt_audit_opts

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

3、all_def_audit_opts

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

取消審計

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

試驗如下：

首先，檢查審計功能是否開啟。

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>

審計相關的表安裝

SQL> connect / AS SYSDBA

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

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

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

SQL> connect / as sysdba

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

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

安裝後要重啟資料庫

基於Oracle的穩定性及效能考慮，可以將審計相關的表移動到其他表空間。

connect / as sysdba;
alter table aud$ move tablespace ;
alter index I_aud1 rebuild online tablespace ;
alter table audit$ move tablespace;
alter index i_audit rebuild online tablespace ;
alter table audit_actions move tablespace ;
alter index i_audit_actions rebuild online tablespace ;

開啟審計功能。

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

--審計管理使用者(以sysdba/sysoper角色登陸)

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

SQL> startup force;

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     TRUE
audit_trail                          string      DB, EXTENDED
SQL>

1、對建立表的審計

會話1中（已sysdba登入）

SQL> audit create table by user1;

審計已成功。

會話2中（已user1使用者登入）

SQL> create table test (id number);

Table created

SQL> create table test (id number);

create table test (id number)

ORA-00955: 名稱已由現有物件使用

SQL> select username,returncode,action_name from dba_audit_trail;

USERNAME                       RETURNCODE ACTION_NAME
------------------------------ ---------- ----------------------------
USER1                                     0 CREATE TABLE
USER1                                   955 CREATE TABLE

SQL>

2、對修改、刪除表的審計

由於沒有對drop table進行單獨審計的操作,需要新增audit table by user(此命令將對create table ,drop table, truncate table 進行審計)

會話1中：

SQL> audit table by user1;

審計已成功。

會話2中：

SQL> create table test(id number);

Table created

SQL> alter table test add name varchar2(100);

Table altered

SQL> drop table test;

Table dropped

SQL> select username,returncode,action_name from dba_audit_trail;

USERNAME                       RETURNCODE ACTION_NAME
------------------------------ ---------- ----------------------------
USER1                                     0 CREATE TABLE
USER1                                     0 ALTER TABLE
USER1                                     0 DROP TABLE

3、對檢視的審計

對建立檢視(create view)進行審計

會話1中：

SQL> audit create view by USER1;

審計已成功。

會話2中：

SQL> create view test0 as select * from test;

SQL> create view test0 as select * from test;

名稱已由現有物件使用 
 
SQL>select username,returncode,action_name from dba_audit_trail; 
USERNAME                       RETURNCODE ACTION_NAME
------------------------------ ---------- ----------------------------

USER1				   0 CREATE VIEW
USER1                                 955 CREATE VIEW

會話1中：

SQL> audit view by USER1;

審計已成功。

會話2中：
SQL> drop view test0;

View dropped

SQL> drop view test0;

drop view test0

ORA-00942: 表或檢視不存在

SQL> select username,returncode,action_name from dba_audit_trail;

USERNAME                       RETURNCODE ACTION_NAME
------------------------------ ---------- ----------------------------
USER1                                     0 DROP VIEW
USER1                                   942 DROP VIEW

4、對程式包的審計

會話1中：

SQL> audit procedure by user1;

審計已成功。

SQL> truncate table aud$;

表被截斷。

會話2中：

SQL> create procedure test1 as
2 begin
3 null;
4 end;
5 /

Procedure created

SQL> drop procedure test1;

Procedure dropped

SQL> drop procedure test1;

drop procedure test1

ORA-04043: 物件 TEST1 不存在

SQL> select username,returncode,action_name from dba_audit_trail;

USERNAME                       RETURNCODE ACTION_NAME
------------------------------ ---------- ----------------------------
USER1                                     0 CREATE PROCEDURE
USER1                                     0 DROP PROCEDURE
USER1                                  4043 DROP PROCEDURE

5、對使用者的審計

audit user by user

綜上所述: 
1. 對錶的審計:可以單獨對錶的create,alter進行審計,如果要對drop操作進行審計需要對錶加audit table(該命令包含有create table,drop table,truncate table). 
2. 對檢視的審計:可以單獨對檢視的create進行審計,如果要對drop操作進行審計需要對檢視加audit view(該命令包含有create view,drop view). 
3. 對程式包的審計:可以對包(函式,儲存過程等)的create進行審計,如果需要對drop操作進行審計需要加audit procedure(該命令對CREATE FUNCTION, CREATE LIBRARY , CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE進行審計) 
4. 對使用者的審計:可以透過audit user(該命令包含 create user,alter user,drop user)進行審計

以下是對上面內容的一個補充。

Maintenance and Security

Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest. Over time you can refine the level of auditing to match your requirements.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

The OS and XML audit trails are managed through the OS. These files should be secured at the OS level by assigning the correct file permissions.

Fine Grained Auditing (FGA)

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.

CONN audit_test/password

CREATE TABLE emp (
 empno     NUMBER(4) NOT NULL,
 ename     VARCHAR2(10),
 job       VARCHAR2(9),
 mgr       NUMBER(4),
 hiredate  DATE,
 sal       NUMBER(7,2),
 comm      NUMBER(7,2),
 deptno    NUMBER(2)
);

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
COMMIT;

The following policy audits any queries of salaries greater than £50,000.

CONN sys/password AS sysdba

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL');
END;
/

Querying both employees proves the auditing policy works as expected.

CONN audit_test/password
SELECT sal FROM emp WHERE ename = 'Tim';
SELECT sal FROM emp WHERE ename = 'Larry';

CONN sys/password AS SYSDBA
SELECT sql_text
FROM   dba_fga_audit_trail;

SQL_TEXT
------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'

1 row selected.

SQL>

Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL',
    handler_schema  => 'AUDIT_TEST',
    handler_module  => 'FIRE_CLERK',
    enable          => TRUE);
END;
/

The DBMS_FGA package contains the following procedures:

ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY

In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

-- Clear down the audit trail.
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL column of the EMP table.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT',
    audit_condition => NULL, -- Equivalent to TRUE
    audit_column    => 'SAL',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
CONN audit_test/password
SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;

-- Check the audit trail.
CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998

4 rows selected.

-- Drop the policy.
CONN sys/password AS SYSDBA
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT');
END;
/

Oracle Audit 功能的使用和說明

Maintenance and Security

Fine Grained Auditing (FGA)

相關文章