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?
Comments
Post a Comment