How to use two authentication type together - Oracle Application Express + Custom Database Authentication

This blog is about how you can use two different authentication scheme in one single custom authentication.

For example, if I wanted my users to be authenticated from my custom database table, but at the same time I want few admin users - who are my application's super users (I might want to enable few extra options to them) and they are already my Application Express Users (can be a developer or administrator) and I wanted to authenticate both of them in a single authentication function.

So, here I will need to achieve two things,

• Authenticate Function
• First check if the user is authenticated via APEX user's credentials.
• If not, the user is authenticated via My Custom authentication function.

• Authorization Function
• Check if logged in user is a valid Application Express user
• If not, the user is a regular database user.

Following is a sample code block for authentication function:

function my_multi_auth(
  p_username in varchar2,
  p_password in varchar2
) is

begin

  /*Validate using APEX authentication scheme*/
  if APEX_UTIL.IS_LOGIN_PASSWORD_VALID(p_username => p_username, p_password => p_password) then
    RETURN TRUE;
  end if;
  
  /*Validate using my custom table*/
  if MY_AUTH_PKG.isValid(p_username => p_username, p_password => p_password) then
    RETURN TRUE;
  end if;

  return false;
end my_custom_auth;

Following is a sample code block for authorization function:

function my_multi_authorize(
  p_username in varchar2
 ,p_rolename in varchar2
) is
begin

  /*I want to pass authorization if the logged-in user is the my APEX user*/
  if p_rolename IS NOT NULL AND APEX_UTIL.GET_CURRENT_USER_ID is not null then
    return 1;
  end if;
  
  /*Otherwise, I will check if it is from my custom table*/
  if MY_AUTH_PKG.isAuthorize(p_username => p_username, p_rolename => p_rolename) = 1 then
    return 1;
  end if;
  
  return 0;
end my_multi_authorize;

Hope this helps!

Regards,

Jaydip Bosamiya

jbosamiya@gmail.com