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

17 Voters

Please provide a sql-suitable APEX_AUTHORIZATION.IS_AUTHORIZED function.

ronald.hollak Public · Aug 24 2021

Idea Summary
Provide a SQL-suitable variant for PL/SQL API function APEX_AUTHORIZATION.IS_AUTHORIZED.
When trying to wrap this function in SQL, we end up with an ORA-14551: cannot perform a DML operation inside a query. So we need to to write direct access to the ACL views.

Use Case
In any use case where we need to access authorized-constrained Apex-components via SQL.
For example dynamic App-menu's, or any other content we base on queries for reports where row/column-results are restricted by Apex authorization rules.

Preferred Solution (Optional)
APEX_AUTHORIZATION.IS_AUTHORIZED_YN returning Y/N where no DML is performed.

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Known Duplicates

Known duplicates

FR-3382
FR-3382 Create a version of the apex_authorization.is_authorized function supported by SQl

Comments

Comments

  • mike_kutz OP 2.7 years ago

    Your business requirement really sounds like it should be implemented via Row Level Security. (VPD or RAS)

    How is your Business Requirement different from RLS?

  • ino.laurensse OP 2.7 years ago

    should be implemented via Row Level Security. (VPD or RAS)

    If I'm not mistaken that is an Enterprise Edition feature, not Standard Edition.

  • mike_kutz OP 2.7 years ago

    @ino.laurensse  EE and (newer) XE

    Quick look:

    It look like OP should be using Roles instead of Authorization for pseudo RLS.  (use Roles for limiting DB activity & use Authorization for limiting UI activity ).  Post in the regular Forum for HOWTO.

    The information of who is logged in with which dynamic roles is in APEX_SESSION_GROUPS (see APEX_DICTIONARY for column description).

  • ronald.hollak OP 2.7 years ago

    Hi Mike,

    At first thx for any suggesting on this topic. But I think this is not what I meant or reported; This would not be a case for RLS (VPD); This concerns the built-in authorization logic within Apex (see Security, chapter 20.3 in the Apex Developer Apps Guide).  Setting up can be done declaratively, but there are also many use-cases for various Apex components which are implemented Dynamically (via SQL, views etc,). 
    The Apex API on the ACL engine provides an IS_AUTHORIZED function, but that seems internally doing a DML (for whatever reason) . The workaround is to access and query the ACL views directly.
    The documentation states: “_The_ _APEX_AUTHORIZATION_ package contains public utility functions used for controlling and querying access rights to the application.”
    The querying access is not possible via SQL,

  • carsten.czarski APEX Team OP 2.5 years ago

    An Authorization scheme does write data for result caching. On the auth scheme edit page, you can see the “Validate authorization scheme” attribute. If one of the caching methods is enabled, APEX has to write the session state to an internal table, thus the DML.

    So, you can avoid that situation by simply turning the caching off by choosing the Always (no caching) option. Any out-of-the-box solution provided by APEX would do exactly the same.