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.