One authorization scheme for APEX interactive grids

I

In previous article I have showed you a way how you can manage the whole application security through user defined roles (and I mean defined by business users not by developers).

Today I will show you how you can achieve the same with interactive grids. How you can use the same IS_USER role to manage which DML actions will be allowed on the grid for specific application role. I will be using the Tasks application and demonstrating it there.

The security for grids is assured on multiple levels:

• AUTH_PAGES - protects access to the pages and page visibility in navigation (protecting access to the whole page with grid)
• AUTH_COMPONENTS - protects access to the individual page components (protecting access to the grid region, grid handler, specific columns, related DAs...)
• AUTH_TABLES - protects against unauthorized DML actions inside of the TAPI procedures, but also on a grid through dedicated IS_USER_C|U|D autorization schemes
• AUTH_PROCEDURES - protects access the procedures and also protects grid actions as a whole

This article is focusing on the last two.

I assume you have created application roles and assigned them to users. To secure your grids, you have to specify allowed operations (C - create/add, U - update, D - delete) in AUTH_TABLES table for each role. I have a Map Tables page to simplify that.

This is the low level security in TAPI package ensuring that requested C|U|D action is allowed for user roles. Here is example of one TAPI procedure, note check_allowed_dml procedure from AUTH package which will perform the security check:

    PROCEDURE projects (
        rec                     IN OUT NOCOPY   tsk_projects%ROWTYPE,
        --
        in_action               CHAR                            := NULL,
        in_client_id            tsk_projects.client_id%TYPE     := NULL,		-- old PK
        in_project_id           tsk_projects.project_id%TYPE    := NULL			-- old PK
    )
    AS
        c_action                CONSTANT CHAR   := get_action(in_action);
    BEGIN
        -- you have to add this call to your every TAPI procedure
        tsk_auth.check_allowed_dml (
            in_table_name       => get_table_name(),    -- basically returns TSK_PROJECTS for this case
            in_action           => c_action,            -- represents C|U|D action performed on grid
            in_user_id          => core.get_user_id(),  -- user id, obviously
            in_client_id        => rec.client_id,       -- primary key needed for auth evaluation
            in_project_id       => rec.project_id       -- becase we have different roles for different clients and projects
        );

        -- delete record
        IF c_action = 'D' THEN
            tsk_tapi.projects_d(NVL(in_project_id, rec.project_id));
            --
            RETURN;
        END IF;

        -- upsert record
        UPDATE tsk_projects t
        SET ROW             = rec
        WHERE t.project_id  = rec.project_id;
        --
        IF SQL%ROWCOUNT = 0 THEN
            INSERT INTO tsk_projects VALUES rec;
        END IF;
    END;

The evaluation is done in check_allowed_dml procedure, resp. is_allowed_dml function in AUTH package (which basically checks if user has a valid role to performs requested operation on requested table):

SELECT MAX('Y')
FROM tsk_auth_tables t
JOIN tsk_auth_roles r
    ON r.client_id      = in_client_id
    AND (r.project_id   = in_project_id OR r.project_id IS NULL)
    AND r.user_id       = in_user_id
    AND r.role_id       = t.role_id
    AND r.is_active     = 'Y'
JOIN tsk_auth_users a
    ON a.user_id        = r.user_id
    AND a.client_id     = r.client_id
    AND a.is_active     = 'Y'
JOIN tsk_users u
    ON u.user_id        = r.user_id
    AND u.is_active     = 'Y'
WHERE t.table_name      = in_table_name
    AND t.is_active     = 'Y'
    AND (
        (t.is_allowed_create = 'Y' AND NULLIF(in_action, 'C') IS NULL) OR
        (t.is_allowed_update = 'Y' AND NULLIF(in_action, 'U') IS NULL) OR
        (t.is_allowed_delete = 'Y' AND NULLIF(in_action, 'D') IS NULL)
    );

The trick

So now when you have defined which actions on which tables needs to be protected, you can start building the security wall. If you are using the TAPI package to store data in your table, then you are already protected on the backend side.

But it would be nice to remove unwanted options from the grid itself, like option to delete the rows it that is no allowed, or remove the Add Row button if creating new rows is not allowed. That is the tricky part, because if you set the IS_USER autorization scheme to grid attributes - add/update/delete, it will not work as expected. That is because inside of this function you don't know for which action it is being called. I have created a workaround for this.

The trick is to create IS_USER_C, IS_USER_U and IS_USER_D authorization schemes (you can name them however you like) with extra argument for the action and map them to the relevant grid attributes. I did created schemes like this:

-- IS_USER_C authorization scheme, caching Always (no)
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,
    in_action           => 'C'  -- adding new row in grid
);

-- IS_USER_U authorization scheme, caching Always (no)
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,
    in_action           => 'U'  -- updating row in grid
);

-- IS_USER_D authorization scheme, caching Always (no)
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,
    in_action           => 'D'  -- deleting row from grid
);

So I am still calling the original IS_USER function, just with extra parameter is_action containing C|U|D char. The code to evaluate the permissions looks like this:

        -- can we do requested operation too?
        IF in_action IS NOT NULL THEN
            v_authorized := NULL;
            --
            FOR c IN (
                -- check if region is listed as grid and has valid auth setup
                SELECT t.table_name
                FROM tsk_auth_grids_v t
                WHERE t.page_id         = in_page_id
                    AND t.region_id     = in_component_id
                    AND t.table_name    IS NOT NULL
                    AND t.auth_process  = 'Y'
                    AND (
                        (in_action = 'C' AND t.auth_c = 'Y') OR
                        (in_action = 'U' AND t.auth_u = 'Y') OR
                        (in_action = 'D' AND t.auth_d = 'Y')
                    )
            ) LOOP
                -- check if current user is allowed to do requested action
                IF tsk_auth.is_allowed_dml (
                    in_table_name       => c.table_name,
                    in_action           => in_action,
                    in_user_id          => in_user_id
                )
                THEN
                    v_authorized := 'Y';
                    EXIT;
                END IF;
            END LOOP;
        END IF;

You should check the tsk_auth_grids_v view, and the whole AUTH package for more details. The point is, that for normal components it will not check the TAPI setup (AUTH_TABLES table), but it will do that for grids (and forms).

If you create these IS_USER_C|U|D schemes and assign them to every grid attributes, then you can decide at any time which roles can do them or not, from frontend, without changing a single line in code or in APEX.

Isn't this cool?