# 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
- support multiple named Oracle environments such as `DEV1` and `UAT3`
- 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

## Quick Start

1. Copy `oracle-mcp.example.jsonc` to `oracle-mcp.jsonc` and fill in your real environment entries such as `DEV1` or `UAT3`.
2. Start the server with `make run-server` or your MCP client.
3. In the LLM workflow:
   - call `list_environments()`
   - call `check_database("DEV1")`
   - then run queries like `run_query("DEV1", "select * from ...")`

`default_schema` is optional. Use it only if your read-only login user is different from the application schema you normally query.

## Features

- `run_query(environment, sql)`
  - environment-specific execution using a moniker parameter
  - execute a single read-only `SELECT` or `WITH ... SELECT` query
  - returns columns, rows, row count, and truncation status
- `list_environments()`
  - list configured environment monikers and connection mode
- `check_database(environment)`
  - verify whether the selected environment is available
  - performs a real Oracle connection and `select 1 from dual`
- `preview_query(sql)`
  - validates SQL with the same read-only guard as `run_query`
  - returns normalized SQL without executing it
- `list_tables(environment, schema?, name_pattern?)`
  - list accessible tables
- `list_views(environment, schema?, name_pattern?)`
  - list accessible views
- `search_objects(environment, name_pattern, schema?, object_types?)`
  - search tables, views, synonyms, or other allowed object types by SQL `LIKE` pattern
- `describe_table(environment, table_name, schema?)`
  - full table/view structure including type, precision/scale, defaults, and comments when available
- `list_columns(environment, table_name, schema?)`
  - lightweight column listing for quick schema inspection
- `get_primary_key(environment, table_name, schema?)`
  - list primary key columns and position order
- `list_foreign_keys(environment, table_name, schema?)`
  - list outbound foreign key relationships and referenced columns
- `get_table_sample(environment, table_name, schema?, limit?)`
  - return a capped sample of rows from a table or view
- `list_schemas(environment)`
  - 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
- `SELECT ... FOR UPDATE` is also blocked by the guard

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 Oracle account should not have `EXECUTE` on dangerous packages such as `DBMS_SQL`, `UTL_FILE`, or `DBMS_LOB`

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

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

## Configuration

This server now uses a single JSONC configuration file with named environments.

Use:
- `oracle-mcp.example.jsonc` as the template
- `oracle-mcp.jsonc` as your real local config file

The real `oracle-mcp.jsonc` is ignored by git because it contains plain-text credentials.

Default config path:
- `oracle-mcp.jsonc` in the project root

Override config path with:
- `ORACLE_MCP_CONFIG_PATH`

Example:

```jsonc
{
  "defaults": {
    "fetch_max_rows": 500,
    "connect_timeout": 15,
    "protocol": "tcp"
  },
  "environments": {
    "DEV1": {
      "mode": "tns",
      "tnsnames_path": "C:\\oracle\\network\\admin\\tnsnames.ora",
      "dsn_alias": "DEV1",
      "username": "readonly_user",
      "password": "plain_text_password",
      "default_schema": "APP" // optional
    },
    "UAT3": {
      "mode": "direct",
      "host": "uat3-db.internal",
      "port": 1521,
      "service_name": "UAT3",
      "username": "readonly_user",
      "password": "plain_text_password"
    }
  }
}
```

TNS environments still use the file path to `tnsnames.ora`, and the server uses the file's parent directory as Oracle's `config_dir` for alias resolution.

Schema resolution order for metadata/sample tools is:

1. explicit `schema` argument passed to the tool
2. `default_schema` from the selected environment, if present
3. the Oracle username for that environment

## Run

```bash
oracle-mcp-server
```

Or during development:

```bash
python -m oracle_mcp_server.server
```

If your config file is not in the project root:

```bash
ORACLE_MCP_CONFIG_PATH=C:\path\to\oracle-mcp.jsonc python -m oracle_mcp_server.server
```

Or use the included `Makefile` helpers:

```bash
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:

```json
{
  "$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_MCP_CONFIG_PATH": "C:\\path\\to\\oracle-mcp.jsonc"
      }
    }
  }
}
```

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

Typical workflow with the LLM:

1. `list_environments()`
2. `check_database("DEV1")`
3. `run_query("DEV1", "select * from ...")`

## Generic MCP Client Configuration

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

```json
{
  "mcpServers": {
    "oracle-readonly": {
      "command": "/absolute/path/to/.venv/bin/oracle-mcp-server",
      "env": {
        "ORACLE_MCP_CONFIG_PATH": "C:\\path\\to\\oracle-mcp.jsonc"
      }
    }
  }
}
```

## Tests

```bash
pytest
```

Or with `make`:

```bash
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:

```bash
pytest --run-integration -m integration
```

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

```env
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:
- JSONC config validation with named environments
- unknown environment rejection
- SQL guard behavior for read-only validation
- connection kwargs for direct and TNS modes
- `tnsnames.ora` parent-directory resolution, ensuring the server passes the containing directory as `config_dir`
- environment-specific DB availability checks
- opt-in live integration tests for direct-mode queries, metadata tools, sampling, health checks, 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.