Simplifying Authorization in Dynamic List on List Region

Handling APEX_AUTHORIZATION.IS_AUTHORIZED in SQL Queries Made Easy

Today, I worked on creating a list region with navigation menus and sub-lists. This approach helps in building a dynamic navigation menu, which can be reused in a list region. I achieved this using the following dynamic list query:

SELECT  
    NULL,  
    ENTRY_TEXT AS LABEL,  
    ENTRY_TARGET AS TARGET,  
    NULL AS IS_CURRENT,  
    ENTRY_IMAGE AS IMAGE  
FROM  
    APEX_APPLICATION_LIST_ENTRIES  
WHERE  
    APPLICATION_ID = :APP_ID  
    AND UPPER(LIST_NAME) = UPPER('Desktop Navigation Menu')  
    AND UPPER(PARENT_ENTRY_TEXT) = UPPER('#LIST_NAME#')  
ORDER BY  
    DISPLAY_SEQUENCE;        

While this works well for retrieving the menu items, there’s a catch. Each list in Oracle APEX can have an authorization scheme, which APEX handles for navigation menus. However, when using a dynamic menu to create a region, we need to manually validate authorization.

Oracle APEX provides the APEX_AUTHORIZATION.IS_AUTHORIZED function, which returns a Boolean value. But SQL doesn’t support Boolean values directly, causing issues when we try to use this function in queries.

The Solution: Wrapper Function

To solve this, I created a wrapper function, IS_AUTHORIZED_WRAPPER, that converts the Boolean value returned by APEX_AUTHORIZATION.IS_AUTHORIZED into a number (1 for authorized, 0 for unauthorized). Here's the function:

CREATE OR REPLACE FUNCTION IS_AUTHORIZED_WRAPPER (p_scheme_name IN VARCHAR2)  
RETURN NUMBER  
AS  
BEGIN  
    RETURN CASE  
             WHEN APEX_AUTHORIZATION.IS_AUTHORIZED(p_scheme_name) THEN 1  
             ELSE 0  
           END;  
END;  
/        

Using the Wrapper Function in SQL

With the wrapper function in place, I updated my dynamic list query to check the authorization status:

SELECT  
    NULL AS NULLCOLUMN,  
    ENTRY_TEXT AS LABEL,  
    ENTRY_TARGET AS TARGET,  
    NULL AS IS_CURRENT,  
    ENTRY_IMAGE AS IMAGE  
FROM  
    APEX_APPLICATION_LIST_ENTRIES  
WHERE  
    APPLICATION_ID = :APP_ID  
    AND UPPER(LIST_NAME) = UPPER('Desktop Navigation Menu')  
    AND UPPER(PARENT_ENTRY_TEXT) = UPPER('#LIST_NAME#')  
    AND IS_AUTHORIZED_WRAPPER(AUTHORIZATION_SCHEME) = 1  
ORDER BY  
    DISPLAY_SEQUENCE;        

Benefits of This Approach

1. Reusability: The wrapper function can be used across multiple queries for authorization checks.
2. SQL Compatibility: It allows us to use APEX_AUTHORIZATION.IS_AUTHORIZED within SQL queries.
3. Dynamic Menu and Region: This setup makes it easy to create a dynamic navigation menu in a list region.

Conclusion

By using the IS_AUTHORIZED_WRAPPER function, we can efficiently check authorization in dynamic list queries while maintaining SQL compatibility. This approach makes it easier to build secure and reusable navigation menus in Oracle APEX.