The reason and solution of Oracle user being locked

Was told that the test user was locked when logging in

1. Log in with the user of the dba role and unlock it. Set the specific time format first in order to view the specific time.

SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'; Session altered.

2. Check the specific lock time

SQL > select username,lock_date from dba_users where username='TEST'; USERNAME LOCK_DATE TEST 2009-03-10 08:51:03

3. Unlock

SQL > alter user test account unlock; User altered.

4. Check that the ip caused the test user to be locked.

View the $ORACLE_HOME/network/admin/log/listener.log log

10-MAR-2009 08:51:03 * (CONNECT_DATA= (SID=lhoms) (SERVER=DEDICATED) (CID= (PROGRAM=Oracle) (HOST=omstestdb) (USER=oraoms) * (ADDRESS= (PROTOCOL=tcp) (HOST=10.69.1.11) (PORT=49434)) * establish * lhoms * 010-MAR-2009 08:51:03 * (CONNECT_DATA= (SID=lhoms) (SERVER=DEDICATED) (CID= (PROGRAM=oracle) (HOST=omstestdb) (USER=oraoms) * (ADDRESS= (PROTOCOL=tcp) (HOST=10.69.1.11) (PORT=49435)) * establish * lhoms * 0

In this way, it can be seen that the ip of 10.69.1.11 above is locked due to repeated failed login attempts.

Note:

By default, the database locks the user after 10 failed attempts.

1. Check the value of FAILED_LOGIN_ATTEMPTS

Select * from dba_profiles

2. Modify it to 30 times

Alter profile default limit FAILED_LOGIN_ATTEMPTS 30

3. Modify it to unlimited number of times (not recommended for security reasons)

Alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited

The above is the reasons and solutions for the lock of Oracle users introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!