Storing Passwords In The Database

-  Using the DBMS_OBFUSCATION_TOOLKIT package

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��

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>