Application instance analysis of Oracle 11g Database Audit Function

Last time we introduced:Oracle 11gDescription of default Audit options for databases. This article mainly introduces an Oracle 11g Database.Application Example of audit functionTo help you.

There is a property in the user's profile attribute: FAILED_LOGIN_ATTEMPTS. The default value of this parameter is 10. That is, when we enter the wrong password 10 times in a row, this user will be locked. The number of USER join failures is recorded in the lcount field of the table USER $. The default value is 0. If one failure occurs, the value is added to 1. logon is successful, and the value is cleared.

Generally, this parameter is set in the production environment according to the actual situation. If the user is prevented from being locked, this parameter is set to UNLIMITED. This is important. Of course, setting it to an infinite number also has its drawbacks. For example, it cannot prevent brute force password cracking.

For more information about the profile, see:

Oracle user profile attributes

Http://blog.csdn.net/tianlesoftware/article/details/6238279

By default, Oracle 11g enables LOGON/LOGOFF auditing. If we find that the user is locked, the 11g audit function can be applied to check the machine from which the link failed to cause the user to be locked, which can help us locate the problem.

The script is as follows:

 
 

  
  
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 
 
 

Note:For databases with a high logon per second, if the database user password in the application configuration file is incorrect and the application initiates a large number of sessions to log on to the database within a short period of time, frequent dc_users dictionary cache locks may occur, user Logon fails, and the entire instance is hang. For this question, refer to Maclean's blog:

Row Cache lock Problem

Http://www.oracledatabase12g.com/archives/row-cache-lock-problem.html

Here is an example of the Oracle 11g Database Audit function. I hope this introduction will be helpful to you!