在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.
- 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.
- 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.
- 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.
關於Oracle 11g數據庫默認審計選項說明的相關知識就介紹到這裡了,希望本次的介紹能夠對您有所幫助。