Building a Secure AI Data API with MySQL REST Service (MRS) and JSON Duality Views
Introduction
Giving an LLM direct database credentials or arbitrary "Text-to-SQL" access is an anti-pattern in enterprise security. A single jailbreak or prompt injection can wipe schemas, exfiltrate PII, or execute rogue queries.
By placing MySQL REST Service (MRS) as a mediated gateway between the AI agent and the database, we enforce strict privilege separation. The agent operates strictly via discrete OpenAPI-documented tools, bounded endpoints, and deterministic business logic.
1. Executive Summary & Architecture Overview
Instead of granting raw database connections to an AI reasoning engine, the agent interacts solely with approved REST endpoints exposed via MySQL REST Service.
The Security Model
Principle of Least Privilege: The AI agent never receives SQL credentials, table catalogs, or arbitrary DDL/DML execution privileges.
Granular Scoping: Endpoints restrict agent actions to discrete operations (
GET /orders/{id},POST /returns).Controlled Write Paths: Complex workflows go through stored procedures or updatable Duality Views rather than unchecked table updates.
2. Real-World Scenario: Order & Support AI Copilot
To illustrate this architecture, consider an E-Commerce Customer Support Copilot.
Agent Responsibilities
Retrieve order history and delivery statuses for authenticated customers.
Check inventory availability before promising replacements.
Initiate return requests within business-rule constraints (e.g., return windows, item condition).
Explicit Boundaries
The agent cannot run
DROP,ALTER, or ad-hocSELECT *.The agent cannot query internal margin data, employee salaries, or payment vault tokens.
The agent cannot directly modify account balances or bypass status workflows.
3. Normalized Relational Schema
Here is the normalized baseline schema containing customers, products, orders, and items.
4. Bonus Challenge: JSON Duality Views
Relational normalization is ideal for storage and transactional integrity, but AI agents reason best over self-contained JSON documents. JSON Duality Views bridge this gap by exposing normalized tables as structured documents with fine-grained mutation policies (WITH INSERT, UPDATE, DELETE).
Security Advantage: Notice that
order_statusis markedWITH UPDATE, butcustomerattributes anditemslack write annotations. The agent can update delivery states, but cannot modify customer identity details or order prices through this view.
5. MySQL REST Service (MRS) Configuration & Schema Publication
MRS enables publishing database artifacts directly over HTTPS. We map the customer_orders_dv duality view and create dedicated endpoints.
# Enable MRS module on schema retail_ops
mrs-admin enable-schema retail_ops --auth-type=oauth2
# Publish Duality View as an endpoint
mrs-admin publish-view \
--schema=retail_ops \
--view=customer_orders_dv \
--endpoint=/orders \
--methods=GET,PUT
Alternatively, metadata routing can be configured using a declarative MRS route file:
6. Authentication, Authorization, and Least-Privilege Scoping
Never run MRS under the root MySQL user. Create a restricted proxy account bounded by exact GRANT statements.
At the API gateway layer, configure OAuth2 client credentials with distinct scopes:
7. Handling Complex Business Logic: Stored Procedures as Controlled APIs
Direct REST mutations work well for simple updates, but operations like initiating a return involve business validation (e.g., verifying return eligibility and adjusting stock). Rather than having the AI coordinate multiple raw queries, encapsulate the logic in a transactional stored procedure.
Expose the procedure in MRS:
8. Exposing MRS Endpoints to the AI Agent via OpenAPI Tools
To give the AI agent access to these capabilities, expose them as function/tool definitions conforming to the model's schema.
9. AI Agent Implementation: Client & Dispatcher
Here is a Python implementation of an agent that receives user requests, determines tool calls, and routes them to the MySQL REST Service over HTTP.
10. End-to-End Execution Trace
Here is how the system handles a customer return request end-to-end:
User Interaction
User: "Hi, I received order #1042 yesterday, but the keyboard is defective. Can I return it?"
Execution Flow
Agent Reasoning: Identifies intent (
requestOrderReturn) and arguments:{"orderId": 1042, "reason": "Defective keyboard"}.HTTP Dispatch: Agent invokes
POST [https://mrs-host.internal/api/v1/orders/1042/request-return](https://mrs-host.internal/api/v1/orders/1042/request-return).Database Execution: MRS calls stored procedure
sp_request_order_return. The procedure checks that the order isDELIVEREDand within the 30-day window, then sets the status toRETURN_REQUESTED.MRS Response:
JSONAgent Final Answer: "I have initiated the return request for your keyboard on order #1042. You will receive an email shortly with the return shipping label."
11. Security Threat Analysis: Direct SQL vs. MRS API Gateway
To look at the security difference another way, think of Direct Text-to-SQL as giving an AI agent full keys to your house (unrestricted raw DB access) and relying entirely on its "good behavior" (prompt guidelines) not to open the wrong doors or break things—where a single slip or "prompt injection" can result in catastrophic, unmonitored access. On the other hand, the MySQL REST Service (MRS) operates like valet parking or a guarded window: the agent is placed in a sandbox with absolutely no database credentials, only able to press pre-approved, strictly typed buttons (the API endpoints). It cannot construct destructively inventive queries, explore hidden areas, or escalate its permissions because the "buttons" simply do not exist for unauthorized actions. Access is "secure by default" since every operation must conform to rigid, pre-defined HTTP/JSON contracts and is monitored via transparent API logs, isolating your database completely from the unpredictable, open-ended natural language reasoning of the AI.12. Rapid Prototyping: Time-to-Market Comparison
Eliminates boilerplate middleware: No need to maintain manual ORM mappings, pagination engines, or connection pooling code.
Rapid deployment: An existing schema can be safely exposed in minutes via simple configuration commands (
mrs-admin publish).Declarative consistency: Security rules, pagination, and projection are handled directly at the database edge.
13. When to Use MRS vs. a Full Application Service Layer
While MRS accelerates API development, selecting the right architectural layer depends on operational requirements:
Choose MySQL REST Service (MRS) When:
Developing internal tools, microservices, or AI copilot backends centered on relational data.
Exposing controlled reads, simple updates, and stored-procedure-backed transactions.
Prioritizing speed, minimal operational infrastructure, and native MySQL security alignment.
Choose a Conventional Application Layer (Node.js, Go, Python) When:
Complex cross-system distributed transactions are required (e.g., coordinating MySQL, Kafka, and external third-party payment gateways in a single call).
Long-running CPU-bound tasks, image/audio binary processing, or websockets are involved.
Domain-Driven Design (DDD) with deep in-memory domain models is an organizational standard.
14. Architecture Limitations & Operational Caveats
A comprehensive design must account for the following architectural constraints:
Stateful vs. Stateless Rate Limiting: While MRS enforces connection handling, complex per-user adaptive token-bucket rate limits often still require an API Gateway (like Kong, Envoy, or Cloudflare) upstream of MRS.
Procedural Encapsulation: Putting all business logic into Stored Procedures can complicate CI/CD, testing, and migration workflows compared to application-tier code.
Complex Caching: Application-level distributed caches (like Redis) require explicit cache invalidation logic, which is simpler to orchestrate in a custom application layer than directly inside database-level REST triggers.
15. Conclusion & Actionable Blueprint
Using MySQL REST Service with JSON Duality Views provides a structured, secure framework for integrating AI agents with enterprise data:
Model normalized schemas with clear relational integrity constraints.
Abstract read/write access through JSON Duality Views, restricting which fields can be modified.
Expose endpoints via MRS, securing them with OAuth2 scopes and a least-privilege MySQL service user.
Encapsulate multi-step business logic in Stored Procedures rather than ad-hoc queries.
Bind AI agents to OpenAPI definitions, ensuring the model operates through structured tool calls rather than uncontrolled SQL.



















Comments
Post a Comment