Letzte Artikel von Ciprian N. Iancu (Alle anzeigen)

Oracle JET Routing - 22. November 2018
Using Vagrant and Docker for APEX Development – a DOAG story - 21. November 2018
Social Sign-In in Oracle APEX for Microsoft Azure - 4. November 2018

Starting with Oracle APEX version 5.1.4(after this Oracle changed the versioning numbers to 18.1) we have a new feature for the authentication which is called Social Sign-In, basically allows an OpenID or OAuth2 provider to act as authentication provider. Authentication is the process of establishing each user’s identify before they can access your application. In this article I will show you how to configure this with Microsoft Azure OpenID as this requires some configuration steps.

I already have a workspace and an application in my Oracle APEX version 18.1, so I’ll skip the steps for creating those.

Overview

Configure Microsoft certificates into an Oracle Wallet
Configure Oracle APEX Instance Wallet
Register Microsoft Azure Application
Create Microsoft Azure Credential
Add Oracle Database ACL for accessing Microsoft servers
Configure Web Credentials for the Oracle APEX application
Configure Social Login for the Oracle APEX application

Configure Microsoft certificates into an Oracle Wallet

Get the Microsoft certificates and copy them to the Oracle Database machine, you can simply download them from here.

Then connect with putty to the Oracle Database machine with the oracle user and execute the following commands (the certifications are in /home/oracle folder):

cd /home/oracle mkdir -p /home/oracle/wallet/certs
/u01/app/oracle/product/12.1.0/xe/bin/orapki wallet create -wallet /home/oracle/wallet/ -pwd welcome1
/u01/app/oracle/product/12.1.0/xe/bin/orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle/wallet/certs/baltMicrosof.cer" -pwd welcome1
/u01/app/oracle/product/12.1.0/xe/bin/orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle/wallet/certs/microsoftITTLSCA4.cer" -pwd welcome1
/u01/app/oracle/product/12.1.0/xe/bin/orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle/wallet/certs/stamp2loginmicrosoftonlinecom.cer" -pwd welcome1
/u01/app/oracle/product/12.1.0/xe/bin/orapki wallet display -wallet /home/oracle/wallet -pwd welcome1

The last command should print something like this:

Subject:        CN=stamp2.login.microsoftonline.com
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE
Subject:        CN=Microsoft IT TLS CA 4,OU=Microsoft IT,O=Microsoft Corporation,L=Redmond,ST=Washington,C=US

Also notice that I’ve used ‘welcome1’ as the wallet password, this you might want to change if you do these settings in production. The wallet path in my instance is ‘/home/oracle/wallet’, take note on these two settings as these are needed on the next step.

Configure Oracle APEX Instance Wallet

Now that we’ve created an wallet we have to configure Oracle APEX to use it, by following the steps bellow:

Navigate to Oracle APEX internal space
Click on Manage Instance menu on the top bar
In the Instance Settings area you have a link called ‘Instance Settings’, click on it.
Navigate to the Wallet tab and provide the following information:
- Wallet path: file:/home/oracle/wallet
- Password: welcome1
- Thick the check-box: Check to confirm that you wish to change the wallet password
Click on Apply Changes button

You can exit now from the internal workspace, at this stage you have imported the Microsoft certifications into an Oracle Wallet and configured Oracle APEX to use this wallet.

Register Microsoft Azure Application

The steps to register an application in Microsoft Azure are quite straight forward apart from 2 settings, follow the steps bellow to register the Oracle APEX application with Microsoft Azure:

Navigate to https://portal.azure.com
In the left navigation go to Azure Active Directory
Another navigation is revealed where you have App Registrations
Push the New registration button
Provide the Name: v7-oracle-apex and the redirect url: http://localhost:8080/ (I have my Oracle Database instance on my local laptop)
Click Register
In the next screen click on the View API Permissions button
In the next screen click on the Microsoft Graph API, here you will configure the access to the directory because we will ultimately need to get also the user groups to implement the authorization in Oracle APEX
Click on Add Permissions button
Select the Delegated permissions button and the permissions from the screen bellows:
Click on Add permissions button
You will be presented with the following screen:
Bellow to this page you see a Grant consent area
An administrator must click on that button so that you finally have all the rights to access the added permissions from Microsoft Graph.
Going back to the Overview screen for v7-oracle-apex application
Click on the link bellow Redirect URls and add the following urls:
- http://localhost:8080/apex/apex_authentication.callback
- http://localhost:8080/apex/f?p=101
- http://localhost:8080/apex/f?p=101:2::::::
In the above url’s you must replace 101 with the ID of your APEX application
Click Save button
From the Overview Screen, click on left navigation menu on Manifest and edit the property “oauth2AllowImplicitFlow” and set it to true.
Click Save

After these steps can configure the Social Sign-In in Oracle APEX.

Create Microsoft Azure Credential

In this step I will create a new Microsoft Azure Credential which will be later used in configuring an Oracle APEX Application Web Credential.

Execute the following steps:

Navigate to the Overview page of the Application you just created in Microsoft Azure
In the Left Navigation you have Certificates & Secrets, click on it
Click on New client secret
Provide a description and select the validity for the secret
Click on Add
After clicking on the Add button see that a new secrete is created and the value for the secrete is generated
Copy the value, when you leave this screen the value will be always hidden, and you will not be able to retrieve it.

Store this credential secrete somewhere secure because you will need it later when creating the Oracle APEX application Web Credential.

Add Oracle Database ACL for accessing Microsoft servers

Before doing the configuration we have to create the ACL’s in the Oracle Database to allow connections from PL/SQL code to Microsoft hosts.

Execute the following script to create the necessary ACLs:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.create_acl (
        acl          => 'v7_azure_acl_file.xml', 
        description  => 'ACL to grant access to virtual7 AD server',
        principal    => 'APEX_180100',
        is_grant     => TRUE, 
        privilege    => 'connect',
        start_date   => SYSTIMESTAMP,
        end_date     => NULL);

    DBMS_NETWORK_ACL_ADMIN.assign_acl (
        acl         => 'v7_azure_acl_file.xml',
        host        => 'login.microsoftonline.com', 
        lower_port  => 443,
        upper_port  => NULL);
        
    DBMS_NETWORK_ACL_ADMIN.assign_acl (
        acl         => 'v7_azure_acl_file.xml',
        host        => 'graph.windows.net', 
        lower_port  => 443,
        upper_port  => NULL);
    
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'v7_azure_acl_file.xml',
        principal   => 'U_ONEPOINT_V7',
        is_grant    =>  TRUE,
        privilege   => 'connect');
  COMMIT;
END;
/

In the above code you must replace U_ONEPOINT_V7 with the schema on which you have installed the APEX application.

This will allow any PL/SQL code to make https connections on port 443 to the following hosts:

microsoftonline.com
graph.windows.net

Configure Web Credentials for the Oracle APEX application

In this step we configure, for the APEX application, a Web Credential, this will be used in the OAuth2 mechanism to allow calls to authentication services and get the appropriate access token.

Follow the steps bellow to configure it:

Login into the workspace that holds the APEX application that requires this authentication schema
Navigate to the application
Go to Shared Components
Under Security category click in Web Credentials
Click on Create
Provide the following information:
- Name: v7-azure-credentials
- Authentication Type: OAuth2 Client Credentials Flow
- Client ID or Username: here you must get the Application (client) ID from Microsoft Azure
- Remember you had to keep in mind the credential password you generated in the steps when you registered the Microsoft Azure application, this you must enter in the fields Client Secret or Password and Verify Client Secret or Password.
Click Create Button

Configure Social Login for the Oracle APEX application

The final step is to configure the APEX application to use the Social Sign-In for Authentication.

Follow the steps bellow to configure it:

Login into the workspace that holds the APEX application that requires this authentication schema
Navigate to the application
Go to Shared Components
Under Security category go to Authentication Schemes
Click on Create button, then on Next button on the first screen
Provide the following information:
- Name: v7-azure-authentication
- Scheme Type: Social Sign-In
- Credential Store: v7-azure-credentials
- Authentication Provider: OpenID Connect Provider
- Discovery URL: https://login.microsoftonline.com/common/.well-known/openid-configuration
- Scope: profile, email
- Username Attribute: name
- Additional User Attributes: email
Click on Create Authentication Scheme

After this step make sure the above authentication scheme is used by your application:

Go to Shared Components
Under Security category go to Security Attributes
Check that Authentication Scheme is set on v7-azure-authentication

Test the application

Run now the Oracle APEX application and see that before accessing any page the browser is redirected to Microsoft Azure, after the authentication process you are redirected to the application home page.

Summary

This article shows the steps and configurations to execute so that you can leverage Microsoft Azure OpenID authentication. In one of my next blog articles I will show how to get from Microsoft Azure the user groups. Using user groups we will be able to make an authorization scheme to allow/deny access to APEX application pages.

Social Sign-In in Oracle APEX for Microsoft Azure