Oracle 12c new password verify function

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 Name Limit Description
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 Name Limit
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 Name Limit
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:

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.

5 thoughts on “Oracle 12c new password verify function

  1. Paul P. Miller

    point a: As user SYSTEM can utilize this function to check password complexity, I do not doubt if the content of the function has anything wrong, as long as i am using exactly the same ALTER USER statement, same password in SYSTEM’s session and user A’s session.

  2. StefanStefan Oehrli Post author

    Hi you may not use the same statement in SYSTEM session as in USER Session since the user is lacking privileges. If a user would like to change the password it should any way use the PASSWORD Statement in SQL Plus or the corresponding OCI Procedure. Changing password with alter system has the disadvantage the password is visible on an unencrypted SQLNet connection. With PASSWORD it is not visible.

    Below I’ll try to change the password with alter system as SYS or as SCOTT

    SQL> CONNECT / AS sysdba
    Connected.
    SQL> ALTER USER scott IDENTIFIED BY tiger2;
    ALTER USER scott IDENTIFIED BY tiger2
    *
    ERROR at line 1:
    ORA-28003: password verification FOR the specified password failed
    ORA-20001: Password LENGTH less than 8


    SQL> conn scott/tiger
    Connected.
    SQL> ALTER USER scott IDENTIFIED BY tiger2;
    ALTER USER scott IDENTIFIED BY tiger2
    *
    ERROR at line 1:
    ORA-28221: REPLACE NOT specified

    SQL> ALTER USER scott IDENTIFIED BY tiger2 REPLACE tiger;
    ALTER USER scott IDENTIFIED BY tiger2 REPLACE tiger
    *
    ERROR at line 1:
    ORA-28003: password verification FOR the specified password failed
    ORA-20001: Password LENGTH less than 8

    SQL> password
    Changing password FOR SCOTT
    OLD password:
    NEW password:
    Retype NEW password:
    ERROR:
    ORA-28003: password verification FOR the specified password failed
    ORA-20001: Password LENGTH less than 8


    Password unchanged
  3. Jonathan Begazo

    Great work, and thanks for the clarification on changing the password. Can this function be used on 11g databases too, or is it particular for 12c versions? Thanks in advance.

    Also, is there a work around for securing the “alter user identified by ” as the sys user? In other words, how can I enforce that passwords differ from previous passwords when the command “alter user identified by is executed by the sys user. Currently, this type of password changes don’t check for differences between the old and new passwords when issued by the sys user. Please advice.

  4. StefanStefan Oehrli Post author

    Hi

    I do not see any reason why it should not work for 11g databases. To be sure I’ve just tested it on my 11.2.0.3.7 database and it worked as expected.

    Stefan

  5. Pingback: Oracle password verify function | Blog: Keyword Oracle

Comments are closed.