Even with Oracle Database 12c, 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 
utlpwdmg.sql to setup the default password resource limits. The script is provided by Oracle and is used to update the default profile. It has been updated for Oracle Database 12c, but it still does not run automatically when creating a database. The 12c DBCA is missing a flag or a radio button to select something like extended standard security settings as this was known from 11g.
New Password Resource Limits
Without modification, utlpwdmg.sql updates the profile DEFAULT, which is the default profile for all users. The following limits are the same as of Oracle Database 11g except a different password verify function.
Resource NameLimitDescription
| PASSWORD_LIFE_TIME | 180 | Sets the number of days the user can use his current password. |
| PASSWORD_GRACE_TIME | 7 | Sets the number of days that a user has to change his password before it expires. |
| PASSWORD_REUSE_TIME | UNLIMITED | Sets the number of days before which a password cannot be reused. |
| PASSWORD_REUSE_MAX | UNLIMITED | Sets the number of password changes required before the current password can be reused. |
| FAILED_LOGIN_ATTEMPTS | 10 | Specify the number of failed attempts to log in to the user account before the account is locked. |
| PASSWORD_LOCK_TIME | 1 | Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. |
| PASSWORD_VERIFY_FUNCTION | ora12c_verify_function | PL/SQL password complexity verification function to enforce password complexity. |
In the comment of the script you find other password resource limits. Recommendations from Center for Internet Security (CIS Oracle 11g).
Resource NameLimit
| PASSWORD_LIFE_TIME | 90 |
| PASSWORD_GRACE_TIME | 3 |
| PASSWORD_REUSE_TIME | 365 |
| PASSWORD_REUSE_MAX | 20 |
| FAILED_LOGIN_ATTEMPTS | 3 |
| PASSWORD_LOCK_TIME | 1 |
| PASSWORD_VERIFY_FUNCTION | ora12c_verify_function |
Recommendations from Department of Defense Database Security Technical Implementation Guide (STIG v8R1).
Resource NameLimit
| PASSWORD_LIFE_TIME | 60 |
| PASSWORD_REUSE_TIME | 365 |
| PASSWORD_REUSE_MAX | 5 |
| FAILED_LOGIN_ATTEMPTS | 3 |
| PASSWORD_VERIFY_FUNCTION | ora12c_strong_verify_function |
New Functions
The function has been cleaned up by Oracle. As before, there are the two functions verify_function (10g) and verify_function_11G (11g). New there are four more functions for 12c, ora12c_verify_function and ora12c_strong_verify_function and two helper functions complexity_check and string_distance.
string_distance
This function calculates the Levenshtein distance between two strings ‘s’ and ‘t’ or a bit simpler how much do two strings differ from each other. The Levenshtein algorithms has already be used in the old verify_function_11G. It is now just a function for itself to be easier used in custom password verify functions.
differ := string_distance(old_password, password);
complexity_check
This function verifies the complexity of a password string. Beside the password string it accepts a few value to describe the complexity. Nothing basically new but it makes it a bit easier to define custom password verify functions.
- chars – All characters (i.e. string length)
- letter – Alphabetic characters A-Z and a-z
- upper – Uppercase letters A-Z
- lower – Lowercase letters a-z
- digit – Numeric characters 0-9
- special – All characters not in A-Z, a-z, 0-9 except DOUBLE QUOTE which is a password delimiter
Verify if the password has at least 8 characters, 1 letter and 1 digit.
IF NOT complexity_check(password, chars => 8, letter => 1, digit => 1) THEN RETURN(FALSE); END IF;
Verify if the password has at least 9 characters, 2 upper/lower case character, 2 digits and 2 special characters.
IF NOT complexity_check(password, chars => 9, upper => 2, lower => 2, digit => 2, special => 2) THEN RETURN(FALSE); END IF;
ora12c_verify_function
This function is the new 12c password verify function. It enforce a similar respectively slightly stronger password complexity as verify_function_11G. verify_function_11G just checked for DB_NAME or ORACLE with 1 to 100 attached. e.g. oracle1 or oracle83. With the new function DB_NAME or ORACLE may not be part of the password at all. 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
This function is provided to give stronger password complexity. It considers recommendations of the Department of Defense Database (STIG) with the following limits.
- 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
References
Links all around Critical Patch Update:
- Oracle Database 12.1.0.1 utlpwdmg.sql taken from Oracle 12.1.0.1 on Linux x86-64bit
- Oracle Password Management Policy [114930.1]
- Password Verify Function Not Enforcing Difference Between Old and New Passwords [816932.1]
- Is the Password Encrypted when I Logon and Other Related Questions [271825.1]
- Wikipedia description for the Levenshtein distance
- Center for Internet SecurityCIS Oracle 11g
- Department of Defense Database Application Security – Database (Oracle)
- Department of Defense Database Oracle 11 Database STIG, Version 8 Release 1.9
- All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]
Conclusion
Oracle Database 12c brings a slightly enhanced utlpwdmg.sql script which can much easier be adapted to custom requirements. Nevertheless a DBA has to define a password verify function himself or run utlpwdmg.sql. Oracle does not enforce passwords by default. It is recommended to define different profiles for different user groups e.g. DBA, App Users, Schema Owner etc. and to use as well a password verify function. The examples in utlpwdmg.sql can and must be adapted to fulfill minimal security requirements.
