How Do I Edit the Attributes of an Existing Authorization Scheme in Oracle APEX

 Introduction

Editing the attributes of an existing authorization scheme in Oracle APEX is a critical task for maintaining and updating access control within your application. Authorization schemes define which authenticated users can access specific pages, regions, or components, and modifying their attributes allows you to adapt to changing security requirements or refine access logic. This blog post will guide you through the process of editing authorization scheme attributes in Oracle APEX, provide detailed steps for making changes, share best practices, and include a link to the official Oracle APEX documentation for further guidance.

Steps to Edit the Attributes of an Existing Authorization Scheme in Oracle APEX

1. Locating the Authorization Scheme
   Authorization schemes are managed within the Shared Components section of an Oracle APEX application.

   • Navigate to Shared Components > Authorization Schemes in your Oracle APEX application.
   • Identify the existing scheme you want to edit from the list (e.g., "Admin_Access" or "Editor_Role").
   • Click the scheme name to open its attributes for editing.

2. Editing Core Attributes
   Once the scheme is opened, you can modify its key attributes:

   • Name: Update the scheme name to reflect its purpose clearly (e.g., change "Admin_Access" to "Admin_Only_Access" for clarity). Avoid changing names if the scheme is widely used to prevent confusion.
   • Scheme Type: Modify the type if needed. Common types include:
     • Exists SQL Query: Edit the SQL query to adjust the logic. Example:

       SELECT 1
       FROM user_roles
       WHERE username = :APP_USER
       AND role_name = 'ADMIN';
       

       Update the query to include additional conditions, such as department checks:

       SELECT 1
       FROM user_roles r
       JOIN departments d ON r.dept_id = d.dept_id
       WHERE r.username = :APP_USER
       AND r.role_name = 'ADMIN'
       AND d.dept_name = 'HR';
       

     • PL/SQL Function Returning Boolean: Modify the PL/SQL function for complex logic. Example:

       FUNCTION is_editor (p_username IN VARCHAR2) RETURN BOOLEAN IS
         l_count NUMBER;
       BEGIN
         SELECT COUNT(*) INTO l_count
         FROM user_roles
         WHERE username = p_username
         AND role_name = 'EDITOR';
         RETURN l_count > 0;
       END;
       

       Update to add conditions, such as checking active status:

       FUNCTION is_editor (p_username IN VARCHAR2) RETURN BOOLEAN IS
         l_count NUMBER;
       BEGIN
         SELECT COUNT(*) INTO l_count
         FROM user_roles
         WHERE username = p_username
         AND role_name = 'EDITOR'
         AND status = 'ACTIVE';
         RETURN l_count > 0;
       END;
       

     • Value of Item in Expression 1 Equals Expression 2: Adjust the item or comparison value. Example: Change P1_USER_ROLE = 'ADMIN' to P1_USER_ROLE = 'MANAGER'.
   • Evaluation Point: Change the frequency of evaluation ("Once per Session" for static roles or "Once per Page View" for dynamic checks). For example, switch to "Once per Page View" if roles change frequently.
   • Error Message: Update the message displayed when access is denied (e.g., from "Access Denied" to "You do not have sufficient privileges to access this feature.").

3. Updating Scheme Application
   After editing attributes, verify where the scheme is applied:

   • Check the Used In column in the Authorization Schemes list to see which pages, regions, or components use the scheme.
   • If the updated logic affects additional components, navigate to Page Designer and apply the scheme to new pages, regions, or buttons via their Security tab.
   • Remove the scheme from components if it’s no longer relevant by setting the Authorization Scheme to "No Authorization Required."

4. Leveraging Application Access Control
   If the scheme integrates with Application Access Control:

   • Go to Shared Components > Application Access Control to update role definitions or user-role mappings in the underlying table (e.g., apex_access_control).
   • Adjust the scheme’s logic to reflect changes. Example:

     SELECT 1
     FROM apex_access_control
     WHERE username = :APP_USER
     AND access_level = 'EDITOR';
     

     Update to include new roles:

     SELECT 1
     FROM apex_access_control
     WHERE username = :APP_USER
     AND access_level IN ('EDITOR', 'SENIOR_EDITOR');
     

5. Testing Changes

   • Test the updated scheme by logging in as users with different roles to ensure the new logic works as intended.
   • Use APEX Debug Mode or query the APEX_ACTIVITY_LOG view to troubleshoot issues with scheme evaluation.
   • Verify that error messages are clear and display correctly when access is denied.

6. Managing Combined Schemes
   If the scheme is part of a combined authorization setup:

   • Review the Combine with Other Schemes settings in the components where the scheme is applied.
   • Update the combined logic if needed. Example:

     RETURN is_editor(:APP_USER) AND :P1_DEPT_ID = '10';
     

     Modify to include new conditions:

     RETURN is_editor(:APP_USER) AND :P1_DEPT_ID IN ('10', '20');
     

Editing the attributes of an existing authorization scheme allows you to update the conditions and behaviors that control user access to different parts of your application. This can be essential for fine-tuning security settings based on evolving requirements.

Steps to Edit the Attributes of an Existing Authorization Scheme

1. From the Workspace home page, click on the App Builder icon.

2. Select the application where you want to modify the authorization scheme.

3. On the Application home page, click on Shared Components.
   This opens the Shared Components page.

4. Under the Security section, select Authorization Schemes.
   The Authorization Schemes page appears. By default, the schemes are displayed as icons. You can use the search bar at the top of the page to filter and customize how the schemes are displayed.

5. Click on the authorization scheme you wish to edit.

6. Edit the necessary attributes of the scheme, such as: 

• Authorization Scheme Type (e.g., SQL query, PL/SQL function).

• Authorization Logic (modify the SQL query or PL/SQL function).

• Error Message (customize the message shown when authorization fails).
  For more details, refer to field-level help within the page.

7. Once you've made the necessary changes, click Apply Changes to save your edits.

Changing the Evaluation Point for an Authorization Scheme

The Evaluation Point attribute controls when an authorization scheme is validated. You can adjust this setting to determine how often the authorization scheme is re-evaluated during a session.

Authorization schemes are generally evaluated when they are first used in a session. The Validate Authorization Scheme attribute allows you to set when and how often re-evaluations occur, based on your application's needs.

Steps to Change the Authorization Scheme Evaluation Point

1. On the Workspace home page, click the App Builder icon.

2. Select the application you want to modify.

3. On the Application home page, click Shared Components.
   This will bring you to the Shared Components page.

4. Under the Security section, click Authorization Schemes.
   The Authorization Schemes page will appear. You can search or filter the schemes using the search bar at the top of the page.

5. Select the authorization scheme you want to modify.

6. Scroll down to the Evaluation Point section and update the Validate Authorization Scheme setting.
   You have several options:

• Once per session: The scheme is evaluated only once per session, and the result is memorized for subsequent requests.

• Once per page view: The scheme is evaluated for each page view, but the memorized result is used if the authorization scheme is referenced more than once on the same page.

• Once per component: The scheme is evaluated once per component on the page, with the result stored in the session for future use on that component.

• Always (No Caching): The authorization scheme will always be evaluated for every request without caching the result.

If you choose Once per session, it is the most efficient option for general use. Consider using another setting if the authorization check depends on factors that change during the session, such as changes in session state or user roles.

7. After making your changes, click Apply Changes to save the updated settings.

By carefully adjusting the evaluation point, you can optimize the performance of your application while ensuring the correct authorization checks are made at the right time.

Best Practices for Editing Authorization Scheme Attributes in Oracle APEX

• Plan Changes Carefully: Before editing, document the current scheme configuration and the intended changes to avoid unintended access issues.
• Use Descriptive Names: Ensure scheme names clearly reflect their purpose (e.g., "HR_Admin_Access") to simplify maintenance.
• Test in Development: Make and test changes in a development environment before applying them to production to catch errors early.
• Minimize Logic Complexity: Keep SQL queries and PL/SQL functions simple and efficient to avoid performance issues.
• Optimize Evaluation Frequency: Use "Once per Session" for static permissions to reduce database queries, or "Once per Page View" only when dynamic evaluation is necessary.
• Leverage Application Access Control: Centralize role management for consistency, especially when editing schemes tied to roles.
• Document Updates: Maintain detailed records of changes, including the updated logic, affected components, and testing outcomes.
• Monitor and Audit: Enable Application Activity Logging in Shared Components > Security Attributes to track access attempts and review logs after changes to detect anomalies.

Oracle APEX Documentation
For detailed guidance on editing and managing authorization schemes in Oracle APEX, refer to the official documentation:
Oracle APEX Authorization Schemes Documentation

Conclusion
Editing the attributes of an existing authorization scheme in Oracle APEX allows developers to refine access controls to meet evolving security needs. By carefully updating scheme logic, testing changes, and following best practices, you can ensure that your application remains secure and accessible only to authorized users. Regularly consult the Oracle APEX documentation to stay informed about advanced features and maintain a robust security framework for your application.