Password Strength Revisited

This is an update to one of my very first posts, bringing it up to date to reflect current password verification techniques for Oracle 19c.

Oracle has made the enforcement of password strength criteria a lot easier over the years. Their documentation contains a detailed example on how to create a password verification function and attach it to user profiles. One thing I have found useful to add to their basic criteria, besides making them generally stronger in terms of complexity requirements, is a dictionary search that prevents users from incorporating known dictionary words into their passwords. The default VERIFY_FUNCTION included in Oracle 19c even includes a placeholder for a dictionary check, though Oracle does not actually provide a robust check out of the box. On Linux and Unix systems this is remarkably easy to do however, as these operating systems generally come with their own dictionary files that I can easily use to load a reference table for my modified verification function.

In Red Hat Enterprise Linux or Oracle Linux, for example, the operating system dictionary is located in /usr/share/dict/linux.words. To load this into my database and use it to validate password changes, I use the following steps as SYSDBA (password verification functions must be owned by SYS):

1. Create a table to hold my dictionary words. I run the following command in SQL*Plus to create a single column lookup table for dictionary words:

CREATE TABLE "SYS"."WORDS" 
   ("WORD" VARCHAR2(50 byte) NOT NULL)
TABLESPACE "SYSAUX" 
LOGGING;

2. Next, I create the dictionary check function. It does a quick check of my new password against the dictionary to see if the password matches or contains any known words:

create or replace function sys.ora_dictionary_check
   (p_password varchar2)
return boolean is 
   v_contains_word number;
begin
   select count(*)
     into v_contains_word 
     from sys.words w 
    where instr(nls_lower(p_password), nls_lower(w.word)) > 0;

   if v_contains_word = 0 then
      return TRUE;
   else
      return FALSE;
   end if;
end;

3. Finally I create a modified the default VERIFY_FUNCTION function named VERIFY_FUNCTION_STIG. This modified function not only includes the dictionary check, but also STIG-compliant complexity checks for length, number and type of characters, and relative difference from the previous password:

create or replace function verify_function_stig
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   canon_username dbms_id := username;
BEGIN
   -- Bug 22369990: Dbms_Utility may not be available at this point, 
   -- so switch to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
			using IN username, OUT canon_username;
   END IF;
   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(canon_username) THEN
     raise_application_error(-20001, 'Password same as or similar to user');
   END IF;

   -- Check if the password contains at least fifteen characters,
   -- including
 four letters (two upper case and two lower), 
   -- two digits and two punctuation marks.
   IF NOT ora_complexity_check(password, 
                               chars => 15, 
                               letter => 4, 
                               uppercase => 2, 
                               lowercase => 2, 
                               digit => 2,
			       special => 2) THEN
      raise_application_error(-20002, 'New password should contain at least two numeric, two upper case, two lower case, and two punctuation characters');
   END IF;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NOT ora_dictionary_check(password) THEN
      raise_application_error(-20003, 'Password too simple - do not use dictionary words');
   END IF;

   -- Check if the password differs from the previous password by at 
   -- least
 4 letters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 4 THEN
	 raise_application_error(-20004, 'Password should differ by at least 4 characters');
     END IF;
   END IF;

   RETURN(TRUE);
END;

This satisfies the following STIG controls:

• V-61719: The DBMS must support organizational requirements to enforce minimum password length.
• V-61723: The DBMS must support organizational requirements to enforce password complexity by the number of upper-case characters used.
• V-61725: The DBMS must support organizational requirements to enforce password complexity by the number of lower-case characters used.
• V-61727: The DBMS must support organizational requirements to enforce password complexity by the number of numeric characters used.
• V-61729: The DBMS must support organizational requirements to enforce password complexity by the number of special characters used.
• V-61731: The DBMS must support organizational requirements to enforce the number of characters that get changed when passwords are changed.

4. Once my verification function is created, I can attach it to a user profile.

ALTER PROFILE "DEFAULT"
LIMIT
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_STIG;

5. Now I’m ready to load my dictionary table. SQL*Loader is the best tool for this job. My Loader control file, words.ctl, should look something like this:

load data
infile '/usr/share/dict/linux.words'
into table sys.words
fields terminated by ''
(word char)

6. To load the file, I execute the following command from the bash prompt. On a Red Hat Enterprise Linux system, the dictionary file is often over 480,000 words.

# sqlldr userid=[username] control=words.ctl log=words.log

7. Once the words are loaded, I want to delete words that are too short or too long, contain abbreviations, or special characters that would disqualify them as “words” for comparison. There is no need to make my dictionary longer than it needs to be by having it contain these extra “words”, so I execute the following delete statement to lean things up. It should remove somewhere around 138,000 words.

delete from sys.words
where
-- words are too short or too long to be worth checking
length(word) < 4 or
length(word) > 15 or
-- "words" are mixed case (proper names)
substr(word,2,1) = upper(substr(word,2,1)) or
(substr(word,1,1) = upper(substr(word,1,1)) and substr(word,2,1) = lower(substr(word,2,1))) or
-- "words" contain numbers or special characters
word like '%0%' or
word like '%1%' or
word like '%2%' or
word like '%3%' or
word like '%4%' or
word like '%5%' or
word like '%6%' or
word like '%7%' or
word like '%8%' or
word like '%9%' or
word like '%-%' or
word like '%.%' or
word like '%''%' or
word like '%/%' or
word like '%&%' or
-- "word" is all caps (acronym)
word = upper(word);

8. Now that I’ve trimmed my dictionary table down to an appropriate size, I should compress it to reclaim all the empty space I created with my deletes. I can do this with the following SQL commands:

ALTER TABLE SYS.WORDS ENABLE ROW MOVEMENT;
ALTER TABLE SYS.WORDS SHRINK SPACE;

At this point, my dictionary table and password verification function are ready to go. The dictionary check should not add any significant processing time to password changes.