Check the system default policy, continuous verification 10 times the wrong account will be locked
Sql>SelectResource_name, limit from Dba_profiles where profile='DEFAULT'; Resource_name LIMIT-------------------------------- ----------------------------------------composite_limit unlimitedsessions_per_user unlimitedcpu_per_session unlimitedcpu_per_call Unlimitedlogical_reads_per_session Unlimitedlogical_reads_per_call Unlimitedidle_time UN Limitedconnect_time UNLIMITEDPRIVATE_SGA UNLIMITEDfailed_login_attempts TenPassword_life_time theresource_name LIMIT-------------------------------- ----------------------------------------password_reuse_time Unlimitedpassword_reuse_max unlimitedpassword_verify_function NULLPASSWORD_LOC K_time1Password_grace_time7 -Rows selected.
View User Locked status
Select username,account_status from dba_users where Username='USER1'; USERNAME account_status--------------------------------------------------------------USER1 LOCKED (TIMED) SQLSelect name,lcount from user$ where Name='USER1'; NAME LCOUNT----------------------------------------USER1
Handle the problem first, change the number of validation errors to unrestricted, unlock the user
sql> Alter profile default limit Failed_login_attempts unlimited; Profile altered. SQL> alter user user1 account unlock; User altered.
Review the number of user authentication errors, and if this account has been verified, you can see that the number of times has been increasing
Select name,lcount from user$ where Name='USER1';
Tracing the request source IP through the log file/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml, but the effect is not ideal
1. Cannot see the requested user name, cannot see the request result, may be wrong to judge the request source IP
2. Log too many, temporarily think of keyword filtering
[email protected] adump]$ Lsnrctl statuslsnrctl forLinux:version11.2.0.1.0-Production on A-may- . One: $: theCopyright (c)1991, the, Oracle. All rights reserved. Connecting to (DESCRIPTION= (address= (protocol=tcp) (HOST=IZ11Y546TZLZ) (port=1521)) STATUS of the LISTENER------------------------Alias listenerversion Tnslsnr forLinux:version11.2.0.1.0-Productionstart DateTen-may- . the: -: +Uptime2Days2hr.1Min. -sectrace level offsecurity on:local OS authenticationsnmp offlis Tener Parameter File/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/Listener.oralistener Log File/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xmllistening Endpoints Summary ... [[Email protected]~]$Tail-f/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/Log.xml<msg Time='2016-05-12t11:52:33.423+08:00'Org_id='Oracle'Comp_id='Tnslsnr'type='UNKNOWN'Level=' -'Host_id=' localhost'host_addr='10.174.70.172'> <txt> A-may- . One: the: -* (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.82.160.106) (port=59584)) * Establish * ORCL *0</txt></msg>
Set the format, check ReturnCode for 1017, can see very clearly authenticated User (UserID) computer name (Userhost, LAN useful) Request source IP (comment$text)
Sql> Set pagesize -; SQL> Set Linesize Max; SQL>Selectsessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017; 53080USER1WORKGROUP\HUJF-pcauthenticated by:database; Client Address: (Address= (protocol=tcp) (host=110.82.160.106) (port=59584)) HUJF A-may- - 03.52.34.569085AM53085SYSTEMWORKGROUP\HUJF-pcauthenticated by:database; Client Address: (Address= (protocol=tcp) (host=110.82.160.106) (port=6720)) HUJF A-may- - 03.55.39.857892Am
[Email protected] ~]$ Oerr ora2800028000,00000,"The account is locked"//*cause:the user have 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[Email protected] ~]$ Oerr ora101701017,00000,"invalid Username/password; logon denied"//*cause://*action:
Ora-28000:the account is locked to check which specific IP address caused