關於oracle 11g 審計檔案,oracle11g審計檔案
關於審計:
11g預設啟用的審計選項,AUDIT_TRAIL參數的預設值為DB,表示審計資料將記錄在資料庫中的SYS.AUD$審計字典基表上。而在Oracle 10g中該參數預設值為none,即不啟用審計。Oracle官方宣稱預設啟用的審計日誌不會對絕大多數產品資料庫的效能帶來過大的負面影響,同時Oracle公司還推薦使用基於OS檔案的審計日誌記錄方式(OS audit trail files)。
因為在11g中CREATE SESSION將被作為受審計的許可權來被記錄,因此當SYSTEM資料表空間因磁碟空間而無法擴充時將導致這部分審計記錄無法產生,最終導致普通使用者的新會話將無法正常建立,普通使用者將無法登陸資料庫。在這種情境中仍可以使用SYSDBA身份的使用者建立會話,在將審計資料合適備份後刪除一部分記錄,或者TRUNCATEAUD$都可以解決上述問題。
當AUDIT_TRAIL設定為OS時,審計記錄檔案將在AUDIT_FILE_DEST參數所指定的目錄中產生。全部這些檔案均可以隨時被刪除或複製。
以下許可權將對所有使用者審計:
SQL> select privilege,success,failure fromdba_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
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
ALTER SYSTEM BY ACCESS BY ACCESS
23 rows selected.
當前資料庫中的現有的審計記錄,LOGNO/LOGOFF為審計create session產生的:
SQL> select action_name,count(*) from dba_audit_trailgroup by action_name;
ACTION_NAME COUNT(*)
---------------------------- ----------
LOGOFF BY CLEANUP 40
LOGON 460
LOGOFF 377
ALTER USER 2
SYSTEM GRANT 12
ALTER SYSTEM 10
CREATE PUBLIC SYNONYM 5
ALTER DATABASE 2
CREATE DATABASE LINK 1
DROP PUBLIC SYNONYM 5
96.216 SYSTEM資料表空間過大:
96.216中 LOGOFF/LOGOFF分別為2億多條記錄,使用得aud$表大小為80G。
解決方案:
1.當aud$表過大時,可以清除表中的審訊資料:
SQL> truncate table sys.aud$;
2.可以關閉對create session的審訊:
SQL> noaudit create session;
3.關閉資料庫的審訊,需要重啟資料庫:
SQL> alter system set audit_trail=none scope=spfile;