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

Tool	What it does
list_schemas	Returns the allowlist of schemas the server is configured to query.
describe_table	Column metadata for SCHEMA.TABLE. Allowlist-enforced.
run_select	Validates + runs a SELECT / WITH query. Row-capped, PII-redacted.
explain_plan	Oracle EXPLAIN PLAN output for a query (DBMS_XPLAN.DISPLAY).
top_sql	Top SQL by elapsed time from v$sql over the last N minutes.

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):

Variable	Default	Meaning
ORA_USER	(required)	DB user (should be read-only)
ORA_PASSWORD	(required)	DB password
ORA_DSN	(required)	Easy-Connect or TNS-format DSN
MCP_MAX_ROWS	100	Hard cap on rows returned by run_select
MCP_STATEMENT_TIMEOUT_SECONDS	5	Server-side statement timeout
MCP_SCHEMA_ALLOWLIST	APPS,APPLSYS,SYS,RAGAPP	Comma-separated schemas allowed for describe_table
MCP_COLUMN_DENYLIST	SSN,SALARY,TAX_ID,PASSWORD,…	Column-name substrings to redact
MCP_AUDIT_LOG	./audit.log	JSON-line audit log path

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

• [x] Core tools: list_schemas, describe_table, run_select, explain_plan, top_sql
• [x] SQL guardrails + 45 security tests
• [x] PII column redaction
• [x] 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.