Roles and Permissions - Authentication

You control how your application interacts with users. If all users share the same access rights, they are considered public users. However, if your application needs to track users individually, you must define an authentication method to establish each user's identity.

Authentication verifies who a user is before granting access to the application. Most authentication methods require users to provide credentials, such as a username and password. These credentials are then validated—if they are correct, the user is granted access; otherwise, access is denied.

Once authenticated, Oracle APEX keeps track of the user by assigning their identity to the built-in substitution string APP_USER. As the user navigates through the application, APEX dynamically updates APP_USER, allowing it to serve as a unique identifier for tracking session activity. The APP_USER value is essential for enforcing security and user-specific functionality.

Accessing APP_USER in APEX

You can reference APP_USER in different ways, depending on where you use it:

• As a bind variable in SQL or PL/SQL:

:APP_USER

• From PL/SQL packages or triggers:

V('APP_USER')

• Using the session context in SQL:

sys_context('APEX$SESSION', 'APP_USER')

Using APP_USER for Security Checks

The APP_USER value can be used to enforce security rules, control application behavior, and restrict access to specific users. One way to do this is by storing user privileges in a table and checking permissions dynamically.

Example: Creating a Security Table

To manage user privileges, you can create a table like this:

CREATE TABLE my_security_table (

    user_id   VARCHAR2(30),

    privilege VARCHAR2(30)

);

After inserting privilege data for each user, you can reference this table to control access to pages, navigation items, buttons, and other UI components.

For example, to conditionally display a button only for users with a specific privilege, you can use this SQL condition:

EXISTS (

    SELECT 1 

    FROM my_security_table 

    WHERE user_id = :APP_USER 

    AND privilege = 'ADMIN'

)

Applying User-Based Security in APEX

You can use APP_USER to:

• Restrict page access based on user roles.

• Show or hide UI components dynamically.

• Log user activity for auditing purposes.

• Implement custom authentication and authorization logic.

By leveraging APP_USER, Oracle APEX ensures secure, personalized, and role-based access control, allowing applications to deliver a tailored user experience while maintaining strong security measures.