Oracle 11gR2, 12c: creating a user profile with unlimited password expiration

Environment: Oracle database 11.2.0.3.0

When you create a new user account in Oracle 11g, the user is by default assigned the “DEFAULT” profile. This isn’t something that changed from previous versions. However, the definition of the “DEFAULT” profile changed in Oracle 11g.  Especially important to notice is the “PASSWORD_LIFE_TIME” resource name; this changed from UNLIMITED to 180 in 11g. Basically, this means that after 180 days your user accounts will get “ORA-28002: the password will expire within 7 days” warnings and their account will expire after 7 days (the grace period) if no further action is taken.  And of course this will happen when the DBA is on holiday ;-)

To view the definition of the “DEFAULT” profile, you can execute the following query (with user SYS as SYSDBA):

select * from dba_profiles where profile=’DEFAULT’ and resource_name in (‘PASSWORD_LIFE_TIME’,’PASSWORD_GRACE_TIME’)

This is the output for 10g:

PROFILE,RESOURCE_NAME,RESOURCE_TYPE,LIMIT
DEFAULT,PASSWORD_LIFE_TIME,PASSWORD,UNLIMITED
DEFAULT,PASSWORD_GRACE_TIME,PASSWORD,UNLIMITED

And this is how it looks in 11g:

PROFILE,RESOURCE_NAME,RESOURCE_TYPE,LIMIT
DEFAULT,PASSWORD_LIFE_TIME,PASSWORD,180
DEFAULT,PASSWORD_GRACE_TIME,PASSWORD,7

One way to avoid the expiration of passwords after 180 days would be to change the definition of the DEFAULT profile, but I wouldn’t touch this. I would keep the DEFAULT profile for user accounts that require regular password changes, like developer accounts. For the accounts that need to keep their passwords unexpired, you could create a new user profile. Here is an example:

CREATE PROFILE "UNLIMITED_PWD_EXPIRATION" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G

Besides having an unlimited password expiration, this user profile also enables the password verification function “VERIFY_FUNCTION_11G”, which adds some required complexity to the user passwords. This function can be installed by running the utlpwdmg.sql script in $ORACLE_HOME/rdbms/admin:

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 18 13:03:05 2012

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

SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/utlpwdmg.sql

Function created.

Profile altered.

Function created.

SQL> exit

To use the “UNLIMITED_PWD_EXPIRATION” profile, you can specify it in the CREATE USER… statement for new users, or use ALTER USER… to switch existing users to the new profile (note: this will not check the complexity of the passwords!).

Examples:

CREATE USER new_user IDENTIFIED BY <complex password> PROFILE  “UNLIMITED_PWD_EXPIRATION”;

ALTER USER existing_user PROFILE  “UNLIMITED_PWD_EXPIRATION” IDENTIFIED BY <new complex password>;

Update:

This also works for Oracle 12c and higher, In 12c some new password verify functions have been introduced: ora12c_verify_function and ora12c_strong_verify_function.

See this blog post for more information.

 

 

Matthias