Managing users, roles and authorization schemes in APEX

T

This is my solution how to manage users and roles in APEX apps in a very effective and clear way. All code is part of the CORE project.

Authentication - Users

You have multiple options how to manage users in APEX. Just explore Shared Components - Authentication Schemes. These methods are available:

• APEX Accounts
• Custom
• Database Accounts
• HTTP Header Variable
• LDAP Directory
• No Authentication
• Open Door Credentials
• Social Sign-in

For my business projects I usually go for SSO or LDAP for legacy or small projects. For my personal projects it is usually APEX od DB accounts. Either way I have my own Users table, so I can reference users in other tables (with foreign keys).

CREATE TABLE users (
    user_id             VARCHAR2(30)    CONSTRAINT nn_users_user_id     NOT NULL,
    user_login          VARCHAR2(128)   CONSTRAINT nn_users_login       NOT NULL,
    user_name           VARCHAR2(64),
    --
    lang_id             VARCHAR2(5),
    is_active           CHAR(1),
    --
    CONSTRAINT pk_users
        PRIMARY KEY (user_id),
    --
    CONSTRAINT uq_users_user_login
        UNIQUE (user_login),
    --
    CONSTRAINT ch_users_is_active
        CHECK (is_active = 'Y' OR is_active IS NULL)
)
STORAGE (BUFFER_POOL KEEP);

How it works

When user login into my application a record in Users table is automatically created (by app.create_session procedure). Unless user is already there. If user is deactivated (is_active IS NULL) than access to the app is denied. This way I can block users from the app no matter how is their status in the company Active Directory.

I also have an option to not create these accounts/records automatically. For some projects I don't want company wide access to the app. Than I will manage list of users manually, so even if app uses SSO/LDAP to verify user against AD, user won't be able to login into the app unless someone manually inserts him into the Users table first.

I have a page 920 Users & Roles (available to app administrators) with User region for users and some stats (sessions, visited pages, errors, events...) as editable grid. Other regions are dedicated to roles and described later.

In past I had custom authentication just against Users table, but that required storing user passwords which complicates things a lot (GDPR, UX). Anyway if you need to store more info about the users, you can easily add more columns.

Authorization - Roles

Now we know that user is who he claims. We need to verify his/hers permissions (roles) to use different parts of the app.

As always, there are many paths to one goal. You can have roles defined in APEX workspace. You can have roles (groups) in/from Active Directory. I am not a fan of either because it requires to depend on another team and that means delays. I prefer to manage app roles in the app itself. It is the quickest and the most effective way for me and mostly even for the business folks. I have usually multiple app administrators and they take care of assigning roles to the users.

Which leads to the 3rd path and that is the Roles table:

CREATE TABLE roles (
    app_id              NUMBER(4)       CONSTRAINT nn_roles_app_id      NOT NULL,
    role_id             VARCHAR2(30)    CONSTRAINT nn_roles_role_id     NOT NULL,
    --
    is_active           CHAR(1),
    --
    CONSTRAINT pk_roles
        PRIMARY KEY (app_id, role_id),
    --
    CONSTRAINT ch_roles_is_active
        CHECK (is_active = 'Y' OR is_active IS NULL)
)
STORAGE (BUFFER_POOL KEEP);

How it works

To manage application roles I use a Application Roles region (check the screen above). You can see number of users assigned to each role, some options to deactivate roles or group them into meaningful groups for better overview.

Let's go throught typical project start and add some roles:

After submit (Save button in grid) many things happend:

• User Roles region - columns were added for each new role
• Application Roles region - nicely groupped new roles
• Auth Schemes region - new groups matched to auth schemes with some warning

Authorization - Auth Schemes

Check Auth Schemes region on screen above. It lists all authorization schemes for the current app, all roles defined in Application Roles, match them by the name and show some usage stats and caching info.

Here is the tedoius part. You should create APEX authorization schemes for each new role. The purpose is simple. To have all app roles also registered as authorization schemes so you can easily pick role in Security attributes of each page/region/component. I highly recommend to move logic for each role into a function so you can easily change it and also use it in your views (procedures or any type of code). You just call this function from matching auth scheme.

Here is an example code to check if user has IS_MOD_A_USER role or not:

CREATE OR REPLACE PACKAGE BODY a770 AS

    FUNCTION is_mod_a_user
    RETURN CHAR AS
        is_valid                CHAR;
    BEGIN
        SELECT 'Y' INTO is_valid
        FROM user_roles u
        JOIN roles r
            ON r.app_id         = u.app_id
            AND r.role_id       = u.role_id
        WHERE u.app_id          = app.get_app_id()
            AND u.user_id       = app.get_user_id()
            AND u.role_id       = app.get_caller_name()
            AND r.is_active     = 'Y';
        --
        RETURN is_valid;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'N';
    END;

END;
/

Now let's create authorization scheme in APEX (Shared Components - Authorization Schemes):

Let's check Auth Schemes region after we created the function and the auth scheme:

Warnings for this role are gone. Done. You should do this with every role.

Authorization - User Roles

We have Users, we have Roles. We have authorization schemes and a function for usage in your code. Now we need just to assign roles to users (map Users to Roles), throught User_roles table:

CREATE TABLE user_roles (
    app_id              NUMBER(4)       CONSTRAINT nn_user_roles_app_id     NOT NULL,
    user_id             VARCHAR2(30)    CONSTRAINT nn_user_roles_user_id    NOT NULL,
    role_id             VARCHAR2(30)    CONSTRAINT nn_user_roles_role_id    NOT NULL,
    --
    CONSTRAINT pk_user_roles
        PRIMARY KEY (app_id, user_id, role_id),
    --
    CONSTRAINT fk_users_roles_user_id
        FOREIGN KEY (user_id)
        REFERENCES users (user_id),
    --
    CONSTRAINT fk_users_roles_role_id
        FOREIGN KEY (app_id, role_id)
        REFERENCES roles (app_id, role_id)
        DEFERRABLE INITIALLY DEFERRED,
    --
    CONSTRAINT ch_user_roles_is_active
        CHECK (is_active = 'Y' OR is_active IS NULL)
)
STORAGE (BUFFER_POOL KEEP);
--
ALTER TABLE user_roles MODIFY CONSTRAINT fk_users_roles_user_id DISABLE;  -- to assign roles before user is created

I disabled the foreign key to Users because sometimes I need to assign role to user before he even login into the app.

To manage these assignments I use User Roles region. This is technically the most difficult region. It is a dynamic pivot. Users are in rows, roles are in columns. When you add new role to App Roles, it will appears as a new column here (you can manage columns order via order# column). For me this is the clearest way how to vizualize roles assigned to users.

So when you assign some roles to yourself and go to you User Info page, you should also see as a user what roles you have including the role description.

Wrap up

User verification is done against both SSO/LDAP and Users table. User must be present and active in both.

Application roles are managed in the application itself, same for their assignment to users. Each role is also created as authorization scheme in APEX so it is available in Security attributes in various APEX components and as a function so it can be used in your code.