How which ip causes the user to be locked in Oracle

Editor to share with you how in Oracle which ip caused the user to be locked. I hope you will get something after reading this article. Let's discuss it together.

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 * 0

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=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

After reading this article, I believe you have a certain understanding of "how in Oracle which ip caused the user to be locked". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!