To Check Failed login attempts count in oracle database for security purpose. It helps you to identify the username and what action has been performed by it. There are some other details which you can check from DBA_AUDIT_SESSION view.
set lin 300
set pagesize 300
col username format a25
select USERNAME, ACTION_NAME, RETURNCODE, count(*)
from DBA_AUDIT_SESSION
where TIMESTAMP = (sysdate-1)
and RETURNCODE != 0
group by USERNAME, ACTION_NAME, RETURNCODE
order by count(*);
To find invalid logins by users who has return error code=1017 in oracle database you can fire the below statement for any users:
set pages 200 lines 300
COL USERNAME format a30
col USERHOST format a25
col ACTION_NAME format a25
col OS_USERNAME format a20
SQL > select username,USERHOST,ACTION_NAME, OS_USERNAME,TIMESTAMP from dba_audit_trail
where username=upper('&user') and action name='LOGON' and returncode=1017 order by TIMESTAMP;
SQL > select username,USERHOST,ACTION_NAME, OS_USERNAME,TIMESTAMP from dba_audit_trail
where action_name='LOGON' and returncode=1017 order by TIMESTAMP;
if the return code is 28000, that mean you will get an error : ora-2800: the account is locked.
Let’s check out :
select USERNAME, PASSWORD_VERSIONS,account_status from dba_users where username='TEST';
select * from dba_profiles WHERE RESOURCE_NAME='PASSWORD_LIFE_TIME';
alter user TEST account unlock;
select USERNAME, PASSWORD_VERSIONS,account_status from dba_users where username='TEST';
You can use the below command to set the password_life_time to unlimited so that password would not be expire. User profiles are very useful in oracle database to limit the user functionality and increase the security of database. By default in oracle database the profile name is DEFAULT which is assigned to users. You can create your own profiles and set the limitation and assign it to users accordingly.
ALTER PROFILE DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;
select * from dba_profiles WHERE RESOURCE_NAME='PASSWORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —–
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
The account which already has been LOCKED did not back to normal automatically, we need to alter the user account using the below command:
alter user TEST account unlock;
After that you can monitor it by using the below command.
select USERNAME, PASSWORD_VERSIONS,account_status from dba_users where username='ORDER_CAPTURE_USER';
USERNAME PASSWORD ACCOUNT_STATUS
—————————— ——– ——————————–
TEST 10G 11G OPEN
Click here for checking USER last login details in oracle database
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.