import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListResourcesRequestSchema,
ListToolsRequestSchema,
ReadResourceRequestSchema,
ListPromptsRequestSchema,
GetPromptRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import oracledb from 'oracledb';
import * as fs from 'fs';
import * as path from 'path';
import * as os from 'os';
// Initialize Oracle Client if environment variables are set
if (process.env.ORACLE_HOME || process.env.TNS_ADMIN) {
oracledb.initOracleClient({
libDir: process.env.ORACLE_HOME,
configDir: process.env.TNS_ADMIN
});
}
// ============================================================================
// Type Definitions
// ============================================================================
type OracleErrorType = Error | null;
interface TableRow {
TABLE_NAME: string;
}
interface ColumnRow {
COLUMN_NAME: string;
DATA_TYPE: string;
}
type AccessMode = 'readonly' | 'readwrite' | 'full';
type StatementType = 'SELECT' | 'INSERT' | 'UPDATE' | 'DELETE' | 'DDL' | 'UNKNOWN';
interface DatabaseConfig {
name: string;
user: string;
password: string;
connectString: string;
accessMode?: AccessMode; // Default: 'readonly'
allowedTables?: string[]; // Whitelist patterns
deniedTables?: string[]; // Blacklist patterns
}
interface DatabasesFileConfig {
databases: DatabaseConfig[];
}
// ============================================================================
// Configuration
// ============================================================================
const serverConfig = {
name: "mcp-server-oracle",
version: "1.2.0",
};
/**
* Parse database configurations from config file and/or environment variables.
*/
function parseDatabaseConfigs(): Map<string, DatabaseConfig> {
const databases = new Map<string, DatabaseConfig>();
const configPath = process.env.ORACLE_CONFIG_PATH
|| path.join(os.homedir(), '.mcp_oracle', 'databases.json');
if (fs.existsSync(configPath)) {
try {
const fileContent = fs.readFileSync(configPath, 'utf-8');
const fileConfig: DatabasesFileConfig = JSON.parse(fileContent);
for (const db of fileConfig.databases) {
// Set default accessMode to 'readonly' if not specified
if (!db.accessMode) {
db.accessMode = 'readonly';
}
databases.set(db.name, db);
}
console.error(`Loaded ${fileConfig.databases.length} database(s) from ${configPath}`);
} catch (err) {
console.error(`Failed to parse config file ${configPath}:`, err);
}
}
// Legacy mode: single database via environment variables
if (process.env.ORACLE_USER && process.env.ORACLE_CONNECTION_STRING) {
const defaultDb: DatabaseConfig = {
name: "default",
user: process.env.ORACLE_USER,
password: process.env.ORACLE_PASS || "",
connectString: process.env.ORACLE_CONNECTION_STRING,
accessMode: 'readonly', // Default to readonly for safety
};
databases.set("default", defaultDb);
console.error(`Loaded "default" database from environment variables`);
}
if (databases.size === 0) {
console.error("Warning: No database configurations found.");
}
return databases;
}
const databases = parseDatabaseConfigs();
// ============================================================================
// SQL Validation
// ============================================================================
/**
* Detect the type of SQL statement.
*/
function getStatementType(sql: string): StatementType {
const trimmed = sql.trim().toUpperCase();
if (trimmed.startsWith('SELECT') || trimmed.startsWith('WITH')) return 'SELECT';
if (trimmed.startsWith('INSERT')) return 'INSERT';
if (trimmed.startsWith('UPDATE')) return 'UPDATE';
if (trimmed.startsWith('DELETE')) return 'DELETE';
if (/^(CREATE|ALTER|DROP|TRUNCATE|GRANT|REVOKE)/.test(trimmed)) return 'DDL';
return 'UNKNOWN';
}
/**
* Extract table names from SQL (simplified extraction).
* Note: This is a simplified implementation. For production use, consider a proper SQL parser.
*/
function extractTableNames(sql: string): string[] {
const tables: string[] = [];
const sqlUpper = sql.toUpperCase();
// Match patterns like: FROM table, INTO table, UPDATE table, JOIN table
const patterns = [
/\bFROM\s+([A-Z_][A-Z0-9_]*)/gi,
/\bINTO\s+([A-Z_][A-Z0-9_]*)/gi,
/\bUPDATE\s+([A-Z_][A-Z0-9_]*)/gi,
/\bJOIN\s+([A-Z_][A-Z0-9_]*)/gi,
/\bTABLE\s+([A-Z_][A-Z0-9_]*)/gi,
/\bTRUNCATE\s+(?:TABLE\s+)?([A-Z_][A-Z0-9_]*)/gi,
];
for (const pattern of patterns) {
let match;
const regex = new RegExp(pattern.source, pattern.flags);
while ((match = regex.exec(sql)) !== null) {
const tableName = match[1].toUpperCase();
if (!tables.includes(tableName)) {
tables.push(tableName);
}
}
}
return tables;
}
/**
* Check if a table name matches a pattern (exact, wildcard, or regex).
*/
function matchesPattern(tableName: string, pattern: string): boolean {
const tableUpper = tableName.toUpperCase();
// Regex pattern (starts and ends with /)
if (pattern.startsWith('/') && pattern.endsWith('/')) {
try {
const regex = new RegExp(pattern.slice(1, -1), 'i');
return regex.test(tableUpper);
} catch {
return false;
}
}
// Wildcard pattern (contains *)
if (pattern.includes('*')) {
const regexPattern = pattern.toUpperCase().replace(/\*/g, '.*');
return new RegExp(`^${regexPattern}$`).test(tableUpper);
}
// Exact match
return tableUpper === pattern.toUpperCase();
}
/**
* Check if a table is allowed based on whitelist/blacklist patterns.
*/
function isTableAllowed(tableName: string, dbConfig: DatabaseConfig): boolean {
// Check blacklist first (deniedTables)
if (dbConfig.deniedTables && dbConfig.deniedTables.length > 0) {
for (const pattern of dbConfig.deniedTables) {
if (matchesPattern(tableName, pattern)) {
return false;
}
}
}
// Check whitelist (allowedTables)
if (dbConfig.allowedTables && dbConfig.allowedTables.length > 0) {
for (const pattern of dbConfig.allowedTables) {
if (matchesPattern(tableName, pattern)) {
return true;
}
}
return false; // Not in whitelist
}
return true; // No restrictions
}
/**
* Validate SQL access based on database configuration.
*/
function validateAccess(sql: string, dbConfig: DatabaseConfig): void {
const stmtType = getStatementType(sql);
const mode = dbConfig.accessMode || 'readonly';
// Check statement type against access mode
if (mode === 'readonly' && stmtType !== 'SELECT') {
throw new Error(`Database "${dbConfig.name}" is read-only. Only SELECT queries are allowed.`);
}
if (mode === 'readwrite' && stmtType === 'DDL') {
throw new Error(`Database "${dbConfig.name}" does not allow DDL operations.`);
}
// Check table-level permissions for write operations
if (stmtType !== 'SELECT') {
const tables = extractTableNames(sql);
for (const table of tables) {
if (!isTableAllowed(table, dbConfig)) {
throw new Error(`Access denied: Table "${table}" is not allowed for write operations in database "${dbConfig.name}".`);
}
}
}
}
// ============================================================================
// Database Operations
// ============================================================================
const oracleQuery = <T>(
connection: oracledb.Connection,
sql: string,
params: any[] = [],
): Promise<T> => {
return new Promise((resolve, reject) => {
connection.execute(sql, params, { outFormat: oracledb.OUT_FORMAT_OBJECT }, (err, result) => {
if (err) reject(err);
else resolve(result.rows as T);
});
});
};
/**
* Get database configuration by name.
*/
function getDatabase(dbName?: string): DatabaseConfig {
let targetDb: DatabaseConfig | undefined;
if (dbName) {
targetDb = databases.get(dbName);
if (!targetDb) {
throw new Error(`Database "${dbName}" not found. Available: ${Array.from(databases.keys()).join(', ')}`);
}
} else if (databases.size === 1) {
targetDb = databases.values().next().value;
} else if (databases.size > 1) {
throw new Error(`Multiple databases configured. Please specify a database name. Available: ${Array.from(databases.keys()).join(', ')}`);
} else {
throw new Error("No database configurations found.");
}
if (!targetDb) {
throw new Error("No database configuration available.");
}
return targetDb;
}
/**
* Get a database connection by name.
*/
const oracleGetConnection = async (dbName?: string): Promise<oracledb.Connection> => {
const targetDb = getDatabase(dbName);
try {
const connection = await oracledb.getConnection({
user: targetDb.user,
password: targetDb.password,
connectString: targetDb.connectString,
});
return connection;
} catch (err) {
throw new Error(`Error connecting to Oracle (${targetDb.name}): ${(err as Error).message}`);
}
};
const oracleBeginReadOnlyTransaction = async (connection: oracledb.Connection): Promise<void> => {
await connection.execute(`SET TRANSACTION READ ONLY`);
};
const oracleBeginTransaction = async (connection: oracledb.Connection): Promise<void> => {
await connection.execute(`SET TRANSACTION NAME 'mcp_write'`);
};
const oracleRollback = async (connection: oracledb.Connection): Promise<void> => {
await connection.rollback();
};
const oracleCommit = async (connection: oracledb.Connection): Promise<void> => {
await connection.commit();
};
const oracleReleaseConnection = async (connection: oracledb.Connection): Promise<void> => {
try {
await connection.close();
} catch (err) {
console.error("Error releasing connection:", err);
}
};
// ============================================================================
// Query Execution Functions
// ============================================================================
async function executeQuery<T>(sql: string, params: any[] = [], dbName?: string): Promise<T> {
const connection = await oracleGetConnection(dbName);
try {
const results = await oracleQuery<T>(connection, sql, params);
return results;
} finally {
await oracleReleaseConnection(connection);
}
}
/**
* Execute a read-only query with validation and read-only transaction.
*/
async function executeReadOnlyQuery<T>(sql: string, dbName?: string): Promise<T> {
const dbConfig = getDatabase(dbName);
// Validate: only SELECT allowed
const stmtType = getStatementType(sql);
if (stmtType !== 'SELECT') {
throw new Error('Only SELECT queries are allowed with oracle_query. Use oracle_execute for write operations.');
}
const connection = await oracleGetConnection(dbName);
try {
// Database-level protection: read-only transaction
await oracleBeginReadOnlyTransaction(connection);
const results = await oracleQuery(connection, sql);
return <T>{
content: [
{
type: "text",
text: JSON.stringify(results, null, 2),
},
],
isError: false,
};
} catch (error) {
throw error;
} finally {
await oracleReleaseConnection(connection);
}
}
/**
* Execute a write operation with validation and confirmation.
*/
async function executeWriteQuery<T>(sql: string, dbName: string, confirm: boolean): Promise<T> {
if (!confirm) {
throw new Error('Write operations require confirmation. Set confirm=true to proceed.');
}
const dbConfig = getDatabase(dbName);
// Validate access based on configuration
validateAccess(sql, dbConfig);
const connection = await oracleGetConnection(dbName);
try {
await oracleBeginTransaction(connection);
const results = await oracleQuery(connection, sql);
await oracleCommit(connection);
return <T>{
content: [
{
type: "text",
text: JSON.stringify({
success: true,
message: "Operation completed successfully",
results: results,
}, null, 2),
},
],
isError: false,
};
} catch (error) {
await oracleRollback(connection);
throw error;
} finally {
await oracleReleaseConnection(connection);
}
}
// ============================================================================
// MCP Server Setup
// ============================================================================
const server = new Server(serverConfig, {
capabilities: {
resources: {},
tools: {},
prompts: {},
},
});
// ============================================================================
// Helper: Check if any database is writable
// ============================================================================
function hasWritableDatabase(): boolean {
return Array.from(databases.values()).some(
db => db.accessMode === 'readwrite' || db.accessMode === 'full'
);
}
function getWritableDatabases(): string[] {
return Array.from(databases.entries())
.filter(([, db]) => db.accessMode === 'readwrite' || db.accessMode === 'full')
.map(([name]) => name);
}
// ============================================================================
// Resource Handlers
// ============================================================================
server.setRequestHandler(ListResourcesRequestSchema, async () => {
const resources: Array<{
uri: string;
mimeType: string;
name: string;
description?: string;
}> = [];
resources.push({
uri: "oracle://connections",
mimeType: "application/json",
name: "Oracle Database Connections",
description: "List of available Oracle database connections with access modes",
});
for (const [dbName] of databases) {
try {
const connection = await oracleGetConnection(dbName);
const tables = await oracleQuery<TableRow[]>(
connection,
"SELECT table_name FROM user_tables"
);
await oracleReleaseConnection(connection);
for (const table of tables) {
resources.push({
uri: `oracle://${dbName}/tables/${table.TABLE_NAME}/schema`,
mimeType: "application/json",
name: `[${dbName}] "${table.TABLE_NAME}" schema`,
description: `Schema for table ${table.TABLE_NAME} in database ${dbName}`,
});
}
} catch (err) {
console.error(`Failed to list tables for ${dbName}:`, err);
}
}
return { resources };
});
server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
const uri = request.params.uri;
if (uri === "oracle://connections") {
const connectionList = Array.from(databases.entries()).map(([name, config]) => ({
name,
connectString: config.connectString,
user: config.user,
accessMode: config.accessMode || 'readonly',
allowedTables: config.allowedTables,
deniedTables: config.deniedTables,
}));
return {
contents: [{
uri,
mimeType: "application/json",
text: JSON.stringify(connectionList, null, 2),
}],
};
}
const match = uri.match(/^oracle:\/\/([^\/]+)\/tables\/([^\/]+)\/schema$/);
if (match) {
const [, dbName, tableName] = match;
const connection = await oracleGetConnection(dbName);
try {
const results = await oracleQuery<ColumnRow[]>(
connection,
"SELECT column_name, data_type FROM all_tab_columns WHERE table_name = :tableName",
[tableName]
);
return {
contents: [{
uri,
mimeType: "application/json",
text: JSON.stringify(results, null, 2),
}],
};
} finally {
await oracleReleaseConnection(connection);
}
}
throw new Error(`Invalid resource URI: ${uri}`);
});
// ============================================================================
// Prompt Handlers
// ============================================================================
server.setRequestHandler(ListPromptsRequestSchema, async () => ({
prompts: [
{
name: "oracle_usage_guide",
description: "Guide for querying Oracle databases",
},
],
}));
server.setRequestHandler(GetPromptRequestSchema, async (request) => {
if (request.params.name !== "oracle_usage_guide") {
throw new Error(`Unknown prompt: ${request.params.name}`);
}
const dbList = Array.from(databases.entries()).map(([name, config]) =>
`${name} (${config.accessMode || 'readonly'})`
).join(', ');
const writableList = getWritableDatabases();
const writeSection = writableList.length > 0
? `\n\nFor write operations (INSERT/UPDATE/DELETE), use the oracle_execute tool with:\n- database: Must be one of [${writableList.join(', ')}]\n- sql: The SQL statement\n- confirm: Must be true to execute`
: '';
return {
description: "Oracle Database Query Guide",
messages: [
{
role: "user",
content: {
type: "text",
text: `You have access to the following Oracle databases: [${dbList}].
For read-only queries (SELECT), use the oracle_query tool with:
- database: The database name
- sql: The SQL query to execute${writeSection}
Examples:
- "查询 dev 库中的用户表" -> Use oracle_query with database="dev"
- "查看 test 环境的订单数据" -> Use oracle_query with database="test"
Use oracle://connections resource to see all database connections and their access modes.`,
},
},
],
};
});
// ============================================================================
// Tool Handlers
// ============================================================================
server.setRequestHandler(ListToolsRequestSchema, async () => {
const tools: Array<{
name: string;
description: string;
inputSchema: object;
}> = [
{
name: "oracle_query",
description: "Run a read-only SELECT query on an Oracle database",
inputSchema: {
type: "object",
properties: {
database: {
type: "string",
description: `Database connection name. Available: ${Array.from(databases.keys()).join(', ')}`,
},
sql: {
type: "string",
description: "SELECT query to execute (read-only)",
},
},
required: ["sql"],
},
},
];
// Dynamically expose oracle_execute only if writable databases exist
if (hasWritableDatabase()) {
const writableList = getWritableDatabases();
tools.push({
name: "oracle_execute",
description: "Execute a write operation (INSERT/UPDATE/DELETE/DDL) on an Oracle database. Requires confirmation.",
inputSchema: {
type: "object",
properties: {
database: {
type: "string",
description: `Database connection name. Writable databases: ${writableList.join(', ')}`,
},
sql: {
type: "string",
description: "SQL statement to execute (INSERT/UPDATE/DELETE/DDL)",
},
confirm: {
type: "boolean",
description: "Must be true to confirm and execute the operation",
},
},
required: ["database", "sql", "confirm"],
},
});
}
return { tools };
});
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const toolName = request.params.name;
if (toolName === "oracle_query") {
const sql = request.params.arguments?.sql as string;
const dbName = request.params.arguments?.database as string | undefined;
return executeReadOnlyQuery(sql, dbName);
}
if (toolName === "oracle_execute") {
const sql = request.params.arguments?.sql as string;
const dbName = request.params.arguments?.database as string;
const confirm = request.params.arguments?.confirm as boolean;
if (!dbName) {
throw new Error("Database name is required for write operations.");
}
return executeWriteQuery(sql, dbName, confirm);
}
throw new Error(`Unknown tool: ${toolName}`);
});
// ============================================================================
// Server Startup and Shutdown
// ============================================================================
async function runServer() {
const transport = new StdioServerTransport();
await server.connect(transport);
console.error("Oracle MCP Server started");
console.error(`Databases: ${Array.from(databases.entries()).map(([n, c]) => `${n}(${c.accessMode})`).join(', ')}`);
}
process.on("SIGINT", async () => {
console.error("Received SIGINT. Shutting down...");
process.exit(0);
});
process.on("SIGTERM", async () => {
console.error("Received SIGTERM. Shutting down...");
process.exit(0);
});
runServer().catch((error: unknown) => {
console.error("Server error:", error);
process.exit(1);
});
export { executeQuery, executeReadOnlyQuery, executeWriteQuery, databases };
