APEX Authentication with Microsoft account

In the upcoming Oracle Application Express (APEX) 5.2 release, support has been added for so-called "Social Sign-In" authentication. This means that your users can login to an APEX application using an external authentication provider. In APEX 5.2, there is built-in support for Google and Facebook accounts. There is also support for "Generic OAuth Providers" and "OpenID Connect Providers". This blog post describes how you can use the "OpenID Connect Provider" to set up authentication with a Microsoft account, such as an Outlook.com account.

Here is how to do it:

A. Register the APEX application with Microsoft

1. Go to Microsoft Application Registration Portal. Note that you need a Microsoft account, such as an Outlook.com email account, to login and use this portal:

https://apps.dev.microsoft.com/

2. Add an app (enter a descriptive name, your users will see this name during the consent prompt in step 20 below). An Application ID gets generated. Click "Generate New Password" to generate a password.

3. Click "Add platform" and choose "Web". Enter the APEX callback URL as the "Redirect URL", for the APEX 5.2 Early Adopter this is:

https://apexea.oracle.com/pls/apex/apex_authentication.callback

4. Add the URL of the APEX app as the "Home Page URL":

https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10

5. Click "Save"

B. Setup APEX Credentials

6. In APEX, go to Shared Components and click on "Credentials"

7. Create a new Credential and give it a name (for example "Azure OpenID Credentials"). Select "OAuth2 Client Credentials Flow" as the credential type.

8. Add the Application ID from step 2 above as the "Client ID", and the password from step 2 above as the "Client Secret".

9. Save the credentials.

C. Setup APEX Authentication Scheme

10. Go to Shared Components and click on "Authentication Schemes"

11. Create a new authentication scheme and give it a name (for example "Azure AD OpenID"). Select "Social Sign-In" as the scheme type.

12. As credential store, select the credentials created in step 9 ("Azure OpenID Credentials").

13. As authentication provider, select "OpenID Connect Provider".

14. As discovery URL, use the following:

https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration

15. As scope, type "email" (without the quotes)

16. As username attribute, type "email" (without the quotes)

17. Save the authentication scheme.

D. Test the Login

18. Go to the home page of your application:

https://apexea.oracle.com/pls/apex/f?p=your_app_alias:10

19. You should be redirected to a Microsoft login page. Log in with a Microsoft account (such as yourname@outlook.com).

20. You should be prompted to allow the APEX application to log you in and retrieve your email address to identify you. Accept this.

21. You should see the home page of your APEX application, and the value of APP_USER should now be equal to the email address you logged in with at Microsoft.

Switching between authentication schemes in the same APEX session

APEX version 5.2 also includes a new attributte for authentication schemes called "Switch in Session" that can be either Enabled or Disabled. If enabled, the current session's authentication scheme can be changed by passing APEX_AUTHENTICATION=scheme name in a URL's request parameter.

You can use this to present users with multiple login options. For example, you can have the standard APEX Authentication (or your own table-based authentication) set as the current authentication scheme, and then add another button to the login page which switches the authentication scheme to the Microsoft scheme (or Facebook, or Google, etc).

The screenshot above shows an example of a standard APEX login page where I have added an extra button (called "LOGIN_MICROSOFT"), set the icon CSS class to "fa-windows" to get the Windows logo on the button, and set the action attribute of the button to redirect to page 10 (the home page of the application) and also setting the request of the link to "APEX_AUTHENTICATION=name_of_microsoft_auth_scheme". Clicking this button should redirect to a Microsoft page for login, and then redirect the user back to the requested page.

Using the Slack webhook API from PL/SQL

Slack is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the "webhook" integration, which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."

To create a new Slack webhook, login to Slack and go to "Apps" in the left sidebar menu. Click "Manage apps..." and click "Custom Integrations" and then "Incoming Webhooks". Click on "Add Configuration" to create a new webhook. Specify the channel the webhook will post in, and click "Add incoming webhooks integration".

 

Next, take a note of the Webhook URL:

Then go to the Alexandria PL/SQL Utility Library and install the SLACK_UTIL_PKG package in your database schema. Modify your database Network ACL settings to include "hooks.slack.com" (port 443) to allow connections to the Slack site from the database.

Then use the package like this (see also demo script):

In addition to application-specific messages you could perhaps also set up the database (via a background job) to notify you when you are about to run out of disk space, when the number of APEX page views reach a certain threshold, when a specific user logs in, and so on. Use your imagination! :-)

Pivot Table plugin for APEX (Updated)

Some years ago I created an APEX region plugin for displaying the results of a query as a Pivot Table. You can read the original blog post about that here.

The plugin proved quite popular, but people were having problems using it with APEX 5 due to a jQuery versioning conflict.

I've now updated the plugin so it works with APEX, both 5.0 and 5.1.

To use it, simply add a region to the page, change the region type to "Pivot Table" and write an SQL query as the region source:

On the region attributes page, specify the options. I've added some help text to explain the various options. Note that you can have more than one Pivot Table region on the same page, but be sure to use a unique name in the "DOM Element Name" attribute for each pivot table.

I've also added/enabled some extra features such as spreadsheet export (via TSV - tab separated values) and charting.

It should be noted that the plugin is just an APEX wrapper for the Javascript Pivot Table component by Nicolas Kruchten who did all the hard work. I merely packaged it up for use with APEX.

You can download the plugin from my APEX plugins page. Enjoy! :-)