Overview

The MCP (Model Context Protocol) server provides a lightweight Node.js layer for accessing Oracle databases with configurable, model-oriented access controls. Instead of letting application code run arbitrary SQL, MCP lets you declare models and rules that control what queries can run, which columns are visible, and which operations are permitted — plus it can return query explain plans, runtime statistics, and basic schema inspection. This simplifies enforcing least-privilege access and auditing database usage from backend services.

MCP is useful when you need a guarded database access surface for microservices, analytics tools, or developer sandboxes. It exposes a small set of REST endpoints for queries, explain plans, stats, and schema metadata, and integrates with Oracle via the standard Node.js Oracle driver. Configuration is file-based (JSON/YAML) so you can version-control access policies and adjust them without code changes.

Features

Role- and model-based access controls (allow/deny per operation)
Column whitelisting and blacklisting per model
Query explain plans (to inspect execution plans produced by Oracle)
Runtime statistics for queries (execution time, rows returned)
Schema inspection endpoints (tables, columns, types)
Config-driven: models, policies, and limits defined in JSON/YAML
REST API for programmatic access from Node.js, services, or CLIs
Compatibility with Node.js Oracle driver (oracledb)

Installation / Configuration

Prerequisites:

Node.js 14+ (or the version supported by the project)
Oracle client libraries and credentials for oracledb
Network access from the MCP server to the Oracle instance

Clone and install:

git clone https://github.com/marcelo-ochoa/servers.git
cd servers/mcp-oracle-server
npm install

Environment variables (example .env):

# .env
PORT=3000
ORACLE_USER=app_user
ORACLE_PASSWORD=secret_password
ORACLE_CONNECT_STRING=//dbhost:1521/ORCLPDB1
CONFIG_PATH=./config/models.json
LOG_LEVEL=info

Example model configuration (config/models.json):

{
  "models": {
    "employees": {
      "table": "HR.EMPLOYEES",
      "read": {
        "roles": ["analyst", "admin"],
        "columns": ["EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "HIRE_DATE"],
        "maxRows": 1000
      },
      "stats": true,
      "explain": true
    },
    "salaries": {
      "table": "HR.SALARIES",
      "read": {
        "roles": ["admin"],
        "columns": ["EMPLOYEE_ID", "SALARY"],
        "maxRows": 500
      },
      "explain": false
    }
  }
}

Start the server:

npm start
# or explicitly
node server.js

The server reads the configuration file specified by CONFIG_PATH and enforces model rules for incoming requests.

Available Resources

Common REST endpoints (base URL: http://localhost:3000):

Endpoint	Method	Description
/query	POST	Execute a model-scoped query (payload includes model, filters, columns)
/explain	POST	Return the Oracle execution plan for a query (if model allows explain)
/stats	GET	Retrieve recent query statistics (latency, rows)
/schema	GET	Inspect tables, columns, and types visible to the configured user
/models	GET	List configured models and their metadata

Example: query a model with curl

curl -X POST http://localhost:3000/query \
  -H "Content-Type: application/json" \
  -d '{"model":"employees","filters":{"department_id":90},"columns":["EMPLOYEE_ID","FIRST_NAME","LAST_NAME"]}'

Example: request an explain plan

curl -X POST http://localhost:3000/explain \
  -H "Content-Type: application/json" \
  -d '{"model":"employees","filters":{"employee_id":101}}'

Use Cases

Role-based API for frontend apps: Frontend or mobile apps call MCP instead of the DB directly. MCP enforces column-level visibility (e.g., hide salary fields for non-admins).
Analytics and ad-hoc queries: Provide analysts with a controlled environment to run queries and get explain plans and runtime stats without granting broad DB privileges.
Audit and compliance: Centralize database access through MCP to log which models and queries are used, who executed them, and capture execution stats for compliance reports.
Microservice integration: Use MCP as a shared data-access layer for microservices, isolating schema details and enforcing consistent access rules across teams.
Developer sandboxes: Give developers safe read-only access to certain models and allow explain-only operations to investigate performance without risking writes.

Security and Best Practices

Run MCP behind HTTPS and an API gateway for authentication, rate limiting, and IP restrictions.
Use a database account with the minimum privileges necessary for configured models (read-only where possible).
Store credentials in a secrets manager and avoid committing .env files to source control.
Keep model configuration in version control and review changes in pull requests to track policy updates.
Enable logging and monitor the /stats endpoint for slow queries and unusual patterns.

Troubleshooting

oracledb installation issues: ensure Oracle Instant Client is installed and LD_LIBRARY_PATH (or equivalent) is set.
Connection errors: verify ORACLE_CONNECT_STRING and network reachability to the database listener.
Policy violations: check the configured model rules if a query is rejected (columns, role, maxRows).

This MCP server provides a pragmatic layer for securely exposing Oracle data to Node.js applications while keeping access policies explicit and auditable.

Oracle MCP Server: Node.js Database Access Controls