Storing Passwords In The Database 8i to 9iR2 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
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_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)
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_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('fernandov','mypass');
PL/SQL procedure successfully completed. SQL> select * from app_users;
ID USERNAME PASSWORD | ---------- ------------------------------ ---------------- 1 fernandov f>~âÝ♀?£åeÍ?+▄} Next we check the | VALID_USER
procedure:
SQL> EXEC app_user_security.valid_user('fernandov','mypass');
- PL/SQL procedure successfully completed.
- SQL> EXEC app_user_security.valid_user('fernandov','abcd');
- *
ERROR at line 1: - ORA-20000: Invalid username/password.
ORA-06512: at "FVALENZUELA.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('fernandov','mypass') THEN
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('fernandov','abcd') THEN
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('fernandov','mypass','password2');
PL/SQL procedure successfully completed. 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
PD:article original http://www.oracle-base.com/articles/9i/StoringPasswordsInTheDatabase9i.php
CONSTRAINT app_users_pk PRIMARY KEY (id) 
function could be used to replace the 
SQL> exec app_user_security.change_password('fernandov','abcd','abcd1'); 
You can find out the version of your workflow using the following query 
