 |
|
Oracle Application Password Security Management
Oracle Security Tips by
Burleson Consulting |
This is an excerpt from the
bestselling book "Oracle
Privacy Security Auditing", a complete Oracle security reference
with working Oracle security scripts.
Application Password Management
A table may contain the user IDs and passwords
in the following manner.
USERID VARCHAR2(10)
USERPASS VARCHAR2(30)
Selecting from this table:
select * from app_users;
We see:
USERID USERPASS
---------- ------------------------------
APPUSER1 ??sÆfíµ?
APPUSER2 ¼FK-?=?
Note how the password has been encrypted.
Encryption is discussed in Chapter 4, for now, assume the password
is encrypted in some way. However, every encryption system needs
some key to encrypt and decrypt, and this key is literally the path
to discover the password. Therefore, the key must be protected at
all costs. How do we achieve the task of authenticating these
application users?
One option is for the application to issue a
statement like:
select userpass from app_users where userid =
'APPUSER1';
Then decrypt the value, match it against the
password supplied by the user, and allow the application to proceed
if they match. However, this scheme assumes that the application
user knows how to decrypt the password. This can create a security
lapse, and should be covered as thoroughly as possible. The use of a
password verifier function is helpful in this situation.
We will create a function that accepts the user
ID and password values, and examines them against the data stored in
the database. It will return YES if they match or NO if they don't.
This procedure uses an Oracle supplied function named
dbms_obfuscation_toolkit, that is described in detail in the chapter
on Encryption.
Using a function to check passwords also
prevents a very common but serious form of attack known as SQL
Injection Attack. This is described later in this chapter.
* is_password_correct.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
--
*********************************************
create or replace function is_password_correct
(
p_userid IN varchar2,
p_password IN varchar2
)
return varchar2
is
v_userpass varchar2(2000);
v_password varchar2(2000);
errm varchar2(2000);
v_key varchar2(2000) :=
'adfwff2dwcc3299fmc0cecc0fc';
begin
-- First get the encrypted password from
the table
select userpass
into v_userpass
from app_users
where userid = p_userid;
-- Decrypt the string to get the password
-- Note the key used.
dbms_obfuscation_toolkit.DES3Decrypt (
input_string => v_userpass,
key_string => v_key,
decrypted_string => v_password,
which => 1);
-- Compare the passwords
v_password := rtrim(v_password);
-- compare the passwords
if (v_password = p_password)
then
return 'YES';
else
return 'NO';
end if;
exception
when OTHERS then
errm := SQLERRM;
raise_application_error (-20001,errm);
end;
/
Note the use of the function. It does not
actually return the value of the user's password. Rather it returns
YES if the password supplied by the user is correct and NO if it
isn't. This is analogous to the challenge-response type of
authentication, where the challenge is merely answered with a yes or
no response. The application user never needs to know the decrypted
value of the password.
Some user who is not normally involved in the
process of the application owns this function. The authors recommend
using a user id called SECUSER, who owns all the security related
objects. In this case, the user SECUSER owns this function and
grants execute privileges to APPUSER1 and APPUSER2 (or more, if
necessary).
When the application user APPUSER1 needs to
authenticate himself or herself, he or she calls the function in the
following manner
if (
secuser.is_password_correct (
'APPUSER1','app1') = 'YES'
) then
-- user is authenticated
else
-- user is not authenticated
end if;
The user never knows the value of the password
string inside. All he or she knows is that the password is app1 and
the function responds with a YES or NO answer. Even if the user
APPUSER1 selects from the table APP_USERS (which will not happen,
since he or she does not have the privilege, but assume the
privilege was granted by mistake), he or she will not know the
password of APPUSER2. It will be encrypted, and the user does not
know the key to decryption.
The other part of the puzzle is how to encrypt
the passwords and store them in the table. We will achieve that
using the following function
* get_app_password.sql
--**********************************************
--
-- Copyright © 2003 by Rampant TechPress Inc.
--
-- Free for non-commercial use.
-- For commercial licensing, e-mail
info@rampant.cc
--
--
*********************************************
create or replace function get_app_password
(
p_password IN varchar2
)
return varchar2
is
v_password varchar2(2000);
v_userpass varchar2(2000);
errm varchar2(2000);
v_passlen integer;
v_extra integer;
v_padlen integer;
v_key varchar2(2000) :=
'adfwff2dwcc3299fmc0cecc0fc';
begin
-- The input to the encryption routine
needs to be
-- multiples of 8 bytes. So we will pad it
if it
-- is not so.
v_passlen := length (p_password);
if (v_passlen < 8)
then
v_padlen := 8;
else
v_extra := mod(v_passlen, 8);
if (v_extra = 0)
then
v_padlen := v_passlen;
else
v_padlen := v_passlen - v_extra +
8;
end if;
end if;
v_password := rpad(p_password,v_padlen);
-- Encrypt the the password
dbms_obfuscation_toolkit.DES3Encrypt (
input_string => v_password,
key_string => v_key,
encrypted_string => v_userpass,
which => 1);
return v_userpass;
exception
when OTHERS then
errm := SQLERRM;
raise_application_error (-20001,errm);
end;
/
Note the very important concept, the key, which
is the same in the Encryption and Decryption procedures. If the key
is different, the password is never retrieved.
This function returns the encrypted value,
which can then be stored in the database table by this user. For
example, when a third user called APPUSER3 is created with password
app3, the user SECUSER can call:
insert
into app_users
values
(
'APPUSER3', get_app_password('app3')
)
/
When the time comes to update the password of
the user APPUSER1, the following approach may be used:
update
app_users
set
userpass = get_app_password ('newpass1')
/
Another way is to create a procedure that
accepts the user ID and password, and then inserts into the table
directly. That way, the user who executes the procedure does not
even know which table contains the passwords.
Note an important concept here – the user
executing the procedure is not aware of the table behind the
authentication or the authentication keys. This offers tremendous
security advantages and a flexible password management framework for
application users.
HIPAA requires that all users, whether they
connect through the database or the application, should be securely
authenticated. Until now, there was no proper way of authenticating
the application users who were not database users. Using these
password management utilities, this requirement can be easily
fulfilled.
* Action Item: Identify if there are some
applications that use authentication via passwords stored in tables.
Change the method to the secured method described here.
 |
This is an excerpt
from the book "Oracle
Privacy Security Auditing". You can buy it direct from the
publisher for 30%-off and get instant access to the code depot
of Oracle security and auditing scripts. |
