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 minuter for password expire
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 1/1440;

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