Skip to Main Content
Feature Request FR-2830
Product Area Application Builder
Status CLOSED

16 Voters

Allow all suitable reachable database objects in wizards

mark.daems Public · Nov 3 2022

Idea Summary
Some wizards don't work correctly if you specify database object names that do not belong to the parsing schema even if the parsing schema has the necessary permissions to use these objects. Same issue with synonyms.

Use Case
For security reasons we don't want to use our main application's database schema as the parsing schema for APEX. For objects we want to give apex permission to we grant detailed permissions to the parsing schema. And create private synonyms in the parsing schema to avoid prefixing with the application schema in all apex queries/settings. However most wizards do not recognize these obects and fail to generate the desired pages. Eg. Data load page wizard.

Switching the parsing schema temporarily during execution of the wizards sometimes helps, but that's a dirty work around. 

Preferred Solution (Optional)
Let the queries to retrieve object info in these wizards behave as ‘all_tables instead of user_tables’ and maybe even include the available synonyms for these objects. Because that's exactly what the database will look for when executing the resulting code at runtime.

We reviewed this idea carefully, and while it was interesting, we concluded that it is unlikely to make its way into APEX in the foreseeable future.

Comments

Comments

  • phillip.grimshaw OP 2.7 years ago

    Absolutely long overdue.

  • vincent morneau Admin OP 2.3 years ago

    Hi @mark.daems ,

    It does sound like a bug that APEX should fix. Would you be able to specify which wizards do not work  as expected besides “Data load page wizard”? And which wizard does work? That would help us a lot identifying and prioritizing the bug.

  • mark.daems OP 2.3 years ago

    Hi Vincent,

    It's more a missing feature than a bug :) It's not that evident that a table synonym should be shown as a regular table (and even less when the referenced table is in another schema that's not supposed to be accessible for APEX).

    Basically ‘all’ wizards show this issue.
    Create smart filters, interactive grid, … based on a table → you can select a table/view owner (but I see only the parsing schema) and then you have the table LOV that only shows the objects of that schema. However I want to see the objects in another schema than the parsing schema (using a synonym and permissions granted to the parsing schema using a role).

    Example:
    table dataschema.t_user

    grant select, update on dataschema.t_user to apex_role

    grant apex_role to apexparseschema

    private synonym apexparseschema.t_user for dataschema.t_user

    Now create an app with parsing schema = apexparseschema and start a create page wizard. You can only select apexparseschema as owner and you don't see t_user in the table LOV.

    In acceptance and production environments the dataschema isn't even mapped to the workspace. In development we just mapped it so we can use the workaround switching temporarily to the dataschema as parsing schema.

    When you base the page on an sql query you can simply 'select * from t_user' and that works perfectly.

    Just adding dataschema to the owner LOV will not fix the issue because that would add the owner name hardcoded to the resulting queries, which is not desirable because that would force us to use the same dataschema name when deploying the app on other environments. Hence the private synonyms we use.