Managed Password parameter in profile for User in Oracle

Managed Password parameter in profile for User in Oracle

In user profile manage the password parameter include the following parameters:

Password_parameters

[FAILED_LOGIN_ATTEMPTS      expr|UNLIMITED|DEFAULT]
[PASSWORD_LIFE_TIME         expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_TIME        expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_MAX         expr|UNLIMITED|DEFAULT]
[PASSWORD_LOCK_TIME         expr|UNLIMITED|DEFAULT]
[PASSWORD_GRACE_TIME        expr|UNLIMITED|DEFAULT]
[PASSWORD_VERIFY_FUNCTION   function_name|NULL|DEFAULT]

Check the user belong to which profile

select profile from dba_users where username = 'TEST';

Profile
------------
DEFAULT

Check resource_name and limit defined for the profile.
It include both resource parameter and password parameter. We are working on password parameter in this blog.

select resource_name, limit from DBA_PROFILES where profile = 'DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

Meaning of password resources
FAILED_LOGIN_ATTEMPTS : Attempts made for login exceed this limit will lock the account.
PASSWORD_LIFE_TIME : Number of days password can be used for authentication. It also depend upon PASSWORD_GRACE_TIME is set then password will work until grace period defined after it will not allow to connect. It will give warning only if PASSWORD_GRACE_TIME is unlimited value.
PASSWORD_GRACE_TIME : Warning is issue after number of days value of PASSWORD_LIFE_TIME exceed upto value of PASSWORD_GRACE_TIME defined after that password is expired.
PASSWORD_REUSE_TIME defined in days
PASSWORD_REUSE_MAX defined in days
Both use PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 15, then the user can reuse the password after 30 days if the password has already been changed 15 times.
PASSWORD_LOCK_TIME : Number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION : PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.

Create the password profile

CREATE PROFILE app_user_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

Alter the password parameter in profile

---defined Password must be changed after 30 days
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;

---Extra 5 day grace period for change password
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 5;

Testing
for testing you can specify minutes (n/1440) or even seconds (n/86400)
Example:

-- Change the profile setting to 1 minute for password expire
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 1/1440

--Set grace period after that 2 days
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 2;
Profile altered.

--on login with user find the following message:
C:\Users\e3019447>sqlplus test@xe
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 5 14:56:15 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28002: the password will expire within 2 days
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

--rollback to unlimited.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;




Refer:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm