Data Management

Oracle Tip: Consider MD5 checksums for application passwords

If someone gains access to your database, there's a chance that they could see the username/password table and be able to obtain all application passwords. Learn how you can combat this risk by storing a checksum or hash key for a username/password combination.

It's common to store username and password combinations in a database table for application-level security. This is a good practice because it avoids giving your actual database username and password combinations, which someone could use to access the tables and application code directly.

There is still a slight risk that someone who gains database access can see the username/password table and be able to obtain all application passwords. For this reason, it's a good idea to obscure the passwords when they're stored in the database. However, even encrypted passwords aren't 100 percent safe. Encryption implies that, if someone has a valid key encryption, they would be able to decrypt the passwords and get the original text necessary to gain access to the database.

A solution to this problem is to store a checksum or hash key for a username/password combination. A checksum takes the individual bytes of some text and computes a numeric value that is highly unlikely to reproduce. This is the way that UNIX and many other systems check username/password values.

MD5 is a fairly common checksum calculation on arbitrary text, which produces a 128-bit or 16-byte value. While the MD5 algorithm is well documented and freely available, it's not possible to figure out the original text used to generate the MD5—only the potential text combinations. It also takes a long time, computationally, to create and try every possible MD5 value.

Since Oracle 9i, a utility has been available to generate MD5 values from either text or raw parameters. The DBMS_OBFUSCATION_TOOLKIT package contains MD5 generation functions that returns the key value and procedures that return the key through an OUT parameter. In both cases, the parameter and the return value must be of the same datatype.

Declare
    md5key  raw(16);
    val raw(2000) := utl_raw.cast_to_raw('myusername/mypassword');
begin
    md5key := dbms_obfuscation_toolkit.md5(val);
    dbms_obfuscation_toolkit.md5(val,md5key);
end;

Since an MD5 key is always RAW(16), you can create a table to store and retrieve the MD5 values and a package to encapsulate access. In this example, I also use GUID (another 128-bit value) as a unique identifier for each user, with a username/password combination using an MD5 checksum:

drop table app_auth_tbl;
create table app_auth_tbl
(
    userid raw(16),
    md5key raw(16)
);

create or replace package app_auth is
    app_auth_user_not_found exception;
    subtype guidtype is raw(16);
    subtype md5type is raw(16);
    procedure generate_md5
    (
        p_username in varchar2,
        p_password in varchar2,
        p_md5key out md5type
    );
    procedure get_user
    (
        p_username in varchar2,
        p_password in varchar2,
        p_userid out guidtype
    );
    procedure new_user(p_username varchar2,p_password varchar2);
    procedure change_password
    (
        p_username varchar2,
        p_old_password varchar2,
        p_new_password varchar2
    );
    procedure delete_user(p_username varchar2,p_password varchar2);
end app_auth;
/
show errors;

create or replace package body app_auth is
    procedure generate_md5
    (
        p_username in varchar2,
        p_password in varchar2,
        p_md5key out md5type
    )
    is
    begin
        dbms_obfuscation_toolkit.md5
        (
            input => utl_raw.cast_to_raw(p_username||p_password),
            checksum => p_md5key
        );
    end generate_md5;
    —
    procedure get_user
    (
        p_username in varchar2,
        p_password in varchar2,
        p_userid out guidtype
    )
    is
        l_md5key md5type;
    begin
        generate_md5(p_username,p_password,l_md5key);
        select userid
          into p_userid
          from app_auth_tbl
         where l_md5key = md5key;
    exception
        when no_data_found then
            p_userid := null;
    end get_user;
    —
    procedure new_user(p_username varchar2,p_password varchar2)
    is
        l_userid guidtype;
        l_md5key md5type;
    begin
        l_userid := sys_guid();
        generate_md5(p_username,p_password,l_md5key);
        insert into app_auth_tbl(userid, md5key)
            values (l_userid,l_md5key);
    end new_user;
    —
    procedure change_password
    (
        p_username varchar2,
        p_old_password varchar2,
        p_new_password varchar2
    )
    is
        l_userid    guidtype;
        l_md5key    md5type;
    begin
        get_user(p_username,p_old_password,l_userid);
        if l_userid is null then
            raise app_auth_user_not_found;
        end if;
        generate_md5(p_username,p_new_password,l_md5key);
        update app_auth_tbl
           set md5key = l_md5key
         where userid = l_userid;
    end change_password;
    —
    procedure delete_user(p_username varchar2,p_password varchar2)
    is
        l_userid guidtype;
    begin
        get_user(p_username,p_password,l_userid);
        if l_userid is null then
            raise app_auth_user_not_found;
        end if;
        delete from app_auth_tbl where userid = l_userid;
    end delete_user;
end app_auth;
/
show errors;

set serveroutput on;
declare
    l_userid    app_auth.guidtype;
begin
    app_auth.new_user('myuser','mypass');
    app_auth.get_user('myuser','notmypass',l_userid);
    if l_userid is null then
        dbms_output.put_line('invalid password worked');
    end if;
    app_auth.get_user('myuser','mypass',l_userid);
    if l_userid is not null then
        dbms_output.put_line('valid password worked');
    end if;
    app_auth.change_password('myuser','mypass','newpass');
    app_auth.get_user('myuser','newpass',l_userid);
    if l_userid is not null then
        dbms_output.put_line('password changed');
    end if;
    app_auth.delete_user('myuser','newpass');
end;
/
show errors;

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox