E2E Identity Propagation → ADB (`oracle-database`)¶

Folder: user_e2e_token_exchange_adb_dbtoken/

The token_factory target_tool="oracle-database" reference. A user signs in via IDCS OAuth2, and that same identity — not a shared service credential — is propagated through a FastAPI backend, to mcp_adw, into a scoped Oracle Database token, and finally into the database session itself. Oracle's own Virtual Private Database (VPD) then enforces row-level access based on the caller's actual enabled database roles, derived from IDCS group membership.

Architecture¶

Browser ──► FastAPI backend (port 8000)
                 │  OAuth2 authorization code flow (IDCS), CSRF-protected
                 │  Proxies tool calls with BearerAuth
                 ▼
         MCP ADW server (port 8003)
                 │  OCITokenAuthMiddleware validates JWT (JWKS) before any tool runs
                 │  query_adw / whoami read the token from the verified auth context
                 │  TokenFactory.get_token(token, 'oracle-database') → scoped DB token
                 ▼
         OCI Autonomous Database
                 │  externalauth=True session, identity-propagated (no app credential)
                 │  VPD policy (DBMS_RLS) filters rows by the caller's enabled Oracle role
                 ▼
         IAM_ROLE_USER.AP_SUPPLIERS_SLIM / AP_INVOICES_SLIM

Token Flow¶

User clicks Login → /login issues a single-use, random state value and redirects to IDCS /oauth2/v1/authorize
IDCS authenticates user → redirects to /callback?code=...&state=...
Backend rejects the callback if state doesn't match (CSRF protection), then exchanges code for access_token
Backend redirects to /#access_token=<token> (URL fragment, not a query string); the UI stores it in localStorage
UI attaches Authorization: Bearer <token> to all API requests, and calls /whoami to populate the identity/role banner
Backend creates a FastMCP Client with BearerAuth(token) → calls an MCP tool on mcp_adw
mcp_adw's OCITokenAuthMiddleware validates the JWT against the Identity Domain's JWKS before any tool runs; the tool reads the verified token from the auth context (never a client-supplied argument) → TokenFactory.get_token(token, 'oracle-database') returns a scoped DB token → the database session opens externalauth=True under that identity
The database itself — not the application — decides which rows come back, via the VPD policy below

from auth.token_factory import TokenFactory

database_scope = "urn:oracle:db::id::*"
factory = TokenFactory()
db_token, session_private_key_pem, expires_in, token_type, issued_token_type = factory.get_token(
    token,
    "oracle-database",
    scope=database_scope,
)

Features (UI)¶

Identity + role banner — after login, the header shows Logged in as <name> (<email>) — Roles: <roles>, where <roles> are the actual Oracle roles enabled in the propagated DB session (GET /whoami), not just IDCS token claims. This is the most direct way to see VPD enforcement is wired correctly for the logged-in user.
Query ADW — runs a fixed sample query through mcp_adw; the rows returned depend on the caller's enabled role (VPD).

VPD Policies (Row-Level Security)¶

setup_vpd.sql configures Oracle Virtual Private Database (VPD, via DBMS_RLS) on IAM_ROLE_USER.AP_SUPPLIERS_SLIM and IAM_ROLE_USER.AP_INVOICES_SLIM. VPD lets Oracle transparently rewrite every query against a protected table to add a WHERE predicate decided by a PL/SQL function — the application never sees or controls that predicate.

How the identity chain reaches VPD¶

VPD's predicate function only knows about the current database session — it has no idea about IDCS, OAuth, or bearer tokens. What connects a browser login to a VPD decision:

IDCS group membership — a user is assigned to an Identity Domain group (AP_Clerk, AP_Manager, or Financial_Manager).

Globally identified Oracle roles, bound 1:1 to those groups:

CREATE ROLE ap_clerk_role         IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=OracleIdentityCloudService/AP_Clerk';
CREATE ROLE ap_manager_role       IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=OracleIdentityCloudService/AP_Manager';
CREATE ROLE financial_manager_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=OracleIdentityCloudService/Financial_Manager';

Oracle enables the role automatically based on the connecting identity's group membership — no local password or explicit GRANT ROLE TO user.

Identity-propagated DB session — when mcp_adw connects with externalauth=True using the TokenFactory-issued, per-user scoped DB token, Oracle authenticates the session as that specific IDCS-mapped identity and enables whichever globally-identified roles map to it.

VPD policy function checks which role is enabled for this session and returns a predicate:

CREATE OR REPLACE FUNCTION admin.ap_suppliers_vpd_func(p_schema IN VARCHAR2, p_object IN VARCHAR2) RETURN VARCHAR2
  AUTHID CURRENT_USER IS
BEGIN
  IF DBMS_SESSION.SESSION_IS_ROLE_ENABLED('FINANCIAL_MANAGER_ROLE') THEN
    RETURN '1=1';              -- full visibility
  ELSIF DBMS_SESSION.SESSION_IS_ROLE_ENABLED('AP_MANAGER_ROLE') THEN
    RETURN 'VENDOR_ID < 8';    -- partial visibility
  ELSIF DBMS_SESSION.SESSION_IS_ROLE_ENABLED('AP_CLERK_ROLE') THEN
    RETURN 'VENDOR_ID < 5';    -- narrower visibility
  ELSE
    RETURN '1=0';              -- no rows
  END IF;
END;

DBMS_RLS.ADD_POLICY attaches that function to the table for SELECT statements — from that point on, every SELECT against the protected tables is silently rewritten with the returned predicate, for every caller, every time.

Row-level vs. column-level¶

What's implemented is row-level security — VPD decides which rows a query returns, but every column of an accessible row is still fully visible. Oracle also supports column-level enforcement, which setup_vpd.sql doesn't currently use:

DBMS_RLS.ADD_POLICY's sec_relevant_cols / sec_relevant_cols_opt parameters restrict the predicate to apply only when specific columns are referenced.
DBMS_REDACT (Oracle Data Redaction) masks specific column values independently of row visibility.

Protecting specific sensitive columns rather than whole rows means adding a DBMS_REDACT policy or a sec_relevant_cols-scoped VPD policy — the same DBMS_SESSION.SESSION_IS_ROLE_ENABLED() check works for either.

Applying it

Run setup_vpd.sql as ADMIN. The DROP ROLE/DROP TABLE vpd_debug statements at the top aren't exception-wrapped — on a first run against a fresh database they'll print harmless "does not exist" errors and SQLcl will continue. Verify by logging in as users in different IDCS groups and comparing the Roles: banner and the row counts/VENDOR_ID ranges returned.

When to use: Any enterprise application requiring auditable, user-specific data access to Oracle Database through a multi-tier stack.

E2E Identity Propagation → ADB (oracle-database)¶