Audit Unsuccessful Logins

Audit Unsuccessful Logins

select username,os_username, userhost, client_id, trunc(timestamp),count(*) failed_logins from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password timestamp > sysdate -7 group by username,os_username,userhost, client_id,trunc(timestamp);

To get more details on the record found for "ALTER USER" command issued:


SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL /tmp/AUDIT_INFO.html
select * from dba_audit_trail where action_name='ALTER USER' and timestamp> sysdate-10;
select * from unified_audit_trail where EVENT_TIMESTAMP > sysdate-10;


SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
Alter user commad from dba_audit_trail:

SQL> select username,TIMESTAMP,ACTION_NAME,RETURNCODE,SQL_TEXT from dba_audit_trail where ACTION_NAME like '%ALTER USER%';

How to check the password_verify_function:

set lines 170
col owner format a30
col name format a45
col text format a50
select * from dba_source where owner = 'SYS' and name in(select limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION' and limit <> 'NULL') order by name, line;


How to check the dba profile from sqlplus:

set lines 170
col profile format a20
col limit format a30
select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from dba_profiles where PROFILE='<Profile_name>';

How to check the user status and details:
SQL> select USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE from dba_users where username ='User_name';


How to check the last_login details:

SQL> SELECT USERNAME, LAST_LOGIN FROM DBA_USERS WHERE USERNAME='User_name';