Executing SQL and PL/SQL as a logged-in user in Oracle APEX

In Oracle APEX, you may want to execute SQL queries and procedures as the logged-in user instead of the default parsing user (once APEX_PUBLIC_USER, now ORDS_PLSQL_GATEWAY). This approach allows you to leverage the database permissions associated with the logged-in user, which is particularly useful when working with database authentication schemas. This is often needed when using PL/SQL packages that include application-based privilege checks.

Setting Up the Parsing Schema

To execute PL/SQL as the logged-in user, you can use the APEX_UTIL.SET_PARSING_SCHEMA_FOR_REQUEST function. Here's how you can set it up:

1. Navigate to your application in Oracle APEX.
2. Go to Shared Components.
3. Click on Security.
4. Select Security Attributes.
5. Enter the following code in the Initialization Code section:

BEGIN
    IF :APP_USER <> 'nobody' THEN
        IF INSTR(apex_instance_admin.get_schemas('WORKSPACE_NAME'), :APP_USER) < 1 THEN
            apex_instance_admin.add_schema(
                    'WORKSPACE_NAME',
                    :APP_USER,
                    FALSE);
        END IF;
        apex_util.set_parsing_schema_for_request(p_schema => :APP_USER);
    END IF;
END;

Change WORKSPACE_NAME to name of your workspace.

Code Explanation

• :APP_USER: This bind variable represents the current authenticated user in the application.
• IF :APP_USER <> 'nobody' THEN: Ensures the code only runs if there is a logged-in user.
• INSTR(apex_instance_admin.get_schemas('WORKSPACE_NAME'), :APP_USER) < 1: Checks if the user schema is not already added to the workspace. If not, it adds the user schema using apex_instance_admin.add_schema. This is necessary because you must add every user who will use the application as a schema to the workspace. Here, we do this automatically when a user who is not already assigned logs in, but you can also do it manually. If we don't assign the schema to the workspace, the apex_util.set_parsing_schema_for_request procedure will prevent us from switching to that database user.
• apex_util.set_parsing_schema_for_request(p_schema => :APP_USER) sets the parsing schema to the current logged-in user.

And that's it!

A few considerations

1. USER Function: If your code references the USER function (triggers for example), it will still return ORDS_PLSQL_GATEWAY or the default parsing user. To get the authenticated user's name, use: SYS_CONTEXT('APEX$SESSION', 'APP_USER')
2. Default Parsing Schema: The application's default parsing schema must still have all the necessary privileges required for the APEX application to function correctly.
3. Development: It's important to keep in mind, that during development in the APEX Builder (Page Designer), you typically use the default parsing schema, for instance for SQL code validation. However, when the application is run, it uses the database user specified during application login.
4. Performance Implications: This code runs on every page load, which might affect performance. It's crucial to test and monitor the impact on your application's speed.
5. Security: Make sure this makes sense in your authorization scenario. Adding a schema to the workspace or setting a different parsing schema for requests may have security implications depending on your setup.

By implementing this setup, you can enhance your Oracle APEX applications by utilizing the specific database privileges of the logged-in user, enabling more secure and tailored database interactions.