APEX Field Notes: HOW DO I Users and Groups of Users

Tuesday, July 8, 2025

HOW DO I Users and Groups of Users

Introduction
In Oracle APEX, managing users and groups of users is a critical part of building secure, role-based applications. By organizing users into logical groups—such as Administrators, Managers, or Employees—you can assign permissions and access rights more efficiently. This structure allows developers to control what each group can see or do within the application, simplifying user management and strengthening overall security.

In Oracle APEX, managing users and groups of users allows you to define access and behavior across your application in a structured and secure way. This is typically implemented using a user management table and a group or role assignment table that lets you associate one or more users with specific roles or permission levels.

To begin, you should first define a user table in your schema. For example:

CREATE TABLE app_users (
  user_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username    VARCHAR2(100) NOT NULL UNIQUE,
  email       VARCHAR2(200),
  status      VARCHAR2(20),
  created_on  DATE DEFAULT SYSDATE
);

Next, create a table to define groups or roles:

CREATE TABLE user_groups (
  group_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  group_name  VARCHAR2(100) NOT NULL UNIQUE
);

Then, define a mapping table that links users to groups. This allows a single user to belong to multiple groups if needed:

CREATE TABLE user_group_membership (
  user_id   NUMBER NOT NULL,
  group_id  NUMBER NOT NULL,
  PRIMARY KEY (user_id, group_id),
  FOREIGN KEY (user_id) REFERENCES app_users(user_id),
  FOREIGN KEY (group_id) REFERENCES user_groups(group_id)
);

Once the structure is in place, insert your users and groups. For example:

INSERT INTO app_users (username, email, status) VALUES ('ADMIN1', 'admin@example.com', 'ACTIVE');
INSERT INTO user_groups (group_name) VALUES ('ADMIN');
INSERT INTO user_groups (group_name) VALUES ('MANAGER');

-- Associate user with ADMIN group
INSERT INTO user_group_membership (user_id, group_id)
SELECT u.user_id, g.group_id
FROM app_users u, user_groups g
WHERE u.username = 'ADMIN1' AND g.group_name = 'ADMIN';

In APEX, you can use this structure to enforce group-based access control. Start by creating Authorization Schemes under Shared Components. For example, to restrict access to users in the “ADMIN” group:

Create a new authorization scheme
Set the type to PL/SQL Function Returning Boolean
Use a block like this:

RETURN EXISTS (
  SELECT 1 
  FROM app_users u
  JOIN user_group_membership ugm ON u.user_id = ugm.user_id
  JOIN user_groups g ON ugm.group_id = g.group_id
  WHERE u.username = :APP_USER
    AND g.group_name = 'ADMIN'
);

Apply this scheme to pages, buttons, or processes by setting the Authorization Scheme property in the Security section of the component.

You can also use a Before Header process to store the user’s group(s) into session state:

DECLARE
  v_group VARCHAR2(100);
BEGIN
  SELECT g.group_name
  INTO v_group
  FROM app_users u
  JOIN user_group_membership ugm ON u.user_id = ugm.user_id
  JOIN user_groups g ON ugm.group_id = g.group_id
  WHERE u.username = :APP_USER
  FETCH FIRST ROW ONLY;

  :APP_USER_GROUP := v_group;
END;

This allows you to easily reference :APP_USER_GROUP in conditions or dynamic actions.

Using users and groups of users in Oracle APEX gives you a flexible, scalable way to define access rights and customize user experience. It’s essential for maintaining clean, role-based application design, especially as your user base grows and requires differentiated access to data and features.

Step 1- Users and User Groups are created from the application level by selecting the man+wrench icon .