The CISSP

Today I took the CISSP after studying with coworkers for quite a while. My employer was also kind enough to provide my coworkers and I a week long review class courtesy of CA and (ISC)². So, now all I can do is wait and find out how I did in about a week when I receive my email. Hopefully, everything went as well as I feel it did. I will let you know either way, though I would prefer to give good news instead of bad. Keep your fingers crossed for me!

Database passwords: Are they as secure as user passwords?

I have recently been reviewing security policies in regards to database management and came across an interesting, and cool, feature supported by Oracle. I do not know if other databases support such a feature, but I would hope so. If not, they need to.

Oracle supports a feature, as part of a PROFILE that allows database administrators to enforce password policy requirements at the database level for all types of users. First, lets create a simple profile for application accounts.

CREATE PROFILE application_account_prof LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME UNLIMITED
   PASSWORD_REUSE_MAX UNLIMITED
   PASSWORD_VERIFY_FUNCTION appl_passwd_verify_proc;

So, what does the above PL/SQL accomplish? Lets take a deeper look.

The first line, CREATE PROFILE application_account_prof LIMIT provides a descriptive name for the policy so that we can distinguish it from other policies that you may have in your system. Examples of other important policies you may want to implement include user account policies, DBA account policies, and super user policies just to name a few.

The next line, FAILED_LOGIN_ATTEMPTS 5 sets the maximum number of failed login attempts before locking the account. For an application, this an extremely high number of failed attempts as I will explain a little later, but I mitigate this risk as explained in the next section.

The third line, PASSWORD_LOCK_TIME UNLIMITED5 goes hand in hand with the second in that it sets the amount of time that the account should stay locked. In this case, the time is set to UNLIMITED meaning that a DBA, or more accurately a person having the DBA role, must explicitly unlock the account with a command similar to ALTER USER blah ACCOUNT UNLOCK. The reason I chose such a stringent policy for application accounts is that only applications should be using the account! Not end users, a.k.a. not people. Since applications should be reading the database password from a properties file, or better yet have it configured in the application server's connection pooling service, they should never get the password wrong. If a number of account lockouts starts to accumulate, this should be a clue to the DBA that someone is probably trying to brute force the password, and efforts need to be taken to cut off the potential attack. Special care should be taken at this point to ensure that the connection pool configuration or properties file was not modified so as to prevent the application itself from logging in once the issue is resolved.

The fourth line, PASSWORD_REUSE_MAX UNLIMITED ensures that an application can never repeat a password. This could be set to a value like 5, but again this is for an application account so ease of password reuse should not be an issue.

The last line, PASSWORD_VERIFY_FUNCTION appl_passwd_verify_proc defines which stored procedure is on charge of verifying the complexity of the account's password. Essentially, this line defines what function the DBMS should use to enforce password policy standards for the account.

So, now that we have our policy defined, we need to define the appl_passwd_verify_proc stored procedure that will enforce our password policy for application accounts.

CREATE OR REPLACE FUNCTION appl_passwd_verify_proc 
  (username varchar2, 
   password varchar2, 
   old_password varchar2) 
RETURN boolean IS  
  n boolean; 
  m integer; 
  differ integer; 
  isdigit boolean; 
  ischar  boolean; 
  ispunct boolean; 
  digitArray varchar2(20); 
  upperCharArray varchar2(26);
  lowerCharArray varchar2(26);

BEGIN  
  digitArray:= '0123456789'; 
  upperCharArray:= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  lowerCharArray:= 'abcdefghijklmnopqrstuvwxyz'; 

  -- Check if the password is same as the username 
  IF password = username THEN 
    raise_application_error(-20001, 'Password same as user'); 
  END IF; 

  -- Check for the minimum length of the password 
  IF length(password) < 25 THEN 
    raise_application_error(-20002, 'Password length less than 4'); 
  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 NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 
                             'password', 'oracle', 'computer', 'abcd') 
    THEN raise_application_error(-20002, 'Password too simple');
  END IF; 

  -- Check if the password contains at least one upper case letter,
  -- one lower case letter, and one digit. This could be extended to
  -- include a check for one special character.

  -- 1. Check for at least one digit 
  isdigit:=FALSE; 
  m := length(password); 
  FOR i IN 1..10 LOOP  
    FOR j IN 1..m LOOP  
      IF substr(password,j,1) = substr(digitArray,i,1) THEN 
        isdigit:=TRUE; 
        GOTO findUpperChar; 
      END IF; 
    END LOOP; 
  END LOOP; 
  
  IF isdigit = FALSE THEN 
    raise_application_error(-20003, 'Password should contain at least one 
    digit'); 
  END IF; 

  -- 2. Check for at least one upper case character 
  <<findUpperChar> >
  ischar:=FALSE; 
  FOR i IN 1..length(upperCharArray) LOOP  
    FOR j IN 1..m LOOP  
      IF substr(password,j,1) = substr(upperCharArray,i,1) THEN 
        ischar:=TRUE; 
        GOTO findLowerChar; 
      END IF; 
    END LOOP; 
  END LOOP; 

  IF ischar = FALSE THEN 
    raise_application_error(-20003, 'Password should contain at least one \ 
    upper case character'); 
  END IF;

  -- 3. Check for at least one lower case character 
  <<findLowerChar>>
  ischar:=FALSE; 
  FOR i IN 1..length(lowerCharArray) LOOP  
    FOR j IN 1..m LOOP  
      IF substr(password,j,1) = substr(lowerCharArray,i,1) THEN 
        ischar:=TRUE; 
        GOTO endSearch; 
      END IF; 
    END LOOP; 
  END LOOP; 

  IF ischar = FALSE THEN 
    raise_application_error(-20003, 'Password should contain at least one \ 
    upper case character'); 
  END IF;


  <<endSearch>> 
  -- Check if the password differs from the previous password by at least 3 
  letters 
  IF old_password = '' THEN 
    raise_application_error(-20004, 'Old password is null');
  END IF; 

  differ := length(old_password) - length(password); 
  IF abs(differ) < 3 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 < 3 THEN 
      raise_application_error(-20004, 'Password should differ by at \ 
      least 3 characters'); 
    END IF; 
  END IF; 

  --Everything is fine   
  RETURN(TRUE); 
END; 

The biggest thing to get out of the PL/SQL code above is the password policy checks. For those that don't want read the code above here they are:

1). Passwords must be a minimum of 25 characters.
2). Passwords must contain at least one upper case character.
3). Passwords must contain at least one lower case character.
4). Passwords must contain at least 1 digit.
5). Passwords cannot be the same as the username.
6). Passwords must differ from the previous password by at least 3 characters.

The last thing to do is to assign accounts to the above profile and you are done.

ALTER USER blah PROFILE application_account_prof