Oracle11g 审计登陆失败用户

   在生产环境上，你可能面临有些恶意的程序不断的试你数据库的密码，导致账号被锁，找到元凶只有开审计。

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

show parameter audit;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      F:\APP\ADMINISTRATOR\ADMIN\ORA11\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
alter system set audit_trail=DB scope=spfile;
--设置后重启数据库

audit session by TEST whenever not successful;

sqlplus test/111@10.10.31.132/orcl --错误密码 

col userid format a10;
col userhost format a15;
col terminal format a10;

col comment$text format a50;

--查看错误信息

select a.userid,a.userhost,a.terminal,a.comment$text from aud$ a where returncode=1017;
USERID     USERHOST        TERMINAL   COMMENT$TEXT
---------- --------------- ---------- --------------------------------------------------
SYS        COMTOP\8JKMS32  8JKMS32    Authenticated by: DATABASE
TEST       COMTOP\8JKMS32  8JKMS32    Authenticated by: DATABASE; Client address: (ADDRE
                                      SS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=60742))


TEST       COMTOP\8JKMS32  8JKMS32    Authenticated by: DATABASE; Client address: (ADDRE
                                      SS=(PROTOCOL=tcp)(HOST=10.10.31.132)(PORT=62316))
建议把表aud$放在独立的表空间里面，避免增长太快对业务有影响。