Build Extension Sign-in in Oracle APEX
Introduction
Building an extension sign-in process in Oracle APEX allows developers to create customized authentication flows tailored to specific business requirements. Unlike the default login page, an extension sign-in can incorporate external identity providers, added security layers, and flexible user interface enhancements. This approach is especially useful for applications requiring integration with third-party systems, branding control, or advanced user validation logic.
To build an extension sign-in process in Oracle APEX, you must override the default authentication method and design a customized login experience that meets your application's requirements. This approach allows you to introduce custom validations, external identity checks, multi-step flows, or a branded user interface. Below are the detailed steps to create a functional and secure extension sign-in:
1. Create a New Authentication Scheme
Navigate to Shared Components > Authentication Schemes. Click “Create” and choose “From Scratch.” Select “Custom” as the scheme type. Give it a name like “Custom Extension Sign-in.” Set it as current.
2. Implement Custom PL/SQL Code
In the authentication scheme’s attributes, under the “PL/SQL Function Returning Boolean” section, define the logic for authentication. For example:
return custom_auth_pkg.authenticate_user(:USERNAME, :PASSWORD);
You will need to create this package and function in your database. A basic version might look like this:
CREATE OR REPLACE PACKAGE BODY custom_auth_pkg AS
FUNCTION authenticate_user(p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN IS
v_stored_password users.password%TYPE;
BEGIN
SELECT password INTO v_stored_password
FROM users
WHERE username = UPPER(p_username);
IF v_stored_password = custom_auth_pkg.hash_password(p_password) THEN
APEX_UTIL.SET_AUTHENTICATION_RESULT(0);
RETURN TRUE;
ELSE
APEX_UTIL.SET_AUTHENTICATION_RESULT(1);
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APEX_UTIL.SET_AUTHENTICATION_RESULT(1);
RETURN FALSE;
END;
FUNCTION hash_password(p_password IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN DBMS_CRYPTO.hash(UTL_I18N.string_to_raw(p_password, 'AL32UTF8'), DBMS_CRYPTO.hash_sh256);
END;
END custom_auth_pkg;
3. Create a Custom Login Page
Create a new APEX page (Page Mode: Dialog or Normal), and add two items: PXXX_USERNAME and PXXX_PASSWORD. Add a login button.
Under the button’s "Action," set it to “Defined by Dynamic Action.”
4. Create a Dynamic Action to Authenticate
When the login button is clicked:
Action: Execute PL/SQL Code
Code:
IF custom_auth_pkg.authenticate_user(:PXXX_USERNAME, :PXXX_PASSWORD) THEN
APEX_AUTHENTICATION.POST_LOGIN(:PXXX_USERNAME);
ELSE
APEX_ERROR.ADD_ERROR(
p_message => 'Invalid username or password.',
p_display_location => apex_error.c_inline_in_notification);
END IF;
Items to Submit:
PXXX_USERNAME,PXXX_PASSWORDPage Action on Success: Redirect to desired page (e.g., home page)
5. Optional: Logging and Security Enhancements
Log every login attempt using an insert statement to a custom log table. Add account lockout after N failed attempts, or validate against third-party services like Okta, Active Directory, or OAuth 2.0 providers.
6. Redirect Unauthorized Access
In the authentication scheme, set the “Invalid Session” and “Logout URL” to point to your custom login page so that session timeouts or logouts return users correctly.
7. Apply Your Branding
Modify the HTML or use CSS to match your sign-in page to your brand. You can adjust the login region template, use custom button styles, or add logos, footers, and user instructions.
8. Testing and Troubleshooting
Test different scenarios: valid login, invalid credentials, expired sessions, and account locks. Use debug logs or insert logs into a database table to trace issues. Ensure HTTPS is used and passwords are never logged or stored in plaintext.
Custom extension sign-in gives you complete control over the authentication experience in Oracle APEX. With PL/SQL, dynamic actions, and APEX utilities, you can build a secure, branded, and adaptable login process tailored to your users and enterprise requirements.
The Builder Extension Sign-in authentication scheme in Oracle APEX allows users to log in to an Extension App without requiring a separate authentication process if they are already signed into an APEX session. This authentication scheme checks for an active APEX session and grants access based on the existing session credentials.
How Builder Extension Sign-in Works
This authentication scheme relies on the existing APEX session to validate users.
Users who are already logged into Oracle APEX can access the extension application without needing to re-enter credentials.
If a valid session is not found, the user is redirected to the APEX login page.
Steps to Use Builder Extension Sign-in
1. Navigate to Authentication Schemes
Open App Builder and select your application.
Click Shared Components > Authentication Schemes.
2. Create a New Authentication Scheme
Click Create to add a new authentication scheme.
Select Based on a preconfigured scheme from the gallery.
Choose Builder Extension Sign-in from the list of authentication schemes.
3. Configure Authentication Settings
Set the scheme Name (e.g., "Extension App Sign-in").
Configure Session Timeout settings to ensure session security.
Optionally, define Post-Authentication Procedures for additional validation.
Click Create Authentication Scheme.
4. Activate the Authentication Scheme
Once created, the scheme is not active by default.
Click Make Current to set it as the active authentication scheme for the application.
Use Cases for Builder Extension Sign-in
Seamless access to custom APEX extensions without requiring users to log in again.
Simplified user experience for APEX-based tools, dashboards, and utilities.
Improved security by leveraging APEX session management instead of custom authentication methods.
By using the Builder Extension Sign-in authentication scheme, APEX developers can ensure a smooth and secure login experience for extension applications within their workspace.
Conclusion
A well-designed extension sign-in process in Oracle APEX enhances both user experience and application security. By leveraging APEX's built-in authentication framework and extending it through PL/SQL, REST APIs, or JavaScript, developers can deliver secure and seamless login mechanisms suited to complex enterprise environments. With proper planning and testing, the extension sign-in becomes a powerful feature that elevates the professionalism and robustness of your application.
