Its pretty well known in the APEX world that built-in security comes in two main flavors: Authentication (can I get in to the app at all) and Authorization (what am I able to do once I am allowed in). And, while there has been a lot written around various custom and built-in Authentication Schemes, I haven't seen much written about Authorization and even less about making use of APEX's built in Access Control functionality. So after a recent visit to a customer who had that very question, I decided to write a "How To" on the topic. Hold on to your seat - this is likely to be a long and winding road. Don't worry though, there's lots of pictures!
APEX does a great job of providing an awful lot for free, and that goes a long way provided that you stay within the framework for which each component was intended. But what if your case is special? (Isn't everyones?). The customer I met with is building a system that will be available to potentially anyone, so they want to build a custom authentication scheme where they can maintain users and their attributes. Their system will be open to individuals as well as organizations so, depending on their role, individual users may have very different authorities in the system.
Though this example is going to be significantly simplified from what my client is likely to implement, what I'm going to attempt to do is show all the working parts and how they fit together to create a working security model. Namely:
As I mentioned, there are various blog posts out there on Authentication. But I don't want to send you scurrying around the web scrubbing through multiple posts, so I'll post the code for a simple custom authentication scheme here.
First we start with a function called HASH_PASSWORD. This function receives two inputs (p_user_name and p_password) and uses the Username, Password and a Salt string to produce a unidirectional hash of the password that can be saved to the database.
Next is the actual USERS table. This version is very simple, holding only a unique key for a user, their user name and their password. Notice that we are making sure that the username is unique so we don't have any clashes. Also notice that in the Before Update and Before Insert trigger we're hashing the password using the HASH_PASSWORD function. This makes sure we never store the password in clear text.
Finally we have the AUTHENTICATE_USER function that will form the basis of our Custom Authentication scheme. The function first checks to see if the user exists, then hashes the password provided and then compares the new hash to the hash that we have stored in the database. If anything goes wrong, we reject the user and log the results.
And to get us started I would add a default user just so when we create the new Authentication Scheme and turn it on, we can log into any app using it.
APEX provides you with a number of built-in Authentication Scheme types, including Application Express Accounts, Database Accounts, LDAP Directory, Social Sign-in, and others. However, as we've discussed, there are times when you're going to want to roll your own. In this case you'll want to use the Custom Authentication Scheme type.
Creating a simple custom theme based on our AUTHENTICATE_USER function and the underlying tables is actually pretty easy. Simply navigate to the shared components of your application, click on the Authentication Schemes link under Security and click Create >. At this point a wizard will walk you through the required steps.
Your first choice will be whether you copy an existing scheme, or create a new one based on one from the gallery. In our case we're using the gallery, so clicking Next > will take us to a list of available schemes.
At this point you give your new Authentication Scheme a name (something like MyCustomAuthentication) and choose the scheme type (custom). The moment that you choose Custom as the type, you're presented with a number of fields to fill in. Don't get freaked out... You really only have to fill in one - the Authentication Function Name. In that field simply type authenticate_user and click Create Authentication Scheme.
NOTE: APEX assumes that the function you indicate for your Authentication Function takes in two parameters (P_USERNAME and P_PASSWORD) and returns a boolean that indicates whether the user has been successfully authenticated. That's why you only need to provide the function name.
After creating the new Authentication Scheme, APEX automatically sets it as the default for the application. At this point you are no longer logging in to your application using your APEX user, but instead using the entries in your USERS table. Seeing as we only created one user (admin/admin) you should only be able to log in using that.
The Ability to have APEX create access control components for you has been around for quite a while, but starting with version 18.1 they got a face lift and became a more integral part of the wizards.
Reading the APEX Builder's Guide description of Access Control, we learn that this feature:
"Creates pages to manage an access control list. Use the Application Access Control shared component to associate application roles with application users. This wizard also adds a reader, contributor and administrator role and corresponding authorization scheme to your application. Apply these authorization schemes to pages and page components to manage access by user and role."
So the wizard will create a number of pages to manage things such as User to Role Assignment and whether Access Control is even enforced, as well as some sample roles and authorization schemes. But it actually creates quite a few things under the covers. Let's unpack what it actually does.
When the wizard completes and you check the results you see that it creates:
Actually, that's quite a lot that the wizard does for you, and as an example, its very useful. However, out of the box, the feature isn't without its challenges. For instance, there is no hard link between the usernames you assign roles to and the users that are actually defined in the system. Meaning you could just make up a user that doesn't exist and assign a role to it. And while the default roles (and authorization schemes) are great examples, most systems are more complicated than that, requiring more granular roles and varying level of authority.
I know it's very tempting to create the Access Control Feature using the wizard and then go in to its component parts and fix the things that don't fit your model, and that would be quite legitimate, but for this post we're going to abandon what the wizard gives us and create what we need (and only what we need) from scratch so we understand how they all link together.
The first thing you'll need to do is understand what authorization levels will need to be implemented in your application because this will define not only the roles you create in the Application Access Control shared component, but will also guide what Authorization Schemes you define and are able to apply to the various APEX components.
Most applications will have a static set of authorization levels. Although the example in this blog post is extremely simplified, as a basis this solution should work well for those circumstances.
* If you are in a situation where you are required to use a third party system to dictate authorization levels, or you for some reason need to implement dynamic levels, then your job becomes much more complicated and is far outside of the scope of this post.
For our example we're going to use 4 different authorization tiers.
Although not much more complex than what APEX gives you by default, it gives us enough levels to see how we can create both Exclusive and Cascading authorization schemes.
To create these 4 roles in our sample application, we navigate to Shared Components > Application Access Control. From there use the Add Role > button to add the for roles above. You'll end up with something like this:
At this point we're not going to enter any User Role Assignments as we want to make sure we do that through the app we're building and that we only deal with users that actually exist in our USERS table. We'll get to that a little later.
By themselves, the Access Control Roles don't, in essence, do anything but provide the ability to link a user to a role. It's the related Authorization Schemes that provide the logic that allow you to tie a Role to a given Component in APEX. Discussing how to properly implement Authorization schemes can be a rabbit hole, but in the interest of keeping things as simple as possible, we're going to rely on as much built in functionality as we can for our example.
Just know that you could write detailed and complex PL/SQL logic that checks all sorts of things including time of day, validity of the user's account, Application Status, and a dozen other things to decide whether the current user is really authorized to do the thing they're trying to do.
For our example we're going to create two separate Authorization sets:
First let's attack the Exclusive Set. If you navigate to your application's shared components and then to Authorization Schemes, you may see that one called "Administration Rights" already exists. This is a default Authorization scheme that is created for the app that always returns TRUE. For our purposes, you can ignore it. But if it bothers you, you can delete it. We won't be needing it.
To create the Exclusive Authorization Schemes that we need we can click on the Create > button and follow the wizard. We'll be creating all of these From Scratch so simply take the defaults on the first page of the wizard and move on.
On the next page, enter the details of the Authorization scheme as shown in the following screenshot.
Going over the individual fields:
If we continue to create 3 more Exclusive Authorization Schemes (one each for the Application Roles we have left) following the guidelines above, you end up with a list that looks like this:
Now that we have our Exclusive Authentication Schemes let's see how to create a set of Cascading Schemes. We use the same wizard and most of the same attributes, but one major thing will change; The list of names for the Application Roles we assign to each scheme. Here's the first one for Cascading Consumer scheme:
Notice in the Name(s) field we have a comma separated list of roles. The list of roles indicates that anyone who is assigned one of the listed roles may see the protected component. As you work your way up the ladder, the CASCADING USER Authorization Scheme would exclude the Consumer role, the CASCADING MANAGER would exclude the Consumer and User roles, etc. finally ending up with the CASCADING ADMIN Authorization Scheme that only references the Administrator role.
In the end you should have 8 Authorization schemes; 4 Exclusive and 4 Cascading.
The last shared component we need is a simple list of values that presents the Application Access Control roles so that we can use them in our User Interface. There is nothing particularly special about this LOV other than the fact that it uses the APEX view APEX_APPL_ACL_ROLES. To create it, simply create a dynamic List of Values called ACCESS_ROLES using the following query.
Now that we have all the back end components we need, we can create a UI that allows us to test it all out. For the purposes of demonstration I'm going to do everything on one page.
The first thing we'll do is create an extremely simple form on the page to allow us to apply and remove roles from the admin user. To do this create a Static Content region named Apply Roles and in it, place a Check Box item called P1_ROLE_IDS. In the attributes of the check box, set the List of Values to the ACCESS_ROLES LOV we created earlier. We also need a button to save our changes, so add a button to the region and call it SUBMIT.
You should end up with a form that looks something like this:
Now we need to add in the logic to read and display the currently assigned roles, and save any changes we may apply.
To read the currently assigned roles and display them using the P1_ROLE_IDS item, we need to create a new APEX Process in the Pre-Rendering > Before Header execution point of the page. This region should contain the following code:
To save any changes we need to create a process in the Processing execution point and link it to the SUBMIT button. Create a process called Assign Roles to User, and use the following PL/SQL code for its action. Don't forget to set the Success Message and the When Button Pressed attribute the SUBMIT.
Notice that this process uses the APEX_ACL API. In this case we're using the REPLACE_USER_ROLES procedure to overwrite any roles that may already exist for the user, but you could just as easily use the API to individually add and remove roles.
Note: In the two code blocks above we've hardcoded the ADMIN user as the one we're assigning roles to. In the real world we'd have a form that allowed us to create and modify users and reference the user currently being edited instead of the hardcoded ADMIN user. In a system where people may be signing up for access, you may want to have a process whereby any new user is auto-assigned a default role (say, CONTRIBUTOR) and only given a more privileged role once vetted. There are loads of things you can do, and once you've chosen to create your own Authentication and Authorization schemes, you're in complete control of all of them.
The last step is to create a set of components to which we can assign our Authorization schemes and see how they work. For this application I created two empty Static Content regions named Exclusive Items and Cascading Items and placed them side by side in the page. I then created 4 more Static content regions in each side, set their template to Hero and chose an icon for each. I gave each of them name that would relate to the Authorization Scheme that I then assigned to them. See the image below.
For each of the Hero regions on the left side, in the Security attributes section I assigned the EXCLUSIVE Authorization scheme that matched the name of the region (EXCLUSIVE ADMIN for ADMINISTRATORS ONLY, EXCLUSIVE MANAGER for MANAGERS ONLY and so on). Once they are applied, the region will only be rendered if you have assigned the related role to the admin user using the form.
Similarly, on the right side, I assigned the matching CASCADING role to each of the Hero regions. Once applied you will see that if you apply any role to the admin user, the regions for that role and any cascaded role will be rendered. An example below shows the User role being applied. Notice that in the EXCLUSIVE section, you only see the USER protected region, but on the CASCADING side, you see both USER and CONSUMER due to the cascading nature of the USER Authorization Scheme.
In this post we've explored the components of Access Control and how to use them to implement a custom Authorization Scheme using the built in components. There is obviously a lot more you could do to extend and demonstrate authorization techniques, but this post is longer than even I thought it would be.
You can try out the application here on apex.oracle.com. Simply log in with admin/admin and play around with assigning roles. if you want to pick apart what I've done, you can also download the full application here.
Thanks for putting up with the extremely long post and, as always, if you have questions I'm happy to try to help.