ORA-28000: the account is locked to check which specific IP address causes

ORA-28000: the account is locked to check which specific IP address causes

Check the default system policy and the account will be locked after 10 consecutive verification errors.

SQL> select resource_name, limit from dba_profiles where profile = 'default ';

RESOURCE_NAME LIMIT
------------------------------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180

RESOURCE_NAME LIMIT
------------------------------------------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

16 rows selected.

 

View User lock status

 

SQL> select username, account_status from dba_users where username = 'user1 ';

USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
USER1 LOCKED (TIMED)
SQL> select name, lcount from user $ where name = 'user1 ';

NAME LCOUNT
----------------------------------------
USER1 10

 

Solve the problem first, change the number of verification errors to unrestricted, and unlock the user

 

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered.

SQL> alter user user1 account unlock;

User altered.

 

Check the number of user verification errors. If the account has been being verified, you can see that the number of user verification errors has been increasing.

SQL> select name, lcount from user $ where name = 'user1 ';

Trace the source ip address of the request through the log file/u01/app/Oracle/diag/tnslsnr/localhost/listener/alert/log. xml, but the effect is not satisfactory.

1. the user name and result of the request cannot be viewed, and the source ip address of the request may be incorrect.

2. Too many logs, so you cannot think of keyword Filtering for the moment.

 

[Oracle @ localhost adump] $ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 12-MAY-2016 11:46:39

Copyright (c) 1991,200 9, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = iZ11y546tzlZ) (PORT = 1521 )))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 10-MAY-2016 09:44:40
Uptime 2 days 2 hr. 1 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener. ora
Listener Log File/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log. xml
Listening Endpoints Summary...

[Oracle @ localhost ~] $ Tail-f/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log. xml

<Msg time = '2017-05-12T11: 52: 2016 + 08: 00' org_id = 'oracle 'comp_id = 'tnslsnr'
Type = 'unknown 'level = '16' host_id = 'localhost'
Host_addr = '10. 174.70.172 '>
<Txt> 12-MAY-2016 11:52:33 * (CONNECT_DATA = (SERVICE_NAME = orcl) (CID = (PROGRAM = C: \ Program? Files ?? X86? \ PremiumSoft \ Navicat? Premium \ navicat.exe) (HOST = HUJF-PC) (USER = hujf) * (ADDRESS = (PROTOCOL = tcp) (HOST = 110.800000.106) (PORT = 59584 )) * establish * orcl * 0
</Txt>
</Msg>

 

Set the format and check that returncode is 1017. You can clearly see that the authenticated user (userid) computer name (userhost, useful for LAN) requests the Source ip (comment $ text)

 

SQL> set pagesize 100;
SQL> set linesize 150;
SQL> select sessionid, userid, userhost, comment $ text, spare1, ntimestamp # from aud $ where returncode = 1017;
53080 USER1
WORKGROUP \ HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = 110.800000.106) (PORT = 59584 ))
Hujf
12-MAY-16 03.52.34.569085 AM

53085. SYSTEM
WORKGROUP \ HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = 110.800000.106) (PORT = 6720 ))
Hujf
12-MAY-16 03.55.39.857892 AM

 

 

[Oracle @ localhost ~] $ Oerr ora 1, 28000
28000,000 00, "the account is locked"
// * Cause: The user has entered wrong password consequently for maximum
// Number of times specified by the user's profile parameter
// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// * Action: Wait for PASSWORD_LOCK_TIME or contact DBA
[Oracle @ localhost ~] $ Oerr ora 1, 1017
01017,000 00, "invalid username/password; logon denied"
// * Cause:
// * Action: