I received a warning message this morning while I was connecting to an Oracle database: “ORA-28002: the password will expire within 7 days“. The database account and password were still valid and this message simply meant that it was time to change the password for security reasons. This database was used for testing purpose in a VM and there was no security threats here. An alternative solution was therefore to create a new database user profile to turn off the password expiration. I have documented how I did it step by step in this tutorial.
Oracle database users account are associated with a user profile that defines the password policies among other things. The default policy defines that passwords expire after 180 days, and that explained why I had received the warning message:
I quickly changed the user (SYSTEM in that example) password using the ALTER USER statement:
The password expiration policy was not too important in this VM environment. Therefore I decided to create a new user profile to turn it off and to associate it with some users.
First I queried for the user profiles associated with the database users using the “SELECT username, profile FROM dba_users ORDER BY username;” statement. As you can see in the next screenshot, all users (including the SYSTEM user) were associated with the predefined “DEFAULT” user profile.
I used the “SELECT RESOURCE_TYPE, RESOURCE_NAME, LIMIT from DBA_PROFILES WHERE PROFILE=’DEFAULT’;” statement to see the definition of the “DEFAULT” user profile.
There was two types of resources defined in this profile: The “KERNEL” ones were used to specify resource limits for a user while the “PASSWORD” ones defined the password policies. The eight “PASSWORD” resources are:
- FAILED_LOGIN_ATTEMPS to specify the number of consecutive failed attempts to log in to the user account before the account is locked.
- PASSWORD_LIFE_TIME to specify the number of days the same password can be used for authentication.
- PASSWORD_REUSE_TIME to specify the number of days before which a password cannot be reused.
- PASSWORD_REUSE_MAX to specify the number of password changes required before the current password can be reused.
- PASSWORD_VERIFY_FUNCTION to specify the name of a PL/SQL password complexity verification script used when setting a new password.
- PASSWORD_LOCK_TIME to specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
- PASSWORD_GRACE_TIME to specify the number of days after the grace period begins during which a warning is issued and login is allowed.
- INACTIVE_ACCOUNT_TIME to specify the permitted number of consecutive days of no logins to the user account, after which the account will be locked.
I didn’t want to alter the “DEFAULT” user profile as it could potentially be reset during an upgrade of the database server version. Instead I decided to create a new user profile that would turn off password expiration by executing the following statement: “CREATE PROFILE C##NOPWDEXPIRATION LIMIT PASSWORD_LIFE_TIME UNLIMITED;” as an administrator. I didn’t included any other password resources so their values would therefore be inherited from the DEFAULT user profile.
The profile name started here with the “C##” prefix (specified by the COMMON_USER_PREFIX initialization parameter) because it was a CDB common profile. If it had been a PDB local profile its name could not have started with the “C##” or “c##” prefix. The “LIMIT PASSWORD_LIFE_TIME UNLIMITED” part specified that the password should not expire.
The new was profile was now available and I could assign it to the SYSTEM user by using the “ALTER USER SYSTEM PROFILE C##NOPWDEXPIRATION;” statement.
As you cas see it was something very easy to do, and It will no longer be mandatory to change the password of the “SYSTEM” user account in this environment.
Disclaimer
The post How to create an Oracle 19c DB user profile to turn off password expiration appeared first on MyBIJourney
