恢复完UAT环境，发现业务用户总被锁定，问谁都说自己的程序密码是对的，本来想写个触发器记录是谁总用错误的密码登陆数据库，发现这个数据库的审计没有关闭(11g默认审计功能是开启的)，是打开的。数据库版本11.2.0.4。

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adu
                                                 mp
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

在11g如果没有关闭掉审计的功能，默认是可以记录错误密码登陆信息的，很幸运的是，这个功能并没有被禁掉。那么查询AUD$表就能查询到错误密码登陆信息。以下是在没有做过任何设置的11.2.0.4.0版本的数据库中做的测试，先使用错误密码登陆数据库。

[oracle@secdb1 admin]$ sqlplus dbdream/oracle@localhost/PROD

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:48:17 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

查询AUD$表，其中returncode字段记录的就是用户登录信息，1017位密码错误，登录失败，0为正常登录数据库。

SQL> select userid,userhost,terminal,returncode,spare1 from aud$;

USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
---------- ---------- ---------- ---------- ----------
DBDREAM    secdb1     pts/1            1017 oracle

11g默认不止开启了错误密码登陆的审计，正常登录到数据库的连接也会被审计到，下面先通过正确的密码登陆数据库。

[oracle@secdb1 ~]$ sqlplus dbdream/dbdream@localhost/PROD

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:54:21 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

查询AUD$表会发现，这个连接也被记录了。

SQL> select userid,userhost,terminal,returncode,spare1 from aud$;

USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
---------- ---------- ---------- ---------- ----------
DBDREAM    secdb1     pts/1            1017 oracle
DBDREAM    secdb1     pts/1               0 oracle

在10g版本，审计默认是关闭的，下面是10.2.0.1.0版本的数据库，审计默认关闭。

SYS@EMREP> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SYS@EMREP> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/EMREP/ad
                                                 ump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

打开审计功能，看看是否可以审计到用户登录信息。

YS@EMREP> alter system set audit_trail=db scope=spfile;

System altered.

SYS@EMREP> startup force
ORACLE instance started.

Total System Global Area  587202560 bytes
Fixed Size                  1220724 bytes
Variable Size             188747660 bytes
Database Buffers          394264576 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SYS@EMREP> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/EMREP/ad
                                                 ump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

audit_trail是静态参数，修改后需要重启数据库才能生效。使用错误的密码登陆数据库，看看是否会被审计到。

[oracle@dbdream admin]$ sqlplus dbdream/oracle@localhost/EMREP

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:13:57 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

查询AUD$表，看看是否记录错误密码登陆的信息。

SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;

no rows selected

10g版本的审计默认是不记录错误密码登陆的信息，需要手动设置。

SYS@EMREP> audit session whenever not successful;

Audit succeeded.

再次使用错误密码登陆数据库，此时就会被记录下来。

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
[oracle@dbdream admin]$ sqlplus dbdream/oracle@localhost/EMREP

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:15:04 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;

USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
---------- ---------- ---------- ---------- ----------
DBDREAM    dbdream    pts/2            1017 oracle

那么正常登录到数据库是否会被审计记录下来呢？下面使用正确的密码登陆数据库。

[oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:16:07 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

DBDREAM@localhost/EMREP> 

SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;

USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
---------- ---------- ---------- ---------- ----------
DBDREAM    dbdream    pts/2            1017 oracle

查询发现正常登录数据库的操作并没有被记录下来，要想记录正常登录的信息，也需要手动配置。

SYS@EMREP> audit session whenever successful;

Audit succeeded.

SYS@EMREP> !
[oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:17:51 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

DBDREAM@localhost/EMREP> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
[oracle@dbdream admin]$ exit
exit

SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;

USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
---------- ---------- ---------- ---------- ----------
DBDREAM    dbdream    pts/2            1017 oracle
DBDREAM    dbdream    pts/2               0 oracle

11g简化了审计的配置，但是AUD$表会越来越大，需要定期清理，而很多人是不会注意这些的，就会导致system表空间使用率很高。

本文固定链接: http://www.dbdream.com.cn/2015/07/%e4%bd%bf%e7%94%a8%e5%ae%a1%e8%ae%a1%e5%8a%9f%e8%83%bd%e8%ae%b0%e5%bd%95%e9%94%99%e8%af%af%e5%af%86%e7%a0%81%e7%99%bb%e9%99%86%e4%bf%a1%e6%81%af/ | 信春哥，系统稳，闭眼上线不回滚！