11g默认启用强大的审计选项，AUDIT_TRAIL参数的缺省值为DB，这意为着审计数据将记录在数据库中的AUD$审计字典基表上。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响，同时Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。
注意因为在11g中CREATE SESSION将被作为受审计的权限来被记录，因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成，这将最终导致普通用户的新会话将无法正常创建，普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话，在将审计数据合适备份后删除一部分记录，或者干脆TRUNCATE AUD$都可以解决上述问题。
当AUDIT_TRAIL设置为OS时，审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成。全部这些文件均可以随时被删除或复制。
注意在默认情况下会以AUTOEXTEND ON自动扩展选项创建SYSTEM表空间，因此系统表空间在必要情况下还是会自动增长的，我们所需注意的是磁盘上的剩余空间是否能够满足其增长需求，以及数据文件扩展的上限，对于普通的8k smallfile表空间而言单个数据文件的最大尺寸是32G。
以下权限将对所有用户审计:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmac.cn SQL> select privilege,success,failure from 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> select audit_option,success,failure from 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 BY ACCESS BY ACCESS PROFILE BY ACCESS 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.
当前数据库中的现有的审计记录:
SQL> select action_name,count(*) from dba_audit_trail group 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 10 rows selected.
SQL> select * from v$version;BANNER CON_ID——————————————————————————– ———-Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta 0PL/SQL Release 12.1.0.0.2 – Beta 0CORE 12.1.0.0.2 Beta 0TNS for Linux: Version 12.1.0.0.2 – Beta 0NLSRTL Version 12.1.0.0.2 – Beta 0SQL> select privilege,success,failure from dba_priv_audit_opts;PRIVILEGE SUCCESS FAILURE—————————————- ———- ———-EXEMPT IDENTITY POLICY BY ACCESS BY ACCESSEXEMPT IDENTITY POLICY BY ACCESS BY ACCESSEXEMPT IDENTITY POLICY BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESS177 rows selected.