Oracle 11g 預設審計選項說明

一. Oracle 11g 預設審計說明

之前整理的一篇有關審計的說明：

Oracle Audit 審計說明

http://space.itpub.net/15880878/viewspace-720044

在Maclean 的blog上看到了2篇介紹Oracle 11g 預設審計的文章，原文連結如下：

11g預設審計選項

http://www.oracledatabase12g.com/archives/11g%E9%BB%98%E8%AE%A4%E5%AE%A1%E8%AE%A1%E9%80%89%E9%A1%B9.html

Find password cracker in 11g

http://www.oracledatabase12g.com/archives/script-find-password-cracker.html

根據這2篇文章重新整理一下。

在Oracle 11g中預設啟用審計選項，AUDIT_TRAIL引數的預設值為DB，而在Oracle 10g中該引數預設值為none，即不啟用審計。關於這些引數的說明，可以參考我之前整理的審計的文章。

審計資料預設存放SYSTEM 表空間下的AUD$審計字典基表上。Oracle官方宣稱預設啟用的審計日誌不會對絕大多數產品資料庫的效能帶來過大的負面影響，同時Oracle公司還推薦使用基於OS檔案的審計日誌記錄方式(OS audit trail files)。

注意在Oracle11g中CREATE SESSION將被作為受審計的許可權來被記錄，因此當SYSTEM表空間因磁碟空間而無法擴充套件時將導致這部分審計記錄無法生成，這將最終導致普通使用者的新會話將無法正常建立，普通使用者將無法登陸資料庫。在這種場景中仍可以使用SYSDBA身份的使用者建立會話，在將審計資料合適備份後刪除一部分記錄，或者乾脆TRUNCATE AUD$都可以解決上述問題。

當AUDIT_TRAIL設定為OS時，審計記錄檔案將在AUDIT_FILE_DEST引數所指定的目錄中生成。全部這些檔案均可以隨時被刪除或複製。

注意在預設情況下會以AUTOEXTEND ON自動擴充套件選項建立SYSTEM表空間，因此係統表空間在必要情況下還是會自動增長的，我們所需注意的是磁碟上的剩餘空間是否能夠滿足其增長需求，以及資料檔案擴充套件的上限，對於普通的8k smallfile表空間而言單個資料檔案的最大尺寸是32G。

SQL> select * from v$version whererownum=1;

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production

以下許可權將對所有使用者審計:

DBA_PRIV_AUDIT_OPTS describescurrent system privileges being audited across the system and by user.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_4183.htm#REFRN23167

SQL> select privilege,success,failurefrom dba_priv_audit_opts;

PRIVILEGE SUCCESS FAILURE

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

CREATE EXTERNAL JOB BY ACCESS BY ACCESS

CREATE ANY JOB BY ACCESS BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS BY ACCESS

CREATE ANY LIBRARY BY ACCESS BY ACCESS

GRANT ANY PRIVILEGE BY ACCESS BY ACCESS

DROP PROFILE BY ACCESS BY ACCESS

ALTER PROFILE BY ACCESS BY ACCESS

DROP ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

PRIVILEGE SUCCESS FAILURE

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

ALTER DATABASE BY ACCESS BY ACCESS

GRANT ANY ROLE BY ACCESS BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

DROP ANY TABLE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

AUDIT SYSTEM BY ACCESS BY ACCESS

PRIVILEGE SUCCESS FAILURE

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

ALTER SYSTEM BY ACCESS BY ACCESS

23 rows selected.

SQL>

以下語句也將對所有使用者審計:

DBA_STMT_AUDIT_OPTS describescurrent system auditing options across the system and by user.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_4292.htm#REFRN23255

SQL> select audit_option,success,failurefrom dba_stmt_audit_opts;

AUDIT_OPTION SUCCESS FAILURE

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

ALTER SYSTEM BY ACCESS BY ACCESS

SYSTEM AUDIT BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

PUBLIC SYNONYM BY ACCESS BY ACCESS

DATABASE LINK BY ACCESS BY ACCESS

ROLE BYACCESS BY ACCESS

PROFILE BYACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

AUDIT_OPTION SUCCESS FAILURE

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

ALTER ANY TABLE BY ACCESS BY ACCESS

DROP ANY TABLE BY ACCESS BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

GRANT ANY ROLE BY ACCESS BY ACCESS

SYSTEM GRANT BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

DROP ANY PROCEDURE BY ACCESS BY ACCESS

ALTER PROFILE BY ACCESS BY ACCESS

DROP PROFILE BY ACCESS BY ACCESS

AUDIT_OPTION SUCCESS FAILURE

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

GRANT ANY PRIVILEGE BY ACCESS BY ACCESS

CREATE ANY LIBRARY BY ACCESS BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS

CREATE ANY JOB BY ACCESS BY ACCESS

CREATE EXTERNAL JOB BY ACCESS BY ACCESS

28 rows selected.

查詢當前資料庫中的現有的審計記錄:

DBA_AUDIT_TRAIL displaysall standard audit trail entries.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_3081.htm#REFRN23023

SQL> select action_name,count(*) from dba_audit_trail group by action_name;

ACTION_NAME COUNT(*)

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

SYSTEM REVOKE 1

LOGON 90

DROP DATABASE LINK 5

LOGOFF 59

ALTER SYSTEM 5

CREATE PUBLIC SYNONYM 2

ALTER DATABASE 3

DROP PUBLIC SYNONYM 2

CREATE DATABASE LINK 5

9 rows selected.

二. 審計應用一例

在使用者的profile 屬性裡面有一個屬性：FAILED_LOGIN_ATTEMPTS, 該引數預設值是10. 即當我們使用者連續10次輸入錯誤密碼，這個使用者就會被鎖住。使用者連詞失敗次數是在表USER$ 中的lcount欄位記錄的。該值預設為0. 當失敗一次，該值加1. 成功登入，該值清零。

一般在生產環境下，會根據具體情況設定這個引數，如果防止使用者被鎖，則將這個引數設定為UNLIMITED。這個是注意的地方。當然設定成無限也有它的弊端，比如不能防止暴力破解資料庫密碼。

有關profile 的更多內容參考：

Oracle 使用者 profile 屬性

http://blog.csdn.net/tianlesoftware/article/details/6238279

在11g中預設啟用了對登入登出操作LOGON/LOGOFF的審計，那麼如果我們發現使用者被鎖，那麼可以應用11g的審計功能來檢視從哪臺機器上發來的連結失敗導致使用者被鎖，可以幫助我們定位問題。

指令碼如下：

SQL> selectos_username,userhost,terminal,username,count(*)

2 from dba_audit_trail

3 where returncode = 1017

4 group byos_username,userhost,username,terminal;

OS_USERNAME USERHOST TERMINAL USERNAME COUNT(*)

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

DavidDai\Administrator WORKGROUP\DAVIDDAI DAVIDDAI ICD 7

DavidDai\Administrator WORKGROUP\DAVIDDAI DAVIDDAI SYSTEM 9

DavidDai\Administrator WORKGROUP\DAVIDDAI DAVIDDAI SYS 3

DavidDai\Administrator WORKGROUP\DAVIDDAI DAVIDDAI EXIT 1

注意對於LOGON PER SECOND很高的資料庫，如果應用程式配置檔案中的資料庫使用者密碼不正確，同時應用在短期內發起大量會話登入資料庫的話可能引發頻繁的dc_users字典快取鎖，使用者登入無法成功，乃至整個例項hang住。這個問題直接參考Maclean的blog：

Row Cache lock Problem

http://www.oracledatabase12g.com/archives/row-cache-lock-problem.html

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

Oracle 11g 預設審計選項 說明

一. Oracle 11g 預設審計說明

以下許可權將對所有使用者審計:

以下語句也將對所有使用者審計:

查詢當前資料庫中的現有的審計記錄:

二. 審計應用一例

相關文章

Oracle 11g 預設審計選項說明