查找oracle数据库失败登录用户 FAILED_LOGIN_ATTEMPTS

alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED
alter profile default limit FAILED_LOGIN_ATTEMPTS 10
alter user ioc account unlock;
ALTER USER ioc PROFILE default

------ 19c
 alter session set container=cdev;---CDB 和PDB的结果不同
 select * 
from unified_audit_trail where dbusername='TESTPWD1' and return_code=1017;

Is there any way to check if a session that has an account with a PASSWORD_ROLLOVER_TIME set has used the old password or the new password?

 

SOLUTION

That is only possible with the combination of unified auditing and a logon policy active.

You can perform a query that makes use of the AUTHENTICATION_TYPE field for a LOGIN audit record to find users who still use their old passwords.
The unified audit trail can identify which users are still connecting to the database using an old password. The AUTHENTICATION_TYPE field for a LOGON audit record can show if the old verifier was used. This information enables you to find applications that have not been updated with gradual database password rollover to use the new password. The LOGON audit record indicates which application server must be updated.

 1. Connect to the database as a user who has the AUDIT_VIEWER or AUDIT_MGMT role.
 2. Execute the following query:

SELECT DBUSERNAME, AUTHENTICATION_TYPE, OS_USERNAME, USERHOST, EVENT_TIMESTAMP
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME='LOGON' AND EVENT_TIMESTAMP > SYSDATE-1
AND REGEXP_LIKE(AUTHENTICATION_TYPE, '\(VERIFIER=.*?\-OLD\)');

If there are users who are still using their old password, then the output will be similar to the following appears:

  DBUSERNAME AUTHENTICATION_TYPE OS_USERNAME USERHOST EVENT_TIMESTAMP

 
 ------ 19c
 
 select dbusername,event_timestamp,OS_USERNAME,ACTION_NAME,RETURN_CODE,UNIFIED_AUDIT_POLICIES
from unified_audit_trail where dbusername='xxx' and return_code=1017;

----12c 前

 select userid, userhost, terminal, clientid from sys.aud$ where returncode=1017;
 
 select name,LCOUNT from sys.USER$  where name='xxx'
 
 
 ---19c 默认开启了
 By the way, the Predefined Unified Audit Policies 'ORA_LOGON_FAILURES' is turned on by default on all db users, so please disable ORA_LOGON_FAILURES if you want to use new LOGON failure audit policy.

-----下面是按需定制策略

In order to decrease unnecessary audit log, it is possible to enable the policy in the following way to avoid generating LOGON failure 
audit records for unintended DB users.

CREATE AUDIT POLICY <failed_logon_pol> ACTIONS LOGON;

Option 1: AUDIT POLICY <failed_logon_pol> BY <comma separated list of suspicious/targeted DB users>;

Option 2: AUDIT POLICY <failed_logon_pol> EXCEPT <comma separated list of trusted DB users>;

Option 3: AUDIT POLICY <failed_logon_pol> BY USERS WITH GRANTED ROLES <comma separated list of DB roles>;
    In this case, we can cover all those DB users to whom the mentioned DB role(s) is either directly or indirectly granted.

CREATE AUDIT POLICY ASP_AUDPOL_13
 ACTIONS LOGON
   WHEN '(
   (SYS_CONTEXT(''USERENV'',''SESSION_USER'')= ''IOC'' ) 
 )'
 EVALUATE PER SESSION;
 
 AUDIT POLICY ASP_AUDPOL_13;
 

For example:

SQL> select dbusername,event_timestamp,OS_USERNAME,ACTION_NAME,RETURN_CODE,UNIFIED_AUDIT_POLICIES
from unified_audit_trail where UNIFIED_AUDIT_POLICIES like '%POL66%';
 

SQL> noaudit POLICY POL66 ;

Noaudit succeeded.

SQL> AUDIT POLICY POL66 by USER1,SYSTEM WHENEVER NOT SUCCESSFUL;

Audit succeeded.