Wednesday, February 20, 2019

Peer Code Review Presentation at RMOUG Training Days 2019

I just finished giving my first presentation at RMOUG Training Days 2019. So far it has been a great event. The venue is very nice and convenient. The presentations cover interesting topics and are presented by experts. The organization has been flawless. A big thank you to the RMOUG team.

During my presentation I mentioned that I would provide a link to the artifacts of my presentation on this blog...so here it is:
https://github.com/ainielse/rando/tree/master/peer_review

There are three files:

I'd love to hear any stories of organizations putting this into practice.

Finally, a plug for my session tomorrow: APEX, ORDS and IoT. I hope to see you there.

Tuesday, February 12, 2019

Oracle APEX Consolidated Help

I recently had the need to show "most" of the application help on a single page in my APEX application. In order to make the procedure generic and easy to use, I didn't want to create any additional database objects--just a procedure to output what I needed. I decided to create a separate APEX Navigation List to create the hierarchy of pages that I want to include in the consolidated help page. The easiest way was to copy my "Desktop Navigation Menu" and then modify it. Of course, I could have just used "Desktop Navigation Menu" if it met my needs. The result looks like this





Others may need to do the same thing so I'm providing the code here. I apologize that I don't have good code formatting on this blog. I'm providing it as a standalone procedure, but you should put it in a package.

https://github.com/ainielse/rando/blob/master/output_consolidated_help.sql

Thursday, February 07, 2019

Oracle APEX Less Social Sign-On with MS Azure and Office 365

After my last blog post a natural question came up: what if I'm a little anti-social?

Specifically, what if I do NOT want to make my APEX application available to everyone on the planet that has (or is willing to get) a Microsoft Azure / Office 365 account? What if I only want people in my company, that uses Azure AD, to be able to log in?

There are several ways you can do this, and I recommend you employ at least two. No, recommend is too lenient, I insist you employ at least two :). The first is to change the way you call the Microsoft OAuth2 provider. Instead of using the values in my last blog post:


Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token

Use the following:

Authorization Endpoint URL: https://login.microsoftonline.com/yourCompanyDomain/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/yourCompanyDomain/oauth2/v2.0/token

For Insum, this would be insum.ca:

Authorization Endpoint URL: https://login.microsoftonline.com/insum.ca/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/insum.ca/oauth2/v2.0/token

The method above does NOT secure your application to just your domain. It just makes it harder for someone to use another domain. A savvy user can bypass that by just typing "common" in the url.

The REAL step to secure your application is to do one or both of the following:

  1. Create an Authentication Scheme sentry function that makes sure the username includes @yourdomain
  2. Create Authorization Scheme that makes sure the username includes @yourdomain and apply it to the application.

I am often logged into multiple Azure AD accounts at the same time. By adding yourCompanyDomain to the Endpoint URLs you have the added bonus that users do not need to select a login each time they go to your application. Microsoft will detect the correct one to use.

Thursday, January 31, 2019

Oracle APEX Social Sign-On with Microsoft Azure

My co-worker Adrian Png has written a few great posts on social sign-on with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:
https://fuzziebrain.com/content/id/1709/
https://fuzziebrain.com/content/id/1711/

Here is the quick cookbook.

Let's assume your APEX url is
https://myapex.com/ords/

Log in to the Azure registration portal
https://apps.dev.microsoft.com/

and click "Add an app"
Enter a name and click "Create"


Click "Generate New Password"






COPY THAT PASSWORD! You won't have another chance!
hbUChicago1990){(upzadLTF3%

While you're at it, grab the application ID:
dbf9c4ac-a7d1-4885-84c4-5b80777703f3

(Careful readers may see that I altered the secret and application ID in the text. I never saved the pic above on Azure, but figured I'd change it in the text anyway.)

Click "Add Platform" and choose Web






Enter
Redirect URLs
https://myapex.com/ords/apex_authentication.callback

and here is the magic!! The logout URL can NOT have a ? in it. I tried several times to use something like this:
https://myapex.com/ords/f?p=logout
and I got a very vague error when trying to save:

The problem is the logout URL but it's not at all obvious. So, you'll need to figure out another way to logout. I used an apache rewrite rule, which allows me to do this:
https://myapex.com/logmeout


Add a home page url if you want:
https://myapex.com/ords/f?p=myapp

Scroll to the bottom and save your changes.

At this point the Microsoft portion is functional but users will get a notice asking them if they consent to share some information with the application. It's not a big deal, but you can avoid that notice by having an admin consent for everyone in the organization. There is certainly a way to do it within the Azure admin...well, I would guess there is, but I couldn't find it. I did discover this method, though. Go to the following URL:


https://login.microsoftonline.com/common/adminconsent?client_id=<APPLICATION_ID>&state=12345&prompt=admin_consent

in our example

https://login.microsoftonline.com/common/adminconsent?client_id=dbf9c4ac-a7d1-4885-84c4-5b80777703f3&state=12345&prompt=admin_consent

log in as an admin and consent.


OK, on to the APEX installation portion.

Here's an important item...the client secrete (or password) that we got from the application registration is hbUChicago1990){(upzadLTF3%. That's a great secret, but it won't work just as you see it. You'll get the following error:

AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z





The problem is that the secret needs to be url encoded. So, let's do that first.

Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:

select apex_util.url_encode('hbUChicago1990){(upzadLTF3%') the_stuff
  from dual





That's the stuff:

hbUChicago1990)%7B(upzadLTF3%25



Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.



I put the secret in the comments so you can see it.

Almost there. Now go to your application and create an authentication scheme.






Name: Your Choice
Scheme Type: Social Sign-In
Credential Store: MyAppAzure (the one you created)
Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
User Info Endpoint URL: https://graph.microsoft.com/v1.0/me
Scope: User.Read
Username Attribute: userPrincipalName

You might be done...if you have the root cert for https://graph.microsoft.com/v1.0/me already in your wallet. If not get your dba to add the certificate from https://graph.microsoft.com/v1.0/me to the Oracle wallet.

The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.

That does it!