Search this Blog

Wednesday, January 22, 2025

Password rollover time

A new security feature announced for Oracle 23ai has been back ported to 19c as of RU 19.12.
The feature offers the possibility to keep using an old password for a certain period after a new password for an account was set. This feature is called "Password Rollover Time".

And it is great!!

Now whenever an application account gets the password changed, one can postpone the update of the application itself to a moment where downtime can be planned. The same with regular users: If a user changes his/her password and the next day forgot that they did, they still can login with the old password. Sounds pretty cool, but the first question would be:
How about security ?
Allowing users to logon with an old password lowers the level of security, doesn't it?

It does not!!  

When a password life time is used, forcing users to change their password after a given amount of time, there is also a grace time set, within which a password must be changed it expires.

If the user changes his/her password, a kind of extra grace period added for the user or the application owner to have the new password configured in all their applications before the new password becomes valid definitely. 

Password Rollover Time is controlled by the profile a user has. The value entered is a numeric value and ranges from 0 to 60 days. When set to the default, being 0, the functionality is disabled. 
Setting/Enabling this functionality is done by setting a non-zero value like :

ALTER PROFILE USER_PROFILE LIMIT PASSWORD_ROLLOVER_TIME 10;

This gives a user 10 days to still keep using their old password after a password has been changed.
So the value given is in days. To set the value e.g. to 6 hours enter the value as following:

ALTER PROFILE USER_PROFILE LIMIT PASSWORD_ROLLOVER_TIME 6/24;

Important:
When also the PASSWORD_GRACE_TIME and PASSWORD_LIFE_TIME are used in the same profile, the maximum number or days for PASSWORD_ROLLOVER_TIME can not be greater than the PASSWORD_GRACE_TIME.
This is expected, otherwise password life time would be extended.
Also, if you try to set PASSWORD_ROLLOVER_TIME to a value larger than PASSWORD_GRACE_TIME an error is thrown:

>> ALTER PROFILE KRG_USERS LIMIT
   PASSWORD_GRACE_TIME 10
   PASSWORD_ROLLOVER_TIME 20
Error at line 1
ORA-02377: invalid profile limit PASSWORD_ROLLOVER_TIME

The timer will start at the first login after a password has been reset. The last date/time when the password was reset, can be seen in the dba_users table. 
Also, when a password rollover has been enabled from the profile, the account status for that users changes from "OPEN" to "


An example:
Create a profile that has grace_time, life_time and rollover_time for a password set:

CREATE PROFILE PWROLLTIME LIMIT
  PASSWORD_GRACE_TIME 7
  PASSWORD_LIFE_TIME 90
  PASSWORD_ROLLOVER_TIME 6;

Create a user that uses this profile:

CREATE USER ORCL
  IDENTIFIED BY P0wer_Rail
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE PWROLLTIME
  ACCOUNT UNLOCK;

GRANT CREATE SESSION TO ORCL;

ALTER USER ORCL QUOTA UNLIMITED ON USERS;

Now when the password is changed, either by the user, or by and administrator, the account status in dba_users changes:

SQL> ALTER USER ORCL IDENTIFIED BY ELEC_TR4IN;

SQL> SELECT USERNAME, ACCOUNT_STATUS, PASSWORD_CHANGE_DATE 
     FROM  DBA_USERS WHERE USERNAME='ORCL';

USERNAME   ACCOUNT_STATUS                   PASSWORD_CHANGE_DATE
---------- -------------------------------- --------------------
ORCL       OPEN & IN ROLLOVER               22-JAN-25           
1 row selected.


Following the created profile, this state will remain for the coming 6 days. After 6 days the account_status will change back to "OPEN" and the old password will not be valid anymore. 
If within these 6 days the password of this user is changed again, then first change password is not valid anymore. Within one period of PASSWORD_ROLLOVER_TIME, always the oldest and most new password are valid;

So if we would issue another password change within the 6 days, on the account above:

SQL> ALTER USER ORCL IDENTIFIED BY ORACLE_4U;

then the user can login using the passwords P0wer_Rail and ORACLE_4U.

In the example above these two password will both be valid until Jan 28th.
If in the period between now and Jan 28th the rollover time in the profile is changed, the validness of the passwords is adjusted accordingly.










Thursday, January 9, 2025

Restore database from NFS storage

In this blog the restore of an Oracle database is described. The database was, before it got lost, daily backed up to an NFS mounted storage device. Next to that, every 10 minutes backups of the archived log files were created.

Starting point:

  • Clean installed VM, no database running
  • File systems created and sized to needed size
  • No DB, no spfiles etc. found on the system

 

Steps:

  • Set ORACLE_SID
  • Mount NetApp storage for this database under /mnt/backup/$ORACLE_SID
  • Find newest backup file that contains the spfile and restore it

     ls -lrt /mnt/backup/$ORACLE_SID/c*

     If you do not have a valid spfile or init.ora RMAN has the ability to nomount an instance without the requirement of a spfile. This will allow you to restore your spfile from a valid backup.

$ rman target / 
( do not connect to the recovery catalog, as it will not find this forcefully started instance)

RMAN> startup nomount force;

     To be able to restore the spfile, we do need the DBID for the database to be restored. Use SQLplus and query the DBID from the RMAN catalog;

SQL>  select db_id from  db where reg_db_unique_name='DBAPRDC';

  • Return to RMAN and execute the following statements:

RMAN> set DBID=<ID from query>;

RMAN> restore spfile from ‘<file found in ls -lrt command>';

-          Shutdown the database and restart  it in nomount using the restored spfile and then connect to the RMAN catalog:

RMAN> shutdown immediate;

RMAN> startup nomount;

Quit RMAN and restart using the recovery catalog;

rman target / rcvcat /@tsmora

  • Restore the controlfiles from the same backup file that held the spfile:

RMAN> restore controlfile from ‘<file found in ls -lrt command>’;

The control files are automatically restored to the correct location.

  • Restore and recover database

Now it is time to restore and recover the database.
Since we assume that we don’t have anything but the backup of the database, a full recovery is not possible as we are missing the latest redo information from the online redo files. We do have to recovery with the amount of redo we have from archived log backups:


RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database until available redo;

Depending on the database size, this may take a while.

  • Open the database using RESETLOGS, to let the new redo-log file being created:

RMAN> alter database open resetlogs;

That completes the restore of the database.
Remember that the last couple minutes between the latest archivelog backup and the point in time where database got lost are gone. Data inconsistencies may therefore exist.