The APEX Login Procedure in Oracle APEX

 Understanding how the login process works in Oracle APEX is essential for securing your applications and controlling access to data. Whether you're building a public app or a secure enterprise system, the login procedure defines how users authenticate and begin interacting with your app. Oracle APEX provides a flexible, declarative way to implement login behavior using authentication schemes and built-in procedures. In this post, we’ll dive deep into how the APEX login process works, how to configure and customize it, and how to follow best practices to keep your application secure.

How the Login Procedure Works in Oracle APEX

Oracle APEX uses authentication schemes to manage how users log in. The default authentication scheme is Application Express (using APEX workspace credentials), but you can choose other methods such as database accounts, LDAP, social sign-in, or custom PL/SQL procedures.

The login process typically involves these steps:

1. User accesses the application.
   If authentication is required, APEX redirects to the login page.

2. Login page (typically page 101) is displayed.
   The user enters their credentials.

3. Credentials are validated using the selected authentication scheme.
   For APEX authentication, APEX validates the user against workspace users.

4. If successful, the user is redirected to the home page or target page.
   The username is stored in the built-in APP_USER variable.

5. If authentication fails, an error is displayed, and the login page is shown again.

Login processing has the following steps:

1. Run authentication scheme's pre-authentication procedure.

2. Run authentication scheme's authentication function to check the user credentials (p_username, p_password), returning TRUE on success.

3. If result=true: run post-authentication procedure.

4. If result=true: save username in session table.

5. If result=true: set redirect url to deep link.

6. If result=false: set redirect url to current page, with an error message in the notification_msg parameter.

7. Log authentication result.

8. Redirect.

Syntax

APEX_AUTHENTICATION.LOGIN ( 

    p_username IN VARCHAR2, 

    p_password IN VARCHAR2, 

    p_uppercase_username IN BOOLEAN DEFAULT TRUE );

Creating and Managing Authentication Schemes

To view or create an authentication scheme:

1. Go to Shared Components > Authentication Schemes.

2. You will see a list of existing schemes. The one with the green checkmark is the currently active scheme.

3. Click Create to start a new one. Choose from predefined types or select Custom for a PL/SQL-based login.

4. Configure the scheme’s properties, including:

   • Authentication Function Name (for custom schemes)

   • Login Processing options

   • Logout URL

   • Post-Logout Redirect

Custom Login Using PL/SQL Function

If you need custom logic, you can define a PL/SQL function that returns BOOLEAN. For example:

FUNCTION custom_login (
   p_username IN VARCHAR2,
   p_password IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
   IF p_username = 'demo_user' AND p_password = 'demo123' THEN
      APEX_UTIL.SET_AUTHENTICATED_SESSION(p_username);
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

You would reference this function in your custom authentication scheme.

Accessing Logged-in User Information

After login, you can reference:

• APP_USER — contains the logged-in username.

• V('APP_USER') — same value, in SQL or PL/SQL.

• APEX_UTIL.GET_SESSION_STATE('APP_USER') — retrieves session value.

Best Practices for Login Procedure

• Use built-in APEX authentication for admin and developer applications.

• For production apps, consider Social Sign-In, OAuth2, or LDAP for scalability and security.

• Avoid storing plain text passwords. Always hash and validate securely.

• Use Page Access Protection settings (e.g., Arguments Must Have Checksum) to prevent URL tampering.

• Do not disable Session State Protection without understanding the risks.

• Customize login error messages and branding through Page 101 for a better user experience.

Extending Login with Post-Authentication Logic

Use the Post-Authentication Procedure Name to run PL/SQL after a successful login. For example:

BEGIN
   APEX_CUSTOM_AUTH.SET_USER_ROLES(:APP_USER);
END;

You can use this to log activity, check additional permissions, or set session variables.

Oracle APEX Documentation Reference

For more details, visit the official documentation:
Authentication in Oracle APEX

Conclusion

The APEX login procedure is a foundational part of any secure Oracle APEX application. Whether you use the built-in schemes or create a custom one, understanding how authentication works gives you full control over who accesses your app and how. With proper implementation, you can ensure security, streamline access, and create a seamless login experience for your users. Take time to configure and test your authentication schemes thoroughly — it’s one of the best investments you can make in your APEX development.