This requirement pops up every now and then, time to put it in a blogpost so I can find it myself again when needed :-)
In various integration scenarios it is required to have users swiftly move between applications written in various technologies. This is very common for legacy Forms applications where some functionality is moved to APEX. From the Forms menu one entry could open a Form, another one an APEX page.
But this is just one example, basically the idea is to auto-logon a user to an APEX app just by providing a one-time token.
To be able to do that, we need a token table at first. Something like this will do:
Name Null? Type ------------ ----- ------------- TOKEN_STRING VARCHAR2(200) USERNAME VARCHAR2(200) CREATED_AT DATE USED_AT DATE
This table stores the very basic information we need: the token itself, the username it should be mapped to, the time it was created and when it was used.
Next we need a way to authenticate using a token. There are 2 possible solutions, either using the Page Sentry Function, or the Invalid Session Function. Let’s look at both:
Page Sentry Function
In Shared Components > Authentication Schemes we create a new custom scheme.
The code for Token_Sentry_Function looks something like this:
FUNCTION TOKEN_SENTRY_FUNCTION RETURN BOOLEAN IS l_query_string VARCHAR2(4000) := SYS.OWA_UTIL.GET_CGI_ENV('QUERY_STRING'); l_token VARCHAR2(200); l_username VARCHAR2(200); l_session NUMBER; BEGIN -- URL for testing http://localhost:8080/ords/f?p=116:1&x01=TOKEN:ABC -- don't do anything if user is already logged on IF APEX_APPLICATION.G_USER <> 'nobody' THEN RETURN TRUE; END IF; -- grab the token value l_token := REGEXP_SUBSTR ( srcstr => l_query_string , pattern => '&X01=TOKEN:([^&]+)' , modifier => 'i' , subexpression => 1 ); -- QUERY_STRING contains something like p=116:1&x01=TOKEN:ABC IF l_token IS NOT NULL THEN -- test token, see if it is -- 1) found in token table -- 2) unused -- 3) still valid (max age x seconds) SELECT USERNAME INTO l_username FROM RAG_TOKEN WHERE TOKEN_STRING = l_token -- AND USED_AT IS NULL -- AND CREATED_AT < SYSDATE-1/24/60 ; -- mark token as used at current time UPDATE RAG_TOKEN SET USED_AT = SYSDATE WHERE TOKEN_STRING = l_token ; -- is there already a session? l_session := APEX_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE; IF l_session IS NOT NULL THEN -- test if the session is still valid and get a new session id, if not valid IF NOT APEX_CUSTOM_AUTH.IS_SESSION_VALID THEN l_session := APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID; END IF; ELSE -- no session in cookie found, get a new session id l_session := APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID; END IF; -- initialize the session APEX_CUSTOM_AUTH.DEFINE_USER_SESSION ( l_username, l_session); RETURN TRUE; END IF; RETURN FALSE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE; END;
What does this function do? The Page Sentry Function is used to determine whether the current session is valid, or not. It is invoked by APEX to find out if a login is necessary. For our requirement this is perfect, because we can instantiate a session if the given token is valid, and get APEX to show the login-screen if token is invalid or not present.
Let’s walk through the function and explain what we do:
Line 4 grabs the URL. We need to do it this way, because in the Page Sentry Function we cannot access Page or Application Items (or APEX_APPLICATION.g_x01), since they haven’t been initialized by APEX, at this point in time.
Lines 12-15 are used for any subsequent page-load after a session has been established using either regular login or a token authentication. It pretty much says, don’t bother doing anything if a session is already active.
Line 18 grabs the token value out of the query string. Remember, there could be multiple parameters, like &x02 or &cs and such.
Line 32 validates the token against our table. This is the place to set up all kind of checks, like if the token was already used, how old it is, and so on.
Line 41 sets the used timestamp in the token table.
Lines 47-60 try to reuse an existing session-id. If no session exists, or it isn’t valid anymore, a new one will be created.
Line 63 tells APEX to define a session for given username.
That was easy, right?
Invalid Session Function
The second possible solution for token based authentication is by using the Invalid Session Function.
PROCEDURE TOKEN_INVALID_SESSION IS l_query_string VARCHAR2(4000) := SYS.OWA_UTIL.GET_CGI_ENV('QUERY_STRING'); l_token VARCHAR2(200); l_username VARCHAR2(200); l_session NUMBER; BEGIN -- URL for testing http://localhost:8080/ords/f?p=116:1&x01=TOKEN:ABC -- grab the token value l_token := REGEXP_SUBSTR ( srcstr => l_query_string , pattern => '&X01=TOKEN:([^&]+)' , modifier => 'i' , subexpression => 1 ); -- QUERY_STRING contains something like p=116:1&x01=TOKEN:ABC IF l_token IS NOT NULL THEN -- test token, see if it is -- 1) found in token table -- 2) unused -- 3) still valid (max age x seconds) SELECT USERNAME INTO l_username FROM RAG_TOKEN WHERE TOKEN_STRING = l_token -- AND USED_AT IS NULL -- AND CREATED_AT < SYSDATE-1/24/60 ; -- mark token as used at current time UPDATE RAG_TOKEN SET USED_AT = SYSDATE WHERE TOKEN_STRING = l_token ; APEX_AUTHENTICATION.POST_LOGIN ( p_username => l_username , p_password => null , p_uppercase_username => true ); END IF; END;
It looks very similar to our first approach, the big difference is that his procedure will be invoked by APEX only if there is no or an invalid session and that it will create a new session with every call.
Line 39 tells APEX that the given username is alright and automatically logged on.
Starting with APEX 5.1.4 it is possible to use :APP_AJAX_X01 instead of manually parsing the query string in the Invalid Session Function.
Using X01 (or any other Xxx Parameter) has a big advantage over using a Page or Application Item: it doesn’t conflict with any kind of Session State Protection.
The early adopter version of APEX 5.2 also shows a new API called APEX_JWT, which can generate and verify JSON Web Tokens. This can also be helpful, but that will be another blogpost…
Here is a demo:
A big thank you goes out to Christian Neumüller, for ever awesome answers to all my questions.
Really Appreciate @Peter Raganitsch. This is very helpful to authenticate user through token. I will try this in my application where user need to authenticate from Oracle Fusion. Thanks again for sharing such cool stuff.
this is great for mobile devices. We use Windows-pass-through Authentication on our PCs, but had to log on manually from our iOS devices.
Now our users request a single URL with token on the PC once a week. The URL ist automatically sent to the iOS devices. No more fiddeling in the field.
Perfect usecase, to send one-off links to make login on mobile devices easier!
When using method 1: If a new session is created, how do you make the token value available to the new session for using later in the application? I’ve tried using the set_session_state after just before returning true in the function, but when asking for the values in the application later, they are null. How do you achieve this? Do you have to insert the values in a database table, or is there a better way? Thanks in advance.
I like your post very much, thank you for sharing!
I think we both were on the SS Rotterdam last month:-)
Question: We have a legacy app from wich we want to open pages in an Apex app. My idea is to use your example to create SSO
Before opening the APEX app I want to store a token in a user table from within the legacy app, and check in APEX the token like you do in this example
You posted that you wanted to create a JWT example. Did you manage to do this? Do you have an example how to use this?
Thanks in advance
@Piotr: yes, this is the perfect usecase. Create and store the token, then call the APEX app. I haven’t used JWT, yet, but you will find the docs in the APEX API Reference Documentation at https://docs.oracle.com/en/database/oracle/application-express/19.1/aeapi/APEX_JWT.html#GUID-D44A3F15-75BE-41DC-A557-17FA70D466B9
Christian Neumüller just released a blog post showing a simple usecase of using JWT in APEX.
Not worked for me, I got:
You are not authorized to view this application, either because you have not been granted access, or your account has been locked. Please contact the application administrator
I got blow on APEX 18.2
You are not authorized to view this application, either because you have not been granted access, or your account has been locked. Please contact the application administrator.
Access denied by Application security check
Trying to implement this on our Oracle EBS R12 and APEX. I try to simulate every thing via backend, ie. create token, generate the url and copy paste the url in a browser. I keep getting redirected to the Login Page.
Is it because I copy paste the URL? Is this normal behavior?