Introduction

Managing user access through role assignments is a critical part of building secure, multi-user applications in Oracle APEX. Assigning roles allows you to control what features or data users can access based on their responsibilities. Whether you're building an internal tool or a public-facing app, defining and assigning roles is a fundamental step in implementing authorization logic in APEX.

How to Add User Role Assignments in Oracle APEX

1. Define Roles in Your Application
   Roles in Oracle APEX are often implemented using Authorization Schemes. You can create custom schemes to represent roles like Admin, Editor, or Viewer.

   • Navigate to Shared Components > Security > Authorization Schemes

   • Click Create

   • Choose Based on PL/SQL Function Returning Boolean

   • Name the scheme according to the role (e.g., IS_ADMIN)

   • In the PL/SQL code, check the user’s role:

     return apex_util.get_session_state('APP_USER') in (select username from user_roles where role = 'ADMIN');
     

   • Apply this scheme to pages, regions, or buttons to restrict access

2. Store and Manage Role Assignments
   Create a database table to manage user-role mappings:

   CREATE TABLE user_roles (
       username VARCHAR2(100),
       role     VARCHAR2(50)
   );
   

   You can populate this table manually or through an APEX form. This allows flexibility to add, remove, or change roles without modifying the application.

3. Use the Role in Authorization Logic
   Once roles are defined, you can:

   • Apply them to specific pages or components via the Authorization Scheme dropdown

   • Use them inside PL/SQL code to conditionally execute logic based on user roles

   • Combine multiple roles using logic (e.g., IN ('ADMIN', 'EDITOR'))

4. Create a Role Management Interface
   For admin users, build a simple APEX form and interactive report that allows managing role assignments stored in the user_roles table. Use Page Access Control to ensure only authorized users can access this page.

Best Practices

• Use Session State Wisely: Avoid unnecessary role lookups; cache the role in session variables if needed.

• Normalize Role Names: Use consistent role names across your application and database.

• Combine with Authentication: Ensure users are authenticated before role checks occur.

• Avoid Hardcoding: Keep role logic dynamic using the database to allow runtime updates.

• Use One Authorization Scheme per Role: This keeps your logic modular and reusable across multiple pages or regions.

You can assign specific roles to users in your application through the Application Access Control page. This allows you to control access based on predefined roles.

Steps to Add a User Role Assignment

1. Navigate to the Shared Components page:

• Go to the Workspace home page.

• Click the App Builder icon.

• Select the application where you want to assign roles.

• On the Application home page, click Shared Components.

2. Access the Application Access Control page:

• Under Security, click Application Access Control.

3. Add a new user role assignment:

• Under User Role Assignments, click Add User Role Assignment.

• The User Assignment dialog will appear.

4. Define the user assignment:

• User Name – Enter the name of the user. The name must contain only alphanumeric characters or underscores (_).

• Application Role – Select the appropriate role for the user.

5. Save the assignment:

• Click Create Assignment to complete the process.

• The newly assigned user role will now be displayed under User Role Assignments.

These role assignments help enforce access control within your application, ensuring users have the appropriate privileges.

Note:

Application users are not included when exporting an application. After deploying your application, you must manually configure user-to-role assignments. However, roles are exported along with the application and will be imported automatically during the application import process.

Oracle APEX Documentation
Official guide to Authorization Schemes:
https://docs.oracle.com/en/database/oracle/apex/23.2/htmdb/creating-authorization-schemes.html

Conclusion
Implementing user role assignments in Oracle APEX provides a flexible and scalable way to control access within your application. By leveraging authorization schemes, PL/SQL conditions, and custom role tables, you can enforce user permissions efficiently. Following best practices helps maintain security, scalability, and ease of management as your app grows.