Understanding Authorization Scheme Types in ORACLE APEX

Introduction

Authorization schemes in Oracle APEX are vital for controlling access to application components, ensuring that only authorized users can view or interact with specific pages, regions, or buttons. These schemes come in various types, each designed to evaluate user permissions differently, offering flexibility to meet diverse security requirements. Understanding the different authorization scheme types in Oracle APEX allows developers to implement precise access controls tailored to their application’s needs. This blog post will explore the types of authorization schemes, provide detailed steps for their implementation, share best practices, and link to the official Oracle APEX documentation for further guidance.

Understanding Authorization Scheme Types in Oracle APEX

1. Overview of Authorization Scheme Types
   Authorization schemes in Oracle APEX are defined in Shared Components and determine whether a user has permission to access specific application components. Each scheme type evaluates permissions using different logic, such as SQL queries, PL/SQL functions, or item comparisons. The available types include Exists SQL Query, PL/SQL Function Returning Boolean, Value of Item in Expression 1 Equals Expression 2, and others, each suited for specific use cases.

2. Accessing Authorization Schemes
   To work with authorization schemes:

   • Navigate to Shared Components > Authorization Schemes in your Oracle APEX application.
   • Click Create to define a new scheme, specifying its type, name, and evaluation frequency (e.g., "Once per Session" or "Once per Page View").
   • Assign the scheme to components like pages, regions, or buttons under their Security tab.

3. Types of Authorization Schemes
   Oracle APEX offers several authorization scheme types, each with unique functionality:

   • Exists SQL Query: Evaluates access by checking if a SQL query returns at least one row. Suitable for simple role-based checks.

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

     Use this for straightforward database-driven permissions, such as verifying a user’s role.
   • PL/SQL Function Returning Boolean: Uses a PL/SQL function to return TRUE (authorized) or FALSE (not authorized). Ideal for complex logic.

     FUNCTION is_manager (p_username IN VARCHAR2) RETURN BOOLEAN IS
       l_count NUMBER;
     BEGIN
       SELECT COUNT(*) INTO l_count
       FROM employees
       WHERE username = p_username
       AND job_title = 'MANAGER';
       RETURN l_count > 0;
     END;
     

     This type is flexible for custom conditions, such as combining multiple criteria.
   • Value of Item in Expression 1 Equals Expression 2: Compares the value of an application item to a constant or another item. Example:

     Expression 1: P1_USER_ROLE
     Expression 2: ADMIN
     

     Use this for simple checks where permissions are stored in application items.
   • Not Exists SQL Query: The opposite of Exists SQL Query; grants access if the query returns no rows. Example:

     SELECT 1
     FROM restricted_users
     WHERE username = :APP_USER;
     

     Useful for denying access to specific users or groups.
   • PL/SQL Expression: Evaluates a PL/SQL expression that returns TRUE or FALSE. Example:

     :APP_USER IN ('USER1', 'USER2') AND :P1_DEPT_ID = '10'
     

     Best for inline conditions combining multiple variables.

4. Applying Authorization Schemes
   After defining a scheme:

   • Assign it to components (e.g., pages, regions, buttons) via the Security tab in their properties.
   • For example, apply an "Admin_Access" scheme to a page to restrict it to administrators only.
   • Combine schemes for complex requirements using the Combine with Other Schemes option (e.g., requiring both "Admin Role" and "Specific Department").

5. Using Application Access Control
   The Application Access Control feature simplifies role-based authorization:

   • Navigate to Shared Components > Application Access Control.
   • Define roles (e.g., Administrator, Editor) and map them to users in a table like:

     CREATE TABLE apex_access_control (
       username VARCHAR2(100),
       access_level VARCHAR2(50)
     );
     

   • Create an Exists SQL Query scheme to check roles:

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

   • Apply this scheme to relevant components.

6. Testing and Debugging

   • Test each scheme type by logging in as users with different roles to verify access restrictions.
   • Use APEX’s debug mode or the APEX_ACTIVITY_LOG view to diagnose issues with scheme evaluations.
   • Ensure error messages (set in the scheme’s configuration) are clear, e.g., "Access Denied: Insufficient Privileges."

7. Choosing the Right Scheme Type

   • Use Exists SQL Query for simple, database-driven checks.
   • Use PL/SQL Function Returning Boolean for complex or multi-step logic.
   • Use Value of Item in Expression 1 Equals Expression 2 for item-based comparisons.
   • Use Not Exists SQL Query for exclusion-based access control.
   • Use PL/SQL Expression for quick, inline conditions.

Authorization schemes in Oracle APEX define rules that control access to application components. When creating an authorization scheme, you must select an authorization scheme type, which determines how the system evaluates access permissions. These types allow developers to control user interactions based on conditions such as database queries, PL/SQL logic, item values, and user group memberships.

Oracle APEX also allows developers to create custom authorization type plug-ins to extend these predefined options.

Types of Authorization Schemes

1. Exists SQL Query

This type grants access if a specified SQL query returns at least one row. If the query returns no rows, the authorization fails, restricting access.

2. NOT Exists SQL Query

This type grants access if a specified SQL query returns no rows. If the query returns one or more rows, the authorization fails, restricting access.

3. PL/SQL Function Returning Boolean

This type executes a PL/SQL function that must return TRUE or FALSE. If the function returns TRUE, the authorization succeeds, granting access.

4. Item in Expression 1 is NULL

This type checks whether a specified page item is null. If the item has no value, the authorization succeeds, granting access.

5. Item in Expression 1 is NOT NULL

This type checks whether a specified page item is not null. If the item contains a value, the authorization succeeds, granting access.

6. Value of Item in Expression 1 Equals Expression 2

This type compares the value of a specified page item to a given value. If they are equal, the authorization succeeds, granting access.

7. Value of Item in Expression 1 Does NOT Equal Expression 2

This type grants access if a specified page item’s value does not match a given value. If the values are different, the authorization succeeds.

8. Value of Preference in Expression 1 Equals Expression 2

This type checks whether a user preference matches a specified value. If they are equal, the authorization succeeds.

9. Value of Preference in Expression 1 Does NOT Equal Expression 2

This type checks whether a user preference does not match a specified value. If they are different, the authorization succeeds.

10. Is In Group

This type checks whether the user belongs to a specified group. If the user is in the group, the authorization succeeds, granting access.

• If the application uses APEX Accounts Authentication, the system also checks workspace groups assigned to the user.

• If the application uses Database Authentication, the system also considers database roles granted to the user.

11. Is Not In Group

This type checks whether the user is not part of a specified group. If the user is not in the group, the authorization succeeds, granting access.

Applying Authorization Schemes in Oracle APEX

Once an authorization scheme is created, it can be assigned to:

• The entire application to control overall access.

• A specific page to restrict access to certain users.

• UI components such as buttons, regions, or reports.

To assign an authorization scheme to a component:

1. Open the Page Designer in Oracle APEX.

2. Select the component you want to secure.

3. Find the Authorization Scheme setting.

4. Choose the appropriate scheme from the dropdown list.

By carefully selecting and applying authorization schemes, developers can enforce fine-grained access control, ensuring that only authorized users can view or interact with specific parts of the application.

Best Practices for Authorization Scheme Types in Oracle APEX

• Match Scheme Type to Use Case: Choose the scheme type that best fits your logic (e.g., SQL for simple queries, PL/SQL for complex conditions).
• Use Descriptive Names: Name schemes clearly (e.g., "Admin_Only_Query" or "Editor_Function") to reflect their purpose.
• Optimize Evaluation Frequency: Set schemes to "Once per Session" for static permissions to improve performance, or "Once per Page View" for dynamic checks.
• Centralize Role Management: Use Application Access Control for consistent role assignments across schemes.
• Secure Data Access: Combine authorization schemes with database-level security (e.g., Virtual Private Database) for comprehensive protection.
• Test Thoroughly: Validate schemes in a development environment with various user scenarios to ensure correct behavior.
• Document Logic: Document each scheme’s purpose, type, and associated components for easier maintenance.
• Monitor Access: Enable Application Activity Logging in Shared Components > Security Attributes to track access attempts and review logs regularly.

Oracle APEX Documentation
For comprehensive details on authorization scheme types and their configuration, refer to the official Oracle APEX documentation:
Oracle APEX Authorization Schemes Documentation

Conclusion
Understanding the different authorization scheme types in Oracle APEX empowers developers to implement precise and flexible access controls tailored to their application’s security needs. By selecting the appropriate scheme type—whether SQL-based, PL/SQL-based, or item-based—and following best practices, you can ensure that users only access authorized components. Regularly consult the Oracle APEX documentation to explore advanced features and maintain a robust security framework for your application.

Understanding Authorization Scheme Types in Oracle APEX

In Oracle APEX, Authorization Schemes define who can access what within your application. They allow developers to control access to pages, regions, buttons, processes, and other UI components based on logic that evaluates user roles or permissions. Understanding the types of Authorization Schemes is essential for implementing precise, secure user access.

1. No Authorization Required

This is the default behavior when no restriction is applied. Any authenticated or public user can access the component. It's typically used for public content or pages like login or help.

• Use case: Public landing pages or general announcements.

• Warning: Avoid using this setting on sensitive areas of your app.

2. Must Not Be Public User

This scheme checks that the current user is authenticated. In Oracle APEX, the APEX_PUBLIC_USER account is used for unauthenticated users.

• Use case: Restrict access to logged-in users only.

• How it works: Evaluates :APP_USER != 'APEX_PUBLIC_USER'

• Example: Used on dashboards or personal profiles that require login.

3. Is In Role / Is In Group

These schemes check whether the user belongs to a specific access control role or group. This is commonly used when you’ve implemented Access Control using APEX's built-in roles and user table.

• Use case: Display admin-only pages or features.

• How it works: Requires a user-role mapping table.

• Example:

  select 1 from user_roles 
   where user_name = :APP_USER 
     and role_name = 'ADMIN'

4. PL/SQL Function Returning Boolean

This is the most flexible and powerful scheme. It lets you write custom PL/SQL logic that returns TRUE or FALSE.

• Use case: Complex business rules like time-based access, department-based rules, or multi-condition checks.

• How it works: You write a function like:

  return :APP_USER in ('HR_ADMIN', 'SECURITY_OFFICER');
  

• Advanced example:

  return exists (
    select 1 from hr_access
     where user_id = :APP_USER
       and access_level = 'FULL'
  );

5. SQL Query Returning at Least One Row

This scheme checks if a SQL query returns any result. If it does, the authorization passes.

• Use case: When you prefer to write SQL logic instead of PL/SQL.

• How it works: Runs a query and evaluates success based on row count.

• Example:

  select 1 
    from app_permissions 
   where username = :APP_USER 
     and module = 'REPORTING'

Choosing the Right Type

Scenario	Recommended Scheme Type
Restricting to authenticated users	Must Not Be Public User
Role-based access (simple)	SQL Query or Is in Role
Complex logic with parameters	PL/SQL Function Returning Boolean
Lightweight check on a table	SQL Query
Public access (no restriction)	No Authorization Required

Where to Use Authorization Schemes

You can apply any of these schemes at multiple levels in your APEX app:

• Page level: Prevent access entirely.

• Region level: Hide/show content based on permissions.

• Button or Item level: Limit interaction to specific users.

• Process level: Control back-end actions.

Authorization schemes in Oracle APEX define rules that control access to application components. When creating an authorization scheme, you must select an authorization scheme type, which determines how the system evaluates access permissions. These types allow developers to control user interactions based on conditions such as database queries, PL/SQL logic, item values, and user group memberships.

Oracle APEX also allows developers to create custom authorization type plug-ins to extend these predefined options.

Types of Authorization Schemes

1. Exists SQL Query

This type grants access if a specified SQL query returns at least one row. If the query returns no rows, the authorization fails, restricting access.

2. NOT Exists SQL Query

This type grants access if a specified SQL query returns no rows. If the query returns one or more rows, the authorization fails, restricting access.

3. PL/SQL Function Returning Boolean

This type executes a PL/SQL function that must return TRUE or FALSE. If the function returns TRUE, the authorization succeeds, granting access.

4. Item in Expression 1 is NULL

This type checks whether a specified page item is null. If the item has no value, the authorization succeeds, granting access.

5. Item in Expression 1 is NOT NULL

This type checks whether a specified page item is not null. If the item contains a value, the authorization succeeds, granting access.

6. Value of Item in Expression 1 Equals Expression 2

This type compares the value of a specified page item to a given value. If they are equal, the authorization succeeds, granting access.

7. Value of Item in Expression 1 Does NOT Equal Expression 2

This type grants access if a specified page item’s value does not match a given value. If the values are different, the authorization succeeds.

8. Value of Preference in Expression 1 Equals Expression 2

This type checks whether a user preference matches a specified value. If they are equal, the authorization succeeds.

9. Value of Preference in Expression 1 Does NOT Equal Expression 2

This type checks whether a user preference does not match a specified value. If they are different, the authorization succeeds.

10. Is In Group

This type checks whether the user belongs to a specified group. If the user is in the group, the authorization succeeds, granting access.

• If the application uses APEX Accounts Authentication, the system also checks workspace groups assigned to the user.

• If the application uses Database Authentication, the system also considers database roles granted to the user.

11. Is Not In Group

This type checks whether the user is not part of a specified group. If the user is not in the group, the authorization succeeds, granting access.

Applying Authorization Schemes in Oracle APEX

Once an authorization scheme is created, it can be assigned to:

• The entire application to control overall access.

• A specific page to restrict access to certain users.

• UI components such as buttons, regions, or reports.

To assign an authorization scheme to a component:

1. Open the Page Designer in Oracle APEX.

2. Select the component you want to secure.

3. Find the Authorization Scheme setting.

4. Choose the appropriate scheme from the dropdown list.

By carefully selecting and applying authorization schemes, developers can enforce fine-grained access control, ensuring that only authorized users can view or interact with specific parts of the application.

Authorization Schemes in Oracle APEX provide a secure, declarative way to control access throughout your application. Understanding the differences between types—especially when to use PL/SQL, SQL, or role-based logic—empowers developers to build applications that are both secure and adaptable to business rules. By combining these schemes with authentication and APEX access control features, you create a robust security model tailored to your app’s needs.