Oracle Read-Only MCP Server

Local Python MCP server for Oracle database access using python-oracledb in thin mode only.

This server is designed to:

• run locally on demand through an MCP client
• connect to Oracle without OCI / Oracle Instant Client
• support read-only SQL queries and schema inspection
• support both tnsnames.ora alias mode and direct host/port/service-name mode

Features

• run_query(sql)
  • execute a single read-only SELECT or WITH ... SELECT query
  • returns columns, rows, row count, and truncation status
• preview_query(sql)
  • validates SQL with the same read-only guard as run_query
  • returns normalized SQL without executing it
• list_tables(schema?, name_pattern?)
  • list accessible tables
• list_views(schema?, name_pattern?)
  • list accessible views
• search_objects(name_pattern, schema?, object_types?)
  • search tables, views, synonyms, or other allowed object types by SQL LIKE pattern
• describe_table(table_name, schema?)
  • full table/view structure including type, precision/scale, defaults, and comments when available
• list_columns(table_name, schema?)
  • lightweight column listing for quick schema inspection
• get_primary_key(table_name, schema?)
  • list primary key columns and position order
• list_foreign_keys(table_name, schema?)
  • list outbound foreign key relationships and referenced columns
• get_table_sample(table_name, schema?, limit?)
  • return a capped sample of rows from a table or view
• list_schemas()
  • list visible Oracle schemas/users

Security Model

This server rejects non-read-only SQL in application code, but the real security boundary should be the Oracle account itself.

Use a database user that only has read access.

The SQL guard allows only a single statement starting with SELECT or WITH and rejects DML, DDL, PL/SQL, and multi-statement input.

Application-level protection:

• only read-oriented MCP tools are exposed
• run_query() and preview_query() validate SQL before execution
• multi-statement input is rejected
• common write and DDL keywords are rejected outside string literals

Database-level protection:

• the Oracle user should only have CREATE SESSION and SELECT privileges as needed
• do not use SYSTEM, SYS, or any account with write privileges in normal use

The MCP server reduces risk, but a read-only Oracle account is what actually guarantees that writes cannot happen.

Requirements

• Python 3.10+
• Oracle database reachable over the network
• No OCI / Oracle Instant Client required

Install

python -m venv .venv
. .venv/bin/activate
pip install -e .[dev]

Configuration

Copy .env.example and set environment variables before starting the server.

TNS Alias Mode

ORACLE_CONNECTION_MODE=tns
ORACLE_TNSNAMES_PATH=/path/to/tnsnames.ora
ORACLE_DSN_ALIAS=MYDB
ORACLE_USERNAME=readonly_user
ORACLE_PASSWORD=secret
ORACLE_FETCH_MAX_ROWS=500

ORACLE_TNSNAMES_PATH points to the file itself. The server uses the file's parent directory as the Oracle Net config directory for thin-mode alias resolution.

Direct Mode

ORACLE_CONNECTION_MODE=direct
ORACLE_HOST=dbhost.example.com
ORACLE_PORT=1521
ORACLE_SERVICE_NAME=orclpdb1
ORACLE_USERNAME=readonly_user
ORACLE_PASSWORD=secret
ORACLE_FETCH_MAX_ROWS=500

Run

oracle-mcp-server

Or during development:

python -m oracle_mcp_server.server

Or use the included Makefile helpers:

make install
make test
make test-integration
make run-server

Makefile Commands

• make install
  • create .venv
  • install the project and dev dependencies
• make test
  • run the default test suite
  • integration tests remain skipped
• make test-integration
  • run the live Oracle integration tests
  • requires a suitable Oracle test environment
• make run-server
  • start the MCP server from the project virtualenv

OpenCode MCP Configuration

OpenCode uses local MCP server definitions under the mcp key in opencode.json or opencode.jsonc.

Example OpenCode config:

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "oracle_readonly": {
      "type": "local",
      "command": ["/absolute/path/to/oracle-mcp/.venv/bin/oracle-mcp-server"],
      "cwd": "/absolute/path/to/oracle-mcp",
      "enabled": true,
      "environment": {
        "ORACLE_CONNECTION_MODE": "tns",
        "ORACLE_TNSNAMES_PATH": "/path/to/tnsnames.ora",
        "ORACLE_DSN_ALIAS": "MYDB",
        "ORACLE_USERNAME": "readonly_user",
        "ORACLE_PASSWORD": "secret",
        "ORACLE_FETCH_MAX_ROWS": "500"
      }
    }
  }
}

Direct mode OpenCode example:

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "oracle_readonly": {
      "type": "local",
      "command": ["/absolute/path/to/oracle-mcp/.venv/bin/oracle-mcp-server"],
      "cwd": "/absolute/path/to/oracle-mcp",
      "enabled": true,
      "environment": {
        "ORACLE_CONNECTION_MODE": "direct",
        "ORACLE_HOST": "dbhost.example.com",
        "ORACLE_PORT": "1521",
        "ORACLE_SERVICE_NAME": "orclpdb1",
        "ORACLE_USERNAME": "readonly_user",
        "ORACLE_PASSWORD": "secret",
        "ORACLE_FETCH_MAX_ROWS": "500"
      }
    }
  }
}

Once configured in OpenCode, the MCP tools are available under the oracle_readonly server namespace.

Generic MCP Client Configuration

Example for a generic client that launches a local stdio server:

{
  "mcpServers": {
    "oracle-readonly": {
      "command": "/absolute/path/to/.venv/bin/oracle-mcp-server",
      "env": {
        "ORACLE_CONNECTION_MODE": "tns",
        "ORACLE_TNSNAMES_PATH": "/path/to/tnsnames.ora",
        "ORACLE_DSN_ALIAS": "MYDB",
        "ORACLE_USERNAME": "readonly_user",
        "ORACLE_PASSWORD": "secret"
      }
    }
  }
}

Tests

pytest

Or with make:

make test
make test-integration

Default test runs exclude live Oracle integration tests.

Run the live integration suite only when you have a suitable Oracle environment available:

pytest --run-integration -m integration

Integration tests use these environment variables, with Docker-friendly defaults matching the sample Oracle XE setup:

ORACLE_TEST_HOST=127.0.0.1
ORACLE_TEST_PORT=1521
ORACLE_TEST_SERVICE_NAME=XE
ORACLE_TEST_USERNAME=system
ORACLE_TEST_PASSWORD=oracle
ORACLE_TEST_DEFAULT_SCHEMA=SYS
ORACLE_TEST_DSN_ALIAS=XETEST
ORACLE_TEST_CONNECT_TIMEOUT=15
ORACLE_TEST_PROTOCOL=tcp

This lets you keep the tests in the repo but skip them in normal development or CI unless explicitly requested.

Test coverage currently includes:

• config validation for direct and TNS modes
• SQL guard behavior for read-only validation
• connection kwargs for direct mode
• tnsnames.ora parent-directory resolution, ensuring the server passes the containing directory as config_dir
• opt-in live integration tests for direct-mode queries, metadata tools, sampling, and TNS alias resolution

There is also a live smoke-tested tests/fixtures/tnsnames.ora example used during development to validate real TNS alias resolution against Oracle XE.