Key OAuth Flows: Powering Secure Oracle Database Access with Microsoft Entra ID

Introduction
In an AI-driven world, data fuels insights, but only if it’s accessed securely. Traditional, password-based database connections can’t keep up with modern demands for just-in-time access, delegated permissions, and multi-cloud identity. Modern enterprises are unifying identity across every tier, including their databases, by treating Oracle Database as an OAuth-protected resource via Microsoft Entra ID.

Depending on your scenario, you can choose from one of the following OAuth2 flows:

1. Human Interactive Flows (MFA supported)

• Authorization Code: Interactive, browser-based login where end users sign in via Entra ID and get a token the app uses to fetch data for them
• Device Code: Interactive login for CLI tools or locked-down hosts without a browser; the user completes MFA on their phone or secondary device to approve access.

2. Application (“Headless”) Flows

• Client Credentials: Automated, background access. Services or daemons authenticate themselves (no human involvement) to Entra ID using client ID and secret, which are recommended to be stored securely in an Oracle Wallet. Applications themselves can obtain the token and pass it through the driver API, or the driver can fetch it directly from Entra ID.
• Managed Identity: For apps running on Azure compute (VMs, Functions), the service uses its assigned identity to get a token without storing any secrets.

3. On-Behalf-Of (OBO)

• Multi-tier delegation: A front-end app obtains a user token, then exchanges it for a second token scoped for the database, ensuring the user’s identity and permissions propagate securely end-to-end.

Below, you’ll find real-world scenarios for each flow, including which Users, Groups, App Roles, Schemas, and Database Roles you need to provision on both Entra and Oracle sides.

#. Authorization Code Flow

Use-Case & Scenario

Imagine the Operations team at Acme Corp. Two groups, Operations Analysts and Operations DBAs, both use SQL Developer to work with the same Oracle schema, but each needs a different level of access:

• Operations Analysts run day-to-day queries against inventory and supply-chain tables, tweak non-critical configuration settings, and build “what-if” models. They require read/write access to the OPS_USERS schema.
• Operations DBAs perform occasional administrative tasks :- creating indexes, managing grants, tuning performance – so they need the Analysts’ CRUD rights plus full DBA privileges.

When either group opens SQL Developer and selects Entra ID authentication, they sign in with their corporate credentials, and complete MFA. Entra ID issues an OAuth access token that carries their group and app-role claims. Oracle reads those claims and maps:

• Analysts → OPS_USERS schema with read/write access
• DBAs → OPS_USERS schema and the DBA role

This approach provides seamless single sign-on, just-in-time privilege elevation (no separate DBA account to remember), and end-to-end auditability; every query and administrative action is logged against the real Entra ID user.

Why It Matters

• Seamless SSO: Analysts and DBAs use the same familiar Entra ID credentials – no extra database passwords to manage.
• Dynamic Privilege Elevation: Analysts get just CRUD; DBAs get CRUD + DBA without reconnecting to a different account.
• Audit & Compliance: Oracle SYS_CONTEXT captures exactly which Entra ID user performed queries, DDL, or admin tasks, satisfying governance requirements.

#. Device Code Flow

Use-Case & Scenario

Your on-call DBA team occasionally needs to jump onto a locked-down bastion host (no browser installed) to run urgent health-check queries and diagnostic scripts against Oracle. They fire up SQLcl (configured for Entra ID), type a special –devicecode flag, and get back a short code plus a URL:

$ sql /@prod –devicecode
Visit: https://microsoft.com/devicelogin and enter code: X1Y2Z3

They grab their phone, navigate to that page, enter corporate credentials and MFA, and approve the request. Meanwhile, SQLcl polls Entra ID; as soon as the device code is confirmed, it receives an OAuth token and connects to Oracle as DIAG_CLI (sample shared schema), granting only the minimal diagnostic rights; no passwords, no SSH key swaps.

#. Client Credentials Flow

Use-Case & Scenario

Automated Reporting Service
In many enterprises, critical reporting jobs run during off-hours without anyone sitting at a keyboard. Imagine an Enterprise_Reporting_App, for example: it triggers each morning, pulls the previous day’s sales, inventory, and financial figures from your Oracle database, loads them into an analytics warehouse, and delivers PDF dashboards to the leadership team. Because no human is present to sign in, this service relies on the OAuth2 Client Credentials flow for all its authentication.

Here’s how it works in practice: The reporting service uses its own Entra ID client ID and client secret to request a token from Entra ID’s /token endpoint. Entra ID validates those credentials and returns an access token scoped specifically for the Enterprise_Reporting_App. The service then hands that token to Oracle and behind the scenes, Oracle maps the token’s app-role claim to a database role that grants exactly the permissions needed (for example, SELECT on the REPORTING_* tables and EXECUTE on stored-procedure pipelines), and nothing more.

Why It Matters

• No embedded credentials: All your code and config files stay free of static passwords, just short-lived OAuth tokens that Entra ID rotates and manages.
• Least-privilege enforcement: The service principal maps to a database role granting only the exact SELECT and EXECUTE rights it needs – nothing more.
• Instant revocation: If the job ever needs to be retired or its credentials compromised, you disable the service principal and its database access stops immediately.

#. Managed Identity Flow

Use-Case & Scenario

Imagine your company has an Azure-based ETL that runs on Azure compute nodes and has access to the managed identity for the node. This service performs hourly loads from various SaaS systems, cleans the data, and writes it into your Oracle Database.

Because it’s a fully automated Azure-hosted service, there’s no user to log in, and you don’t want to store any secrets like client IDs or passwords. Instead, you assign it an Azure Managed Identity.

When the service runs:

• It requests an OAuth2 token from Microsoft Entra ID using its system-assigned managed identity.
• Entra ID returns a token scoped specifically for database access, proving the Azure resource’s identity.
• The service uses this token to connect to Oracle via JDBC Thin driver, ODP.NET Core Managed, or ORDS.
• Oracle validates the token, checks the app role, and grants only the minimal rights needed to load data.

This pattern is perfect for secure, cloud-native pipelines with no embedded credentials.

Why It Matters

• Secretless Authentication: No need to manage or rotate passwords or client secrets; authentication is tied to the Azure resource itself.
• Azure-Native Security: Leverages Azure’s built-in managed identity lifecycle and policy controls.
• Operational Simplicity: Works seamlessly with Azure Functions, VMs, Kubernetes Pods using system- or user-assigned identities.

#. On-Behalf-Of (OBO) Flow

Use-Case & Scenario

Picture your organization using Microsoft Power BI Service to deliver interactive dashboards and self-service reporting.

When users open Power BI, they authenticate with their own Azure Entra ID credentials i.e. with their corporate accounts.

When the user accesses the Oracle database, Power BI exchanges the initial access token for an “OBO” token, which is then sent to the database to allow user access to the database as themselves, not the Power BI service. This allows the same privileges/roles to manage data access centrally instead of relying on a separate authorization model in Power BI to filter the data. This is the “On-Behalf-Of” (OBO) flow in action:

• Power BI gets the user’s database-scoped token when establishing the database connection.
• It forwards that user token to Oracle Database rather than a service credential.
• Oracle Database validates the token (using Entra ID’s public key) and maps the user’s Azure role claims to database users and roles

Why It Matters

• True User Context: The database sees and enforces access for John or Lisa, not a generic “Power BI” service.
• Simplified Security Model: No need for complex row-level security rules in Power BI to mimic database policies.

Final Thoughts

Adopting OAuth2-protected Oracle access via Microsoft Entra ID isn’t just a shiny new feature, it fundamentally raises your security posture and lays the groundwork for a true multi-cloud identity strategy:

• Centralized credential management
  No more scattered passwords or vaults. Every token is issued, rotated, and revoked by Entra ID, reducing the risk of leaked or stale secrets across on-prem and cloud boundaries.
• Short-lived, scoped tokens
  Instead of long-lived database accounts, you get ephemeral, least-privilege tokens. Even if a token is compromised, its lifetime and scope limit the blast radius.
• End-to-end identity propagation
  With flows like OBO, the very same user identity travels from the browser to microservices to the database, ensuring precise, per-user audit trails and eliminating opaque “service account” accesses.
• Multi-cloud readiness
  By decoupling Authentication and Authorization from any single infrastructure, you can extend this model to AWS, GCP, or hybrid environments, using OCI or Azure as your universal identity plane.