# MCP Server for Oracle

[![smithery badge](https://smithery.ai/badge/@zhengyun1008/mcp-server-oracle)](https://smithery.ai/server/@zhengyun1008/mcp-server-oracle)

A Model Context Protocol server that provides access to Oracle databases with fine-grained access control. Supports **multiple databases**, **access modes**, and **table-level permissions**.

## Features

- **Multi-database support**: Connect to multiple Oracle databases simultaneously
- **Access control**: `readonly`, `readwrite`, `full` modes per database
- **Table-level permissions**: Whitelist/blacklist with wildcards and regex
- **Read-only transaction protection**: Database-level safety for readonly mode
- **Backward compatible**: Works with single database environment variables

## Components

### Tools

| Tool | Description |
|------|-------------|
| `oracle_query` | Read-only SELECT queries (always available) |
| `oracle_execute` | Write operations (only visible when writable databases exist) |

### Resources

- **oracle://connections**: List of database connections with access modes
- **oracle://{db}/tables/{table}/schema**: Table schema

### Prompts

- **oracle_usage_guide**: Dynamic guide based on configured databases

## Configuration

### Config File

Create `~/.mcp_oracle/databases.json`:

```json
{
  "databases": [
    {
      "name": "prod",
      "user": "...",
      "password": "...",
      "connectString": "...",
      "accessMode": "readonly"
    },
    {
      "name": "dev",
      "user": "...",
      "password": "...",
      "connectString": "...",
      "accessMode": "readwrite",
      "allowedTables": ["LOG_*", "TMP_*", "/^TEST_.*/"]
    }
  ]
}
```

### Access Modes

| Mode | SELECT | INSERT/UPDATE/DELETE | DDL |
|------|--------|---------------------|-----|
| `readonly` (default) | ✅ | ❌ | ❌ |
| `readwrite` | ✅ | ✅ | ❌ |
| `full` | ✅ | ✅ | ✅ |

### Table Patterns

| Format | Example | Matches |
|--------|---------|---------|
| Exact | `LOG_TABLE` | Only `LOG_TABLE` |
| Wildcard | `LOG_*` | `LOG_` prefix |
| Regex | `/^TEST_\d+$/` | `TEST_` + digits |

### Environment Variables

| Variable | Description |
|----------|-------------|
| `ORACLE_CONFIG_PATH` | Custom config file path |
| `ORACLE_USER` | Legacy single-database user |
| `ORACLE_PASS` | Legacy single-database password |
| `ORACLE_CONNECTION_STRING` | Legacy connection string |
| `ORACLE_HOME` | Oracle client library path |
| `TNS_ADMIN` | TNS admin directory |

## Usage Example

```
User: "查询 prod 库中的用户表"
→ oracle_query(database="prod", sql="SELECT * FROM users")

User: "在 dev 库的 LOG_TEST 表插入一条记录"
→ oracle_execute(database="dev", sql="INSERT INTO LOG_TEST ...", confirm=true)
```

## Security

- **readonly mode**: Uses `SET TRANSACTION READ ONLY` for database-level protection
- **SQL validation**: Validates statement type before execution
- **Table validation**: Checks whitelist/blacklist before write operations
- **Confirmation required**: Write operations require `confirm=true`

## License

MIT License