Thursday, May 21, 2009

How to determine if user can view an APEX region.

If you just want to see how to determine if user is allowed to view a region scroll to bottom of this post, however I suggest you read the explanation as to why/when you may want to use this function

I recently received a request to build an internal application to generate many reports for one of the teams in my organization. At a high level, the application is supposed to have (it's still not built) several "Report Result" pages. Each Report Result page will contain 10 to 15 reports. Their will be a menu page in which end users can select the Report Result page they'd like to run, and the individual reports they want to run on that page:



Query for Checkboxes:


SELECT region_name d,
region_id r
FROM apex_application_page_regions
WHERE application_id = 103
AND page_id = 2
ORDER BY UPPER (d) ASC


When users click "Go", they will go to the selected "Report Result" page and execute the selected reports.

So far this is fairly straight forward (using the APEX Dictionary to help out). To make things more difficult, certain reports should only be accessible depending on the parsing schema. For example if the parsing schema is "SCOTT" then Report/Region 2 should not be displayed.

To meet this requirement I can add a condition (SQL Exists) to the Report 2 region on Page 2:


SELECT 1
FROM DUAL
WHERE :p2_region_static_id_list IS NULL
OR ( :f_parsing_schema != 'SCOTT' -- F_PARSING_SCHEMA is an application_item I added
AND INSTR (':' || 'REPORT2' || ':', :p2_region_static_id_list) > 0)
-- "REPORT2" is the Region's Static ID


When I run Page 2, Reports 1, 3, and 4 are executed which is correct. However, on the menu page the user had the option to select Report 2 when they should have never been allowed to see it in the check list since the parsing schema was SCOTT.

To resolve this issue, I've created the following function to determine if a user can view a specified region:


/**
* Determine if current user has permissions to view region
* @param p_region_id region to test
* @return Y or N
* @author Martin Giffy DSouza - http://apex-smb.blogspot.com/
*/

CREATE OR REPLACE FUNCTION f_apex_permission_flag (
p_region_id IN apex_application_page_regions.region_id%TYPE
)
RETURN VARCHAR2
AS
TYPE v_apex_region_rec_type IS RECORD (
page_authorization_scheme apex_application_page_regions.authorization_scheme%TYPE,
page_build_option_status apex_application_build_options.build_option_status%TYPE,
reg_authorization_scheme apex_application_page_regions.authorization_scheme%TYPE,
reg_build_option_status apex_application_build_options.build_option_status%TYPE,
reg_condition_expression1 apex_application_page_regions.condition_expression1%TYPE,
reg_condition_expression2 apex_application_page_regions.condition_expression2%TYPE,
reg_condition_type apex_standard_conditions.d%TYPE
);

v_apex_region v_apex_region_rec_type;
BEGIN
-- If region is null return N to access
IF p_region_id IS NULL THEN
RETURN 'N';
END IF;

SELECT aap.authorization_scheme page_authorization_scheme,
aap_bo.build_option_status page_build_option_status,
aapr.authorization_scheme reg_authorization_scheme,
aapr_bo.build_option_status reg_build_option_status,
aapr.condition_expression1 reg_condition_expression1,
aapr.condition_expression2 reg_condition_expression2,
asc_reg.d reg_condition_type
INTO v_apex_region
FROM apex_application_pages aap,
apex_application_build_options aap_bo,
apex_application_build_options aapr_bo,
apex_application_page_regions aapr,
apex_standard_conditions asc_reg
WHERE aapr.region_id = p_region_id
AND aapr.page_id = aap.page_id
AND aapr.application_id = aap.application_id
AND UPPER (aap.build_option) = UPPER (aap_bo.build_option_name(+))
AND aapr.build_option_id = aapr_bo.build_option_id(+)
AND aapr.condition_type = asc_reg.r(+);

-- PAGE VALIDATIONS
-- Check Page Build Option
IF UPPER (NVL (v_apex_region.page_build_option_status, 'INCLUDE')) != 'INCLUDE' THEN
RETURN 'N';
END IF;

-- Check Page Authorization
IF v_apex_region.page_authorization_scheme IS NOT NULL THEN
IF apex_util.public_check_authorization (p_security_scheme => v_apex_region.page_authorization_scheme) = FALSE THEN
RETURN 'N';
END IF;
END IF;

-- REGION VALIDATIONS

-- Check Region Build Option
IF UPPER (NVL (v_apex_region.reg_build_option_status, 'INCLUDE')) != 'INCLUDE' THEN
RETURN 'N';
END IF;

-- Check Region Authorization
IF v_apex_region.reg_authorization_scheme IS NOT NULL THEN
IF apex_util.public_check_authorization (p_security_scheme => v_apex_region.reg_authorization_scheme) = FALSE THEN
RETURN 'N';
END IF;
END IF;

-- Check the region condition
IF v_apex_region.reg_condition_type IS NOT NULL THEN
IF wwv_flow_conditions.standard_condition (p_condition_type => v_apex_region.reg_condition_type,
p_condition => v_apex_region.reg_condition_expression1,
p_condition2 => v_apex_region.reg_condition_expression1
) = FALSE THEN
RETURN 'N';
END IF;
END IF;

-- All test passed
RETURN 'Y';
END f_apex_permission_flag;


Using the new function, the Checkbox Query (on Page 1) now becomes:


SELECT region_name d,
region_id r
FROM (SELECT region_id,
region_name,
f_apex_permission_flag (region_id) permission_flag
FROM apex_application_page_regions
WHERE application_id = 103
AND page_id = 2) x
WHERE x.permission_flag = 'Y'
ORDER BY UPPER (d) ASC


A word of precaution: If you're using this function to verify region access on another page you should be aware of how the condition is defined. If it is referencing items specific to its own page then you may get some false positives as those items may not have been defined yet.

5 comments:

  1. Hi Martin,

    wwv_flow_conditions is an internal package of APEX and therefor not granted to PUBLIC. This internal packages are subject to change and you should also be caution to grant them to PUBLIC or other schemes, because they could provide a high privileged access to your system.

    Regards
    Patrick

    ReplyDelete
  2. Hi Patrick,

    Thanks for pointing this out. I had granted permissions to wwv_flow_conditions to PUBLIC for a previous example and forgot that I did it.

    For the time being what I may do is create a schema, called APEX_PUBLIC, and grant wwv_flow_conditions to it. APEX_PUBLIC will then have it's own package that will wrap functions and procedures that I want from wwv_flow_conditions.

    Martin

    ReplyDelete
  3. Hi Patrick,

    Is their another, supported, way to find see if a user can view a region ahead of time?

    Thank you,

    Martin

    ReplyDelete
  4. Not I would know of. But implementing a copy of the wwv_flow_conditions package shouldn't be too complicated.

    But your proposed solution of wrapping it is also a possibility.

    Regards
    Patrick

    ReplyDelete
  5. Hi Patrick,

    I agree that writing our own shouldn't be too complicated but why do it when it's already there. I think we'll go with a wrapper for now. If the code changes in the next release we'll write our own code for it.

    Thanks for the feedback.

    Martin

    ReplyDelete