Securing Your Database: A simple Brute Force Blocker

If you've had to change a Unix password, you know that most systems won't let you use just anything as a password - your password must be a minimum length, and must not be too easy to guess or brute-force. Oracle doesn't come with this capability installed out of the box, but Oracle 8 and above let DBAs define a custom function to complexity-check user passwords. Oracle provides a default password verification function to do some basic checking, although DBAs need to know about it and install it manually.

Jared Still wrote an excellent article on orafaq.com last year about how the very existence of this capability poses a security problem. Essentially, the password verification function must be owned by SYS, but since it can be user-supplied, an attacker who gains access to the SYS account can add code to the password verification function that sniffs passwords as they're changed. If an attacker gains SYS access, your database is already compromised, but the existence of this capability compounds this problem.

That being said, since the capability to create a good password verification function exists, we might as well take advantage of it. Not using this capability doesn't make the security problem go away. The best course of action is to implement the security measures Jared suggests in his article, and to also implement a good password verification function that prevents users from setting easily hacked passwords.

The Oracle-supplied password verification function, which you'll find in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql, checks for the following:

• Check for Username = Password (these clever passwords are also known as "joes")
• Check that the password is at least 4 characters long
• Enforce the rule that the password must contain at least one number, letter, and punctuation mark.

The Oracle-supplied password verification function also checks the proposed password against a tiny "dictionary" of eight words, with the comment:

In this article, we'll look at an easy way to implement that comment.

THE DICTIONARY

Brute-force password cracking tools often use dictionaries with a million or more words, and there are many good dictionaries available for download. We'll use something close at hand. Oracle doesn't come with a built-in dictionary, but most LINUX/UNIX systems do. This dictionary file - really a wordlist - is usually found in /usr/share/dict/words, where "words" is a symlink to the real dictionary file. For example, on my OS X box, /usr/share/dict/words links to /usr/share/dict/web2, a wordlist from Webster's 2nd International. On my RHEL 4 server, /usr/share/dict/words links to linux.words, a list of 483 000+ words, contractions, proper names, etc. Sysadmins use this dictionary to check password strength (eg. with a crack tool); there's no reason that DBAs can't too. (If you're running Oracle on Windows, you'll have to download a dictionary from the web).

USING THE DICTIONARY

The password verify function is just a PL/SQL function, so we need to get this wordlist from the filesystem into Oracle. There are two approaches we could take: import the wordlist into a table in the database, or use External Tables to read the contents of the dictionary file.

Let's think about the security implications of both of these scenarios for a second. First, suppose we import the wordlist into a table in the SYSTEM schema using SQL Loader. We're going to be using this table as a lookup table, as in: Look up the candidate password in the table; if it's found, don't let the user use it as their password. So, the main security consideration is that anyone with access to the SYSTEM schema could delete the contents of the table, which would enable anyone to set any password.

On the other hand, suppose that we set up an external table that points to the wordlist. Now the security considerations involve filesystem access. The wordlist itself is owned by root, though, and anyone with root access can get access to your SYS account. And again, the worst consequence is that anyone could set any password.

So the maximum vulnerability for either consideration is the same as though you had never enabled the password verify function: no dictionary checking for passwords. Personally, I lean toward loading the wordlist into a table in the SYSTEM schema and setting up auditing on the table to alert you if the table contents are changed. I'll give a quick sketch of this method below.

LOADING THE DICTIONARY INTO THE DATABASE

To load the wordlist into the database, you'll need a target table, a SQL*Loader control file, and a SQL*Loader parameter file. Create the table:

SQL> create table system.pwords ( word varchar2(4000));

Now create the control file and the parameter file:

CONTROL FILE: words.ctl

LOAD DATA
infile "/usr/share/dict/linux.words"
REPLACE INTO TABLE system.pwords
(word char(4000))

PARFILE: words.parfile

CONTROL=words.ctl
LOG=words.log
BAD=words.bad
DISCARD=words.dsc
ERRORS=0

And load the data into the database:

[oracle@myserver]$ $ORACLE_HOME/bin/sqlldr control=words.ctl parfile=words.parfile 

After you've loaded the data into the target table, give it an index:

SQL> create index system.ind_pwords on system.pwords(word);

Now that we've got a wordlist in the database, we can write a password verification function that checks a candidate password against the dictionary.

SPECS FOR THE PASSWORD VERIFICATION FUNCTION

Oracle requires that the password verification function:

• be owned by SYS;
• accept three arguments: (username varchar2, password varchar2, old_password varchar2);
• and that it return BOOLEAN.

We have got a couple of other considerations. We need to decide how to handle it if something goes wrong with the word list. If the table has been dropped or truncated, this could indicate a security breach; we could set the function up to raise an error if the table is missing or empty, which would block all password changes until it was restored. For this article, I'll code it to fall back on a simple password check from the original Oracle-supplied function, but I wouldn't recommend that you do the same in production.

THE PASSWORD VERIFICATION FUNCTION

The code itself is very simple. We'll check the proposed password against the wordlist, and if we find a (case-insensitive) match, we'll raise an application error, which blocks the attempted password change.

CREATE OR REPLACE FUNCTION dict_verify_function
  (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean
IS
   v_table_matches number;
   v_word_count number;
   v_use_dictionary boolean;
   v_pw_matches number;

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

   -- Check for the minimum length of the password
   -- (Oracle's default is 4; I've changed it to 8 here)
   IF length(password) < 8 THEN
      raise_application_error(-20002, 'Password length less than 8');
   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.

   --First, check that the dictionary table exists
   v_use_dictionary := false;
   
   select count(*) into v_table_matches
   from dba_tables
   where table_name='PWORDS' and owner='SYSTEM';
   
   if v_table_matches = 1 then
     select count(*) into v_word_count
     from system.pwords ;
     
     if v_word_count > 0 then
   
       v_use_dictionary := true;
   
     end if; --if v_word_count > 0
   end if;  --if v_table_matches=1 (table count)
   
   if v_use_dictionary then
   --If dictionary exists, check the password against it
   
		 select count(*) into v_pw_matches
		 from system.pwords p
		 where NLS_LOWER(password)=NLS_LOWER(p.word) ;
		 
		 if v_pw_matches != 0 then
				raise_application_error(-20006, 'Password found in dictionary');   
		 end if;
   else
   --otherwise, make a simple check
   
		 IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 
                                            'password', 'oracle', 'computer', 'abcd')
                 THEN
				raise_application_error(-20005, 'Password too simple');
		 END IF;

   end if; --if v_use_dictionary

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

TESTING THE FUNCTION

First, we'll give the function a test all by itself:

SQL> edit
Wrote file afiedt.buf

  1  begin
  2     if dict_verify_function('SCOTT','ABOMINABLE','TIGER') then
  3        dbms_output.put_line('TRUE');
  4     else
  5        dbms_output.put_line('FALSE');
  6     end if;
  7* end;
SQL> /
begin
*
ERROR at line 1:
ORA-20006: Password found in dictionary
ORA-06512: at "SYS.DICT_VERIFY_FUNCTION", line 54
ORA-06512: at line 2

INSTALLING THE FUNCTION

Finally, we'll install the function as the password verification function for a new profile we'll create.

The first step is to create the profile:

SQL> create profile chkpwd limit password_verify_function dict_verify_function ;

Profile altered.

Now we'll create a user with that profile:

SQL> create user tstuser identified by naps9i9fjl
  2  default tablespace users temporary tablespace temporary_data
  3  profile chkpwd
SQL> /

User created.

And try to change the password:

SQL> alter user tstuser identified by usurious ;
alter user tstuser identified by usurious
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20006: Password found in dictionary

CONCLUSION

In this article, we've seen how easy it is to set up a password verification function that checks users' proposed passwords against a dictionary wordlist. Hackers often try to gain access by trying every password in the dictionary - indeed, you can download a number of tools that will do this from Pete Finnigan's security site. This simple function will help your Oracle server withstand this kind of attack.

(Note: Setting up a password verification function is no substitute for the myriad other security measures we should all be taking. It's not even a guarantee against every brute force attack; an attacker may have a better dictionary than the one your password verification function uses. It's still a good idea to run an Oracle password cracker against your own databases periodically, for just this reason.)

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.