Excel, OAuth 2.0 and Device Code Flow
September 30, 2022 | 5 minute readThe post describes my experience with a customer where their Visual Basic Application (VBA) code in Excel failed due to a decision Microsoft has made recently. This post will also describe how we corrected the VBA code using Outh 2.0 - Device Code Flow.
Background
By doing a simple google search, you will find that there is a lot of VBA code where Internet Explorer (IE) is embedded into Excel spreadsheets. However, on June 15, 2022 Microsoft decided to remove their Internet Explorer (IE) browser from all systems.
Customers were forced to use their Edge browser (or other 3rd party browsers) moving forward. I suspect it will result in a lot of broken VBA code.
NOTE: It is possible to use the Edge browser embedded in an Excel spreadsheet, however this was considered a hack and not recommended.
This is exactly what I encountered with one of my customers. My customer, let's call them Åcme, was using an embedded browser to authenticate to a Fusion Apps login screen for the purpose of obtaining an 'access token'; to then be used for FA Rest API calls. Let's dive a bit deeper.
Use case
The end goal was that Acme needed to make a Fusion Apps REST API call like getting the status of a Purchase Order (PO).
Then display the results in a cell within the spreadsheet. In order to do this they would need an access token from FA and use that access token for the PO REST API call.
The legacy flow was as follows:
-
Call an FA REST endpoint to obtain an access token.
-
The spreadsheet will display a FA Login screen in one of the cells (embedded IE).
-
Once authenticated an access token will be generated and sent back in the response.
-
-
Use the access token to make an FA rest call.
Once IE is removed from Windows 10 and beyond this flow will no longer work. Welcome to OAuth 2.0 Device Code Flow! Device Code flow was designed for clients who needed to authenticate but lacked the interface for credential authentication types. By using device code flow you essentially removed the responsibility of the client to provide an interface to enter the username and password.
FA does not support device code flow out of the box; however, FA can be configured to use an external IdP. So in order to make this work we need to use a supported Identity Provider (IdP) that supports OAuth 2.0 and specifically Device Code Flow. In my example I will be using OCI IAM Identity Domains (formally known as IDCS).
For more details, I highly recommend that. you read Chris' blog post on Device Code Flow.
It explains how the flow works. You will need to understand the flow in order to follow along in the VBA code below.
Solution
First I will provide you with the entire code and then we will go through the main sections of the code.
Click the link here to see the code.
A few comments about the code:
-
This is not production code. This code was implemented for a Proof Of Concept (POC) and is only used to demonstrate the device code flow. It comes with no warranties whatsoever.
-
The functions jsonParser() and jsonOut() are not shown. If you want to parse the response coming back from the REST API calls you will need to implement these functions.
Let's look at the function makeIDCSRequest(). This function will get called at least twice. The first call is to initiate the device code flow. The response will provide the user with the device code, user code and where to authenticate using the user code. Let's take a look at the request payload and response. To initiate the flow you will need to send a payload as below:
Here is the call to initialte the flow:
responseData = makeIDCSRequest("/oauth2/v1/device", postData1, False)
with 'postdata1' as:
"response_type=device_code&scope=openid&client_id=" + oauth_clientID
The client ID comes from the application you create in IDCS or OCI IAM (Identity Domain). Again more details on this from Chris' blog post.
Here is a sample response:
{“device_code”:“236de6bc4eda4698xxxxxxxxx",
“user_code”: “ABCDEFG”,
“verification_uri”: “https://idcs-xxxxxx.identity.oraclecloud.com:443/ui/v1/device”,
\“expires_in”: 300}
The device code is required for the subsequent calls to the OAuth engine. The verification_url is where the user will need to go in a browser to authenticate. Once authenticated the user will be prompted for the user code. Once that is complete an access token will be sent in a response from a subsequent call.
Now let's examine the second call to makeIDCSRequest().
You will noticed that this call is defined inside a while loop.
While waiting
responseData2 = makeIDCSRequest("/oauth2/v1/token", postData2, False)
Set jsonOut = ParseJSON(responseData2)
error_code = jsonOut("obj.error")
access_token = jsonOut("obj.access_token")
return_code = StrComp(error_code, "authorization_pending")
If return_code = 0 Then
Application.Wait (5000)
ElseIf Not (StrComp(access_token, "")) Then
waiting = False
End If
Wend
What we are doing here is 'polling' the OAuth engine to let us know when the user has authenticated and entered the correct user code. Once authenticated with user code we will get the results (an access token) in the response. The while loop keeps on checking and waits until the user has completed the authentication process. The 'expires_in' parameter is in seconds so the user has 5 minutes to complete the authenticate/user code otherwise the device code will be invalid and you will have to initiate the flow again. Now that we have an access token we can make our call to the FA REST API call and get our purchase orders.
Good Luck!
Vinay Kalra
Previous Post
F5 BIG-IP Virtual Edition on Oracle Cloud Infrastructure – Part 3 of 3
Next Post