HOW DO I Manage User Role Assignments

Introduction
In Oracle APEX, managing what different users can see and do is a key part of building secure applications. One of the most effective ways to control access is by assigning user roles. Roles help define which users can perform administrative tasks, view reports, or make changes to data. Adding user role assignments allows you to organize permissions logically and securely within your APEX applications.

How to Add User Role Assignments in Oracle APEX

1. Create a Table for User Roles
   First, create a custom table in your database to store role assignments:

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

   This table links each user (by username) to a specific role such as ADMIN, MANAGER, or USER.

2. Insert Role Data
   Add users and their assigned roles:

   INSERT INTO user_roles (username, role) VALUES ('JOHNDOE', 'ADMIN');
   INSERT INTO user_roles (username, role) VALUES ('JANEDOE', 'USER');
   

3. Create Authorization Schemes for Roles
   Go to Shared Components > Authorization Schemes. Create a new scheme using PL/SQL that checks if the current user is assigned a specific role.
   Example for an ADMIN role:

   RETURN EXISTS (
     SELECT 1 FROM user_roles 
     WHERE UPPER(username) = UPPER(:APP_USER) 
     AND role = 'ADMIN'
   );
   

4. Apply Authorization Schemes to Pages or Components
   Once your role-based authorization schemes are created, apply them to:

   • Pages (to restrict full page access)

   • Regions or buttons (to show/hide specific UI elements)

   • Processes (to restrict logic execution)

5. Optional: Create a Role Management Page
   You can create an admin-only page that includes a form or interactive report to manage role assignments in the user_roles table. This allows easier maintenance and updates without direct database access.

Best Practices

• Use meaningful and consistent role names such as ADMIN, USER, EDITOR

• Normalize usernames with UPPER() or LOWER() to avoid case mismatches

• Create one Authorization Scheme per role for easier reuse and management

• Don’t hardcode logic in multiple places; centralize it using Authorization Schemes

• Secure your role management page with its own authorization scheme

You can modify or remove user role assignments to control access within your application.

Editing a User Role Assignment

1. Navigate to the Shared Components page:
   a. On the Workspace home page, click App Builder.
   b. Select the application.
   c. On the Application Home page, click Shared Components.
   The Shared Components page appears.

2. Under Security, click Application Access Control.
   The Application Access Control page appears.

3. To modify an existing user role assignment:
   a. Locate the User Role Assignments section and select a user name.
   b. In the User Assignment dialog, choose a new Application Role.
   c. Click Save to apply the changes.

Deleting a User Role Assignment

1. Follow steps 1 and 2 from the Editing a User Role Assignment section.

2. Under User Role Assignments, select the user you want to remove.

3. In the User Assignment dialog, click Delete to remove the assignment.

Important Note:

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

Authentication defines how users access your application and whether their identities are tracked individually or treated as public users with equal privileges.

If your application does not require individual user tracking, all users are considered public users, meaning they share the same access rights. However, if the application needs to differentiate between users, an authentication method must be specified.

How Authentication Works

Authentication verifies the identity of each user attempting to access the application. In most cases, this process requires users to provide credentials, such as a username and password. These credentials are then validated:

• If authentication succeeds, the user is granted access.

• If authentication fails, access is denied.

Tracking Authenticated Users

Once a user is authenticated, Oracle APEX keeps track of their session using the built-in substitution string APP_USER. This value is dynamically updated as the user navigates through the application, ensuring that session data remains tied to the correct user.

APEX uses APP_USER as a key component for managing session state, enabling developers to implement role-based security, personalization, and auditing based on the authenticated user.

Oracle APEX Documentation

Learn more from the official Oracle APEX documentation on Authorization Schemes:
https://docs.oracle.com/en/database/oracle/apex/23.2/htmdb/creating-authorization-schemes.html

Conclusion

Adding user role assignments in Oracle APEX gives you control over how users interact with your application. With a simple role table and authorization schemes, you can build flexible, secure, and maintainable access control. Whether your app has a few users or hundreds, role-based security is a best practice worth implementing early.