Storing Passwords In The Database

When security is managed within applications there is often a need to store passwords in database tables. This in itself can lead to security issues since people with appropriate privileges can read the contents of the security tables. A common approach to solving this is to encrypt the password before storing it. The problem with encryption is that it implies a possible decryption mechanism that could expose a hole in your security. A safer alternative is to store a hash of the username and password. In this article I'll present a simple example of this process using the DBMS_OBFUSCATION_TOOLKIT package that is available in Oracle8i and Oracle9i:
· Security Table
· Security Package
· Testing
Top of Form
Security Table
First we must build a table to hold the security information:
CREATE TABLE app_users (
id NUMBER(10) NOT NULL,
username VARCHAR2(30) NOT NULL,
password VARCHAR2(16) NOT NULL
)
/
ALTER TABLE app_users ADD (
CONSTRAINT app_users_pk PRIMARY KEY (id)
)
/
ALTER TABLE app_users ADD (
CONSTRAINT app_users_uk UNIQUE (username)
)
/
CREATE SEQUENCE app_users_seq
/
Security Package
Next we create the package that contains the specification of the security code:
CREATE OR REPLACE PACKAGE app_user_security AS
FUNCTION get_hash (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE add_user (p_username IN VARCHAR2,
p_password IN VARCHAR2);
PROCEDURE change_password (p_username IN VARCHAR2,
p_old_password IN VARCHAR2,
p_new_password IN VARCHAR2);
PROCEDURE valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2);
FUNCTION valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN;
END;
/
We then create the package body to define the actual operations:
CREATE OR REPLACE PACKAGE BODY app_user_security AS
FUNCTION get_hash (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
input_string => UPPER(p_username) '/' UPPER(p_password));
END;
PROCEDURE add_user (p_username IN VARCHAR2,
p_password IN VARCHAR2) AS
BEGIN
INSERT INTO app_users (
id,
username,
password
)
VALUES (
app_users_seq.NEXTVAL,
UPPER(p_username),
get_hash(p_username, p_password)
);
COMMIT;
END;
PROCEDURE change_password (p_username IN VARCHAR2,
p_old_password IN VARCHAR2,
p_new_password IN VARCHAR2) AS
v_rowid ROWID;
BEGIN
SELECT rowid
INTO v_rowid
FROM app_users
WHERE username = UPPER(p_username)
AND password = get_hash(p_username, p_old_password)
FOR UPDATE;
UPDATE app_users
SET password = get_hash(p_username, p_new_password)
WHERE rowid = v_rowid;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;
PROCEDURE valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2) AS
v_dummy VARCHAR2(1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE username = UPPER(p_username)
AND password = get_hash(p_username, p_password);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;
FUNCTION valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
valid_user(p_username, p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
END;
/
The overloads of VALID_USER allow the security check to be performed in a different manner.The GET_HASH function is used to hash the combination of the username and password. It always returns a VARCHAR2(16) regardless of the length of the input parameters. This level of compression means that the hash value may not be unique, hence the unique constraint on the USERNAME column.The DBMS_UTILITY.GET_HASH_VALUE function could be used to replace the DBMS_OBFUSCATION_TOOLKIT.MD5 function, but the hashing algorithm of the former is not garaunteed to stay constant between database versions.
Testing
First we create a new user:
SQL> exec app_user_security.add_user('tim','hall');
PL/SQL procedure successfully completed.
SQL> select * from app_users;
ID USERNAME PASSWORD
---------- ------------------------------ ----------------
1 TIM [w?44Z䪿?8fE?pre>

Next we check the VALID_USER procedure:
SQL> EXEC app_user_security.valid_user('tim','hall');
PL/SQL procedure successfully completed.
SQL> EXEC app_user_security.valid_user('tim','abcd');
BEGIN app_user_security.valid_user('tim','hall1'); END;
*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 37
ORA-06512: at line 1
Next we check the VALID_USER function:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF app_user_security.valid_user('tim','hall') TH
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
TRUE
PL/SQL procedure successfully completed.
SQL> BEGIN
2 IF app_user_security.valid_user('tim','abcd') T
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
FALSE
PL/SQL procedure successfully completed.
SQL>
Finally we check the CHANGE_PASSWORD procedure:
SQL> exec app_user_security.change_password('tim','hall','hall1');
PL/SQL procedure successfully completed.
SQL> exec app_user_security.change_password('tim','abcd','abcd1');
BEGIN app_user_security.change_password('tim','abcd','abcd1'); END;
*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 47
ORA-06512: at line 1
SQL>