Since ORDS first introduced support for authenticating with JWTs, there have been many questions surrounding configuration and testing. In this example, I cover, from start to finish:
- Registering an application in Microsoft Entra
- Creating a Client ID, Secret, and related Scope
- Use Postman and OAuth2.0 to request a JWT from Microsoft
- Decode the JWT (token) so you can use parts of it to create a JWT profile in ORDS
- Create an ORDS privilege with relevant roles, which you’ll use later for testing a protected ORDS endpoint (in Postman)
If this sounds like what you are looking for, then read on.
There’s a lot to cover here, so there’s no faffing about on this one. Let’s go!
Configuring Microsoft Entra
I assume you know how to locate Microsoft Entra (formerly Microsoft AD; details here).
App registration
Locate the Applications category, and from the drop-down menu, select App registrations.
Fill out the information that best fits your scenario. Make sure you understand which Supported account type to use. I chose option three: Accounts in any organizational directory and personal Microsoft accounts.
NOTE: If you want to test this OAuth code flow with a personal Microsoft email, GitHub, Skype, or another Microsoft "property" then you'll want to make the same choice as me.
Retrieve the Callback URL from Postman
You’ll notice in the Redirect URL section that there is another drop-down menu. You’ll want to select “Web.” You’ll need the Postman-provided “Callback” URL for this to work.
NOTE: This walkthrough assumes you are using Postman, but Insomnia, cURL, and language-specific libraries will have similar, but distinct procedures for the "URL redirect."
The Callback URL looks disabled in Postman, but you can still highlight and copy it. Copy and save it, then return to the App registration page (in Microsoft Entra).
Complete app registration
Click Register. A message will appear, notifying you of the successful app registration. Copy the Application (Client) ID and save it to your clipboard or a text editor. I’m using TextEdit, which you’ll see later.
Create a new Client Secret ID and Value
Next, you must create a Client Secret ID and Secret Value. Navigate to the Certificates & Secrets category. Then click the New client secret button. Add a description of the client’s secret along with an expiration date. 180 days is the recommendation.
Once you’ve completed all the fields, click the Add button. Another confirmation message will appear. You should see your Client Secret Value and Client Secret ID in plain text. COPY your Client Secret Value now, this will be the only time it is visible! Copy your Client Secret ID, too.
Paste them to your clipboard or text editor. We’ll be using them shortly and in more than one place!
Add a scope pt 1
Next, you’ll need to add a scope. Click the Expose an API category and the Add a scope button.
NOTE: The scope name you choose here is used as the privilege name in ORDS. They need to be identical. SO, whatever you put here you HAVE to put as the name of the associated ORDS privilege!
Application ID URI
If you haven’t added an Application ID URI, this wizard will prompt you to set one up before you can proceed. You’ll see this screen if this is your first time doing this.
Microsoft Entra will have prepopulated the Application ID URI field for you. Click the Save and continue button.
Add a scope pt 2
The scope name appends to the Application ID URI as you fill in the first field. In this example, I’ve used a “dot notation” for the scope name. Since this is the naming convention we use in ORDS for privileges, I decided to keep everything standardized.
REMEMBER: This scope and the associated ORDS privilege will share the same name. You’ll see this in practice in a few paragraphs.
Once you’ve filled in all the fields, click the Add scope button. Copy the scope name and save it to your clipboard or text editor. This scope name should combine your Application ID URI + the scope you just created. In my case, it looks like this:
api://367ad5fd-a417-49f9-aed2-7b2290bd4ce3/my.ords.app.scopeObtain required OAuth endpoints
The final step in Microsoft Entra is to retrieve all relevant OAuth2.0 and JWT endpoints. You’ll find 2/3rds of these from the Overview. Click Overview, then the Endpoints tab. You’ll want to copy and save the following endpoints:
- OAuth 2.0 authorization endpoint(v2)
- OAuth 2.0 token endpoint (v2)
The final endpoint identifies the public keys used for decoding JWTs. For some reason, it’s not found in Microsoft Entra, so save this link and copy it to your clipboard or text editor.
That’s it for Microsoft Entra configuration! Two more steps, and then we can test our secure ORDS endpoint.
Request a JWT
With any luck, you’ve been following along and saved all the required information for Postman. Here is what my clipboard looks like, hopefully yours does too:
Configuring Postman
Open Postman and enter the following information:
- Authorization URL
- Access Token URL
- Client ID
- Client Secret
- Scope
- Client Authentication
STOP, README!!! The “Client ID” is the Application ID URI, NOT the Client Secret ID. I don’t know why this is the case, it’s not documented anywhere, it makes no sense. Be warned.
Once you’ve filled everything in, click the Get New Access Token button.
Granting access
If you’ve kept everything identical to what I have, you should see the following prompts asking you to authorize/grant permission to this app so that you may acquire a JWT (Access Token).
Redirecting back to Postman
After the browser redirects you back to Postman, copy the token value. You’ll need to decode it next. You’ll use some of the properties (referred to as “claims” in this context) in the JWT when configuring ORDS.
Decode the JWT
There are many language libraries available to decode these JWTs. However, for a quick test scenario, these browser-based tools work well:
Copy and paste the JWT string into one of the decode boxes to decode the “claims” contained in the JWT. Whichever one you choose, it doesn’t matter, they’ll look identical. Copy the “iss”, or issuer value, and the “aud”, or audience value. Save both of these values to your clipboard or text editor.
Database Actions
Finally, we move to Database Actions! There are two areas of focus here:
- The SQL Worksheet and
- The REST Workshop
Navigate to the SQL Worksheet first.
SQL Worksheet
Regardless of whether you are an ADMIN user, a user with the ORDS ADMINISTRATOR role, or a regular database user, you can find the relevant CREATE_JWT_PROFILE PL/SQL procedure in the ORDS_SECURITY or ORDS_SECURITY_ADMIN packages. You can find them in the ORDS_METADATA schema. I could use either as the ADMIN.
NOTE: Read our latest ORDS 23.4 Release Notes, we've made changes to these security packages. Details here.
ORDS_SECURITY_ADMIN & ORDS_SECURITY
After you’ve located the procedure, select and copy it.
Obtaining the CREATE_JWT_PROFILE procedure
Open a new SQL Worksheet and paste the procedure. It is missing a lot; hopefully, you’ve been taking notes.
Editing the CREATE_JWT_PROFILE procedure
To “fix” this procedure, you’ll need most of the things you’ve been saving along the way:
- Issuer value
- Audience value
- Public keys (also known as JWKs or JSON Web Keys)
Once your PL/SQL procedure looks the same (with your unique details, obviously), click the Run Script button. You’re done with the SQL Worksheet and can move on to the REST Workshop!
REST Workshop
You’ll need to create a privilege next. Remember, this privilege will be identical to the application’s scope name (which you created in Microsoft Entra). Navigate to Privileges, then click the Create Privilege button.
ORDS privilege setup
You can choose whatever you like for the Label and Description, but the name MUST match your application’s scope name.
You’ll need to select SQL Developer for the Role and
CORRECTION: Role is not necessary for this implementation. The association between the ORDS privilege name and the Application's scope takes care of this for you.
choose the “target” resource you want to protect. In my case, it’s just an SQL statement that I’ve REST-enabled (it performs a simple query on a database table).
When complete, you should see a privilege tile similar to the one in my example. The name of that privilege tile should match the name of your application’s scope.
Test a secure ORDS resource with JWTs
Believe it or not, you’ve finished the configuration. Onto testing!
Test an ORDS endpoint
Here, I select an endpoint I’ve protected (with the privilege I just created). I’ll use that in my Postman example.
Executing in Postman
I return to Postman, ensuring that none of my configurations has changed. This time, I’m adding my ORDS URI to the GET address bar. To be safe, I request a new Access Token and add that to my configuration. You’ll see that in the “Current Token” Field in the image below.
Next, I click the Send button, and volià, it works like a charm!
Success
You’ll see a 200 OK Successful Response Status Code and my ORDS payload. And just like that, you’ve done it. You’ve just learned how to access a secure ORDS resource using JWTs!
And what exactly are we looking at here? Well, these are baby names from 2023 US Social Security Card applications filtered using the following SQL:
SELECT DISTINCT
NAME
FROM
NAMES2023
WHERE
NAME LIKE '%eigh%'OR: Find me all names, removing duplicates that contain the string “eigh.” This SQL yields the most pumpkin-spiced latte, flannel-loving, “live, laugh, love” names you’ve ever seen this side of the Mississippi River! People, we really need to put an end to this 😑.
Forcing a 401 Response Status Code
And in case you think I’m lying, set Auth Type to “No Auth” and reattempt the GET request. You should see a 401 Unauthorized Response Status Code. If you don’t, “Houston, we have a problem!“
The end
I think this could help many people, so if you have made it this far, please share and bookmark it for later use. I’m also including the PDF version of the images. I didn’t include notes but placed the arrows and boxes with the utmost precision.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!