SQL> show parameter audit

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u01/app/oracle/admin/ractest/

                                                 adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      DB

SQL> select userid,count(1) from aud$ group by userid;

USERID                           COUNT(1)

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

PUBLIC                                 49

TEST                                    2

SYSTEM                                954

SCOTT                                   1

DBSNMP                               6189

SYSMAN                              43371

SOE                                   122

GGATE                                  70

8 rows selected.

SQL> select owner,segment_name,bytes/1024/1024 mb from dba_segments where segment_name='AUD$';

OWNER

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

SEGMENT_NAME

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

        MB

----------

SYS

AUD$

        13

    5萬條審計記錄佔據了13M的空間。

    從上面的結果可以看出，11gR2的資料庫例項預設將audit_trail設定為DB，審計除了SYSDBA、SYSOPER許可權使用者以外的其他使用者特定操作。

2.對SYSDBA和SYSOPER進行審計。

對SYSDBA和SYSOPER的審計具有如下的特點：
1).審計線索必須儲存在資料庫外部。
2).始終會對以SYSDBA或SYSOPER身份執行的連線進行審計。
3).可使用AUDIT_SYS_OPERATIONS啟用對SYSDBA或SYSOPER操作的附加審計。
4).可使用AUDIT_FILE_DEST控制審計線索。

    無論是遠端或本地SYSDBA、SYSOPER許可權使用者登入都會在audit_file_dest指定的目的地生成相應審計檔案，記錄登入資訊。Windows平臺SYSDBA許可權使用者的審計記錄會被寫到事件檢視器中。

    下面討論一下AUDIT_SYS_OPERATIONS和AUDIT_TRIAL兩個初始化引數的含義：

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 SYSDBAor 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's audit 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.

AUDIT_TRAIL

AUDIT_TRAIL enables or disables database auditing.

Values:

• none

  Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

• os

  Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

• db

  Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• db, extended

  Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• xml

  Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

• xml, extended

  Performs all actions of AUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.

注意：audit_syslog_level引數只在unix和Linux平臺存在。


3.ASM預設的審計策略。

下面是在ASM例項下的操作：

4.預設審計功能的管理。

1).關閉Database審計功能。
    將資料庫例項的audit_trail設定為none，重啟資料庫例項即可關閉資料庫例項對非SYSDBA、SYSOPER許可權使用者的審計功能；該設定並不能關閉對SYSDBA許可權使用者的審計，SYSDBA許可權使用者登入依然會在audit_file_dest指定目錄下生成相應的審計檔案。

2).關閉ASM審計功能。
    同樣沒法關閉對以SYSASM和SYSDBA、SYSOPER登入使用者的審計。

3).遷移AUD$表。
    AUD$表預設被存放到SYSTEM表空間下，頻繁的登入會導致SYSTEM表空間較快擴大，最終可能影響資料庫系統的正常執行。參考如下文章：http://yangtingkun.itpub.net/post/468/496990可完成對AUD$表的遷移。

4).AUD$表記錄的清除操作。
    AUD$表可以被直接TRUNCATE，也可以參考如下文章：http://yangtingkun.itpub.net/post/468/498990清除部分AUD$表資料。可以建立並排程JOB對AUD$表進行自動化的清理。

--end--