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 - 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.