ADD DATABASE FUNCTION THROUGH ORACLE APEX
CUSTOM_HASH Function
create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2
is
l_password varchar2(4000);
l_salt varchar2(4000) := 'XV1MH24EC1IHDCQHSS6XQ6QTJSANT3';
begin
-- 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_OBFUSCATOIN 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;
CUSTOM_AUTH Function
create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is
l_password varchar2(4000);
l_stored_password varchar2(4000);
l_expires_on date;
l_count number;
begin
-- First, check to see if the user is in the user table
select count(*) into l_count from demo_users where user_name = p_username;
if l_count > 0 then
-- Fetch the stored hashed password & expire date
select password, expires_on into l_stored_password, l_expires_on
from demo_users where user_name = p_username;
-- Next, check whether the user's account is expired. If it isn’t, execute the next statement, else return FALSE
if l_expires_on > sysdate or l_expires_on is null then
-- If the account is not expired, apply the custom hash function to the password
l_password := custom_hash(p_username, p_password);
-- Finally, compare them to see if they are the same and return either TRUE or FALSE
if l_password = l_stored_password then
return true;
else
return false;
end if;
else
return false;
end if;
else
-- The username provided is not in the DEMO_USERS table
return false;
end if;
end;
The function receives the username and password on line # 1 as parameters from the login form and compares this information with the values stored in the DEMO_USERS table after applying the CUSTOM_HASH function. If the provided information matches the table values, the user is authenticated and is allowed to access the application.
Uploading Scripts in Oracle APEX
Follow the instructions mentioned below to add the two database functions using the SQL Scripts utility.
1. Save the above two functions as files (Custom_Hash.sql and Custom_Auth.sql) on your PC’s desktop.
2. Select SQL Scripts from SQL Workshop.
3. Click the Upload button.
4. Click the Browse button, select Custom_Hash.sql file, and click Open.
5. Click Upload.
6. Repeat the same process for the Custom_Auth.sql file. After the upload, press the Run button individually for each file.
This action will create and store the two functions in your database that you can see using SQL Workshop | Object Browser and selecting Functions from the drop-down list. Now, when you enter the credentials (mentioned in the previous post), you'll be authenticated as a legitimate user and will be provided access to the application. This way you can implement a custom authentication scheme in Oracle Application Express to have much better control over the process.
An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"
