Databases Reference
In-Depth Information
-- This function should be wrapped, as the hash algorithm is exposed here.
-- You can change the value of l salt or the method of which to call the
-- DBMS OBFUSCATION toolkit, but you must reset all of your passwords
-- if you choose to do this.
l password := utl raw.cast to raw(dbms obfuscation toolkit.md5
(input string => p password || substr(l salt,10,13) || p username ||
substr(l salt, 4,10)));
return l password;
end;
This function is a very good start, but developers must follow the recommendations found in the
comments:
Change the seed value (
l salt
).
•
Change the way the function
md5
is called, essentially change the
input string
.
•
•
Wrap the function using the Oracle wrap utility.
I would also recommend first hashing the value of
p username
. If a rainbow table did contain a
matching string which included the username, it could be an indicator that the username is also the
password.
Additionally, consider storing the usernames and passwords in a table not accessible to the parsing
schema of the application—or even the workspace. Expose only a function that returns a Boolean to
indicate if a username and password pass validation:
-- create the my users table in schema A, which is not accessible to the workspace
-- create this function in schema A
-- grant execute on this function to the application parsing schema (schema B)
create function user password match (p username in varchar2, p password in varchar2)
return boolean as
l exists number := 0;
begin
select 1 into l exists
from my users
where username = p username
and password = custom hash(p username, p password);
return l exists = 1;
exception when no data found then
return false;
end;
/
grant execute on user password match to schema b
/
If there is a SQL Injection vulnerability, the contents of the
MY USERS
table will be protected by the
underlying database security and will not be accessible to the schema used by the application.
