Auth0 + ORDS on ADB – Playbook

Step-by-Step Tutorial: Configure Auth0, Create an ORDS JWT Profile, and Secure an Endpoint

[ there is a companion GitHub to this post ]

This is the hands-on companion to the architecture write-up. If you’ve read that one, you already know the shape of what we’re doing: Auth0 issues a token, ORDS validates it, ADB serves the protected data. Here we’re actually going to build it.

The goal is simple — start with an empty Auth0 API and end with a protected ORDS endpoint returning 200 OK from Autonomous Database. I’ll flag the spots where I got stuck the first time so you (hopefully) don’t have to.

What You’ll End Up With

By the end of this tutorial you’ll have:

an Auth0 tenant issuing an RS256 access token
ORDS validating that token using a schema-level JWT profile
a token carrying the scope read:demo
an ORDS module protected by a privilege called read:demo
an endpoint returning 200 OK when you call it with the token

If something’s off at the end, it’s almost always one of those five lines that doesn’t match what you actually built. Worth bookmarking.

Prerequisites

You’ll need an Auth0 tenant, an ADB schema that’s already ORDS-enabled (or one you can enable), and the ability to run SQL as the schema owner. You’ll also need ORDS_SECURITY_ADMIN access if you’re configuring the JWT profile for a schema other than your own — more on that when we get there.

Step 1 — Create the Auth0 API

In Auth0, create an API to represent the ORDS-backed resource server. Use:

Name: ORDS Demo API
Identifier: https://api.example.com/ords-api
Signing Algorithm: RS256

The API Identifier becomes the JWT aud claim, and this is the part that confused me at first — it’s not the ORDS endpoint URL. It doesn’t need to resolve on the network. It just needs to be a stable identifier that Auth0 puts in the token and ORDS is told to expect. They have to match exactly; beyond that, the string itself doesn’t matter.

Before leaving the API setup, add the permission read:demo. That name matters because we’re using a scope-based ORDS JWT profile, and we’ll reuse the exact same string on the ORDS side later.

Step 2 — Create the Machine-to-Machine Application

Next, create a Machine-to-Machine application in Auth0 and authorise it for the API you just made.

When authorising, make sure the application is allowed to use client_credentials and that you’ve granted it the read:demopermission on the API. There’s no end user in this flow — it’s the application itself that holds credentials and asks for tokens.

Once it’s created, grab four things and keep them somewhere handy:

Auth0 domain
client ID
client secret
audience (which is the API Identifier from Step 1)

If Auth0’s UI offers both user-oriented and client-oriented access when you authorise the app, you want the client-oriented path. Again, there’s no user in this flow.

In all of these, double-check the strings that you paste into auth0 for following spaces – it accepts and preserves following space, but then everything invisibly fails. This lost me about half a day of debugging.

Auth0 Terms vs ORDS Terms

These two worlds use different vocabulary for the same things, which is a big part of why the integration feels harder than it actually is. Here’s the mapping I find useful to keep on a sticky note:

Auth0 tenant domain → ORDS JWT issuer host and JWKS host
Auth0 API Identifier → JWT aud claim → ORDS expected audience
Auth0 API permission/scope read:demo → ORDS privilege name read:demo
Auth0 Machine-to-Machine application → the client (the app) that requests the token
Auth0 access token → the bearer token presented to ORDS

The two that bite people hardest are the audience (which isn’t your ORDS URL) and the scope-to-privilege match (which has to be identical, not just close – watch for following spaces).

Step 3 — Know What The Token Should Look Like

Before you request anything, it helps to know what you’re expecting so you can spot when something’s off. The token claims should come out looking roughly like this:

iss = https://<tenant>.uk.auth0.com/
aud = https://api.example.com/ords-api
scope = read:demo

Three small things that matter: the trailing slash on the issuer is real and has to match on the ORDS side, and this must be an access token, not an ID token. Client credentials gets you an access token by default, but it’s worth knowing in case you end up debugging a token from somewhere else.

Finally for the “its” you will need the tenant address – this is essentially the host name that ORDS will use to retrieve the private key – so this has to be exactly correct. Watch out that you get the country correct, as I missed this on first try – sorry the uk, there is a “.uk.” in the middle – check what your country requires. Incidentally, I use the UK servers as this guarantees that all the credentials & config never leave the UK.

Step 4 — Set Up Your Local Variables

Create a local .env file so you’re not pasting long strings all day:

			
AUTH0_DOMAIN=<tenant>.auth0.com
AUTH0_CLIENT_ID=<client-id>
AUTH0_CLIENT_SECRET=<client-secret>
AUTH0_AUDIENCE=https://api.example.com/ords-api
ORDS_BASE_URL=https://<your-ords-host>/ords
ORDS_SCHEMA=<your-rest-enabled-schema-base-path>
ORDS_PATH=oauth-demo/status

		

If your schema is already ORDS-enabled, use the existing base path for ORDS_SCHEMA. Don’t assume it’s demo — it probably isn’t.

Step 5 — Create the Demo Data

Something trivial to serve behind the endpoint. A tiny table, a couple of rows:

			
BEGIN
  EXECUTE IMMEDIATE q'[
    create table demo_secure_messages (
      id         number generated by default on null as identity primary key,
      message    varchar2(200 char) not null,
      created_at timestamp with time zone default systimestamp not null
    )
  ]';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN
      RAISE;
    END IF;
END;
/
MERGE INTO demo_secure_messages target
USING (
  SELECT 1 AS id, 'Auth0 + ORDS demo is reachable.' AS message FROM dual
  UNION ALL
  SELECT 2 AS id, 'This endpoint is intended to be protected by an ORDS privilege.' AS message FROM dual
) source
ON (target.id = source.id)
WHEN MATCHED THEN
  UPDATE SET target.message = source.message
WHEN NOT MATCHED THEN
  INSERT (id, message)
  VALUES (source.id, source.message);
COMMIT;

		

The EXCEPTION block swallows ORA-00955 so you can re-run it without babysitting.

Step 6 — Create the ORDS Module

One module, one template, one GET handler:

			
BEGIN
  ORDS.DEFINE_MODULE(
    p_module_name    => 'oauth_demo.secured',
    p_base_path      => 'oauth-demo/',
    p_items_per_page => 0,
    p_status         => 'PUBLISHED'
  );
  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'oauth_demo.secured',
    p_pattern     => 'status'
  );
  ORDS.DEFINE_HANDLER(
    p_module_name    => 'oauth_demo.secured',
    p_pattern        => 'status',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => q'[
      select
        id,
        message,
        to_char(
          created_at,
          'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
        ) as created_at
      from demo_secure_messages
      order by id
    ]'
  );
  COMMIT;
END;
/

		

At this point the endpoint exists but it’s wide open. We’ll fix that next.

Step 7 — Create the ORDS Privilege

This is the step I’d underline twice if I could. For a scope-based JWT profile, the ORDS privilege name has to match the Auth0 scope in the token exactly.

			
BEGIN
  ORDS.DEFINE_PRIVILEGE(
    p_privilege_name => 'read:demo',
    p_roles          => owa.vc_arr(),
    p_patterns       => owa.vc_arr(),
    p_modules        => owa.vc_arr(),
    p_label          => 'Demo Read Privilege',
    p_description    => 'Protects the demo module for scope-based JWT access.'
  );
  ORDS.SET_MODULE_PRIVILEGE(
    p_module_name    => 'oauth_demo.secured',
    p_privilege_name => 'read:demo'
  );
  COMMIT;
END;
/

		

If the Auth0 token says read:demo but your privilege is called oauth_demo.read or demo_read or anything else that looks semantically similar, the endpoint will refuse you — and it won’t give you a hint as to why. This is where I burned an afternoon when I first built this.

Step 8 — Create the Schema JWT Profile

For current ORDS releases, JWT profile management lives in ORDS_SECURITY or ORDS_SECURITY_ADMIN. Older tutorials using OAUTH_ADMIN for this are working off legacy patterns — I’d skip them.

If you’re configuring a profile for another schema (the normal case if you’re following this end to end), you’ll use ORDS_SECURITY_ADMIN:

			
BEGIN
  BEGIN
    ORDS_SECURITY_ADMIN.DELETE_JWT_PROFILE(
      p_schema => 'SIMON'
    );
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  ORDS_SECURITY_ADMIN.CREATE_JWT_PROFILE(
    p_schema          => 'SIMON',
    p_issuer          => 'https://<tenant>.auth0.com/',
    p_audience        => 'https://api.example.com/ords-api',
    p_jwk_url         => 'https://<tenant>.auth0.com/.well-known/jwks.json',
    p_description     => 'Auth0 JWT trust profile for the ORDS demo',
    p_allowed_skew    => 30,
    p_allowed_age     => 3600,
    p_role_claim_name => NULL
  );
  COMMIT;
END;
/

		

A few things to double-check before you move on:

the issuer includes the trailing slash if the token does (and with Auth0, it does)
the audience matches the API Identifier from Step 1 exactly
the JWK URL is actually reachable from ORDS

If your environment uses pool-level JWT profiles instead of schema-level, you’d configure the equivalent trust in the ORDS pool using the security.jwt.profile.* settings. The trust values are the same — just the place you put them changes.

Step 9 — Request a Token

Now for the moment of truth. Use the client credentials flow:

			
curl --request POST \
  --url https://<tenant>.auth0.com/oauth/token \
  --header 'content-type: application/json' \
  --data '{
    "client_id":"...",
    "client_secret":"...",
    "audience":"https://api.example.com/ords-api",
    "grant_type":"client_credentials"
  }'

		

Or use the helper script if you’ve got it:

			
set -a
. ./.env
set +a
./scripts/get-token.sh --token-only

Either way, you should get a bearer access token back.

Step 10 — Decode the Token (Before Blaming ORDS)

If anything goes wrong from here on, I’d really encourage you to actually look at the token before assuming the ORDS side is broken. I didn’t, for longer than I should have, and it cost me.

./scripts/decode-jwt.sh "$ACCESS_TOKEN"

Check the issuer, the audience, the scope, and the signing algorithm (alg should be RS256). If any of those don’t match what you told ORDS to expect in Step 8, that’s your problem.

One thing to note: the decode helper is just for inspection. It’s not validating the signature — ORDS will do that for real.

Step 11 — Call the Secured Endpoint

Finally, call the endpoint:

			
curl -H "Authorization: Bearer <access_token>" \
  https://<ords-url>/ords/<schema>/oauth-demo/status

Or with the helper:

			
set -a
. ./.env
set +a
ACCESS_TOKEN="$(./scripts/get-token.sh --token-only)"
./scripts/call-secure-endpoint.sh "$ACCESS_TOKEN"

		

If everything lined up, you’ll get 200 OK and the two rows from demo_secure_messages. That’s the finish line.

Troubleshooting

If you didn’t get 200 OK, here’s where I’d look.

`401 Unauthorized`

The token wasn’t trusted. Usual suspects, in rough order of how often they bit me:

issuer mismatch (often the trailing slash)
audience mismatch
wrong JWKS URL, or one ORDS can’t reach
invalid signature
expired token
an ID token got used instead of an access token
the JWT profile was configured in the wrong place (pool vs schema, or the wrong schema)

`403 Forbidden`

The token was trusted, the resource is protected, but ORDS decided this particular token isn’t allowed in. In a scope-based model, that almost always means the scope in the token and the privilege name on the ORDS side aren’t actually identical.

The Mistake That Catches Almost Everyone

Worth calling out on its own:

Auth0 issues the scope read:demo
ORDS protects the module with a privilege called oauth_demo.read

To a human, those look close enough to be the same thing. To ORDS, they’re different strings. The fix is to make them identical — this is also why I suggested picking the privilege name up front and reusing it everywhere.

Wrapping Up

The working path is genuinely straightforward once the pieces line up:

the Auth0 API Identifier becomes the JWT audience
Auth0 issues the scope read:demo
ORDS trusts the Auth0 issuer through a JWT profile
ORDS protects the module with a privilege also called read:demo

Get those four lined up and Auth0, ORDS, and ADB cooperate cleanly using standard OAuth2 and JWT — no extra glue.

Simon Griffiths