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
Related articles.
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...