HOW DO I USE Roles and User Roles Assignments

 Introduction
Using Roles and User Role Assignments in Oracle APEX allows developers to control access to application components based on predefined user roles. By assigning roles such as ADMIN, MANAGER, or VIEWER to individual users, you can tailor the application experience, enforce security, and ensure that users only see or interact with content relevant to their responsibilities. This approach is a core part of building scalable, multi-user applications that support varying levels of access and functionality.

 Using roles and user role assignments in Oracle APEX allows you to control access to specific pages, regions, buttons, or actions based on a user’s assigned role. This is especially useful when building multi-user applications where different types of users—such as administrators, managers, and staff—need to see or interact with different parts of the application. Assigning roles and checking them through authorization schemes ensures only the right people see and use the right features.

To begin, you need a way to assign roles to users. This is typically done through a table in your application schema. For example:

CREATE TABLE app_users (
  username     VARCHAR2(100),
  user_role    VARCHAR2(50)
);

You populate this table with your application’s users and their roles. For example:

INSERT INTO app_users (username, user_role)
VALUES ('ADMIN01', 'ADMIN');

INSERT INTO app_users (username, user_role)
VALUES ('MGR01', 'MANAGER');

INSERT INTO app_users (username, user_role)
VALUES ('USR01', 'USER');

Next, create an Authorization Scheme in APEX. Go to Shared Components > Authorization Schemes, and click Create. Choose From Scratch, and use the PL/SQL Function Returning Boolean type. Give the scheme a name like “Admin Role” and enter code similar to this:

RETURN EXISTS (
  SELECT 1 
  FROM app_users 
  WHERE username = :APP_USER 
    AND user_role = 'ADMIN'
);

You can repeat this for each role type you want to manage—MANAGER, USER, etc.—by adjusting the user_role condition in the query.

To use the authorization scheme, go to Page Designer, and apply the scheme to the page, region, button, or process that should only be visible or accessible to users with that role. For example, to restrict a page to only admins:

• Open the page in Page Designer

• In the Security section, set Authorization Scheme to “Admin Role”

You can also use the same scheme for conditional display of items or buttons:

• Click on a region or button

• Under Security, set Authorization Scheme to “Admin Role”

When the user logs in, APEX sets the :APP_USER variable, which is used by the authorization scheme to check their role. If the scheme returns TRUE, the user can access the protected element. If it returns FALSE, access is denied or the component is hidden, depending on context.

Optionally, you can load the user’s role into a session state variable for easier reference throughout the app. Use a Before Header process on the home page:

DECLARE
  v_role VARCHAR2(50);
BEGIN
  SELECT user_role INTO v_role
  FROM app_users
  WHERE username = :APP_USER;

  :APP_USER_ROLE := v_role;
END;

Then, you can refer to :APP_USER_ROLE in conditions or PL/SQL expressions to apply logic throughout the app.

Using roles and user role assignments allows you to scale your application with structured access control. It keeps your application secure and gives each user the exact level of access they need, based on their responsibilities. It is one of the most flexible and essential tools in Oracle APEX security design.

Access Control requires that developers define application users and roles. Since roles are applied to users, you must create roles before adding users. You can assign users to one or more roles.

To control access to application pages and components, you need to create an Authorization Scheme.

Roles and users defined for applications can be reviewed using the following views:
APEX_APPL_ACL_USERS
APEX_APPL_ACL_USER_ROLES
APEX_APPL_ACL_ROLES

Use the following API to manage access control settings:
APEX_ACL

HOW DO I Create roles and assign the users to the roles

Conclusion
Roles and User Role Assignments in Oracle APEX provide a structured and flexible way to manage user permissions. By assigning the right roles to users and applying those roles throughout your application using authorization schemes, you maintain a secure, organized, and user-specific experience. This not only enhances the usability of your application but also ensures proper access control across all levels of functionality.