The problem with direct database access
AI agents need schema context, but raw database access creates avoidable risk.
A database account can often see more than a single AI workflow needs. If that account is copied into a local client or wrapped by a broad natural-language SQL tool, the model-facing surface becomes larger than the business task.
Database to MCP should not mean giving an agent a database login and hoping the prompt behaves. The safer pattern is to publish an approved scope, expose schema metadata as tools, and run data execution only through Gateway validation.
In Swagger to MCP Gateway, database access is modeled as a published MCP scope. The scope can represent a small database, one schema, or a business domain such as sales, finance, or inventory. The scope, not the raw database, is what the MCP client can discover.
Use scopes instead of raw database access
A database scope is the security and publishing unit for Database to MCP.
| Concept | What it controls | Why it matters |
|---|---|---|
| DatabaseIntegration | The onboarding and connection root. | The connection can be managed once without publishing the whole database. |
| DatabaseScope | The published MCP surface for a DB, schema, or business domain. | Different scopes can expose different tables, columns, and executor modes. |
| Owner-aware MCP slug | The public MCP route is resolved with the authenticated owner. | Two owners can use the same slug without cross-owner access. |
| Published tables | The tables allowed inside the current scope. | Executor tools cannot query tables outside the published scope. |
| Column permissions | Visibility, sensitivity, selection, filtering, sorting, grouping, and aggregation. | The same physical column can be allowed in one scope and blocked in another. |
Schema tools are metadata-only
Schema tools help an AI client understand structure without returning row payloads.
Each published table becomes a schema metadata tool. The tool name is normalized, for example schema_sales_orders, while original database names stay in metadata.
This is important for privacy and blast-radius control. The model can inspect table descriptions, columns, keys, relationships, indexes, permissions, and allowed operations, but it does not receive sample rows from schema inspection.
Schema tools are available in both QueryPlan and SqlScript executor modes. They explain what can be queried; they do not execute the query.
Metadata-only schema tool shape
json{
"tool": "schema_sales_orders",
"returns": {
"table": "sales.Orders",
"description": "Approved order metadata for support and operations workflows.",
"columns": [
{
"name": "OrderId",
"type": "uuid",
"selectable": true,
"filterable": true
},
{
"name": "CustomerEmail",
"type": "text",
"visible": false,
"sensitive": true
}
],
"relationships": ["sales.Customers"],
"allowedOperations": ["select", "filter", "sort", "aggregate"],
"rowPayloads": "not returned"
}
}Executor tools run after Gateway validation
The LLM can propose a QueryPlan or SQL candidate, but the Gateway decides whether it can execute.
Execution is separate from schema discovery. QueryPlan mode publishes validate_query_plan, explain_query_plan, and execute_query_plan. SqlScript mode publishes validate_sql, explain_sql, and execute_sql. A single scope should not publish both executor modes at the same time.
The Gateway checks active scope state, owner-aware resolution, published tables, allowed relationships, column permissions, limits, and mode policy before provider-specific SQL reaches PostgreSQL or SQL Server.
- Cross-database, cross-schema, cross-scope, and cross-owner access is forbidden.
- Executor tools can only use tables published in the current DatabaseScope.
- Executor tools can only use columns permitted by the current scope-based permissions.
- Generated SQL is validated again before execution.
- Semantic Kernel or another orchestrator may propose the request, but it is not the security boundary.
Column permissions are part of the contract
A table is not safe just because it is published. Column-level rules decide what the agent can actually use.
| Permission | Meaning | Typical default |
|---|---|---|
| visible | The column can be shown in schema metadata. | Allowed for normal business columns. |
| hidden | The column should not be exposed to the MCP surface. | Blocked by default. |
| sensitive | The column needs stronger blocking or masking. | Blocked or masked by default. |
| selectable | The column can appear in query results. | Allowed only when the workflow needs it. |
| filterable | The column can be used in where conditions. | Allowed for safe lookup and segmentation fields. |
| sortable | The column can be used in ordering. | Allowed for common date, status, and metric fields. |
| groupable | The column can be used in group by. | Allowed for non-sensitive dimensions. |
| aggregatable | The column can be used with SUM, AVG, MIN, or MAX. | Allowed for non-sensitive numeric metrics. |
| maskMode | Controls masking in audit logs and parameters. | Required for sensitive values. |
Choose one executor mode per scope
QueryPlan mode is the recommended default. SqlScript mode is advanced and conservative.
| Mode | How it works | Use when |
|---|---|---|
| QueryPlan | The model sends structured JSON with table, joins, selected columns, filters, grouping, ordering, and limit. | You want the safest default for repeatable database access. |
| SqlScript | The model sends SQL text and parameters, then the Gateway validates the SQL conservatively before execution. | A reviewed advanced workflow needs SQL expressiveness that QueryPlan does not yet cover. |
Audit logs must be useful without leaking secrets
Database MCP traffic should be observable without exposing connection strings or sensitive values.
- Audit events include owner, scope, tool name, executor mode, action, success or error state, duration, row count, truncation, and validation errors.
- Normalized SQL hashes and QueryPlan hashes help compare repeated calls without storing unsafe raw detail.
- Connection strings are encrypted at rest and never returned after save.
- Sensitive parameter values and audit payloads are redacted based on parameter names and column permissions.
- Logs should explain why validation failed without exposing secrets, stack traces, or private implementation details.
What the Gateway should reject
A secure Database to MCP design is easiest to evaluate by looking at what fails closed.
| Attempt | Expected Gateway behavior |
|---|---|
| Query a table outside the current scope | Reject before SQL execution. |
| Select a hidden or sensitive column | Reject or mask according to scope policy. |
| Join through an unpublished relationship | Reject because the relationship is outside the approved scope graph. |
| Use SQL mode without a required limit | Reject when RequireLimit is enabled. |
| Use SELECT * in SQL mode | Reject when BlockSelectStar is enabled. |
| Run DML or DDL | Reject. SqlScript mode is SELECT-only by default. |
| Access another owner's MCP slug | Reject during owner-aware scope resolution. |
Safe rollout checklist
Start narrow, then expand only after the query behavior is predictable.
- Create the first scope around one business workflow, not the entire database.
- Publish only the tables needed for that workflow.
- Mark hidden and sensitive columns before exposing schema tools.
- Use QueryPlan mode first unless there is a specific reviewed reason for SqlScript mode.
- Test realistic questions in Gateway Chat and inspect the proposed tool calls.
- Review validation failures and audit redaction before connecting external MCP clients.
- Expand the scope only when the first workflow is understandable, logged, and operationally safe.
Common questions
Do schema tools return sample rows?
No. Schema tools are metadata-only and do not return row payloads.
Can executor tools use hidden columns?
No. Executor tools can only use columns published and permitted in the current database scope.
Can SQL mode run arbitrary SQL?
No. SqlScript mode validates SQL before execution. The default policy is SELECT-only, single-statement, scope-bound, limit-aware, and conservative around CTEs, subqueries, unions, raw expressions, and dangerous functions.
Can two scopes expose the same table differently?
Yes. Scope-based permissions allow the same physical table or column to be visible in one scope and hidden, sensitive, or restricted in another.
When can row data be returned?
Row data can be returned only by validated executor tools after the request passes scope, table, column, relationship, limit, and mode policy checks. Schema tools never return row data.
Does an LLM enforce database security?
No. LLMs can propose a QueryPlan or SQL candidate, but the Gateway validates every executor request before execution.