ADD DATABASE FUNCTION THROUGH ORACLE APEXAfter receiving login information, the APEX engine evaluates and executes the current authentication scheme i.e. Custom. The scheme makes a call to a function named CUSTOM_AUTH. In conjunction with the CUSTOM_HASH function, the function authenticates the user using his/her credentials stored in the DEMO_USERS table. Here are the two functions that you are required to store in your database to implement custom authentication mechanism. The CUSTOM_HASH function is a subordinate function to the CUSTOM_AUTH function and is called from the parent function to obfuscate users’ password using hash algorithm. Besides assisting the custom authentication scheme, this function is used in section 10.3.9 in chapter 10 to obfuscate updated password in the DEMO_USERS table.
create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2
l_salt varchar2(4000) := 'XV1MH24EC1IHDCQHSS6XQ6QTJSANT3';
-- 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)));
create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is
-- 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
-- The username provided is not in the DEMO_USERS table
The function receives 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 with 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 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 legitimate users and will be provided access to the application. This way you can implement custom authentication scheme in Oracle Application Express to have a much better control over the process.
An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"