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.

Security Table
Security Package
Testing

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.

Note. 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','hall');

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.