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.

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

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 (avoids port-forwarding NAT issues that mangle TNS handshakes)

• 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.

What's NOT included (yet)

• AWR / ASH tools (top wait events, time model, snapshot comparison) — see roadmap. Requires Oracle Diagnostic Pack license, so it's gated behind a feature flag.

• 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.

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

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

• 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.