Oracle APEX - Implementing Role-Based Access with Social Login

Image of wall with keypad in red
Authorisation Schemas in Oracle APEX

In our last blog about Social Sign in, we detailed how APEX can be configured to use a 3rd party federated identify provider, such as Google, to handle the authentication process.

Using an Identity Provider (IdP) for authentication may be useful for several reasons; delegating security to a trusted supplier, streamlining IT processes and providing users a simpler login experience being just a few examples.

Extending Authentication to Authorisation

The role of security, however, often does not stop once the user is authenticated. It is a commonplace requirement to provide different types of users authenticating into the same application with differing functionality, based, for example, upon their roles in an organisation.

A user with an admin / management role will often have extended access to screens, reports, processes and data that a user possessing a lower level role will not have.

In APEX this type of access is managed by Authorization Schemes. Once created, these may be applied to various components of the application (a page, button, menu item, process etc).

Oracle APEX uses programmed statements to analyse whether that user has access to each of the authorization schemes configured and the results determine the subsequent UI experience. They can also be evaluated at different points in time:

  • Once per session
  • Once per page view
  • Once per component
  • Always (No Caching)

Naturally, the authorization processes need to have access to configured user / role information so they can evaluate them when a user authenticates. Often this data is managed locally in the database in the form of a small look up table which stores the role information next to a user's main authentication key (such as a username or email address) which is received as part of the authentication process. The login process may then retrieve what roles the user has and evaluates this information against the authorization schemes.

This is perfectly acceptable but, if you have gone to the trouble of delegating the authentication part of security out to a 3rd party, it may feel unsatisfactory to then have to manage the more fine grain authorisation processes back in a local APEX / database based solution.

Furthermore, if your APEX application is embedded in an organisation where user management and security is managed by a dedicated IT team (via an internal Azure, Active Directory or Oracle IDCS for example) then it seems natural for that team to manage all aspects of that user's access to internal business systems in one place. Keeping some of the configuration inside yet another system/database may provide a logistical headache as well as another system to manager securely.

The good news it is possible for IdPs themselves to provide role type information, the OpenID specification having standard ways for requesting and delivering this information back to the client. Below we are going to work through an example of requesting role / group data from an IdP and show how the resultant responses can be mapped to into APEX Authorization schemes.

Requesting and capturing group data in APEX

In our example below we are going to use Oracle IDCS as our identity provider which we will expect to deliver us group type information for each user that authenticates into APEX via it. Please note that in order for this to work the IdP needs to configure the users and the groups (roles) they belong to 1st. This is usually done in conjunction with the developer. For example if, in designing an APEX application, you decide that users will need separate functionality based on 3 roles (for example named read, modify and admin) then you'll have to get the IdP provider to set these groups up 1st and assign the user base to the relevant ones.

To begin with we have to set up the connection to the IDCS authentication server. As we have covered this in a previous post we will not cover the basics of this here but, if you need a refresher of this, you can get all the details via the link below.

Oracle APEX - Social Login
I looked into Social Sign-in as an option for Oracle APEX a few years ago. This was pre APEX 18.1 and, at this time, it was not simple to configure (in fact it would have taken a considerable amount of code to implement) Fortunately, since 18.1 APEX offers

The only major difference in this particular set up (than that described in the link) is that we're going ask for group information in the scope. The IdP then knows to return an array of any group information it has of this type against the user authenticating (as long as our agreement with the IdP allows this of course).

How is group data passed into APEX?

Now we've added the "group" scope to the authentication setting we can now expect the IdP to provide us with the group information of the user when they login. However, we'll need to parse that information and store it somewhere so we can use it with the Authorization schemes we will be setting up.

What format will we get the information in and how to we access it? Whilst we could predict the format of this data by reading all the OAuth2 specs carefully it is sometimes easier just to look directly at the data that is being transferred on login. We can do this simply by putting APEX into debug mode. Debugging authentication is slightly trickier than normal "page" debugging in APEX and to help with this we have written an article specifically on how to debug a Social Login authentication process, the link to which may be found below.

Oracle APEX - Debugging a Social Login
Discover how to troubleshoot Social Login in Oracle APEX. Debugging and configuring Identity Providers for a seamless experience.

Looking at the authentication callback process in the debugger for my new login I find (along with a lot of other interesting data about the handshake) the following JSON payload returned from the IdP. You can see that there is a group array now being returned, in this example telling me that I am part of the apx-admin and apx-modify groups.

Applying IdP Group data in APEX

So how do we best extract this information and use it in APEX for authorisation? Well luckily for us APEX already has a place for storing and querying group information for a user's session, this being APEX_WORKSPACE_SESSION_GROUPS, so the only thing we have to do is to populate this object with the data provided on authentication.

To do this we first need to change one of the security settings found in the application (to be found under shared components). Changing the "Source for Role or Group Schemes" to "Custom Code" tells APEX that it should expect Groups to be populated by the developer, instead of with the default Application Access Control roles.

In the active social sign-in authentication scheme we're now going to define a PL/SQL procedure that will run post-authentication.

This will loop through the groups in the array and push them into the APEX dynamic groups. The nice thing about this is APEX is clever enough to know that it needs to parse the JSON returned from the IdP after the authentication request automatically. A simple example of this is found below

PROCEDURE post_authentication AS

l_group_names apex_t_varchar2;


-- add all group names to l_group_names
FOR i IN 1..apex_json.get_count('groups') LOOP

  apex_string.push(p_table => l_group_names, p_value => apex_json.get_varchar2(p_path => 'groups[%d].name', p0 => i));
-- save group names in session

apex_authorization.enable_dynamic_groups(p_group_names => l_group_names);

END post_authentication;

With these steps, we now have all the data in APEX that we need and can start creating the authorization schemes.

The below example shows a scheme called APEX_ADMIN which queries the workspace user groups for the session instantiated to see if the group name apx-admin is present for that user. If the user logging into the applications is not part of this group then they will not have access to any functionality that this authorization schema controls. In the example that we have imagined we'd need to create two more authorization schemes one for a Modify role and one for a Read role.

To make the application of roles easier you may want to combine roles together in the authorization process. For example, an admin will usually have access to anything that a lower-level user might have access to, so for example, you might define the "modify" scheme like below. This means that both admin and modify groups will have access to any components that are restricted with this scheme, but "read" users will not. Setting up authorization schemes like this means that whoever is responsible for setting up the group security on the IdP side does not have to remember to assign users to both admin and modify groups.

where apex_session_id = :app_session
and group_name in ('apx-modify','apx-admin')

Once the authorization schemes are created then it's simply a process of assigning them to the relevant components in your APEX application that you want to restrict. Note that authorisation schemes can also be referenced in PL/SQL code which can help constructing more complex logic and also help restrict access to data as well as functionality.


Using groups functionality available in the Oauth2 / OIDC specification is a convenient way to extend social sign authentication so that the Identity provider manages the level of application access as well. Doing so may well help streamline user management and user adoption of your APEX application within the business ecosystem.

Find out more about our APEX services