ADW MCP Server

Module: inception_mcp_servers/mcp_adw/
Endpoint: http://127.0.0.1:8003/mcp/
Health: http://127.0.0.1:8003/health
Venv: .venv_mcp_adb

Exposes Oracle Autonomous Data Warehouse (ADW) query operations as an MCP tool. Unlike the SQLcl server (which shells out to a subprocess), this server connects directly to ADW using the oracledb Python driver with wallet-based mTLS — the caller's IDCS identity propagates all the way through to the database audit trail.

---

Exposed Tools

Controlled by TOOL_REGISTRY in registry.py:

Tool	Parameters	Description
query_adw	select_statement: str bind_params: dict = None token: str = None	Executes a SELECT query on ADW with IAM token authentication. Returns row results as JSON.

No resources exposed

RESOURCE_REGISTRY is defined in registry.py as a placeholder but currently empty.

---

Architecture

MCP Client
  │
  ▼ HTTP POST /mcp/  (stateless — no session state between calls)
┌─────────────────────────────────────────┐
│              server.py                  │
│  FastMCP (FASTMCP_STATELESS_HTTP=True)  │
└──────────┬──────────────────────────────┘
           │ routes to query_adw
           ▼
┌─────────────────────────────────────────┐
│              tools.py                   │
│  TokenHandlerIAM                        │
│  ┌──────────────────────────────────┐   │
│  │ 1. Decode JWT claims (log only)  │   │
│  │ 2. POST to TOKEN_EXCHANGE_URL    │   │  ← retry up to 3× (1s, 2s, 4s backoff)
│  │ 3. Receive DB token + private key│   │
│  └──────────────────────────────────┘   │
└──────────┬──────────────────────────────┘
           │ oracledb.connect (mTLS + externalauth=True)
           ▼
┌─────────────────────────────────────────┐
│       Oracle Autonomous Data Warehouse  │
│       (wallet mTLS, no password auth)   │
│       IAM audit trail preserved         │
└─────────────────────────────────────────┘

Key design choices

Choice	Rationale
Stateless HTTP (FASTMCP_STATELESS_HTTP=True)	Matches the token-per-request auth model; no shared session risk
externalauth=True in oracledb	No username/password in connection — identity comes from the exchanged DB token
Per-call DB connection	Avoids session state leaking across caller identities
Token exchange retry (3×, exponential backoff)	Handles transient IAM endpoint failures gracefully

---

IAM Token Exchange Flow

The ADW server uses a two-stage token flow unique to this module:

Stage 1: IDCS Authentication
  MCP Client holds an IDCS OAuth access token
  (obtained via OCI IAM login, stored in MCP_ACCESS_TOKEN)

Stage 2: Database Token Exchange
  TokenHandlerIAM POSTs the access token to TOKEN_EXCHANGE_URL
  Response: { "dbToken": "...", "privateKey": "..." }

Stage 3: ADW Connection
  oracledb.connect() with:
    - wallet_location = IAM_WALLET_DIRECTORY
    - wallet_password = IAM_DB_WALLET_PASSWORD
    - externalauth = True
    - token = dbToken
    - private_key = privateKey
    - ssl_server_dn_match = True

The caller's identity is embedded in the DB token — Oracle ADW records it in the audit trail. No shared service account credentials are used.

Token expiration detection

TokenHandlerIAM detects expired sessions by inspecting the token exchange response body for HTML login forms (a pattern returned by some token exchange services when the session has lapsed). On detection, it raises TokenExpiredError and the caller receives a clear error rather than a failed query.

---

Authentication

This server does not use server_oidc.py / server_token.py. It runs a single entrypoint (server.py) and authentication is caller-supplied via the token parameter in query_adw:

• The caller obtains an IDCS OAuth access token out-of-band (e.g., via the SQLcl or OS server's OIDC flow, or by calling the token endpoint directly)
• The token is passed as token in the tool call — not in the HTTP Authorization header
• TokenHandlerIAM in tools.py handles the exchange; no JWT signature verification is performed server-side

Note

JWT claims are decoded locally for logging only (verify_signature=False). The token exchange service at TOKEN_EXCHANGE_URL is responsible for validating the IDCS token and issuing the DB credentials.

---

Configuration

# .env  (see sample.env for full template)

# MCP Server
MCP_HOST_ADB=127.0.0.1
MCP_PORT_ADB=8003

# IDCS / OCI IAM
IAM_IDCS_CLIENT_ID=
IAM_IDCS_CLIENT_SECRET=
IAM_IDCS_AUTHORIZE_URL=
IAM_IDCS_TOKEN_URL=
IAM_IDCS_REDIRECT_URI=
IAM_IDCS_SERVER_METADATA_URL=
IAM_IDCS_JWKS_URL=

# OCI / ADW
IAM_ADW_COMPARTMENT_ID=
IAM_ADW_DATABASE_ID=
IAM_OCI_REGION=
IAM_USE_PRINCIPALS=false      # false = token propagation, true = instance principals

# Database wallet & connection
IAM_DB_USERNAME=
IAM_DB_PASSWORD=
IAM_DB_DNS_NAME=
IAM_WALLET_DIRECTORY=/path/to/wallets/Wallet_<db>
IAM_DB_WALLET_PASSWORD=

# Token exchange service
TOKEN_EXCHANGE_URL=
TOKEN_EXCHANGE_TIMEOUT_SECONDS=60

# API Gateway (if used)
IAM_API_GATEWAY_URL=

---

Running the Server

cd inception_mcp_servers/mcp_adw

# First-time setup
.venv_mcp_adb/bin/python -m pip install -r requirements.txt

# Start the server
.venv_mcp_adb/bin/python -m src.server

Free the port if needed:

lsof -ti tcp:8003 | xargs kill -9

VM / production deployment

mkdir -p logs
nohup .venv_mcp_adb/bin/python -m src.server > logs/mcp_adw.log 2>&1 &

# Find and stop
ps aux | grep src.server
kill <pid>

Open the firewall port on a VM:

sudo firewall-cmd --add-port=8003/tcp --permanent && sudo firewall-cmd --reload

---

Oracle Wallet Setup

Wallet files are downloaded from the OCI Console → Autonomous Database → DB Connection.

wallets/
└── Wallet_<db_name>/
    ├── sqlnet.ora         ← SSL wallet path config
    ├── tnsnames.ora       ← TNS connection descriptors
    ├── cwallet.sso
    ├── ewallet.p12
    └── *.cer              ← certificates

Set IAM_WALLET_DIRECTORY to the absolute path of the wallet folder. sqlnet.ora must point to the same directory.

---

Tool Response Format

{
  "success": true,
  "data": [
    {"EMPLOYEE_ID": 100, "LAST_NAME": "King", "HIRE_DATE": "1987-06-17T00:00:00"}
  ],
  "rowCount": 1
}

Type coercions applied automatically: - datetime → ISO 8601 string - timedelta → integer days - oracledb.LOB → string (.read())

On error:

{
  "success": false,
  "error": "<error message>"
}

---

Python Setup (Linux VM)

If building Python 3.13 from source on Oracle Linux:

sudo dnf install -y openssl-devel bzip2-devel libffi-devel sqlite-devel \
  ncurses-devel readline-devel zlib-devel expat-devel
sudo dnf groupinstall -y "Development Tools"

wget https://www.python.org/ftp/python/3.13.7/Python-3.13.7.tgz
tar -xzf Python-3.13.7.tgz
cd Python-3.13.7
./configure --enable-optimizations --enable-loadable-sqlite-extensions
make -j$(nproc)
sudo make altinstall

# Create venv
cd inception_mcp_servers/mcp_adw
/usr/local/bin/python3.13 -m venv .venv_mcp_adb
.venv_mcp_adb/bin/pip install --upgrade pip
.venv_mcp_adb/bin/pip install -r requirements.txt

---

Planned Deprecations

The following query_adw parameters will be removed once DB token propagation matures:

Parameter	Reason
bind_params	DB token will handle parameterization at the driver level
groups	DB token propagates end-user identity; app-level group handling not needed
test_type	Test scaffolding; not needed in production

DB token work is tracked in the dbtoken_feature branch of the research repo.