Oracle 11g 默认审计选项 说明
一. Oracle 11g 默认审计说明
之前整理的一篇有关审计的说明：
http://blog.csdn.net/tianlesoftware/article/details/4712932
在Maclean 的blog上看到了2篇介绍Oracle 11g 默认审计的文章，原文链接如下：
11g默认审计选项
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 的更多内容参考：
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
-------------------------------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929(满) DBA5群： 142216823(满)
DBA6 群：158654907(满) 聊天 群：40132017(满) 聊天2群：69087192(满)
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
