Oracle 21c Gradual Password Rollover : Tracking old password usage

By Mouhamadou Diaw

In a previous blog, I talked about the new feature in Oracle 21c, which allows an old and new password to be valid during a period of time. This feature, Gradual Database Password Rollover, is possible using new parameter PASSWORD_ROLLOVER_TIME.

The question that comes may be: Can the DBA track if old passwords are still being used?

Indeed, if the DBA finds that the former password is no loner being used, he can expire the ROLLOVER TIME before the end of the specified time period.

The response of the question is YES, the DBA can. In this blog let’s see how we can do this

First let’s set again the profile for The gradual password rollover

SQL> show con_name;

CON_NAME
------------------------------
PDB2


SQL> CREATE PROFILE testgradualrollover LIMIT
 FAILED_LOGIN_ATTEMPTS 4
 PASSWORD_ROLLOVER_TIME 1;  2    3

Profile created.

SQL>

Now let’s create a new user in PDB2 and let’s assign him the profile we created

SQL> create user tiger identified by "Cool_Toto_Cool"  profile testgradualrollover;

User created.

SQL> grant create session to tiger;

Grant succeeded.

SQL>

Now let’s change the password of the user tiger

SQL> alter user tiger identified by "Thiere_ak_Mboum";

User altered.

SQL>

At this time the user tiger should be able to connect with both passwords

Former password

oracle@oraadserver:/home/oracle/ [PROD (CDB$ROOT)] sqlplus tiger/"Cool_Toto_Cool"@pdb2

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 17 08:24:06 2023
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

SQL> show user
USER is "TIGER"

New  password

oracle@oraadserver:/home/oracle/ [PROD (CDB$ROOT)] sqlplus tiger/"Thiere_ak_Mboum"@pdb2

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 17 08:25:31 2023
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Fri Feb 17 2023 08:24:06 +01:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

SQL> show user
USER is "TIGER"

Tracking  usage of old passwords

Let’s first create an audit policy

SQL> CREATE AUDIT POLICY "LOGON_POL" ACTIONS LOGON WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'', ''CLIENT_PROGRAM_NAME'')), ''SQLPLUS'') > 0' EVALUATE PER  STATEMENT;

Audit policy created.

SQL>

And let’s apply the policy to user tiger

SQL> AUDIT POLICY logon_pol BY tiger;

Audit succeeded.

SQL>

Now let’s connect with the user tiger using either the new password or the former password

To verify that tiger is still using his old password, we can query the table UNIFIED_AUDIT_TRAIL

SQL > SELECT DBUSERNAME, AUTHENTICATION_TYPE, EVENT_TIMESTAMP
FROM UNIFIED_AUDIT_TRAIL WHERE ACTION_NAME='LOGON' AND EVENT_TIMESTAMP > SYSDATE-1 AND AUTHENTICATION_TYPE like '%OLD%';

DBUSERNAME
--------------------------------------------------------------------------------
AUTHENTICATION_TYPE
--------------------------------------------------------------------------------
EVENT_TIMESTAMP
---------------------------------------------------------------------------
TIGER
(TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=192.168.2.21)(PORT=39202)
));(LOGON_INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI)));
17-FEB-23 11.15.22.063495 AM


SQL>

Then check for the LOGON_INFO, if the value is VERIFIER=12C-OLD then the old password is used.

Conclusion

Hope this blog help