I
I have touched the component discovery topic in the One authorization scheme article. I did created a table tsk_auth_components which store application components and as they are discovered (requested for the first time) they are added to this table. This might not be enough.
Today I will show you how to discover all components at once. In a tsk_auth_components_list_v view I have compiled a list of APEX views containing authorization_scheme for current application (the component name is missing, but that is not the important at this time):
SELECT 'APEX_APPLICATION_BC_ENTRIES', breadcrumb_entry_id, NULL, component_signature FROM apex_application_bc_entries WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_COMPUTATIONS', application_computation_id, NULL, component_signature FROM apex_application_computations WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_LIST_ENTRIES', list_entry_id, NULL, component_signature FROM apex_application_list_entries WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_NAV_BAR', nav_bar_id, NULL, component_signature FROM apex_application_nav_bar WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_BRANCHES', branch_id, page_id, component_signature FROM apex_application_page_branches WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_BUTTONS', button_id, page_id, component_signature FROM apex_application_page_buttons WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_CHART_S', series_id, page_id, component_signature FROM apex_application_page_chart_s WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_COMP', computation_id, page_id, component_signature FROM apex_application_page_comp WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_DA', dynamic_action_id, page_id, component_signature FROM apex_application_page_da WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_DA_ACTS', dynamic_action_id, page_id, component_signature FROM apex_application_page_da_acts WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_IR_COL', interactive_report_id, page_id, component_signature FROM apex_application_page_ir_col WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_ITEMS', item_id, page_id, component_signature FROM apex_application_page_items WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_PROC', process_id, page_id, component_signature FROM apex_application_page_proc WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_REGIONS', region_id, page_id, component_signature FROM apex_application_page_regions WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_REG_COLS', region_column_id, page_id, NULL FROM apex_application_page_reg_cols WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_RPT_COLS', region_report_column_id, page_id, component_signature FROM apex_application_page_rpt_cols WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PAGE_VAL', validation_id, page_id, component_signature FROM apex_application_page_val WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PARENT_TABS', parent_tab_id, NULL, component_signature FROM apex_application_parent_tabs WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_PROCESSES', application_process_id, NULL, component_signature FROM apex_application_processes WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_TABS', tab_id, NULL, component_signature FROM apex_application_tabs WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_CARD_ACTIONS', action_id, NULL, NULL FROM apex_appl_page_card_actions WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_FILTERS', item_id, page_id, component_signature FROM apex_appl_page_filters WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_FILTER_GROUPS', item_group_id, page_id, NULL FROM apex_appl_page_filter_groups WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_IG_COLUMNS', column_id, page_id, NULL FROM apex_appl_page_ig_columns WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_IG_RPTS', report_id, page_id, NULL FROM apex_appl_page_ig_rpts WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_PAGE_MAP_LAYERS', layer_id, page_id, NULL FROM apex_appl_page_map_layers WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_SEARCH_CONFIGS', search_config_id, NULL, NULL FROM apex_appl_search_configs WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPL_WEB_SRC_OPERATIONS', operation_id, NULL, component_signature FROM apex_appl_web_src_operations WHERE application_id = &&APP_ID
So now I can add missing components in my table and I can also remove components which no longer exists:
DECLARE
in_app_id CONSTANT PLS_INTEGER := core.get_app_id();
v_count PLS_INTEGER;
v_cols VARCHAR2(4000);
BEGIN
-- go through all APEX view and find out components with authorization_scheme
FOR c IN (
SELECT
d.apex_view_name AS table_name,
MAX(n.comments) AS 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 IN ('AUTHORIZATION_SCHEME_ID', 'APPLICATION_ID')
AND d.apex_view_name NOT IN (
'APEX_APPLICATIONS',
'APEX_APPLICATION_AUTHORIZATION',
'APEX_APPLICATION_ALL_AUTH',
'APEX_DEBUG_MESSAGES',
'APEX_WORKSPACE_ACTIVITY_LOG'
)
GROUP BY d.apex_view_name
HAVING COUNT(*) = 2
ORDER BY 1
) LOOP
-- add missing components
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || c.table_name
|| ' WHERE application_id = :app_id'
|| ' AND authorization_scheme_id IS NOT NULL'
INTO v_count
USING IN in_app_id;
--
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE(c.table_name || ' = ' || v_count);
--
FOR d IN (
-- find and store component_id and page_id
SELECT t.component_id, t.page_id
FROM tsk_auth_components_list_v t
WHERE t.component_type = c.table_name
AND authorization_scheme_id IS NOT NULL
) LOOP
DBMS_OUTPUT.PUT_LINE(' ' || d.component_id);
--
BEGIN
INSERT INTO tsk_auth_components (component_id, component_type, component_name, page_id, is_active)
VALUES (
d.component_id,
c.table_name,
'?',
d.page_id,
'Y'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
END LOOP;
--
DBMS_OUTPUT.PUT_LINE(' --');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || c.table_name);
RAISE;
END;
END LOOP;
-- remove obsolete components
DELETE FROM tsk_auth_components c
WHERE c.component_id IN (
SELECT c.component_id
FROM tsk_auth_components c
LEFT JOIN tsk_auth_components_list_v t
ON t.component_id = c.component_id
WHERE t.component_id IS NULL
);
END;
/
--
SELECT * FROM tsk_auth_components;
Checks
I can now easily check if everything is set as it should be upfront. Also I have moved some components in the report to build a proper tree, so for example processes mapped to buttons are listed under related buttons. This allows me to check proper rights on all components. If you can see the button, you should be able to run the action behind too. Is the button role not allowed to reach the region or page? Than you will never see the button too. Now this is all visible.
Don't forget to setup APEX session first.
SET DEFINE ON
BEGIN
core.create_session('&USER_ID', &APP_ID);
END;
/
Comments
Post a Comment