Oracle11g 审计登陆失败用户

心已赠人 2022-08-21 04:26 233阅读 0赞

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

**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$放在独立的表空间里面，避免增长太快对业务有影响。**