ted_p1132_v
with app1 as
(
-- the application(s) to check
select app2.workspace
, app2.application_id
from apex_applications app2
where app2.application_id = coalesce(nv('APP_ID'), 25384)
)
, aut as
-- using this "with" is much faster than using the view directly (twice) further on in the query
select a.workspace
, a.application_id
, trim(a.authorization_scheme_id) as authorization_scheme_id
, a.authorization_scheme_name
from apex_application_authorization a
join apex_applications app on (app.workspace = a.workspace and app.application_id = a.application_id)
, obj as
-- all objects with an authorization scheme
select workspace, application_id, null as page_id, 'bc_entries' as object_type, p1.breadcrumb_id as object_sequence, p1.entry_label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_bc_entries p1 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'computations' as object_type, p2.computation_sequence as object_sequence, p2.computation_item as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_computations p2 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'list_entries' as object_type, p3.display_sequence as object_sequence, p3.entry_text as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_list_entries p3 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'nav_bar' as object_type, p4.display_sequence as object_sequence, p4.icon_target as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_nav_bar p4 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'pages' as object_type, p5.page_id as object_sequence, p5.page_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_pages p5 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_branches' as object_type, p6.process_sequence as object_sequence, p6.branch_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_branches p6 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_buttons' as object_type, p7.button_sequence as object_sequence, p7.button_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_buttons p7 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_chart_s' as object_type, p8.series_seq as object_sequence, p8.series_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_chart_s p8 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_comp' as object_type, p9.execution_sequence as object_sequence, p9.item_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_comp p9 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_da' as object_type, p10.dynamic_action_sequence as object_sequence, p10.dynamic_action_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_da p10 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_da_acts' as object_type, p11.action_sequence as object_sequence, p11.action_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_da_acts p11 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_ir_col' as object_type, p12.display_order as object_sequence, p12.region_name || ' - ' || p12.column_alias as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_ir_col p12 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_items' as object_type, p13.display_sequence as object_sequence, p13.item_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_items p13 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_proc' as object_type, p14.execution_sequence as object_sequence, p14.process_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_proc p14 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_regions' as object_type, p15.display_sequence as object_sequence, p15.region_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_regions p15 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_reg_cols' as object_type, p16.display_sequence as object_sequence, p16.region_name || ' - ' || p16.name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_reg_cols p16 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_rpt_cols' as object_type, p17.display_sequence as object_sequence, p17.region_name || ' - ' || p17.column_alias as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_rpt_cols p17 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'page_val' as object_type, p18.validation_sequence as object_sequence, p18.validation_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_page_val p18 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'parent_tabs' as object_type, p19.display_sequence as object_sequence, p19.tab_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_parent_tabs p19 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'processes' as object_type, p20.process_sequence as object_sequence, p20.process_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_processes p20 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'tabs' as object_type, p21.display_sequence as object_sequence, p21.tab_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_application_tabs p21 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'card_actions' as object_type, p22.display_sequence as object_sequence, p22.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_card_actions p22 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'comp_actions' as object_type, p23.display_sequence as object_sequence, p23.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_comp_actions p23 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'filters' as object_type, p24.display_sequence as object_sequence, p24.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_filters p24 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'filter_groups' as object_type, p25.display_sequence as object_sequence, p25.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_filter_groups p25 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'ig_columns' as object_type, p26.display_sequence as object_sequence, p26.region_name || ' - ' || p26.name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_ig_columns p26 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'ig_rpts' as object_type, p27.report_id as object_sequence, p27.region_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_ig_rpts p27 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'map_layers' as object_type, p28.display_sequence as object_sequence, p28.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_map_layers p28 where authorization_scheme is not null union all
select workspace, application_id, page_id, 'menu_entries' as object_type, p29.display_sequence as object_sequence, p29.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_page_menu_entries p29 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'search_configs' as object_type, p30.search_config_id as object_sequence, p30.label as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_search_configs p30 where authorization_scheme is not null union all
select workspace, application_id, null as page_id, 'web_src_operations' as object_type, p31.operation_id as object_sequence, p31.module_name as object_name, trim(authorization_scheme_id) as authorization_scheme_id, authorization_scheme from apex_appl_web_src_operations p31 where authorization_scheme is not null
, obj_aut as
-- all objects left joined with apex_application_authorization
select obj.*
, aut.authorization_scheme_id as auth_authorization_scheme_id
, aut.authorization_scheme_name as auth_authorization_scheme_name
from obj
join apex_applications app on (app.workspace = obj.workspace and app.application_id = obj.application_id) -- limit the applications to check
left join aut on
( aut.workspace = obj.workspace
and aut.application_id = obj.application_id
and ( trim(aut.authorization_scheme_id) = obj.authorization_scheme_id
or '!' || trim(aut.authorization_scheme_id) = obj.authorization_scheme_id
where obj.authorization_scheme_id not in ('MUST_NOT_BE_PUBLIC_USER') -- these are always OK
-- all objects without a match in apex_application_authorization,
-- with the authorization scheme that may be (i.e. probably is) the one we need to use
select obj_aut.workspace
, obj_aut.application_id
, obj_aut.page_id
, obj_aut.object_type
, obj_aut.object_sequence
, obj_aut.object_name
, obj_aut.authorization_scheme_id
, obj_aut.authorization_scheme
, ( case when obj_aut.authorization_scheme like 'Not %' then '!' else '' end ) || aut2.authorization_scheme_id as maybe_authorization_scheme_id
, ( case when obj_aut.authorization_scheme like 'Not %' then 'Not ' else '' end ) || aut2.authorization_scheme_name as maybe_authorization_scheme_name
from obj_aut
join app1 on (app1.workspace = obj_aut.workspace and app1.application_id = obj_aut.application_id) -- limit the applications to check
left join aut aut2 on
( aut2.authorization_scheme_id = obj_aut.authorization_scheme_id
or '!' || aut2.authorization_scheme_id = obj_aut.authorization_scheme_id
where obj_aut.auth_authorization_scheme_id is null
-- (optionally) also search for all pages without an authorization scheme
union all
select p5.workspace
, p5.application_id
, p5.page_id
, 'pages' as object_type
, p5.page_id as object_sequence
, p5.page_name as object_name
, null as authorization_scheme_id
, null as authorization_scheme
, null as maybe_authorization_scheme_id
, null as maybe_authorization_scheme_name
from apex_application_pages p5
join app1 on (app1.workspace = p5.workspace and app1.application_id = p5.application_id) -- limit the applications to check
where p5.authorization_scheme is null
and p5.page_id not in (0, 101) -- exclude pages
--
order by workspace
, application_id
, page_id nulls last
, object_type
, object_sequence
, object_name
xxxxxxxxxx
ted_p1132_pre_v
with a as
select tc.*
, count(*) over (order by tc.table_name) as n
, count(*) over () as n2
, ( case
when tc.table_name like 'APEX\_APPLICATION\_%' escape '\' then substr(tc.table_name, length('APEX_APPLICATION_')+1) --'
when tc.table_name like 'APEX\_APPL\_PAGE\_%' escape '\' then substr(tc.table_name, length('APEX_APPL_PAGE_')+1) --'
when tc.table_name like 'APEX\_APPL\_%' escape '\' then substr(tc.table_name, length('APEX_APPL_')+1) --'
else tc.table_name
end
) as object_type
, ( case when tc.table_name like '%\_PAGE%' escape '\' then 'Y' else 'N' end ) as has_page_id --'
from all_tab_columns tc
, (
select max(u1.username) as current_apex_user
from all_users u1
where u1.username like 'APEX%'
and regexp_like (substr(u1.username,'6',1), '^[0-9]*$')
) u
where tc.owner = u.current_apex_user
and tc.table_name like 'APEX%'
and tc.table_name not in ('APEX_APPLICATIONS', 'APEX_APPLICATION_AUTHORIZATION')
and tc.column_name = 'AUTHORIZATION_SCHEME_ID'
order by tc.table_name
select a."OWNER",a."TABLE_NAME",a."COLUMN_NAME",a."DATA_TYPE",a."DATA_TYPE_MOD",a."DATA_TYPE_OWNER",a."DATA_LENGTH",a."DATA_PRECISION",a."DATA_SCALE",a."NULLABLE",a."COLUMN_ID",a."DEFAULT_LENGTH",a."DATA_DEFAULT",a."NUM_DISTINCT",a."LOW_VALUE",a."HIGH_VALUE",a."DENSITY",a."NUM_NULLS",a."NUM_BUCKETS",a."LAST_ANALYZED",a."SAMPLE_SIZE",a."CHARACTER_SET_NAME",a."CHAR_COL_DECL_LENGTH",a."GLOBAL_STATS",a."USER_STATS",a."AVG_COL_LEN",a."CHAR_LENGTH",a."CHAR_USED",a."V80_FMT_IMAGE",a."DATA_UPGRADED",a."HISTOGRAM",a."DEFAULT_ON_NULL",a."IDENTITY_COLUMN",a."EVALUATION_EDITION",a."UNUSABLE_BEFORE",a."UNUSABLE_BEGINNING",a."COLLATION",a."N",a."N2",a."OBJECT_TYPE",a."HAS_PAGE_ID"
, 'select'
|| ' workspace'
|| ', application_id'
|| ', ' || (case when a.has_page_id = 'Y' then ' page_id' else 'null as page_id' end)
|| ', ''' || lower(a.object_type) || ''' as object_type'
|| ', p' || trim(a.n) || '.' || '###' || ' as object_sequence' -- manual edit needed
|| ', p' || trim(a.n) || '.' || '###' || ' as object_name' -- manual edit needed
|| ', trim(authorization_scheme_id) as authorization_scheme_id'
|| ', authorization_scheme'
|| ' from ' || lower(a.table_name) || ' p' || trim(a.n)
|| ' where authorization_scheme is not null'
|| ( case when a.n <> a.n2 then ' union all' else null end )
as x1
, 'select * from ' || lower(a.table_name) || ' p' || trim(a.n) as x2
from a
order by a.n