Tuesday, August 29, 2017

Create a Custom Authentication and Authorization Scheme in Oracle APEX

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Before when creating the APEX application based on a Blueprint, we used the standard APEX Authentication, which means the management of users is done in Oracle Application Express itself. As we created our own user table, we want to manage our users there and have some custom authentication scheme for our multiplication application.

In this post I will show you exactly how I built the custom authentication scheme for my Oracle APEX application you see at the https://www.apexrnd.be/ords/f?p=MTABLEmultiplication table app.

I first decided which kind of login page I wanted. A few years ago I blogged about pimping the logging page, which is what I use on the APEX Office Print login page. This type of page is a more traditional way of building a login page in Oracle APEX. For the registration page and forgot password page we used the same techniques.


For the multiplication table project I decided to do it a bit different. I started from the landing page and build the login mechanism into that page. But also the register and forgot password are on the same page, so not like what we did for APEX Office Print, using different pages for forgot password, register and login.

Here's how my page looks like in the Oracle APEX App Builder:


There are a few regions to help with the layout (Top, Left, Right). In the Right region, there are 3 sub-regions: Login, Register, Forgot Password, which will show one at a time. Dynamic Actions (Show/Hide) control which region is seen.

From the front-end this is what it looks like.
When clicking a button an APEX process is being fired, but all the logic is defined in a package.
The package to handle the authentication I typically call [project trigram]_AUTH_PKG. It doesn't only contain the authentication logic, but also the registration, reset password and authorization logic.

The specifications looks like this:

And the body like this:

I typically use dbms_crypto to generate (hash) the passwords, but as that package is not supported on Oracle Exadata Express at the time of writing, I use another SHA256 PL/SQL implementation.

I'm not going into too much detail on the logic in the PL/SQL package. I hope it's kinda self explanatory, but if you have any question, feel free to ask in the comments field.

Now we will focus on creating a Custom Authentication Scheme in APEX.

Go to App Builder > Shared Components > Authentication Schemes and hit the Create button to add a new one:


Enter the custom_authenticate procedure from the package we created earlier:


By default the new authentication scheme will be current, so make sure you have some data in your tables, otherwise you won't be able to login.

Next I typically add some post authentication to fill some Application Items.
Edit the Custom Authentication and add the code and post_auth as in this picture:


We have now made our application accessible to people by defining our own custom authentication scheme.

Next, we want to define which rights you have in the application. To do this, we will create two Authorization Schemes, one for a normal user and one for an administrator.

In our package we already included a function with the logic. Every user has a role defined to him, and depending the role, it's a normal user or an administrator. An administrator can do everything a normal user can do, but can also access the administrator section where we maintain our application.

Blueprint actually already created our Authorization scheme for administrators, but we will adapt it to use our package. Go to Shared Components > Authorization Schemes and modify like this:


I hope it gives you all the components to build your own custom authentication and authorization schemes.

I also recorded a video which goes in more detail on the entire process of signing up, forgetting password and logging in and the different authorization schemes and code being used.

61 comments:

  1. Thanks for this - I've used a similar scheme for a long time but it's due for a renewal and you've given me some ideas.

    Have you considered using per-user salts instead of a site-wide salt? This should reduce the value of your database of hashes to someone looking to brute-force people's passwords (which are often reused on multiple sites).

    Refer https://stackoverflow.com/a/2999247/103295

    ReplyDelete
  2. Great blog so far.

    Was going to mention the same thing as Jeff. I use l_salt := dbms_crypto.randombytes(256); for every user password and store this in the database along side the hashed value of password||l_salt||username.

    Appreciate you cant use crypto as mentioned but something as an alternative may suffice as long as its based on a Pseudo-Random Number Generator.

    ReplyDelete
  3. Hi Jeffrey and LC,

    Thanks for your comments.

    I've simplified the password algorithm in this project and typically use different algorithms per project/customer.
    But your recommendation is absolutely correct.

    Also for example for the password reset, in higher secure apps I let the ability to reset your password expire and use another table for that.

    Thanks again,
    Dimitri

    ReplyDelete
  4. Great posts Dimitri, really appreciate the view behind your scenes!
    I really enjoy reading about how you do your work.

    Quick note, I know its still in beta but thought you'd wanna know: for me mtable.online didnt work. When I tried www.mtable.online, it did work.

    ReplyDelete
  5. That's awesome Dimitri!

    Thanks for all time and effort in sharing your knowledge.

    I know it's still beta but the link in the post end up in an URL like https://apexrnd-a481992.db.em2.oraclecloudapps.com/apex/f?p=MTABLE:HOME/ZbTRZ/ which doesn't work. But, when I change the URL to https://apexrnd-a481992.db.em2.oraclecloudapps.com/apex/f?p=MTABLE:HOME it works perfectly.

    Thanks again.

    ReplyDelete
  6. Hi, Dimitri, this post is amazing! Great work!!

    Btw did you have a good guide for session timeout with custom authentication using apex api.

    ReplyDelete
  7. APEX Gurus, we are looking at options to move our VB application to the 21st century. APEX is one option.

    What we want to know is, Is APEX driven by Java??? Does APEX need a Java application server like Tomcat?

    If APEX does not use Java for anything (any layer), how does APEX create dynamic web-pages, or how does APEX work, WITHOUT the use of Java??

    Thanks, Rohan

    ReplyDelete
  8. Hi Rohan,

    APEX doesn't need an application server like Java, the dynamic web pages are build in PL/SQL.
    It's advisable to install ORDS, which is build in Java, but only serves to throttle the connections to the APEX engine inside the database (and also for REST webservices). And ORDS contains jetty, a webserver, so you don't need an other application server, but you could if you wanted.

    Hope that explains,
    Dimitri

    ReplyDelete
  9. Thanks Dimitri.

    "It's advisable to install ORDS": This is a bit confusing.

    Because I asked this same question from the Forum: https://community.oracle.com/thread/4079229

    and one of the answers was "In order to fully utilise the current APEX feature set it is necessary to use Oracle REST Data Services (ORDS) running in a Java application server as the APEX web listener."

    So, looks like APEX cannot be used WITHOUT the use of Java after-all???

    ReplyDelete
  10. If you want all the features of APEX you have to configure APEX with ORDS (which is written in Java) yes.
    see http://docs.oracle.com/database/apex-5.1/HTMIG/installing-AE-and-configuring-Oracle-REST-data-services.htm#HTMIG29143

    Is there any objection to run with Java? If it is you can go with two options as specified in the Oracle doc:
    - the EPG configuration
    http://docs.oracle.com/database/apex-5.1/HTMIG/installing-AE-configuring-embedded-PL-SQL-gateway.htm#HTMIG386
    but not recommended for production
    - Apache + mod_plsql
    http://docs.oracle.com/database/apex-5.1/HTMIG/install-AE-and-configure-Oracle-HTTP-server.htm#HTMIG29245
    The mod_plsql module included with Oracle HTTP Server 12.1.3 is fully supported and is tied to the overall Oracle HTTP Server support dates. Future versions of OHS don't support mod_plsql anymore.

    So I stay with my statement that it's advisable to run with ORDS (and yes you need Java for that), but if for whatever reason you don't want Java, you have a few alternatives.

    Dimitri

    ReplyDelete
  11. Thanks Dimitri.

    TBH we are NOT fond of Java.

    It's too heavy and with over 30 frameworks is not manageable or maintainable.

    Java is OVERKILL (i.e. much more of something than is needed, resulting in less effectiveness).

    Java requires you to devote more time on Java than the business requirements and other work at hand.

    We are trying to eliminate Java from all our projects in all layers.

    ReplyDelete
  12. Thanks Dimitri,

    It's great to see good quality examples of how to do custom authentication in APEX. One thing you may want to look into for enhancing your authentication code is to use the apex_util.set_authentication_result and apex_util.set_custom_auth_status procedures to provide more details back to APEX about the success or failure of the authentication attempt. This is especially useful when authentication fails as they properly update the APEX_WORKSPACE_ACCESS_LOG view with the authentication results. Otherwise the access log typically indicates a successful authentication result even when the user failed to log in.

    Properly updating the APEX authentication result can aid in auditing authentication issues.

    Dave

    ReplyDelete
  13. Thanks Dimitri for this blog series. It's really good for begginers.

    You are saying in the video that you have two user roles - admin and user. But in the code admin role is 2, and user should be in 1,2. Shouldn't it be actually oposite? In my opinion user should have just one role, but admin both of them...

    ReplyDelete
  14. Hi Peter,

    When somebody is an administrator, he has automatically the user role.
    That is why the user is in 1,2.

    Hope that clarifies,
    Dimitri

    ReplyDelete
  15. Hi Dimitri,
    This is great! I'm trying to use it to learn more and have the package and an application I am starting with.
    What is the code behind the login button please? I'm new and just need a tiny bit more help please with the process but am excited to be starting with APEX having just a PL/SQL background I can see how powerful this is.
    Kind wishes
    Paul

    ReplyDelete
  16. Hi Paul,

    See video around min 2:15 :)
    It's basically the default login process that APEX creates itself.

    Hope that helps,
    Dimitri

    ReplyDelete
  17. Hi Dimitri,
    wish you a Happy New Year :)
    I see that the function mtl_auth_pkg.custom_authenticate has 2 parameters .
    But when we call it in the Apex cutom Authentication Page we do not pass the parameters (username, Password)
    How are those values Passed to the function from the Login page.

    Thank you.

    ReplyDelete
  18. Hi Rotan,

    That is pre-defined by APEX - so you have to have those parameters as part of your definition.

    Hope that explains,
    Dimitri

    ReplyDelete
  19. Hi Dimitry
    Happy new year 2018 to you.
    Many thanks for your video.
    I'm new to Oracle Apex so was wondering if I could somehow get the property definition of the sub region items . Eg P2_NO_ACCOUNT etc and where and how is .register_link and .login_link defined.

    Any info on that will help heaps.
    Many thanks
    Russel

    ReplyDelete
  20. Hi Dimitry,

    I have the entire login screen setup but am not able to commit new users to my table. I think because my table is not the same as yours. Would you be able to share the code to create the mtl_user table that you've setup for your application. My table consists of "ID, Email, Password, Verification Code, & Role ID". Im not sure what I am missing. When I click the register new user button nothing is committed to my table.

    I would really appreciate your help.

    Thanks,

    Mike

    ReplyDelete
  21. CREATE TABLE "MTL_USER"
    ( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    "ROLE_ID" NUMBER,
    "EMAIL" VARCHAR2(255 CHAR) NOT NULL ENABLE,
    "PASSWORD" VARCHAR2(255 CHAR),
    "VERIFICATION_CODE" VARCHAR2(255 CHAR),
    "CREATED" DATE NOT NULL ENABLE,
    "CREATED_BY" VARCHAR2(255 CHAR) NOT NULL ENABLE,
    "UPDATED" DATE NOT NULL ENABLE,
    "UPDATED_BY" VARCHAR2(255 CHAR) NOT NULL ENABLE,
    CONSTRAINT "MTL_USER_ID_PK" PRIMARY KEY ("ID"),
    CONSTRAINT "MTL_USER_EMAIL_UNQ" UNIQUE ("EMAIL"),
    CONSTRAINT "MTL_USER_ROLE_ID_FK" FOREIGN KEY ("ROLE_ID")
    REFERENCES "MTL_ROLE" ("ID") ON DELETE CASCADE ENABLE
    ) ;

    CREATE INDEX "MTL_USER_I1" ON "MTL_USER" ("ROLE_ID") ;

    CREATE OR REPLACE EDITIONABLE TRIGGER "MTL_USER_BIU"
    before insert or update
    on MTL_USER
    for each row
    begin
    if inserting then
    :new.created := SYSDATE;
    :new.created_by := NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := SYSDATE;
    :new.updated_by := NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'),user);
    :new.EMAIL := LOWER(:new.EMAIL);
    end;
    /

    ReplyDelete
  22. Hi Russel,

    The P2_NO_ACCOUNT is a static value:
    No account yet? a href="javascript:///" class="register_link" >Register for free!</a

    ReplyDelete
  23. Hi Dimitri,

    Thanks for this excellent post. I have implemented most of what you presented here, however I'm struggling a bit with the password reset functionality. I have the email working but it looks like you direct the user to a different page with the link that is sent in the email. I'd like to know how you implement the verify_reset_password and reset_password functions.

    Again, thanks for this post

    ReplyDelete
  24. Hi Dimitri
    I'm getting close to implementing this.
    Can you tell me what is set in attributes of item P2_ACTION
    For somehow I cannot get the reset passwd form to submit the email value.
    I think its something to do with my properties of P2_ACTION.
    For some reason its not getting the value of LOGIN or RESET i think.
    Regards
    Russel

    ReplyDelete
  25. Hello Dimitri,
    If you use your own authentication system, can users save private reports using IG? how?
    Thank you very much and greetings
    Juan Carlos

    ReplyDelete
  26. Hi Dimitri I sorted it out .
    I had to set type Item = Value , LOGIN for the other 2 processes Clear page cache and set username cookie.

    That seems to have done it.
    Now I need to create the reset password page.
    Thanks heaps for this post.

    Re

    ------------------------
    Hi Dimitri
    I'm getting close to implementing this.
    Can you tell me what is set in attributes of item P2_ACTION
    For somehow I cannot get the reset passwd form to submit the email value.
    I think its something to do with my properties of P2_ACTION.
    For some reason its not getting the value of LOGIN or RESET i think.
    Regards
    Russel

    ReplyDelete
  27. Hi Juan,

    sure people can save their own IG, it's not tight to the authentication system, but to the APEX user.
    It's build-in, you just need to enable the ability to save Private reports.

    Dimitri

    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. Hi Dimitri,

    First congrats for such knowledge sharing!

    As your game is offline, can not be accessed, could you please kindly share what is inside of RESET_PWD page (items,da,process) ?

    l_url := apex_util.prepare_url(p_url => c_hostname||'f?p='||v('APP_ID')||':RESET_PWD:0::::P9999_ID,P9999_VC:' || l_id || ',' || l_verification_code, p_checksum_type => 1);

    Best and cheers form Brasil,

    Renato

    ReplyDelete
  30. Hi Renato,

    The url to the app is https://www.apexrnd.be/ords/f?p=MTABLE

    This is the code you probably want:
    declare
    l number;
    begin
    l := mtl_auth_pkg.verify_reset_password(
    p_id => :P9999_ID,
    p_verification_code => :P9999_VC
    );
    end;

    ReplyDelete
  31. Hi Dimitri, thanks for the app link. I am trying to test the whole flow, but already tried to reset password with 3 different registered users (@gmail.com,@hotmail.com,@oracle.com) but no email received (neither at spam folder). Any chance the mail send fucntion at yout apex environment is down ?

    Best

    Renato

    ReplyDelete
  32. I assume, per my review of your package that on page RESET_PWD you have:

    1) oj page load you call the PL/SQL you sent me:
    declare
    l number;
    begin
    l := mtl_auth_pkg.verify_reset_password(
    p_id => :P9999_ID,
    p_verification_code => :P9999_VC
    );
    end;

    2)verify_reset_password will check if the RESET_'||p_verification_code match, if not raise error, if match it then l gets the user id.
    function verify_reset_password(
    p_id in number,
    p_verification_code in varchar2)
    return number
    is
    l_id number;
    begin
    select u.id
    into l_id
    from mtl_user u
    where u.verification_code = 'RESET_'||p_verification_code
    and u.id = p_id;

    return l_id;
    exception
    when no_data_found
    then
    raise_application_error( - 20001, 'Invalid password request url.') ;
    return null;
    end verify_reset_password ;

    3) with valid user id you populate a hidden field and then end user populate a new text field for password and then you call reset_password, passing user id and new password.

    procedure reset_password(
    p_id in number,
    p_password in varchar2)
    is
    l_username varchar2(100) ;
    l_hashed_password varchar2(100) ;
    begin
    select email
    into l_username
    from mtl_user
    where id = p_id;

    l_hashed_password := custom_hash(l_username, p_password) ;

    update mtl_user
    set password = l_hashed_password,
    verification_code = null
    where id = p_id;
    end reset_password

    is this correct ?

    Thanks a lot,

    Renato

    ReplyDelete
  33. Hi Renato, email should work now.

    Dimitri

    ReplyDelete
  34. Hi Dimitri I tried to use you package on oracle apex 18.2
    But it did not work I don't know why is there is any compatibility issue with oracle apex 18.2
    Or I should try again

    ReplyDelete
  35. What error do you get?

    Dimitri

    ReplyDelete
  36. Only Invalid Login Credentials I don't know why I checked the debug the same error message Invalid Login Credentials

    ReplyDelete
  37. I tried to debug the custom autenticate function but I found the L_Password parameter value is
    'ABD92E059691A6D636B09C87104D95376A051B4A8BB90B97E2D703EFDA3B7821' and L_STORED_PASSWORD is '123' that's why the function keeps return false

    ReplyDelete
  38. Richard Bacon5/20/2019 1:56 PM

    Hi Dimitri,
    love your work.
    I'm trying to use a common package for authentication across several similar apps each for a different client. I'd like to pass the application number or another identifier to the authentication proc, but apex_authentication only takes the two fields. Any pointers on how to send the extra parameter to my auth proc?
    Cheers
    Richard

    ReplyDelete
  39. Hi Again Dimitri,
    I found a simple solution, nv('APP_ID')
    However, my dynamic sql is failing and I wonder what I'm missing.

    FUNCTION authenticate (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
    ) RETURN BOOLEAN IS

    p_schema VARCHAR2(30);
    l_value NUMBER;
    l_id NUMBER;
    l_returnvalue BOOLEAN;
    l_sql VARCHAR2(4000);
    l_obs_pass VARCHAR2(32);
    BEGIN
    BEGIN

    IF nv('APP_ID') = 102 THEN
    p_schema := 'ABSCHEF';
    END IF; --works like a charm so I'll set up an app_id-schema table

    l_sql := 'SELECT id
    FROM '|| p_schema || '.app_users
    WHERE upper(user_name) = :1';

    EXECUTE IMMEDIATE l_sql
    INTO l_id USING p_username;--This returns null

    SELECT
    id
    into l_id
    FROM abschef.app_users
    WHERE upper(user_name) = p_username; --this returns the user id

    --carry on checking password etc and set l_value again dynamically this also fails...

    l_returnvalue := l_value = 1;
    RETURN l_returnvalue;
    END authenticate;

    ReplyDelete
  40. Hi, can you share source of this application, i am very new with Oracle Apex and i need to see how did you design the login screen.

    ReplyDelete
  41. HI DIMITRI , you work is very nice, but i am surprised when i found the custom_hash is generating two different encryption when i create use it create different and when i try to login it create another encryption, because of this my user fail to authenticate.please guide the solution.

    ReplyDelete
  42. Richa Gandhi12/22/2019 4:22 AM

    Hi,

    Thanks for the solution.

    But my concern if that Our application use two authentications , one from Database table users and other from LDAP active directory.

    As the authentication scheme allows to have only one scheme as current then how can I use both at the same time.

    Please share some information for this issue.

    Regards,
    Richa Gandhi
    omegacoder.richa@gmail.com

    ReplyDelete
  43. Hello, Will you create a blog which will explain how did you created that GUI of login page including signup & forgot password link or provide a link if you had already created a blog then kindly share the link of that blog.

    ReplyDelete
  44. Hi Dimitri, great work and great idea. But please, for the sake of the beginners like us, can you host the source somewhere for us to really understand everything you want us to learn, please. This is more of a problem than solutions to some of us. You might want to consider Github, please. More blessings, sir!

    ReplyDelete
  45. I have been struggling with you steps since two years now, still no way. Please, save our souls. Source code, please.

    ReplyDelete
  46. Sorry for my previous commemts, please, I would be glad if could be removed even. All odds crushed, except for a little error that's proving stubborn, but I would be happy if you could be of an help over it. Here is the error message I get when I try to register a new user: "Unable to find item ID for item "AI_USER_ID" in appaplication "111"." Please, any suggestions. Thank you very much for coming up with this blog.

    ReplyDelete
  47. Hi Dimitri, can you share source of this application, i am trying but can't do which you doing with Oracle Apex and i need to see how did you design the login screen.

    ReplyDelete
  48. Hi Dimitri,

    Thanks for sharing this process. I am trying to follow it and implement something similar in Oracle APEX in the free cloud and I receive the error 'identifier DBMS_CRYPTO must be declared. I have tried granting access for my user but receive another error "table does not exist". I am assuming that the ability to access these functions are embedded deep in APEX (v20.2 at the moment).

    I logged into Oracle Support and found Doc ID 434878.1 which provided the solution as 'grant execute on SYS.DBMS_CRYPTO to FLOWS_020200;' which is what I had attempted.

    Any ideas?

    Thanks again.

    Brett

    ReplyDelete
  49. @Brett,

    grant execute on sys.dbms_crypto to MYWORKSPACENAME;

    Using the workspace you are doing the package on of course.

    ReplyDelete
  50. @Dimitri,

    One thing I have noticed, if your application is in debug mode, the debug messages log shows the users salt, hash and password in the nobody log. Is there anyway to have this not show (other than disabling debug, but we use that for our Testing and Development Apps)?

    ReplyDelete
  51. You can change or take out the apex_debug statements which are too sensitive.

    ReplyDelete
  52. Thanks @Dustin.

    I tried to execute grant execute but receive an error "table or view not found".

    Cheers.

    ReplyDelete
  53. Thanks Dimitri
    I am able to compile PL/SQL package and create table. How we can see clearly apex pages and their code ( Not good in Apex). Can you put apex pages images or may be create sample app which we can import ?

    ReplyDelete
  54. Thanks Dimiri,

    Awesome!!!

    You did the excellent job.

    I am new to APEX. I am learning APEX from various YouTube tutorials.

    My Assignment from Org is new user Registration with email verification and Reset Password.

    My requirement is very close to your Multiplication Table Game.

    Could you please share your source to download from GIT or email to me. I will import your app to my workspace.


    Tons of Thanks

    Ashok

    ReplyDelete
  55. Hi Dimitri,

    I have read through your post and watch the video and it has really added to my knowledge. However, I am not sure what I am doing wrongly, am using Apex 20.2 and it seems the value of item P2_USERNAME_R (the registration region) is not set. Clicking the register button just redirect me back to Login region without inserting any value in user table? Is there a better way to debug where the process is breaking?

    ReplyDelete
  56. Hi Dimitri,

    Wonderful post.

    I want to customize the authorization part in my application.
    One user can have many roles.
    And the components / regions will be accessible depending on those roles.

    I request you to please shed some light over this.

    Regards,
    Richa Gandhi

    ReplyDelete
  57. can you give me the app for practice please?

    ReplyDelete
  58. i am glad to hear your comment
    https://www.youtube.com/watch?v=8qiXyc3FHxk

    ReplyDelete
  59. Hello Dimitri, thank you very much for your blog, i have learned a lot from you. Can you please help me with an answer, after implementing the custom authorization schema that you have in the blog, when i enter to the system it does not recognize me as a developer, it generates some issues in order to place the right default order for somer reports, maybe you know what can i do?. Thnk you.

    ReplyDelete
  60. Wow, Dimitri, this post is amazing! Great work!! Thanks a lot.

    Six years later and so helpful!!

    ReplyDelete
  61. Hi Dimitri,
    The fucntion returns an error :
    procedure post_auth
    is
    begin
    mtl_auth_pkg.custom_authenticate(
    p_username => :APP_USER,
    out_user_id => :AI_USER_ID,
    out_time_zone => :AI_TIME_ZONE
    );
    end post_auth;

    "ORA-06550: Ligne 5, colonne 3 : PLS-00306: numéro ou types d'arguments erronés dans appel à 'CUSTOM_AUTHENTICATE'"

    I have defined AI_USER_ID and AI_TIME_ZONE as global application elements.

    ReplyDelete