Building your very first application with ORACLE APEX

 

USE HTTP Header Variable Authentication

Introduction
HTTP Header Variable Authentication in Oracle APEX enables applications to authenticate users based on values passed in HTTP headers. This method is commonly used in environments where authentication is handled externally by a web server or proxy, which then forwards user identity information via headers. Using this approach, APEX can trust the upstream system and seamlessly authenticate users without requiring separate login prompts.

How to Use HTTP Header Variable Authentication in Oracle APEX

1. Prepare Your Environment

   • Ensure your web server or reverse proxy is configured to authenticate users and send relevant identity information in HTTP headers, such as REMOTE_USER or a custom header.

2. Open Your APEX Application

   • Log in to Oracle APEX and open the target application.

3. Create or Configure Authentication Scheme

   • Go to Shared Components > Authentication Schemes.

   • Create a new scheme or edit an existing one.

   • Select HTTP Header Variable as the authentication type.

4. Configure HTTP Header Settings

   • Specify the name of the HTTP header that carries the authenticated username (e.g., REMOTE_USER).

   • Configure any additional validation or mapping if needed.

5. Set Application User Retrieval

   • Optionally, configure how the application retrieves additional user information or roles based on the header value.

6. Make the Scheme Current

   • Save the scheme and activate it by clicking Make Current.

7. Test the Authentication

   • Access the application through the web server that sets the HTTP header and verify that users are authenticated automatically based on the header values.

Authenticate users externally by using an HTTP header variable set by the web server.

Overview:
The HTTP Header Variable authentication method allows Oracle APEX to identify users based on a value stored in an HTTP header variable. This method is useful when integrating with centralized web authentication solutions such as Oracle Access Manager, which provides Single Sign-On (SSO). These external authentication systems validate user credentials and pass the authenticated username to APEX using an HTTP header variable (e.g., "REMOTE_USER", which is the default).

Setting Up HTTP Header Variable Authentication

To configure HTTP Header Variable authentication:

1. Access the Authentication Schemes Page:

• Open App Builder from the Workspace home page.

• Select an application.

• On the Application home page, go to Shared Components.

• Under Security, select Authentication Schemes.

2. Create a New Authentication Scheme:

• Click Create.

• Select Based on a pre-configured scheme from the gallery and click Next.

3. Configure Basic Authentication Settings:

• Name: Enter a reference name for the authentication scheme.

• Scheme Type: Select HTTP Header Variable.

4. Specify HTTP Header Variable Settings:

• HTTP Header Variable Name:

• Enter the name of the HTTP header variable containing the username.

• If left blank, REMOTE_USER will be used as the default.

• Action if Username is Empty:

• Choose the action when the HTTP header variable is empty: 

• Redirect to Built-In URL: Redirects to /apex/apex_authentication.callback, forcing authentication via the web server.

• Redirect to URL: Redirects to an external login page, which must then pass back the validated username.

• Display Error: Shows an error message without attempting a login.

• Verify Username:

• Defines how often APEX should verify the username in the HTTP header: 

• Each Request (most secure): Ensures the session username matches the header value on every request. If different, the session is invalidated.

• After Login: Only verifies the username once, after the initial login callback.

• Logout URL of SSO Server:

• (Optional) If using Oracle Access Manager or a similar SSO solution, enter the Single Sign-Out (SSO) logout URL.

• Example for Oracle Access Manager: 

/oamsso/logout.html?end_url=%POST_LOGOUT_URL%

%POST_LOGOUT_URL% will be replaced with an encoded URL for the APEX login page.

5. Save the Authentication Scheme:

• Click Create Authentication Scheme.

This setup enables secure external authentication using an HTTP header variable, ensuring seamless integration with enterprise SSO solutions like Oracle Access Manager.

Best Practices

• Use this method only when the HTTP header is reliably set by a trusted web server or proxy.

• Ensure secure communication (HTTPS) to protect header information.

• Validate the header content to prevent spoofing or unauthorized access.

• Combine with authorization schemes in APEX to control user privileges.

• Document and audit your configuration regularly.

Oracle APEX Documentation
For more information, refer to:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/http-header-authentication.html

Conclusion
HTTP Header Variable Authentication in Oracle APEX offers a seamless way to integrate external authentication systems with your applications. By trusting HTTP headers set by secure upstream systems, you can provide a smooth login experience without compromising security. Following best practices ensures that this method remains safe and effective in your APEX environment.

Set Up Database Account Credentials

Introduction
Setting up database account credentials in Oracle APEX allows your application to authenticate users using Oracle database accounts. This method leverages the database’s native authentication system to control access, which is useful in environments where database-level security is preferred or required. Proper configuration ensures secure and efficient user management directly through the database.

How to Set Up Database Account Credentials in Oracle APEX

1. Create Database Users

   • Use SQL*Plus or SQL Developer to create database users who will access your application.

   • Assign appropriate roles and privileges for these users to access necessary schema objects.

2. Open Your APEX Application

   • Log in to Oracle APEX and open the application where you want to use database authentication.

3. Navigate to Authentication Schemes

   • Go to Shared Components > Authentication Schemes.

4. Create a New Authentication Scheme

   • Click Create and select Database Account as the authentication type.

   • Name the scheme appropriately (e.g., DB_Account_Auth).

5. Configure the Scheme

   • Optionally configure parameters such as session timeout, login/logout pages, and error messages.

   • No additional PL/SQL code is required as APEX uses the Oracle database authentication mechanism.

6. Make the Scheme Current

   • Save the new scheme and click Make Current to activate it for the application.

7. Test the Login

   • Run the application and test login using one of the configured database user credentials.

Follow these steps to configure Database Account Credentials authentication for your application:

1. Navigate to the Workspace home page.

2. Open App Builder.

3. Select an application.

4. On the Application home page, go to Shared Components.

5. Under Security, select Authentication Schemes.

6. On the Authentication Schemes page, click Create.

7. Choose Based on a pre-configured scheme from the gallery and click Next.

8. Under Name: 

• Name: Enter a reference name for the authentication scheme.

• Scheme Type: Select Database Accounts.

9. Click Create Authentication Scheme.

This setup allows users to authenticate using their database account credentials.

Best Practices

• Ensure database users have only the minimal privileges necessary for application functions.

• Avoid using shared database accounts; assign individual user accounts whenever possible.

• Regularly review and manage database account passwords and privileges.

• Use secure password policies enforced at the database level.

• Monitor database account usage and audit failed login attempts.

Oracle APEX Documentation
For more detailed information, visit:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/database-authentication.html

Conclusion
Setting up database account credentials in Oracle APEX integrates application authentication directly with Oracle database users, offering a straightforward and secure way to manage access. By following best practices in user creation and privilege management, you ensure your application remains both secure and compliant with your organization’s policies.

Session Management Security

Introduction
Session management security is critical in Oracle APEX applications to ensure that user sessions are properly controlled and protected from unauthorized access or hijacking. Effective session management helps maintain the integrity of user data, prevents session fixation, and enforces appropriate session timeouts. Understanding and implementing session management security features in APEX is essential for building robust and secure applications.

How to Use Session Management Security in Oracle APEX

1. Session Timeout Settings

   • Navigate to Shared Components > Security Attributes.

   • Set the Session Idle Timeout to define how long a session can remain inactive before automatic logout.

   • Configure Maximum Session Length to limit total session duration regardless of activity.

2. Enable Session State Protection

   • In the application, enable Session State Protection on pages and items to prevent unauthorized or tampered data submissions.

3. Use Session State Validation

   • Use built-in APEX mechanisms to validate that session state values come from expected sources, reducing risk of cross-site scripting (XSS) or CSRF attacks.

4. Implement Logout Functionality

   • Configure a proper logout process that clears session data and invalidates the session token.

5. Monitor Active Sessions

   • Use Oracle APEX views such as APEX_WORKSPACE_SESSIONS to monitor active user sessions for unusual activity.

6. Consider Using Secure Cookies and HTTPS

   • Ensure your application runs over HTTPS to encrypt session cookies and prevent interception.

Learn how Oracle APEX handles session management security, especially when using custom authentication.

APEX prevents two potential security risks:

• Unauthorized Access to Another User’s Session State: While APEX restricts direct access, users can still attempt to manually enter a different session ID in the URL.

• Access to a Stale Session: Users may inadvertently access an outdated session by using browser bookmarks.

To ensure security, APEX validates that the user identity token set by the custom authentication function matches the original user identity recorded when the session was created.

• If the user is not yet authenticated, session state access is allowed only if it does not belong to another user.

• If the session ID in the request does not pass validation, APEX redirects the request to the same page using the correct session ID.

This process helps maintain secure and consistent session management.

Database Accounts

Database Account Credentials authentication relies on database schema accounts to authenticate users.

Database Account Credentials

This method requires each user to have a corresponding database account (schema) in the local database. Users log in using their database username and password, which are validated against the database.

Choose Database Account Credentials authentication if:

• Each named user can have a separate database account.

• Managing user accounts through database tools aligns with your requirements.

This approach is best suited for environments where database-level user management is practical.

Best Practices

• Set reasonable timeout values balancing usability and security.

• Always enable session state protection for sensitive pages and items.

• Educate users to log out after use, especially on shared devices.

• Monitor session activity regularly for anomalies.

• Combine session management with strong authentication and authorization.

Oracle APEX Documentation
For detailed information, refer to:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/session-management.html

Conclusion
Using session management security features in Oracle APEX is vital to protect your applications and users. By configuring session timeouts, enabling state protection, and monitoring sessions effectively, you can reduce risks related to unauthorized access and session hijacking. Following best practices ensures your APEX applications remain secure while providing a smooth user experience.

Set Up Custom Authentication

Introduction
Setting up custom authentication in Oracle APEX enables you to implement your own login logic tailored to specific application needs. This is ideal when the built-in authentication options do not fit your requirements, such as integrating with external user stores, applying unique security rules, or supporting multi-factor authentication. Custom authentication gives you full control over how users are verified before gaining access.

How to Set Up Custom Authentication in Oracle APEX

1. Open Your Application
   Log in to Oracle APEX and open the application where you want to configure custom authentication.

2. Navigate to Authentication Schemes
   Under Shared Components, click on Authentication Schemes found in the Security section.

3. Create a New Custom Authentication Scheme

   • Click Create and select Custom as the scheme type.

   • Enter a meaningful name for the scheme (e.g., My_Custom_Auth).

4. Write the Authentication Function

   • Provide a PL/SQL function that accepts user credentials and returns TRUE if authentication succeeds, or FALSE otherwise.

   • For example:

   DECLARE
     l_valid NUMBER;
   BEGIN
     SELECT COUNT(*) INTO l_valid
     FROM users_table
     WHERE user_name = :USERNAME
       AND user_password = :PASSWORD; -- Use secure hashing here
     RETURN l_valid = 1;
   END;
   

   • Make sure to replace users_table with your actual user table and implement secure password handling.

5. Configure Login and Logout Pages

   • Optionally set custom login and logout pages or URLs for better control over the user experience.

6. Set the Custom Scheme as Current
   Save the scheme and activate it by selecting Make Current.

7. Test Your Authentication Scheme
   Run your application and verify the login process functions as expected with your custom logic.

Learn how to configure a custom authentication scheme in Oracle APEX.

Setting Up Custom Authentication

Learn how to configure a Custom Authentication Scheme in Oracle APEX.

Steps to Create a Custom Authentication Scheme:

Navigate to the Authentication Schemes Page

• On the Workspace home page, click App Builder.

• Select your application.

• On the Application home page, click Shared Components.

• The Shared Components page appears.

Create a New Authentication Scheme

• Under Security, click Authentication Schemes.

• On the Authentication Schemes page, click Create.

Select the Authentication Type

• Choose "Based on a pre-configured scheme from the gallery" and click Next.

Configure Authentication Details

• Name: Enter a descriptive name for the authentication scheme.

• Scheme Type: Select Custom.

Define Custom Authentication Logic

• Fill in the appropriate fields.

• For details about each field, refer to the field-level Help.

Save the Authentication Scheme

• Click Create Authentication Scheme to apply your changes.

Your custom authentication scheme is now set up and ready to be configured for login validation. 

Best Practices

• Never store passwords in plain text; always use hashing with salt.

• Sanitize input parameters to avoid SQL injection risks.

• Keep authentication logic simple and performant to avoid slowing down logins.

• Document your custom authentication code and configuration.

• Test thoroughly in a development environment before deploying to production.

Oracle APEX Documentation
For more information on custom authentication, visit:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/custom-authentication-schemes.html

Conclusion
Setting up custom authentication in Oracle APEX empowers you to tailor user login processes to your unique requirements. By carefully designing and testing your authentication logic, you can create secure, reliable, and user-friendly access controls that integrate seamlessly with your application’s infrastructure.

Custom Authentication Scheme

Introduction
Oracle APEX provides flexible options for securing your applications, including the ability to create custom authentication schemes. Custom authentication lets you implement your own login logic, integrating with external systems, databases, or specific business rules. This approach is useful when preconfigured schemes do not meet your requirements or you need a tailored security solution.

How to Create a Custom Authentication Scheme in Oracle APEX

1. Open Your Application
   Log in to Oracle APEX and open the application where you want to create the custom authentication scheme.

2. Navigate to Authentication Schemes
   Go to Shared Components > Authentication Schemes under the Security section.

3. Create a New Authentication Scheme
   Click Create, then select Custom as the authentication scheme type.

4. Define Your Authentication Logic

   • Provide a name for the scheme (e.g., Custom_DB_Auth).

   • Write a PL/SQL function that validates user credentials. This function should return TRUE if authentication is successful and FALSE otherwise.

   • Example PL/SQL snippet:

   DECLARE
     l_count NUMBER;
   BEGIN
     SELECT COUNT(*) INTO l_count
     FROM my_users
     WHERE username = :USERNAME
       AND password = :PASSWORD;
     RETURN l_count = 1;
   END;
   

   Replace my_users with your user table and implement proper password hashing as needed.

5. Configure Login and Logout URLs
   Set URLs or pages for login and logout if you want custom behavior beyond default APEX pages.

6. Make the Scheme Current
   Save the scheme and select Make Current to activate it for your application.

7. Test the Authentication Scheme
   Run the application and verify the login process works as expected with your custom logic.

Creating a Custom Authentication Scheme in Oracle APEX gives you complete control over the authentication process, session management, and security policies. This method is ideal when built-in authentication methods (such as database authentication or LDAP) do not meet your requirements.

By implementing custom authentication, you can: 

• Build a custom login interface. 

• Define security policies for user sessions. 

• Audit and track user activity.

• Integrate APEX with external applications using a common authentication mechanism.

Why Use Custom Authentication?

Custom authentication is the best choice when:

 Built-in authentication methods (database, LDAP, SAML) are not sufficient.
 

You need a custom login form with additional validation logic.

• Advanced security features are required (e.g., multi-factor authentication (MFA), session tracking, or login throttling).

•  Session expiration and activity tracking need to be customized.

•  Your application consists of multiple applications that need to share the same authentication session.

• You need one-way redirection logic before page processing (e.g., redirecting users based on roles or login time).

•  Your APEX application must integrate with non-APEX systems using a shared authentication framework.

How to Implement Custom Authentication in Oracle APEX

1. Create a Custom Authentication Scheme

Navigate to Authentication Schemes

• Open App Builder > Select your application.

• Go to Shared Components > Click Authentication Schemes.

• Click Create.

Choose Authentication Method

• Select "Based on a preconfigured scheme from the gallery" > Choose Custom.

Enter Authentication Function

• Under PL/SQL Function Returning Boolean, enter a function that validates user credentials.

2. Create a Custom PL/SQL Authentication Function

In SQL Workshop, create a PL/SQL function that verifies user credentials against a custom user table.

CREATE OR REPLACE FUNCTION custom_authentication (

    p_username IN VARCHAR2,

    p_password IN VARCHAR2

) RETURN BOOLEAN IS

    v_count NUMBER;

BEGIN

    SELECT COUNT(*)

    INTO v_count

    FROM users

    WHERE username = LOWER(p_username)

    AND password = UPPER(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => p_password)); -- Example: Hashing passwords

    RETURN v_count = 1;

EXCEPTION

    WHEN OTHERS THEN

        RETURN FALSE;

END custom_authentication;

/

This function checks if the provided username and password exist in the users table.
It uses MD5 hashing for password security (use SHA-256 or bcrypt for better security).

3. Configure Custom Authentication Scheme in APEX

• Under PL/SQL Function Returning Boolean, enter: 

• return custom_authentication(:P101_USERNAME, :P101_PASSWORD);

• Click Apply Changes to save.

4. Create a Custom Login Page

1️. Create a Login Page

• Navigate to App Builder > Click Create Page.

• Select Login Page > Choose Blank Page.

• Add two Text Items: 

• P101_USERNAME (for username)

• P101_PASSWORD (for password)

• Add a Login Button and set its action to Submit Page.

1. Process Authentication on Login

• Go to Processing > Create a new process: 

• Name: Authenticate User

• Type: PL/SQL Code

• Code: 

IF custom_authentication(:P101_USERNAME, :P101_PASSWORD) THEN

    APEX_UTIL.SET_SESSION_STATE('APP_USER', :P101_USERNAME);

    APEX_AUTHENTICATION.LOGIN(p_username => :P101_USERNAME);

ELSE

    APEX_UTIL.SET_SESSION_STATE('LOGIN_FAILED', 'Y');

    RAISE_APPLICATION_ERROR(-20001, 'Invalid Username or Password');

END IF;

• If authentication succeeds, the user is logged in.

• If authentication fails, an error message is displayed.

5. Customizing Session Management & Security

Session Timeout: Set session expiration policies in Security Attributes under Shared Components.

Session Tracking: Store login activity in a custom table:

CREATE TABLE login_audit (

    log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    username VARCHAR2(50),

    login_time TIMESTAMP DEFAULT SYSTIMESTAMP,

    ip_address VARCHAR2(50)

);

Log user login details:

INSERT INTO login_audit (username, ip_address)

VALUES (:APP_USER, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));

COMMIT;

Custom authentication in Oracle APEX gives you full control over login, session management, and security policies. It allows you to integrate with custom user repositories, external applications, and enforce advanced security measures. By using PL/SQL functions and session management techniques, you can build a secure and flexible authentication system tailored to your application's needs.

Best Practices

• Always hash and salt passwords securely; never store them in plain text.

• Keep your authentication function efficient to avoid slowing down user login.

• Validate inputs to prevent SQL injection or other security vulnerabilities.

• Document your custom authentication scheme clearly for future maintenance.

• Thoroughly test all login and logout scenarios before deploying to production.

Oracle APEX Documentation
For detailed guidance on creating custom authentication schemes, visit:
https://docs.oracle.com/en/database/oracle/apex/23.2/aeapp/custom-authentication-schemes.html

Conclusion
Creating a custom authentication scheme in Oracle APEX offers powerful control over user authentication, allowing you to implement specific security policies and integrate with diverse systems. By following best practices and carefully testing your scheme, you can ensure a secure and seamless login experience tailored to your application's needs.