8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Storing Passwords in an Oracle 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, available in Oracle 8i and Oracle 9i, and the DBMS_CRYPTO
package, available in Oracle 10g onward.
Related articles.
- Data Encryption - DBMS_OBFUSCATION_TOOLKIT
- DBMS_CRYPTO in Oracle 10g
- Case Sensitive Passwords in Oracle Database 11g Release 1
- Secure External Password Store
- OS Authentication
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(40) 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 l_salt VARCHAR2(30) := 'PutYourSaltHere'; BEGIN -- Pre Oracle 10g --RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( -- input_string => UPPER(p_username) || l_salt || UPPER(p_password)); -- Oracle 10g+ : Requires EXECUTE on DBMS_CRYPTO RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) || l_salt || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1); 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.
If you are using DBMS_CRYPTO.HASH
, the result will be a VARCHAR2(40)
string.
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','MyPassword'); PL/SQL procedure successfully completed. SQL> select * from app_users; ID USERNAME PASSWORD ---------- ------------------------------ ---------------- 1 TIM [w�44Z�꿿8fE��
Next we check the VALID_USER
procedure.
SQL> EXEC app_user_security.valid_user('tim','MyPassword'); PL/SQL procedure successfully completed. SQL> EXEC app_user_security.valid_user('tim','abcd'); BEGIN app_user_security.valid_user('tim','abcd'); 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.
SET SERVEROUTPUT ON BEGIN IF app_user_security.valid_user('tim','MyPassword') THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END; / TRUE PL/SQL procedure successfully completed. BEGIN IF app_user_security.valid_user('tim','abcd') THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END; / FALSE PL/SQL procedure successfully completed. SQL>
Finally we check the CHANGE_PASSWORD
procedure.
SQL> exec app_user_security.change_password('tim','MyPassword','MyPassword1'); 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>
For more information see:
- Data Encryption - DBMS_OBFUSCATION_TOOLKIT
- DBMS_CRYPTO in Oracle 10g
- Case Sensitive Passwords in Oracle Database 11g Release 1
- Secure External Password Store
- OS Authentication
- DBMS_OBFUSCATION_TOOLKIT (9.2)
- DBMS_OBFUSCATION_TOOLKIT (11.2)
- DBMS_CRYPTO (11.2)
Hope this helps. Regards Tim...