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.
|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).
Recommendations from Department of Defense Database Security Technical Implementation Guide (STIG v8R1).
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.
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.
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.
Verify if the password has at least 9 characters, 2 upper/lower case character, 2 digits and 2 special characters.
digit => 2, special => 2) THEN
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
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
Links all around Critical Patch Update:
- Oracle Database 22.214.171.124 utlpwdmg.sql taken from Oracle 126.96.36.199 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]
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.