Date

June 2021 | Version APEX 18.1 and higher

Context

When working with Oracle APEX whether on Oracle Cloud Infrastructure (OCI) or within your own environment, the wizard for creating a new application permits one to check a box that adds Oracle’s internal user management tools. Selecting this option only starts the process. More is needed to provide a complete security model for authentication and authorization. Let’s explore the robust features that Oracle APEX provides for free within this rapid-development/low-code tool – Application Express (APEX).

Doug Gault wrote a terrific article in September of 2019 (Twitter: @DougAGault) that provides insights into customizing and blending the internal Oracle toolset with hand-coded “custom” options.

For years, my team and I have only ever engaged in custom user authentication schemes augmented with complicated and hand-written authorization schemes. I carried the arrogance: I can do it better.

But can I?

Who has more experience securing massive amounts of important data? Me… or Oracle? Is my way better? Am I as current as I need to be with user authentication? I ought to have ignored that arrogance and explore the toolkit at our fingertips. These tools do a great job with username and password style authentication.

Progressively, our team has been engaging external service providers and API for Multifactor Authentication (MFA) and Single-Sign On (SSO). We’ve been using Okta and Microsoft and other solutions for both single-sign-on (SSO) and MFA. Adrian Png (Twitter: @fuzzieBrain) helped us repeatedly when we get in over our heads with SSO. The recent article by Plamen Mushkov (twitter: @plamen_9) is very good too: https://apexapplab.dev/2021/05/31/okta-authentication-for-apex-in-5-minutes/

Please acknowledge that hosting an application and the associated data on the internet carries risks. The risks and burden to protect these data rests on our shoulders, your shoulders.

Sometimes username and password are the right solution right now. And why not examine the free tools from Oracle before doing what I did and writing hundreds of lines of code, creating a series of custom tables, etc.

Why This Article

#1: John

My business partner of 10+ years specializes in servers, networks, and cybersecurity. Now and again, he puts together an APEX application – a true demonstration how Oracle has created a low-code/no-code system for building web-based software. He wanted to make changes to the user security feature that installed automatically. We both had to reverse engineer where those features came from. We both learned he ticked a box that read: “Access Control Enable Role-based user authorization” when on the “Create an Application” wizard. In a quick effort to replicate the feature, we failed when running them within SQL Developer. I then read, researched, and discovered a surprise treat someone left for me (and you).

After understanding and playing, I recognized that this feature ought to have been including in many of our early development efforts… especially when I often preach “lean into APEX” – embrace the spirit and efficiencies of someone else’s labors, and of a damned good tool.

#2: The Gap

In time I saw the gap. I failed to discover how to update a user’s password. I recognized that this feature emphasized user authorization – who has access to what, once in. Where is the user authentication part? Authentication asks: “Are you, you?”.

The APEX feature, as shown, is called “Access Control”, another phrase for authorization. So where are the elements needed to manage user authentication? They are in the package APEX_UTIL. There is a gap in our Low-Code User Management presentation from Oracle.

Take a look at the section called “APEX Users” below for more information.

Low-Code User Management

My friends at Oracle included the “user authorization” part of user management in the New Application Wizard. The inference is that users will be managed through the APEX developer’s interface. APEX does provide functions and procedures within APEX_UTIL that permit the real management of users. Raised by parent who wrote a lot, one earning a major in English, I grew up hearing the phrase “Maintain Parallel Construction” – which is not a tenet of Oracle. In this case, when you need the data, you query views with public synonyms. When you want to modify data, you call APEX_UTIL, and sometimes you can use APEX_UTIL to fetch out data too. If you want to modify the permissions of a user within an application, then you want to use the package APEX_ACL and a different set of views.

Inventory

In the table below, I am going to inventory the major components needed. There are some smaller functions that are super useful, but slightly redundant.

Notes & Warnings

Many of the procedures listed above require that one calls them from within APEX. Additionally, the user must have sufficient permissions within APEX to add users – so an APEX Admin.

This is a situation where one ought to play within one’s personal APEX space. Please do not mess up a corporate environment without permissions. That may cause an unexpected change in employment status.

APEX Setting

You will not be able to use an API to edit users unless you permit it. You’ll need to go tick the following:

Shared Components > Security > Runtime API Usage

Add a check to “Modify Workspace Repository” thereby permitting an application to reach back into APEX to modify user profiles.

Low-Code versus No-Code

Low-Code means that a developer has a reduced burden for application development. The procedures and views listed above provide a superlative low-code approach to user management. To cross the threshold to No-Code, we’d have to have a sample application with a full suite of user management features included, ideally introduced to an application with a cool little checkbox such as we see with Access Management in the Create Application Wizard.

My goal for next week is to write a robust application in APEX 20.x using the modern DML features. I’ll post this on my GitHub as an APEX application one can install.

I gave myself a giggle about including this code in a Sample App. It could be a bit of a risk for Oracle to do this. You let someone install a sample app as a developer, then maybe it doesn’t work so well without Admin permissions. A developer installs an application that requires admin rights. Now developer wants admin rights, blah, blah, blah. I understand the hesitancy. Providing administrative level access for one application is a different risk level than giving administrative level access to your APEX. I’ll publish the application on Github ensuring my personal stuff and our corporate stuff is safe. And in the blog, I’ll provide code samples for ye olde copy-n-paste.

APEX Users

Can you use the Oracle APEX view/table apex_workspace_apex_users to provide all of the features you need for a proper user table. The basics are covered with fields including username, first name, last name, email. Fields needed for APEX are visible with expiry date for the password, failed attempt counter, and toggles for APEX admin and APEX developer. We also have 10 flexible fields called “attributes”. They are numbered 1 through 10 and can be use to enhance the user table to suit your needs. Maybe you put the user’s language preference in attribute 1, the code (run from within an APEX page and session):

BEGIN 
    APEX_UTIL.SET_ATTRIBUTE ( 
        p_userid => apex_util.get_user_id(p_username => 'FRED'), 
        p_attribute_number => 1, 
        p_attribute_value => 'en'); -- Language preference
END;

Observe that this function uses the numeric primary key for the User ID. When getting the same attribute, use the “username” as shown below:

DECLARE  
    l_language VARCHAR2(4000);
BEGIN 
    l_language	:= APEX_UTIL.GET_ATTRIBUTE ( 
        p_username => 'FRED', 
        p_attribute_number => 1
		);
		APEX_UTIL.SET_SESSION_LANG( P_LANG => l_language);
END;

Of course, if your needs for a user table exceeds the capabilities of this internal APEX table, then you can build a one-to-one relationship to your own table.

Accessing the APEX user table can only be done within an APEX session, requires correct permissions within APEX. Updating data in the APEX user table must be done through functions/procedures provided by the APEX_UTIL package.

Better than Custom?

This approach may be better than custom, but not perfect. A custom table would normally be stored in the same database schema. Most of us store data in plain text except for the hashed password. Hashed passwords are better than encrypted passwords. Our early user tables had encrypted passwords – not good. Later we hashed. Putting user data including password information one step removed from your application schema requires bad actors to take that extra step. That is incrementally better. Better is better.

While these actions add layers and improve the shielding of user data, the responsibility to ensuring the security of data and access to applications belongs to the developers. These decisions rest with you.

APEX User Access

The APEX User Access Management feature assigns users to roles (groups) that are application specific. The native feature does not technically “add a user”, even though there is a button that says “Add User” – poor button, it doesn’t know it is lying to you. Once adding a user to a role within an application, you build out the Shared Component Authorization Scheme for these roles. With the Authorization Schemes created, you then add these to pages, menus, lists, etc.

The Steps

1. Add a user to the APEX environment – make sure this person is not a developer, nor an APEX admin.
2. “Add” the user to the application using the Access Control features described above as part of creating a new application.
3. Assign a user to a role.
4. Create or update the Authorization scheme (Shared Components)
5. Assign Authorization to buttons, pages, navigation menu, lists, and other places as needed.

I noted that the APEX User Access Management feature installs with the legacy DML tools, predating the APEX forms. I spent most of my winter migrating hundreds of pages to the newer Forms. Normally, I am done with a few clicks. Instead, I got both stuck and frustrated with errors. With effort that involved returning to a known-good baseline (legacy DML), changing one variable at a time, I demonstrated to myself that the primary key must not be returned to the page item.

When installing this on APEX 20.x, we see the tag “legacy” which inspires us to push forward. That is shown in the illustration below.

We add the page elements to create a form, as shown.

Then confirm that the primary key is set correctly (Pxxx_USER_ID) as primary key.

Following those steps go to the page process to add the form DML. In there, de-select the return primary key option as shown.

Link It!

The APEX User Access Management feature includes a button that reads “Add User”. This button does not “Add User” to APEX. It adds permissions (roles) to a user. And this only works if the user exists within APEX. So how about a validation link between the two. Set the Pxxxx_USER_NAME to a select list, then add a query as shown:

select 
	user_name d,
	user_name r
from apex_workspace_apex_users
where user_name not in
	(select user_name from apex_appl_acl_users where APPLICATION_ID = :APP_ID)
order by user_name;

A screen shot to provide context on where this query would go…

This will reinforce the distinction between the ACL User (Access Control) and the APEX User who gets authenticated.

Remember set theory? We all live in the land of set theory from the first moment we query a database with an SQL statement. The ACL User list ought to be a subset of the APEX Users list. We certainly had fun adding Fred and Barney and Wilma to our ACL list, but none of my Flintstone characters found a way to log in, not without solving the authentication problem first.

Toggle On / Off

An application can be available to all APEX users, or you can restrict the user’s access using the Access Control features. When installing the User Access Management feature, APEX turns on the Access Control Scope for your application. When “On”, only users assigned roles and assigned to your application have access, also stated as “Access Control List”. When off, there is no ACL (access control list) and any APEX user has access.

To toggle the feature, use this pair of functions. One sets the value, the other queries it. These must be run from with in an APEX application and session.

begin
    if :P10010_ALLOW_OTHER_USERS = 'Y' then
        apex_app_setting.set_value (
            p_name  => 'ACCESS_CONTROL_SCOPE',
            p_value => 'ALL_USERS');
    else
        apex_app_setting.set_value (
            p_name  => 'ACCESS_CONTROL_SCOPE',
            p_value => 'ACL_ONLY');
    end if;
end;

if apex_app_setting.get_value( p_name => 'ACCESS_CONTROL_SCOPE' ) = 'ACL_ONLY' then
    return 'N';
else
    return 'Y';
end if;

Authorization Schemes

While researching this article, I came to appreciate scheme type “Is in Role or Group”. For all of my years writing applications in APEX, I focused on the very database-ness of what I saw – the exists query, the equality to an item, and PL/SQL functions. Certainly, my functions and query demonstrated the cleverest I could be (ok, complicated). Our team created groups of users without using the native groups or functions roles within APEX. I would not likely go backwards to re-do the authorization scheme. That said, I would not again undertake authorization without the simpler approach with Roles / Groups.

Please do go visit Doug’s article on setting this up.

What’s Next?

Seems like a fun challenge to make a sample application that “leans into APEX” for user management.

Update (What could possibly go wrong?!)

Hey reader, I am updating this article on the same day I published it. I started the aforementioned sample app to manage APEX User profile from within an APEX application. While I listed some risks including a sudden change in employment if mistakes happen.

Well… I am the only user on my own Oracle Cloud Infrastructure APEX instance. When I edited the profile, I entirely nullified my administrator permissions. I could not even view or edit the APEX code. In short, I blew myself up entirely. Oops.

oh my

I had to login into APEX Internal as Admin, then reset my lonely little user back to being an Administrator for APEX.

Please do this better than I did!