OAuth Authentication in Oracle APEX

Modern applications often need secure authentication without forcing users to create and manage another username and password. OAuth 2.0 and OpenID Connect (OIDC) provide a standard way to authenticate users through external identity providers such as Google, Microsoft, Oracle Identity, or enterprise identity platforms.

Oracle APEX provides built-in support for OAuth-based authentication through Social Sign-In authentication schemes. APEX can also use OAuth credentials when calling external REST APIs through Web Credentials and APEX_WEB_SERVICE.

1. What is OAuth Authentication?

OAuth 2.0 is an authorization framework that allows an application to securely access protected resources without requiring the application to directly handle the user's password. In Oracle APEX, OAuth-based authentication typically works by redirecting the user from the APEX application to an external Identity Provider (IdP), where the user completes authentication. After successful authentication, the Identity Provider sends an authorization code back to APEX, which exchanges it for an access token and, when using OpenID Connect (OIDC), user identity information. APEX can then use this information to establish an authenticated application session. OpenID Connect extends OAuth 2.0 by adding an identity layer, making it suitable for scenarios where an APEX application needs to know who the authenticated user is.

2. OAuth 2.0 vs OpenID Connect

OAuth 2.0 and OpenID Connect (OIDC) are closely related technologies, but they serve different purposes. OAuth 2.0 is primarily an authorization framework that allows an application to obtain permission to access protected resources, such as REST APIs, without handling the user's password. For example, an application can request access to an API and receive an access token that is then used to access permitted resources. OpenID Connect, on the other hand, builds on OAuth 2.0 by adding an authentication and identity layer. It allows an application such as Oracle APEX to verify who the user is and obtain identity information such as a username, email address, or subject identifier through an ID token or UserInfo endpoint. Therefore, when implementing user login in an Oracle APEX application, an OpenID Connect provider is generally appropriate when the identity provider supports it, while OAuth 2.0 alone is commonly used for delegated access to protected APIs.

3. Understand the OAuth Authorization Code Flow

3. Understand the OAuth Authorization Code Flow

The Authorization Code flow is one of the most commonly used OAuth flows for browser-based authentication because it allows the application to authenticate users without directly handling their passwords. In an Oracle APEX application, the process begins when a user opens the application and selects the login option. APEX redirects the user to the configured Identity Provider (IdP), where the user securely enters their credentials and completes authentication. After successful authentication, the Identity Provider redirects the user back to the APEX callback URL with a temporary authorization code. APEX then securely exchanges this authorization code with the provider's token endpoint to obtain the required tokens. When OpenID Connect is used, the provider can also return identity information such as the user's subject identifier, email address, and name. APEX uses this information to identify the user and establish an authenticated application session, allowing the user to access the application according to the configured authorization rules.

4. Register Your APEX Application with the Identity Provider

Before configuring APEX, register your application with your OAuth/OIDC provider.

Typical information includes:

Application Name:
APEX Hotel Management System

Client ID:
xxxxxxxxxxxxxxxx

Client Secret:
xxxxxxxxxxxxxxxx

Redirect URI:
https://your-domain.com/ords/apex_authentication.callback

The exact redirect URI depends on your APEX deployment and authentication configuration. Oracle's documentation requires the identity provider to be configured with the appropriate APEX callback URL.

For example:

https://example.com/ords/apex_authentication.callback

Do not use a random callback URL. It must match the URI registered with the identity provider.

5. Create a Web Credential in Oracle APEX

In modern APEX environments, sensitive OAuth client information can be stored using Web Credentials.

Navigate to:

Shared Components
   → Web Credentials
   → Create

For an OAuth client credential, configure information such as:

Name:
MY_OAUTH_CREDENTIAL

Authentication Type:
OAuth2 Client Credentials

Depending on the authentication scenario, the credential type and configuration will differ.

APEX securely stores credential secrets rather than exposing them as ordinary application items. Oracle APEX also supports restricting credentials to valid URLs. 

For example:

Valid for URLs:
https://api.example.com/

This prevents the credential from accidentally being used against an unintended endpoint.

6. Create a Social Sign-In Authentication Scheme

For user login, go to:

Shared Components
   → Authentication Schemes
   → Create

Select:

Based on a pre-configured scheme from the gallery

Then select:

Social Sign-In

A typical configuration can look like:

Name:
OAuth Login

Scheme Type:
Social Sign-In

Authentication Provider:
OpenID Connect Provider

You may also configure:

Credential Store:
OAuth Web Credential

Discovery URL:
https://identity.example.com/.well-known/openid-configuration

Scope:
profile,email

Oracle APEX's Social Sign-In configuration supports OpenID Connect providers, OAuth2 providers, scopes, discovery URLs, username attributes, and additional user attributes. 

7. Configure the OpenID Connect Discovery URL

OpenID Connect providers normally expose a discovery document.

For example:

https://identity.example.com/.well-known/openid-configuration

This document can provide endpoints such as:


APEX can use the discovery URL to determine the provider's OAuth/OIDC endpoints.

For example:

Discovery URL:

https://accounts.example.com/.well-known/openid-configuration

Oracle documents the Discovery URL as part of the Social Sign-In configuration.

8. Configure OAuth Scopes

Scopes define what information or access the application requests.

A basic OpenID Connect configuration might use:

openid,profile,email

For example:

Scope:
profile,email

For OIDC providers, APEX handles the required openid scope as appropriate.

A provider may return information such as:


Only request the scopes your application actually needs.

For example, if your application only needs:

email
profile

avoid requesting unrelated permissions.

Oracle APEX supports comma-separated scopes in the Social Sign-In authentication configuration. 

9. Configure the Username Attribute

APEX needs to determine which identity-provider attribute should become the application username.

For example:

Username Attribute:
#email#

or, depending on the provider:

#sub#

The sub claim is the provider's subject identifier.

Example:


You could configure:

Username:
#sub#

or:

Username:
#email#

The correct choice depends on your identity provider and application's identity model.

Oracle's current external-provider example uses #sub# as the username for an Oracle IAM configuration. 

10. Use Post-Authentication Logic

After successful authentication, you may need to create or update a local application user.

For example:


A Post-Authentication procedure can be configured in the authentication scheme.

For larger applications, it is better to place this logic inside a package.

Example:


Implementation:


APEX supports Post-Authentication procedures for Social Sign-In authentication schemes. 

11. Connect OAuth Users with Application Roles

Authentication tells you who the user is.

Authorization determines what the user can do.

For example:

ADMIN
MANAGER
RECEPTIONIST
STAFF

Create a role table:


Example:


Then create an APEX Authorization Scheme.

Example PL/SQL:


This can be applied to:

Pages
Buttons
Regions
Processes
Navigation entries

Oracle APEX separates authentication from authorization and provides authorization schemes for controlling access to application functionality.

12. Use OAuth for REST API Integration

OAuth is not limited to user login.

APEX can also authenticate when calling protected REST APIs.

For example, an API may require:

Client ID
Client Secret
Access Token

You can create an OAuth Web Credential and use it from APEX.

For example:


Oracle APEX provides APEX_WEB_SERVICE.OAUTH_AUTHENTICATE_CREDENTIAL for OAuth authentication using a stored credential.

The resulting token can then be used when calling the protected API.

13. Call a Protected REST API from APEX

Suppose your external API provides:

GET https://api.example.com/customers

After OAuth authentication, APEX can call the service.

Example:


A production implementation should use the appropriate APEX credential configuration rather than manually handling client secrets in application code.

You can then parse JSON:


This approach is useful for integrating APEX applications with CRM, payment, HR, ERP, hotel, or other external platforms.

14. Secure OAuth Authentication in APEX

OAuth implementation should follow strong security practices.

Never hard-code client secrets

Avoid:

l_client_secret := 'my-secret-123';

Instead, use APEX Web Credentials.

Use HTTPS

OAuth endpoints should use:

https://

rather than:

http://

Restrict Web Credential URLs

For example:

https://api.example.com/

rather than allowing credentials to be used against arbitrary endpoints.

Request minimum scopes

Use:

openid,profile,email

when those are the only permissions required.

Protect authorization

OAuth authentication does not automatically mean every authenticated user should access every page.

Use APEX Authorization Schemes.

For example:


Do not expose tokens

Avoid displaying access tokens in:

APEX page items
Browser JavaScript
Debug messages
URLs
Reports

Oracle APEX protects stored Web Credential secrets and supports URL restrictions for credential use. 

15. Complete OAuth Authentication Architecture

A practical Oracle APEX application can use the following architecture:

     

This architecture keeps authentication responsibilities with the identity provider while Oracle APEX manages the application session, authorization, business logic, and database access.

Common OAuth Problems in Oracle APEX

Common OAuth Problems in Oracle APEX

OAuth authentication can sometimes fail because of incorrect callback URLs, client credentials, scopes, or authorization settings. A common issue is redirect_uri_mismatch, which occurs when the redirect or callback URL registered with the Identity Provider does not exactly match the URL configured or expected by Oracle APEX. Another common error is invalid_client, which generally indicates a problem with the Client ID, Client Secret, OAuth Web Credential, or Identity Provider configuration. The invalid_scope error occurs when the application requests scopes that are not supported or permitted by the Identity Provider; for example, an OpenID Connect configuration may use scopes such as openid,profile,email. In some cases, authentication succeeds but the user still cannot access a particular APEX page. This usually indicates an authorization issue rather than an authentication issue. In such situations, check the page's Authorization Scheme, application roles, page-level authorization settings, and the mapping between the authenticated user and the appropriate application role.

OAuth Best Practices Checklist

When deploying an OAuth-enabled Oracle APEX application, security should be considered throughout the entire authentication and authorization process. Always use HTTPS to protect communication between the browser, APEX, and the Identity Provider, and select an appropriate OAuth 2.0 or OpenID Connect flow for the application. Store Client Secrets and other sensitive credentials using APEX Web Credentials instead of hard-coding them in PL/SQL, JavaScript, or application items. Configure the correct callback URL and request only the scopes that the application actually requires. Web Credentials should be restricted to the appropriate valid URLs, and access tokens should never be exposed through browser-visible page items, URLs, reports, or debug messages. Use APEX Authorization Schemes to control access based on application roles and permissions, and implement appropriate post-authentication processing when user information needs to be synchronized with application data. Authentication events can be logged for monitoring and troubleshooting, but sensitive information such as Client Secrets and access tokens should never be written to logs. Finally, test different authentication scenarios, including expired credentials, invalid credentials, unsupported scopes, unauthorized users, restricted pages, and incorrect role mappings, before deploying the application to production.

Conclusion

OAuth authentication provides Oracle APEX applications with a secure and standardized approach for integrating with external identity providers and protected APIs. Through Social Sign-In, APEX can delegate user authentication to providers that support OAuth 2.0 and OpenID Connect, while Web Credentials and APEX_WEB_SERVICE can be used to securely communicate with OAuth-protected REST APIs. A well-designed implementation separates authentication, session management, authorization, business logic, and data access, creating a clear flow from OAuth/OIDC authentication → APEX session → authorization → business logic → database or REST APIs. By combining OAuth, OpenID Connect, Social Sign-In, Web Credentials, Authorization Schemes, and secure PL/SQL practices, developers can build Oracle APEX applications that integrate effectively with modern identity and API platforms. The key distinction is that Social Sign-In is primarily used to authenticate users into an APEX application, while OAuth Web Credentials and APEX_WEB_SERVICE can be used to securely access protected external APIs, allowing APEX to support both modern user authentication and secure API integration.

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications