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

Home » Articles » Misc » Here

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.

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.

We need the following information from the Azure AD application.

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.

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.

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.

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.

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.

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;

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.