8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Azure AD Authentication for Oracle APEX Applications : Social Sign In

This article describes the setup of social sign in to enable Azure AD authentication of APEX applications.

Information from Azure AD
Wallet Setup
Configure APEX To Use the Wallet
Configure Workspace Isolation
Create a network ACL
Add Web Credentials
Create Application Items for Additional Attributes
Add Authentication Scheme (Generic OAuth2 Provider)
Test It
Troubleshooting

Information from Azure AD

Create a new application in Azure AD for use with APEX social sign in. During the creation of the app, we will need a "Redirect URI". We can use multiple redirect URIs if the Azure AD application is used for multiple APEX applications.

Redirect URI: https://my-app.example.com/ords/apex_authentication.callback

We need the following information from the Azure AD application.

Directory (tenant) ID : This will be substituted into some of the URLs below.
Application (client) ID: This is referred to as the Client ID below.
Client Secret: This is referred to as the Client Secret below.

The rest of the configuration is APEX related.

Wallet Setup

Download only the root certificates from these URLs. At the time of writing there were different root certificates for each URL. I saved the certificates as "digicert-root.cer" and "digicert-root2.cer".

https://login.microsoftonline.com/
https://graph.microsoft.com/v1.0/me

Create a wallet on the database server.

mkdir -p /home/oracle/wallet
cd /home/oracle/wallet

orapki wallet create -wallet /home/oracle/wallet -pwd MyWalletPassword -auto_login

Add the certificates to the wallet. Don't worry if they are already present.

$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/wallet \
  -trusted_cert -cert "/tmp/digicert-root.cer" -pwd MyWalletPassword
  
$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/wallet \
  -trusted_cert -cert "/tmp/digicert-root2.cer" -pwd MyWalletPassword

Configure APEX To Use the Wallet

Enter the wallet details for the APEX instance.

Log in to the "INTERNAL" workspace.
Manage Instance > Instance Settings.
Click on the "Wallet" tab.
Enter details.
- Wallet Path: file:/home/oracle/wallet
- Wallet Password: MyWalletPassword (this can be blank for an auto-login wallet)
Click the "Apply Changes" button.

Configure Workspace Isolation

Social sign in uses web service calls, so we have to make sure the workspace can cope with lots of we service requests.

Log in to the "INTERNAL" workspace.
Manage Instance > Security > Workspace Isolation.
Set "Maximum Web Service Requests" to 1000000, or an appropriate value for your needs.
Click the "Apply Changes" button.

Create a network ACL

We create a network ACL for access to the two Azure URLs. Amend the ACL principal to the relevant value for your APEX version.

declare
  l_username varchar2(30) := 'APEX_200200';
begin
  dbms_network_acl_admin.append_host_ace(
    host => 'login.microsoftonline.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));

  dbms_network_acl_admin.append_host_ace(
    host => 'graph.microsoft.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));
  commit;
end;
/

Add Web Credentials

Add the OAuth2 web credentials to access Azure AD.

Log into the workspace that owns the application.
Depending on the APEX version you will got to "Shared Components > Credentials" or "Shared Components > Web Credentials".
Click the "Create" button.
Add Details
- Name: AzureAD MyApp
- Static Identifier: AzureAD MyApp
- Authentication Type: OAuth2 Client Credentials Flow
- OAuth Scope : blank
- ClientID or Username: {Client ID from the Azure AD application}
- Client Secret or Password: {Client Secret from the Azure AD application}
- Verify Client Secret or Password: {Client Secret from the Azure AD application}
Click the "Create" button.

Create Application Items for Additional Attributes

If we need additional AD attributes, we need somewhere to store them. In this example we are using application items.

Shared Components > Application Items
Click the "Create" button.
Name: AD_USERNAME
Scope: Application
Click the "Create Application Item" button.

Repeat for all attributes. (AD_USERNAME, AD_FIRSTNAME, AD_LASTNAME, AD_EMAIL, AD_GRAPHQL).

Add Authentication Scheme (Generic OAuth2 Provider)

Create an authentication scheme for your application. Substitute the "Directory (tenant) ID" from Azure AD into the URLs where directed.

Shared Components > Authentication Schemes
Click the "Create" button, then the "Next" button.
Enter details.
- Name: AzureAD MyApp
- Scheme Type: Social Sign-In
- Reference Master Authentication Scheme From: blank (only visible on edit)
- Credential Store : AzureAD MyApp
- Authentication Provider: Generic OAuth2 Provider
- Authorization Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/authorize
- Token Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/token
- User Info Endpoint URL : https://graph.microsoft.com/v1.0/me
- Token Authentication Method: Basic Authentication and Client ID Body
- Scope: User.Read
- Authentication URI Parameters: blank
- Username Attribute: userPrincipalName
- Convert Username To Upper Case: No
- Additional User Attributes: blank
Click the "Create Authentication Scheme" button.
Edit the newly created Authentication Scheme to edit the "PL/SQL code" parameter. Only visible on edit.

The "PL/SQL Code" option allows us to set the values of application items returned by the GraphQL call. Some parameters are available by default but some are not. The "Additional User Attributes" setting implies you can use this to return parameters that aren't present by default in the "v1.0" API, but it only seems to return default parameters, regardless of the setting. The "POST_AUTHENTICATE" procedure below sets the application items with the default values. It also makes an additional call to return the local AD username (onPremisesSamAccountName). Set the "PL/SQL Code" value as follows.

procedure post_authenticate is
  l_clob  clob;
begin
  -- Defaults
  :AD_FIRSTNAME := apex_json.get_varchar2('givenName');
  :AD_LASTNAME  := apex_json.get_varchar2('surname');
  :AD_EMAIL     := apex_json.get_varchar2('mail');

  -- Custom
  begin
    l_clob := apex_web_service.make_rest_request(
      p_url         => 'https://graph.microsoft.com/v1.0/me?$select=onPremisesSamAccountName',
      p_http_method => 'GET'
    );
    :AD_GRAPHQL  := l_clob;
    :AD_USERNAME := json_value(l_clob, '$.onPremisesSamAccountName');
  exception
    when others then
      :AD_GRAPHQL  := dbms_utility.format_error_backtrace;
      :AD_USERNAME := null;
  end;
end post_authenticate;

Amend the following settings.
- Post-Authentication Procedure Name: post_authenticate
- Switch in Session: Enabled
Click the "Apply Changes" button.

Test It

The application authentication should work using Azure AD to authentication now.

Troubleshooting

If you get errors, do the following from the App Builder.

Click on the administration icon (a person holding a spanner) and select the "Monitor Activity" menu option.
Click the "By View" link.
Find an occurrence of the incident, and click on the "Debug ID" link for the incident.
Read down the log, and try to identify the error.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.