Password Strength


One area that Oracle has made a lot easier over the years is the enforcement of password strength criteria. Oracle’s 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. On Linux and Unix systems this is remarkably easy to do, as these operating systems generally come with their own dictionary files that we can easily use to load a reference table for our verification function.

In Red Hat Enterprise Linux, for example, the operating system dictionary is located in /usr/share/dict/linux.words. To load this into my database, I use the following steps:

1. Create a table to hold my dictionary words. As SYSDBA, I run the following command in SQL*Plus:

CREATE TABLE "SYS"."WORDS" ("WORD" VARCHAR2(50 byte) NOT NULL)
TABLESPACE "SYSAUX" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) LOGGING;

2. Next, still working as SYSDBA, I create the password verification function.

CREATE OR REPLACE FUNCTION SYS."PASSWORD_VERIFY"
-- This function confirms new password complexity. If the new
-- password does not meet the complexity standards, raise an error
-- for the user to let them know they need to alter the password.
-- New passwords should be 10 characters long; contain no dictionary
-- words; contain two numbers, two upper case and two lower case letters,
-- and two special characters; contain no repeating consecutive
-- characters like "11" or "aa", and should differ from the old password
-- by at least four characters.
(username in varchar2, password in varchar2, old_password in varchar2)
return boolean IS
  n boolean;
  m integer;
  differ integer;
  isdigit1 boolean;
  isdigit2 boolean;
  ispunct1 boolean;
  ispunct2 boolean;
  isupper1 boolean;
  isupper2 boolean;
  islower1 boolean;
  islower2 boolean;
  digitarray varchar2(20);
  punctarray varchar2(25);
  lowerarray varchar2(26);
  upperarray varchar2(26);
  cursor word_list is
    select word from sys.words;
  word_rec words%rowtype;

begin
  digitarray := '0123456789';
  lowerarray := 'abcdefghijklmnopqrstuvwxyz';
  upperarray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  punctarray := '!"#$%&()''*+,-/:;<=>?_';

  -- Check if the password is same as the username
  if nls_lower(password) = nls_lower(username) then
    raise_application_error(-20001, 'New password same as or similar to user name');
  end if;

  -- Check for the minimum length of the password
  if length(password) < 10 then
    raise_application_error(-20002, 'New password is too short - length must be at least 10 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.
  open word_list;
  loop
    fetch word_list into word_rec;
    exit when word_list%notfound;

    if instr(nls_lower(password),nls_lower(word_rec.word)) > 0 then
      raise_application_error(-20003, 'New password is too simple - do not use dictionary words');
    end if;
  end loop;

  -- Check if the password contains at least two letters, two digits
  -- and two punctuation marks.
  isupper1:=false;
  isupper2:=false;
  islower1:=false;
  islower2:=false;
  isdigit1:=false;
  isdigit2:=false;
  ispunct1:=false;
  ispunct2:=false;
  m := length(password);
  for i in 1..m loop
    -- Check for digit, character, punctuation
    if instr(digitarray,substr(password,i,1)) > 0 and isdigit1 = false then
      isdigit1:=true;
    elsif instr(digitarray,substr(password,i,1)) > 0 and isdigit1 = true then
      isdigit2:=true;
    elsif instr(upperarray,substr(password,i,1)) > 0 and isupper1 = false then
      isupper1:=true;
    elsif instr(upperarray,substr(password,i,1)) > 0 and isupper1 = true then
      isupper2:=true;
    elsif instr(lowerarray,substr(password,i,1)) > 0 and islower1 = false then
      islower1:=true;
    elsif instr(lowerarray,substr(password,i,1)) > 0 and islower1 = true then
      islower2:=true;
    elsif instr(punctarray,substr(password,i,1)) > 0 and ispunct1 = false then
      ispunct1:=true;
    elsif instr(punctarray,substr(password,i,1)) > 0 and ispunct1 = true then
      ispunct2:=true;
    end if;

    -- Exit loop if password contains a character, digit and punctuation
    if isdigit2 and isupper2 and islower2 and ispunct2 then
      goto repeats;
    end if;
  end loop;

  raise_application_error(-20004, 'New password should contain at least two numeric, two upper case, two lower case, and two punctuation characters');

  -- Check if the password contains repeating characters like "11" or "aa"
  <>
  for i in 1..m-1 loop
    if substr(password,i,1) = substr(password,i+1,1) then
      raise_application_error(-20005, 'New password may not contain repeating characters');
      goto endsearch;
    end if;
  end loop;
  <>

  -- Compare new password to old password
  if old_password is not null then

    differ := length(old_password)-length(password);

    if abs(differ) < 4 then
      if length(password) < length(old_password) then
        m := length(password);
      else
        m := length(old_password);
      end if;

      differ := abs(differ);
      for i in 1..m loop
        if substr(password,i,1) != substr(old_password,i,1) then
          differ := differ+1;
        end if;
      end loop;

      if differ < 4 then
        raise_application_error(-20006, 'New password should differ by more than 4 characters from old password');
      end if;
    end if;
  end if;

  return(true);
end;
/

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

ALTER PROFILE "DEFAULT"
LIMIT
PASSWORD_VERIFY_FUNCTION PASSWORD_VERIFY;

4. 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)

5. 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=[usrname] control=words.ctl log=words.log

6. Once the words are loaded, I want to delete words that are too short or too long, contain abbreviations, special characters, or that contain ascii sequences like repeating characters that would disqualify a password for other reasons. There is no need to make my dictionary longer than it needs to be by having it contain extra “words”. As the SYSDBA user, I execute the following delete statements. They should remove somewhere around 200,000 words.

delete from sys.words
where
length(word) < 4 or
length(word) > 11 or
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
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 = upper(word);

delete from sys.words
where
lower(word) like '%aa%' or
lower(word) like '%bb%' or
lower(word) like '%cc%' or
lower(word) like '%dd%' or
lower(word) like '%ee%' or
lower(word) like '%ff%' or
lower(word) like '%gg%' or
lower(word) like '%hh%' or
lower(word) like '%ii%' or
lower(word) like '%jj%' or
lower(word) like '%kk%' or
lower(word) like '%ll%' or
lower(word) like '%mm%' or
lower(word) like '%nn%' or
lower(word) like '%oo%' or
lower(word) like '%pp%' or
lower(word) like '%qq%' or
lower(word) like '%rr%' or
lower(word) like '%ss%' or
lower(word) like '%tt%' or
lower(word) like '%uu%' or
lower(word) like '%vv%' or
lower(word) like '%ww%' or
lower(word) like '%xx%' or
lower(word) like '%yy%' or
lower(word) like '%zz%';

7. 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. As a SYSDBA user, 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. It will take a few seconds longer to process create or alter user commands now, but the increased security gained from strong password enforcement is worth every bit of it.

3 thoughts on “Password Strength

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.