mcp-oracle-dba

A Model Context Protocol (MCP) server for Oracle Database — read-only, audited, and SQL-guarded. Lets Claude Desktop, Claude Code, Cursor, or any MCP client query your Oracle database safely.

Built by an Oracle Apps DBA. Designed so an LLM can explore production data without ever being able to mutate it.

In the screenshot above, Claude (via this MCP server) successfully runs discovery + a real SELECT over my Oracle 23ai database — and is then refused when it tries to DROP TABLE. Every call is recorded in the audit log.

Why this exists

Most "let your LLM query the database" demos are unsafe by default: they give the LLM a connection string and trust it not to send DROP TABLE. This server flips that model. The LLM gets a narrow, explicit toolset, every call is parsed against a multi-layer SQL guardrail, the result rows are PII-redacted, and every call is audit-logged.

If the LLM hallucinates DROP TABLE users while debugging a slow query, the server refuses before the SQL ever reaches Oracle.

Tools exposed

Core (5 tools, always enabled):

AWR / ASH (5 tools, gated behind MCP_ENABLE_AWR=true):

AWR/ASH tools require Oracle Diagnostic Pack licensing on Standard Edition and Enterprise Edition production databases. Oracle Database Free Edition (23ai) includes the diagnostic features for development use. Set MCP_ENABLE_AWR=true in .env to expose these tools.

Security model (defense in depth)

Five independent layers — any one of them rejects unsafe input before it reaches the database:

1. Single-statement parser: rejects ... ; DROP TABLE x injection.

2. First-keyword allowlist: only SELECT and WITH accepted.

3. Banned-keyword scan: blocks INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, CREATE, ALTER, GRANT, REVOKE, BEGIN, DECLARE, EXECUTE, CALL, COMMIT, ROLLBACK, SAVEPOINT, LOCK, RENAME, FLASHBACK — anywhere in the statement.

4. Dangerous-package regex: blocks any call into DBMS_*, UTL_*, or SYS.* (think DBMS_LOCK.sleep, UTL_HTTP.request, UTL_FILE.fopen).

5. Row cap: every approved query is wrapped in SELECT * FROM (...) FETCH FIRST :max_rows ROWS ONLY.

Plus:

• Read-only DB user (mcp_ro): zero INSERT/UPDATE/DELETE privileges at the SQL layer. The guardrails are belt-and-suspenders on top of this.

• Schema allowlist for describe_table: only configured schemas are introspectable.

• PII redaction: column names matching SSN, SALARY, TAX_ID, PASSWORD, etc., are auto-replaced with [REDACTED] in returned rows.

• Statement timeout: enforced server-side via oracledb's call_timeout.

• Audit log: every tool call (including rejections) emits a JSON line to MCP_AUDIT_LOG (default ./audit.log).

The guardrails come with 45 security tests (pytest tests/) — every test represents a real attack vector explicitly blocked.

Quickstart

Prerequisites

• Python 3.12+

• uv: brew install uv

• An Oracle database with a read-only user

• Optional: an MCP client (Claude Desktop, Claude Code, Cursor)

1. Clone + install

git clone https://github.com/shopsmartai/mcp-oracle-dba.git
cd mcp-oracle-dba
uv sync

2. Configure environment

cp .env.example .env
# Edit .env — set ORA_USER, ORA_PASSWORD, ORA_DSN

ORA_DSN examples:

• localhost:1521/FREEPDB1 — local Oracle 23ai Free

• oracle23ai.orb.local:1521/FREEPDB1 — OrbStack on macOS, when running the server from a normal terminal (avoids port-forwarding NAT issues that mangle TNS handshakes)

• 192.168.215.2:1521/FREEPDB1 — OrbStack container direct IP, required when this MCP server is launched by Claude Desktop or any sandboxed macOS app. Sandboxed child processes do not have access to OrbStack's .orb.local DNS resolver — the connection fails with DPY-6005 / No route to host. Use docker inspect oracle23ai --format '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' to get the IP.

• prod-db.example.com:1521/PRODPDB — production (use a read-only user!)

3. Run the tests (security check)

uv run pytest tests/ -v

You should see 45 passing. Every test maps to a real attack vector — DDL, DML, multi-statement injection, dangerous package calls, etc.

4. Smoke test

uv run python -c "
from mcp_oracle_dba.server import list_schemas, run_select
print('Schemas:', list_schemas())
print(run_select('SELECT user FROM dual'))
"

5. Wire to Claude Desktop

Add to ~/Library/Application Support/Claude/claude_desktop_config.json (macOS) or %APPDATA%\Claude\claude_desktop_config.json (Windows):

{
  "mcpServers": {
    "oracle-dba": {
      "command": "/opt/homebrew/bin/uv",
      "args": [
        "--directory",
        "/absolute/path/to/mcp-oracle-dba",
        "run",
        "mcp-oracle-dba"
      ]
    }
  }
}

Restart Claude Desktop. The tools should appear under the 🔧 icon in the chat input.

Try asking: "List the schemas available in our Oracle DB", "Describe the FND_USER table", "What's the top SQL in the last hour?"

Configuration reference

All settings load from .env (see .env.example):

Recommended database setup

A minimal read-only Oracle user for the MCP server:

CREATE USER mcp_ro IDENTIFIED BY "strong_password";
GRANT CREATE SESSION TO mcp_ro;
GRANT SELECT_CATALOG_ROLE TO mcp_ro;
-- For each business table you want exposed:
GRANT SELECT ON appsapp.fnd_user TO mcp_ro;
-- ...

SELECT_CATALOG_ROLE is preferred over individual V$ grants — it covers all data-dictionary and dynamic-performance views in one line, and avoids the "SYSTEM can't forward SYS-owned grants" issue you hit otherwise.

Oracle version compatibility

For EBS R12.2 + 19c specifically, customize MCP_SCHEMA_ALLOWLIST:

MCP_SCHEMA_ALLOWLIST=APPS,APPLSYS,FND,AR,AP,GL,SYS

What's NOT included (yet)

• Connection pooling — current implementation opens one connection per tool call. Fine for sparse MCP workloads; swap in oracledb.create_pool() if you need higher throughput.

• Write-mode tools — by design. There are no INSERT_* or UPDATE_* tools, and there never will be in this server. Write paths belong in dedicated, application-specific MCP servers with their own threat model.

• Thick-mode support for environments requiring Oracle Wallet — thin mode handles most cases including SSL; thick mode would need a separate code path.

Roadmap

• Core tools: list_schemas, describe_table, run_select, explain_plan, top_sql

• SQL guardrails + 45 security tests

• PII column redaction

• JSON-line audit log

• AWR summary tool (top SQL + waits + time model in one JSON blob)

• ASH wait-event sampler tool

• AWR top SQL + time model tools

• AWR feature flag (MCP_ENABLE_AWR) for Diagnostic Pack gating

• Connection pooling (oracledb.create_pool()) for higher throughput

• Hybrid TNS + thick-mode support (for environments requiring Oracle Wallet)

• Structured failure responses (machine-readable JSON refusals with policy ID + retry guidance, per community feedback)

• CI integration tests against a Docker gvenzl/oracle-free service container

License

MIT. Oracle and Oracle Database are trademarks of Oracle Corporation. This project is not affiliated with or endorsed by Oracle.