Oracle-DB: Monitor gradual password rollover usage

As of Oracle DB release 19.12 it is possible to use the new and the previous password for user logon simultaneously for a limited period.
For details see New features guide.

This is a great and long-awaited feature that lowers the barriers to changing passwords with less operational risk.

But there's an obstacle for practical use:
You must be able to monitor the users who are within this rollover period and are still using the old password.

The only way of monitoring the use of old passwords known to me so far is to:
  • Use Unified Auditing
  • Create a policy for the LOGON action
  • Check for the keyword "VERIFIER=12C-OLD" in the column AUTHENTICATION_TYPE of the logon records in unified audit trail.
The following SQL provides an overview over all the users who are currently within a password rollover period, combined with info about old password usage: 
SELECT u.Account_Status, u.Password_Change_Date, u.Profile,
       a.*,
       u.Password_Change_Date + NUMTODSINTERVAL(pr.Limit, 'SECOND') Rollover_Expiration_Date,
       u.Password_Change_Date + NUMTODSINTERVAL(pr.Limit, 'SECOND')-SYSDATE Remaining_Days_for_Rollover
FROM   DBA_Users u
LEFT OUTER JOIN DBA_Profiles pr ON pr.Profile = u.Profile and pr.Resource_Name = 'PASSWORD_ROLLOVER_TIME'
LEFT OUTER JOIN (
                 SELECT NVL(DBProxy_Username, DBUserName) UserName, DBUserName, COUNT(*) Logon_Count,
                        MIN(Event_Timestamp) Min_TS, MAX(Event_Timestamp) Max_TS,
                        COUNT(DISTINCT OS_UserName         ) OS_UserName_Cnt,          MIN(OS_UserName         ) Min_OS_UserName,
                        COUNT(DISTINCT UserHost            ) UserHost_Cnt,             MIN(UserHost            ) Min_UserHost,
                        COUNT(DISTINCT Terminal            ) Terminal_Cnt,             MIN(Terminal            ) Min_Terminal,
                        COUNT(DISTINCT Instance_ID         ) Instance_ID_Cnt,          MIN(Instance_ID         ) Min_Instance_ID,
                        COUNT(DISTINCT External_UserID     ) External_UserID_Cnt,      MIN(External_UserID     ) Min_External_UserID,
                        COUNT(DISTINCT Global_UserID       ) Global_UserID_Cnt,        MIN(Global_UserID       ) Min_Global_UserID,
                        COUNT(DISTINCT Client_Program_Name ) Client_Program_Name_Cnt,  MIN(Client_Program_Name ) Min_Client_Program_Name,
                        COUNT(DISTINCT DBLink_Info         ) DBLink_Info_Cnt,          MIN(DBLink_Info         ) Min_DBLink_Info
                 FROM   Unified_Audit_Trail
                 WHERE  Action_Name = 'LOGON'
                 AND    Authentication_Type LIKE '%VERIFIER=12C-OLD%'
                 GROUP BY DBUserName, DBProxy_Username
                ) a ON a.UserName = u.UserName
WHERE  u.Account_Status LIKE '%ROLLOVER%'
;


A bit more convenient you may get this info using the analysis tool Panorama.
Click at the number in column "Logons with old password" gives the ability to quickly evaluate the individual unified audit trail records for logons with old password for that user.

Comments

Post a Comment

Popular posts from this blog

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo
Read more

Oracle-DB: Link between audit trail and active session history

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it a
Read more