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

By
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

Comments

  1. Muhammad AhmadMarch 4, 2020 at 6:00 PM

    Dear I use DB users for my application and also make role using DB roles select, insert, delete update but in when i login using DB user in apex it gives all rights as schema user .It does not work as DB user work in oracle forms12c , 6i

    ReplyDelete
  2. APEX RocksApril 16, 2020 at 2:58 PM

    Very useful. Thank you.

    ReplyDelete

Post a Comment

My photo
Jaydip Bosamiya
I am Oracle APEX Consultant, Blogger and Integrator. All-rounder in building small, medium and enterprise applications. Extensive knowledge in various area of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Popular posts from this blog

Oracle APEX - Interactive Report - Scrollbars on Top

By
Image
I have been came across an interested requirement ( which is very valid ) to add scrollbars on the top of the regions! I have seen many times when you want to scroll right to see further data of first few records, you will scroll vertically to get to the horizontal scroll-bars. And then you do horizontal scroll and then vertical scroll to go top of the region. Aren't you ??? :-) So, to ease the user interaction with IR - we should have a scrolling option somewhere on the top of the region ? - For Sure So, how can we add scrollbars on top of the report for an Interactive Report ? By default IR Fix the column headers to the page. This setting gives us an opportunity to add scroll-bars just below the column header as well. Just by adding following CSS line you should get a beautiful scroll-bars just below you header and it works really great as well. #MYIR .t-fht-thead{   overflow: auto !important; } I am also trying to find a way to add same function...
Read more

How to create your own customized nested report regions using jQuery

By
Image
Here is how you can create cool nested reports in Oracle APEX and jQuery, which looks like below: For this example, I have used DEPT & EMP example where as a nested report it will show all the child records of employee under department record. To start with, first create a new classic report region with query like " select * from dept ". Add a new derived column of type = Link to that report and set following attributes: Link: javascript: void(0); Link Text: <i class="fa fa-users" alt="Employees" title="Employees"></i> Link Attribute: class="viewEmp" data-deptno="#DEPTNO#" Add new page item to hold deptno value and call it PX_DEPTNO Create new classic report region (this will be your nested region) and set query like " select * from emp where deptno = :PX_DEPTNO " and set following attributes: Static ID:  EMPLOYEES Custom Attributes:  style="display: none" Page Items t...
Read more

Make your RESTful APIs Secured with OAuth2 - Basic, Simple and Easy Steps

By
Its been a while since my previous article. So, here is my first blog post of 2020. Oracle APEX has an out-of-box and declarative support to create RESTful Web Services using ORDS. Within no-time, you can build and publish your RESTful APIs. Use of ORDS has gradually increased with common standard across different technology strongly adopted via RESTful Web Services and JSON formatted input/output. With the flexibility of data sharing across the technology or within application, we must also think of how secure my RESTful APIs are. We do believe in strong security of our applications using Authentication and Authorization. But, many times we miss the level of security we should implement in our RESTful APIs. So, here is the very simple and basic steps to secure your ORDS RESTful APIs using OAuth2: We will use a default oracle.example.hr web service module to add OAuth2 for this blog. Navigate to SQL Workshop > RESTful Services. From the left side of tree - click on "Ro...
Read more