alter user xx  EXPIRE PASSWORD ROLLOVER PERIOD

• Enabling the Gradual Database Password Rollover

  To enable the gradual database password rollover, you must configure the PASSWORD_ROLLOVER_TIME user profile parameter.

• To configure the gradual database password rollover, set the PASSWORD_ROLLOVER_TIME parameter in the CREATE PROFILE or ALTER PROFILE statement.For example, to set the gradual password rollover time period to 1 day:

  Copy

  CREATE PROFILE prof LIMIT ... PASSWORD_ROLLOVER_TIME 1;

  Note the following:

  • You specify the rollover time period in days, but you can specify hours if you want. For example, enter 1/24 to specify 1 hour, or 6/24 (or 1/4) to specify 6 hours.
  • The minimum value for an active rollover time is 1 hour. The maximum value is 60 days or the lower value of the PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME parameter. If PASSWORD_GRACE_TIME is set to 0 (zero), then it will be ignored with respect to any limits with PASSWORD_ROLLOVER_TIME. The following table describes these limits:

    Table 3-3 Password Rollover Time Limits

• Table 3-3 Password Rollover Time Limits

  Profile Name	PASSWORD_LIFE_TIME	PASSWORD_GRACE_TIME	PASSWORD_ROLLOVER_TIME
  Default	180	7	Minimum: 1/24 (1 hour) Maximum: 7 (days)
  ORA_STIG_PROFILE	60	5	Minimum: 1/24 (1 hour) Maximum: 5 (days)
  User Custom Profile	365	90	Minimum: 1/24 (1 hour) Maximum: 60 (days)

• The default setting for PASSWORD_ROLLOVER_TIME is 0 or NULL, which

Summary

Is There any Query or tool to Identify which Database Users have Weak Passwords?

Solution

There is no such built-in utility or tool to report weak passwords for existing database users.

The recommendation would be to create a new profile or edit an existing one (default) and define the PASSWORD_VERIFY_FUNCTION limit to any of the functions mentioned below (verify_function_11G, ora12c_verify_function, ora12c_strong_verify_function, and ora12c_stig_verify_function). Finally, move your existing users to that profile and expire their passwords. Users will be forced to set a new password which will be validated by password verify function.

The quality of the database passwords is not enforced by default. A password verify function with the corresponding password resource limits has to be developed individually. As a basis, one can use the script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql to setup the default profile password resource limits, and depending the database version, create the password verify function.

a) Make sure to review the script Before executing it in your target database because the script will edit current DEFAULT profile.
- $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Edit the script as needed based on your requirements, and add any additional password validation.

NAME utlpwdmg.sql - script for Default Password Resource Limits

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

11g - In this version the script includes -Create function statements for verify_function_11G and verify_function (10g) + the "alter profile" statements to enable password verify functions

12c, 18c, and 19c - In these versions the script includes -The "alter profile" statements that enable Password Management with new built-in functions ora12c_verify_function and ora12c_strong_verify_function, and two helper functions complexity_check and string_distance, that include recommendations from Center for Internet Security[CIS Oracle 11g] and from Department of Defense Database Security Technical Implementation Guide[STIG v8R1].

There is no need to create the password verify functions manually anymore ( like 10g or 11g), they are already created, they just need to be enabled at profile level.

Note: The password verify functions and their dependent functions are moved from utlpwdmg.sql script to ORACLE_HOME/rdbms/admin/catpvf.sql

Documentation Reference: Password Complexity Verification

- verify_function_11G

- ora12c_verify_function

- ora12c_strong_verify_function

- ora12c_stig_verify_function

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/keeping-your-oracle-database-secure.html#GUID-451679EB-8676-47E6-82A6-DF025FD65156

- ora12c_verify_function

The following is verified

Password at least 8 characters

at least 1 letters

at least 1 digits

must not contain database name

must not contain user name or reverse user name

must not contain oracle

must not be too simple like welcome1

password must differ by at least 3 characters from the old password

- ora12c_strong_verify_function

The following is verified

Password at least 9 characters

at least 2 capital letters

at least 2 small letters

at least 2 digits

at least 2 special characters

password must differ by at least 4 characters from the old password

b) Create a new profile OR edit current profile and define PASSWORD_VERIFY_FUNCTION option + Other PASSWORD limits Password Management limits.

Oracle Password Management Policy(Doc ID KB111996)

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

c) Assign the new profile to existing users

Users will be forced to change the password and they will need to define it in compliance with verify function.

alter user <user_name> profile <profile_name>;

alter user <user_name> password expire;