How Do I Copy or Subscribing to an Authorization Scheme in Oracle APEX

Introduction

Copying or subscribing to an authorization scheme in Oracle APEX enables developers to reuse access control logic across applications or within the same application, promoting consistency and simplifying maintenance. Copying creates an independent duplicate of a scheme, while subscribing links to a master scheme in another application, allowing centralized updates. Both approaches help streamline security management in complex APEX environments. This blog post will detail how to copy or subscribe to an authorization scheme in Oracle APEX, provide step-by-step instructions, share best practices, and include a link to the official Oracle APEX documentation for further guidance.

Steps to Copy or Subscribe to an Authorization Scheme in Oracle APEX

1. Understanding Copying vs. Subscribing

   • Copying: Creates a standalone duplicate of an authorization scheme within the same or a different application. The copied scheme can be modified independently without affecting the original.
   • Subscribing: Links a target application to a master authorization scheme in another application. Changes to the master scheme automatically propagate to all subscribed applications, ensuring consistency.
     Both methods are managed in Shared Components > Authorization Schemes.

2. Copying an Authorization Scheme
   To copy an existing authorization scheme:

   • Navigate to Shared Components > Authorization Schemes in the source application.
   • Locate the scheme to copy (e.g., "Admin_Access" with logic like:

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

   • Click the scheme name, then select Copy from the available actions.
   • Specify the target:
     • Same Application: Provide a new name for the copied scheme (e.g., "Admin_Access_Copy").
     • Different Application: Select the target application ID from the workspace and provide a new name.
   • Save the copied scheme. The new scheme is now independent and can be edited without affecting the original.
   • Apply the copied scheme to components (e.g., pages, regions) via their Security tab in Page Designer.

3. Subscribing to an Authorization Scheme
   To subscribe to a master authorization scheme:

   • Open the target application and navigate to Shared Components > Authorization Schemes.
   • Click Create and select As a Subscription from Master Application.
   • Choose the master application by its ID or name from the list of applications in the workspace.
   • Select the master authorization scheme (e.g., "Admin_Access") from the dropdown.
   • Provide a local name for the scheme in the target application (optional; can match the master scheme’s name).
   • Save the subscription. The scheme now references the master scheme’s logic and cannot be edited directly in the target application.
   • Apply the subscribed scheme to components (e.g., pages, regions, buttons) via their Security tab in Page Designer.
   • Note: Updates to the master scheme (e.g., changing the SQL query to include role_name IN ('ADMIN', 'MANAGER')) automatically apply to all subscribed applications.

4. Managing Copied Schemes

   • After copying, edit the new scheme’s attributes (e.g., SQL query, PL/SQL function, or evaluation frequency) to suit the target application’s needs.
   • Example: Modify a copied scheme to add conditions:

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

   • Verify the copied scheme’s independence by ensuring changes do not affect the original.

5. Managing Subscribed Schemes

   • Changes to the master scheme in the source application propagate to all subscribed applications.
   • To stop subscribing, either:
     • Copy the master scheme to the target application (creating a local, editable copy).
     • Delete the subscription and create a new local scheme.
   • Check the Subscription column in the Authorization Schemes list to confirm subscription status.

6. Integrating with Application Access Control
   If the scheme uses Application Access Control:

   • Ensure the master application’s apex_access_control table is accessible to the target application (e.g., via shared database objects).
   • Example master scheme:

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

   • For subscriptions, verify that user-role mappings are consistent across applications. For copied schemes, replicate the table or logic in the target application if needed.

7. Testing Copied or Subscribed Schemes

   • Test the copied or subscribed scheme by logging in as users with different roles (e.g., ADMIN, EDITOR) to ensure access restrictions work as expected.
   • Use APEX Debug Mode or query the APEX_ACTIVITY_LOG view to troubleshoot issues with scheme evaluation.
   • For subscriptions, test after updating the master scheme to confirm changes propagate correctly.
   • Verify that error messages (e.g., "Access Denied") display appropriately when access is denied.

To copy or subscribe to an authorization scheme, follow these steps:

1. Access the Authorization Schemes Page:

• On the Workspace home page, click App Builder.

• Select the application where you want to copy or subscribe to an authorization scheme.

• On the Application home page, click Shared Components to open the Shared Components page.

• Under the Security section, click Authorization Schemes.

• The Authorization Schemes page will display, including details such as Subscribed From, Subscription Status, and Subscribers.

2. Initiate the Copy Process:

• In the Tasks list, click Copy from another app.

3. Select the Source Application:

• Under Copy From Application, choose the application that contains the authorization scheme you want to copy.

• Click Next to proceed.

4. Set the Name and Copy Options:

• In the To Name field, optionally modify the name of the copied authorization scheme.

• Choose a copy action: 

• Yes – Copy the authorization scheme without subscribing.

• No – Do not copy the authorization scheme.

• Copy and Subscribe – Copy the authorization scheme and subscribe to it. Subscribing ensures that any updates to the original (master) scheme automatically apply to the copied version.

After completing these steps, the copied or subscribed authorization scheme will be available for use in your application.

EXAMPLE:

Step 1 - To create a new scheme, click create.

Step 2- Follow the wizard

Best Practices for Copying or Subscribing to Authorization Schemes in Oracle APEX

• Choose Copying for Flexibility: Copy schemes when you need to customize logic for a specific application without affecting the original.
• Choose Subscribing for Consistency: Subscribe to schemes when maintaining uniform access control across multiple applications is critical.
• Select a Stable Master Application: For subscriptions, use a well-maintained master application to ensure reliable scheme updates.
• Use Descriptive Names: Name schemes clearly (e.g., "Global_Admin_Access" or "Local_Editor_Access") to indicate their purpose and scope.
• Test Thoroughly: Validate copied or subscribed schemes in a development environment to ensure they function correctly with local data.
• Document Configurations: Maintain records of copied and subscribed schemes, including master application IDs, scheme names, and associated components.
• Secure Shared Data: Ensure database objects (e.g., user_roles or apex_access_control tables) used by schemes are accessible to all relevant applications.
• Monitor and Audit: Enable Application Activity Logging in Shared Components > Security Attributes to track access attempts and detect issues after copying or subscribing.

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

Conclusion
Copying or subscribing to an authorization scheme in Oracle APEX provides powerful options for reusing and managing access control logic across applications. Copying allows for independent customization, while subscribing ensures consistency through centralized updates. By following best practices and thoroughly testing your setup, you can maintain a secure and efficient authorization framework. Regularly consult the Oracle APEX documentation to leverage advanced features and keep your application’s security robust.