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!