Introduction to Oracle Password Verification and Complexity Function

Security is in the news again.  It seems there’s no greater click-bait than a story about indecent photos of beautiful young Hollywood actresses stolen from their iPhones. Find a way to rope a cute kitten into the story and the Internet might very well explode. The current theories abounding seem to suggest that the vulnerabilities lay not with Apple, but

Security is in the news again.  It seems there’s no greater click-bait than a story about indecent photos of beautiful young Hollywood actresses stolen from their iPhones. Find a way to rope a cute kitten into the story and the Internet might very well explode.

The current theories abounding seem to suggest that the vulnerabilities lay not with Apple, but with the strength of the actresses’ passwords – or, rather, the lack of it. I guess the old adage is true: it doesn’t matter how many bolts you’ve got on the door if you’re going to leave it ajar.

It is our duty as database administrators to nudge users towards more complex passwords, and Oracle furnishes us with the tools to do so. If you created your database using the DBCA (configuration assistant), you should have the native password complexity functionality switched on by default; if you created your database using the CREATE DATABASE statement, you will need to enable it yourself.

It is easily done. In your $ORACLE_HOME/rdbms/admin folder you should find the utlpwdmg.sql file. What this script contains depends largely on what version of the Oracle database you have installed: before 11g, it would install the verify_function function; for 11g, it would install verify_function_11G; and for 12C, it would additionally install ora12c_verify_function and ora12c_strong_verify_function.

Oracle’s developers would probably not thank me for saying this, but there hasn’t been a great deal of evolution in the years between 10g’s verify_function and 12c’s ora12c_strong_verify_function, even though this latest incarnation apparently adheres to the Department of Defense’s Database STIG (Security Technical Implementation Guide). The real strength comes from applying customisations to adapt the function to our particular needs.

But first, an explanation.

These functions all accept a username and a password; they proceed to test the complexity of the password in ways that we will go into later. They are attached to user profiles (utlpwdmg.sql attaches them to the DEFAULT profile, however you can edit this if you have a more granular system). Once attached to the profile, every time a user of that profile creates a new password Oracle will automatically refer to the function to examine its strength.

Profiles can be configured in the following ways:

 

PASSWORD_LIFE_TIME Number of days before the password expires
PASSWORD_GRACE_TIME Number of days the user has to change their password on expiration before all connections are rejected.
PASSWORD_REUSE_TIME Number of days during which a password cannot be reused.
PASSWORD_REUSE_MAX Number of other passwords that must be used before one can be reused
FAILED_LOGIN_ATTEMPTS Number of failed login attempts allowed before the account is locked
PASSWORD_LOCK_TIME Number of days that an account will be locked following a series of failed login attempts.

 

The following grid shows the values used by the diffferent verify functions.

ora12c_verify_function ora12c_strong_verify_function
PASSWORD_LIFE_TIME 180 60
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED 365
PASSWORD_REUSE_MAX UNLIMITED 5
FAILED_LOGIN_ATTEMPTS 10 3
PASSWORD_LOCK_TIME 1

 

However, while Jennifer Lawrence and Rihanna were probably guilty of not changing their passwords regularly enough, it was the weakness of their chosen passwords that probably left them most exposed. The Oracle rules enforcing complexity can be found within the verify functions themselves.

ora12c_verify_function

  • Password must be at least 8 characters long
  • Password must contain at least 1 letter
  • Password much contain at least 1 number
  • Password must not contain the username
  • Password must not contain the username reversed
  • Password must not contain the server name
  • Password must not be ‘oracle’, ‘password1’ or any of a list of 10 easily guessed passwords.
  • Password must differ from the previous password by at least 3 characters.

 

The rules for ora12c_strong_verify_function are a little tighter

  • Password must be at least 9 characters long
  • Password must contain at least 2 uppercase letters
  • Password must contain at least 2 lowercase letters
  • Password must contain at least 2 numbers
  • Password must contain at least 2 special characters
  • Password must differ from the previous password by at least 4 characters

Now, if you’re a busy Hollywood actress you’re probably thinking that that is too much faff for a password and, depending on the importance of your database, you may be right. On the other hand, you may decide that these rules are not stringent enough; in fact, Oracle themselves advice that we customise them.

A number of customisations are within the reach of some simple PL/SQL.

  • Many experts – including ironically enough, Oracle themselves – recommend a minimum password length of 12 characters.
  • A quick internet search will reveal that there are far more than 10 simple, easily-guessed passwords. I would recommend finding a reasonably comprehensive list, storing it in a table and checking against it from your verify function.
  • Guard against passwords in which simple obfuscation is used to mask basic dictionary words, such as p@ssw0rd or w31c0m3.
  • Guard against personal identifying details such as date of birth and middle name, if you have access to these details.

A word of warning.

There are those that argue that complicated password complexity rules actually weaken enterprise security.  The longer and more arcane a password is, the more likely a person is to simply jot it down on a post-it note and stick it to the side of their monitor.  Which brings us full circle to that old security adage: it doesn’t matter how many bolts you’ve got on the door if you’re going to leave it ajar.