One authorization scheme to rule them all

I

I was working on a project, where we have multiple pages with multiple regions, multiple business roles and very complex requirements what to show and allow to do to these specific roles. So we implemented one authorization scheme per one business role and map them to regions, items, buttons, columns, processes... As the requirements grew, we had to add merged auth schemes like role1_or_role2, role1_except_role2 to cover overlaps, since APEX allows us to have just one autorization scheme asssigned. Doing this with 2-3 roles is fine, doing that with 10+ complex roles is a nightmare especially when the requirements are changing all the time.

Well, I wish I had used the component based authorization from the start. And this is not a new feature, this is available since APEX 5! You basically create an authorization scheme, set the caching to Always or Per Component. When you do this and select type PL/SQL fn. returning boolean, APEX will pass extra values to your function:

RETURN is_user (
    in_user_id          => :APP_USER,
    in_component_id     => :APP_COMPONENT_ID,
    in_component_type   => :APP_COMPONENT_TYPE,
    in_component_name   => :APP_COMPONENT_NAME
);

Component type is the name of the APEX view where you can find the component and the name is the name (not the label for user). You should be more careful with chosing meaningful names. Oh, and component_id might change in between environments, so keep that in mind.

Steps needed

I have created a single authorization scheme called IS_USER (you can use whatever you like) and a function with the same name. Then I used this as an authorization scheme on every page, every region I want to protect, on every button and every page process. You can also protect page items, grid/report columns and many other components. Just use your scheme there.

These are the steps needed:

• create empty IS_USER function returning boolean
• create IS_USER authorization scheme in APEX
• use IS_USER authorization scheme on each component you would like to protect
• get list of protected components so you can map it to your business roles
• create table to map components to your business roles
• alter IS_USER function which will evaluate access to the requested component

Component discovery

This is a list of components which accepts the authorization scheme:

SELECT
    d.apex_view_name,
    n.comments
FROM apex_dictionary d
JOIN apex_dictionary n
    ON n.apex_view_name = d.apex_view_name
    AND n.column_id     = 0
WHERE d.column_name     = 'AUTHORIZATION_SCHEME_ID'
ORDER BY 1;

There is also a All Auth view, but it does not list the component_id, which makes it basically useless:

SELECT a.*
FROM apex_application_all_auth a
WHERE a.application_id = &APP_ID.;

For creating the components list you can go thru the APEX views, which is a bit painful (because there is no single list). So instead of doing that you can actually discover protected components as you request access to them (as you go through your pages).

Lets create table to map components to business roles:

CREATE TABLE auth_components (
    component_id                    NUMBER          NOT NULL,
    component_type                  VARCHAR2(30)    NOT NULL,
    component_name                  VARCHAR2(256)   NOT NULL,
    role_id                         VARCHAR2(64),	-- use number if you want
    --
    CONSTRAINT uq_auth_components
        UNIQUE (component_id, role_id)
);

Table for business roles (just to assign business roles to the users):

CREATE TABLE auth_roles (
    user_id                         VARCHAR2(128)   NOT NULL,
    role_id                         VARCHAR2(64)    NOT NULL,
    --
    CONSTRAINT pk_auth_roles
        PRIMARY (user_id, role_id)
);

Now we can use the table in our autorization function:

    FUNCTION is_user (
        in_user_id              VARCHAR2,
        in_component_id         NUMBER,
        in_component_type       VARCHAR2,
        in_component_name       VARCHAR2
    )
    RETURN BOOLEAN
    AS
        v_authorized            CHAR;
    BEGIN
        -- check access to the page
        SELECT MAX('Y') INTO v_authorized
        FROM auth_components t
        JOIN auth_roles r
            ON r.user_id        = in_user_id
            AND r.role_id       = t.role_id
        WHERE t.component_id    = in_component_id;
        --
        IF v_authorized IS NOT NULL THEN
            RETURN TRUE;
        END IF;

        -- check for new components
        discover_component (
            in_component_id     => in_component_id,
            in_component_type   => in_component_type,
            in_component_name   => in_component_name
        );
        --
        RETURN FALSE;
    END;

And you might want to ease the component discovery:

    PROCEDURE discover_component (
        in_component_id         NUMBER      := NULL,
        in_component_type       VARCHAR2    := NULL,
        in_component_name       VARCHAR2    := NULL
    )
    AS
        PRAGMA AUTONOMOUS_TRANSACTION;
        --
        rec                     auth_components%ROWTYPE;
    BEGIN
        rec.component_id        := in_component_id;
        rec.component_type      := in_component_type;
        rec.component_name      := in_component_name;
        --
        BEGIN
            INSERT INTO auth_components VALUES rec;
        EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            ROLLBACK;
        END;
        --
        COMMIT;
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
    END;

More on the component discovery in the future article.

Map components to roles

Now you have to define which business role is allowed to see/use that component or not. I usually create a pivoted grid for that:

And thats it. This component level authorization schemes is great when you know that the requirements will change. Are you adding a new role? No problem. Are you changing or removing a role? No problem. With this, you can manage your roles on a page and you don't have to change anything in APEX nor in the database.

There is another great point on this. When you add/change/remove a role from your application, do you retest whole application and for all roles? No. That is very time consuming. So did you wrote unit tests to cover these? No. So you test few things here and there and you are just hoping that it will work as expected. Guess what. If you use this component level security, you don't have to retest anything, because you are not changing code in APEX nor in the database. That is a huge argument to use this feature.

Other mappings

May 14th, I have removed Tasks related screens to keep the article more up to the point, I will introduce this in dedicated article later.

If you map the components completely, you might not use these. For me, it provides me more flexibility and extra layer of security. With these, I can also define specific action on forms and grid. And next time, I will show you how I use this single authorization scheme to check if user is allowed to add a new row or update or delete row in a grid.