A blog about Application Express and Oracle related topics.

APEX and Forms: Automatic Login

This note describes a simple way of integrating Oracle APEX with other frameworks. Developers do not want their users to log in again when navigating to APEX pages and they want to pass parameters, without creating a new APEX session on each request.

Very often, developers want to discuss these topics when they have existing Oracle Forms applications and need to extend them with APEX. Hence the title, but the solution below only assumes that you are on APEX >= 18.1 and the product you want to integrate with has access to the same database. For simplicity, the example uses sqlplus instead of Forms.

The idea is to create a JSON Web Token (JWT) with user credentials and some item values that we want to pass along, sign it with a secret key and append it to an APEX URL via the X01 parameter. If your APEX custom authentication scheme’s sentry function detects a JWT, it decodes it, sets the session user and any passed in parameters. Even if your page is checksum protected, X01 will not be part of the checksum. Note that you must keep the secret key hidden from other applications and users on the database. This obviously also means that you should not use the sample key from this example.

Let’s start with the implementation. In APEX, create a new authentication scheme of type “Custom”. It needs a custom sentry function that contains the logic for passing the username and item values:

function sentry
    return boolean
    l_x01      varchar2(32767);
    l_jwt      apex_jwt.t_token;
    l_jwt_user varchar2(255);
    l_jwt_elts apex_t_varchar2;
    -- parse JWT payload in X01
    l_x01 := v('APP_AJAX_X01');
    apex_debug.trace('X01=%s', l_x01);
    if l_x01 like '%.%.%' then
            l_jwt := apex_jwt.decode (
                         p_value         => l_x01,
                         p_signature_key => sys.utl_raw.cast_to_raw('secret!') );
            apex_debug.trace('JWT payload=%s', l_jwt.payload);
            apex_jwt.validate (
                 p_token => l_jwt,
                 p_iss   => 'sqlplus',
                 p_aud   => 'APEX' );
            apex_json.parse (
                 p_source => l_jwt.payload );
            l_jwt_user := apex_json.get_varchar2('sub');
        exception when others then
            apex_debug.trace('...error: %s', sqlerrm);
    end if;
    -- if not logged in yet:
    -- - log in with JWT user if JWT given
    -- - or trigger custom invalid session/login flow
    if apex_authentication.is_public_user then
        if l_jwt_user is not null then
            apex_authentication.post_login (
                p_username => l_jwt_user );
            return false;
        end if;
    elsif apex_application.g_user <> l_jwt_user then
        apex_debug.trace('...login user %s does not match JWT user %s',
            l_jwt_user );
        return false;
    end if;
    -- if JWT given, assign additional parameters to items
    if l_jwt_user is not null then
        l_jwt_elts := apex_json.get_members('.');
        for i in 1 .. l_jwt_elts.count loop
            if l_jwt_elts(i) like 'P%' then
                apex_debug.trace('...setting %s', l_jwt_elts(i));
                apex_util.set_session_state (
                    p_name  => l_jwt_elts(i),
                    p_value => apex_json.get_varchar2(l_jwt_elts(i)) );
            end if;
        end loop;
    end if;
    return true;
end sentry;

Users sometimes need to work with the APEX application, without coming from Forms. It would therefore be useful to have a login option, too. You can set the custom auth scheme’s “Authentication Function Name” to “-BUILTIN-” to use APEX login credentials or “-DBACCOUNT-” for database authentication. Of course, you can also implement your own user credentials checking in a custom function.

In Forms, you need to create the JWT with the same secret key, aud(ience) and iss(uer). For simplicity, the example below uses sqlplus to generate a URL:

CHRISTIAN@apx182dev> declare
  2      l_jwt varchar2(32767);
  3  begin
  4      l_jwt := apex_jwt.encode (
  5                   p_iss           => 'sqlplus',
  6                   p_aud           => 'APEX',
  7                   p_sub           => 'TESTUSER',
  8                   p_exp_sec       => 10,
  9                   p_other_claims  => '"P1_NEW1":'||
 10                                      apex_json.stringify('foo'),
 11                   p_signature_key => sys.utl_raw.cast_to_raw('secret!') );
 12      sys.dbms_output.put_line (
 13          ''||l_jwt);
 14  end;
 15  /

This token is valid for 10 seconds (p_exp_sec). As you can see, you are able to pass items via the p_other_claims JSON fragment. Use apex_json.stringify to properly escape item values.

When trying this out multiple times, you will notice that APEX generates a new session ID on each request. This is not ideal in many cases. You can use the application-level “Rejoin Sessions” attribute to get the same session. There are security implications with this attribute, though. Please follow the link to the documentation for more details.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s