Skip to content

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

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
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

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

3 thoughts on “Oracle 21c Gradual Password Rollover : Tracking old password usage”

  1. using this for some time for rotating application passwords to catch any app that didnt have a password changed, we noticed a VERIFIER=12C-OLD entry with a 1017 return code in audit trail (invalid user password combo) which doesnt make sense as when in grace period it should take the OLD password. Turns out it was just an altogether wrong password that wasnt the NEW or OLD , oracle is tracking that as an OLD entry when it clearly isnt. Can replicate up to 19.27. May help someone…

  2. Seems like this audit only captures if password attempt fails? Successful use of old password while still in rollover period doesn’t show as OLD. Any way to see that all recent connections are using new password while still in rollover period?

Leave a Comment

Your email address will not be published. Required fields are marked *