How to Change Password Expiry to Unlimited in Oracle 11gR2

##########How to change password Expiry to unlimited in Oracle 11gR2##############
----------------------Check user (SCOTT) password expiry date-----------------------------

select username, account_status, to_char(expiry_date, 'DD-MM-YYYY') EXP_DATE from dba_users where username = 'SCOTT';

---------------------------Check User(SCOTT) Profile----------------------------------------------

select profile from dba_users where username='SCOTT';

------------------------------Check this Profile Expiry Limit------------------------------------

select * from dba_profiles where profile = 'DEFAULT' and resource_name LIKE 'PASSWORD_LIFE_TIME';

-----------------------Change Expiry to Unlimited for this Profile-------------------------------

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

------------------------------Reset Password for User(SCOTT)-------------------------------------

ALTER USER SCOTT IDENTIFIED BY tiger;
-------------------------------------------------------------------------------------------------------------------

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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>;