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.