##########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;
-------------------------------------------------------------------------------------------------------------------
----------------------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
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
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" LIMITCPU_PER_SESSION UNLIMITEDCPU_PER_CALL UNLIMITEDCONNECT_TIME UNLIMITEDIDLE_TIME UNLIMITEDSESSIONS_PER_USER UNLIMITEDLOGICAL_READS_PER_SESSION UNLIMITEDLOGICAL_READS_PER_CALL UNLIMITEDPRIVATE_SGA UNLIMITEDCOMPOSITE_LIMIT UNLIMITEDPASSWORD_LIFE_TIME UNLIMITEDPASSWORD_GRACE_TIME DEFAULTPASSWORD_REUSE_MAX UNLIMITEDPASSWORD_REUSE_TIME UNLIMITEDPASSWORD_LOCK_TIME 1FAILED_LOGIN_ATTEMPTS 10PASSWORD_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
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>;
