Build a Secure AI Data API with MySQL REST Service

Introduction

AI agents are becoming increasingly capable of interacting with enterprise data, enabling applications to answer customer questions, retrieve orders, check inventory, and perform business operations. However, giving an AI agent unrestricted SQL access to a production database can introduce significant security and governance risks. For example, consider a customer-support AI assistant that needs to answer a simple question such as, “What is the status of order ORD10025?” The agent may only require permission to retrieve the order status and basic order information; it does not need access to customer passwords, payment details, internal employee records, audit data, or unrelated database tables. A safer approach is to place a deliberately designed API between the AI agent and the database, creating a controlled flow such as User Request → AI Agent → Approved API Operation → MySQL REST Service → MySQL. This article explores how MySQL REST Service (MRS) can provide a controlled interface for exposing selected MySQL data and operations through REST APIs. Using a customer-support application as an example, it covers database design, REST endpoints, authentication and authorization, client invocation, AI-agent integration, security boundaries, and guidance on when MRS is appropriate compared with a conventional application-service layer. The central idea is simple: the AI should receive only the database capabilities it actually needs, rather than unrestricted access to the database itself.

1. Why Should an AI Agent Not Have Direct SQL Access?

Traditional applications usually provide a fixed set of operations such as viewing customer details, checking orders, updating shipping information, or creating support requests. An AI agent is different because it can dynamically interpret natural-language requests and generate actions. If the agent is given unrestricted database credentials, the architecture becomes User → AI Agent → SQL Generation → MySQL. For example, when a customer asks, “Show me the status of my order,” the AI may generate a query against the orders table. However, a malicious prompt or poorly designed agent could potentially lead to queries against unrelated or sensitive tables. Therefore, the key question is not simply whether the AI can generate valid SQL, but what database capabilities the AI should actually be allowed to use. A safer approach is User → AI Agent → Approved Tool → MRS REST Endpoint → MySQL, where the AI can access only the operations specifically exposed by the application.

2. MySQL REST Service as a Security Boundary

The main idea is to expose only the business operations that the application or AI agent actually needs instead of providing unrestricted SELECT, INSERT, UPDATE, and DELETE access. For example, a customer-support application could expose endpoints such as GET /orders/{order_id}, GET /customers/{customer_id}/orders, POST /support-tickets, and PATCH /orders/{order_id}/shipping-address. If a customer asks, “What is the status of order ORD10025?”, the AI can call GET /orders/ORD10025, and MRS returns only the approved order information. This creates a clear security boundary: AI Agent → MRS REST API → MySQL → Order Data. The AI therefore focuses on understanding the user's request, while MRS controls which database operations are actually available.

3. Realistic Example: Customer Support AI Assistant

For this article, consider an e-commerce company called ShopSphere that wants to build an AI-powered customer-support assistant. The application manages customers, products, orders, order items, and support tickets. Customers can ask questions such as “Where is my order?”, “What products did I order?”, or “Create a support ticket for my damaged product.” The AI agent should be able to perform these tasks without having direct or unrestricted SQL access to MySQL. Instead, the agent interacts with approved REST operations exposed through MRS.

This approach keeps MySQL behind a controlled API while allowing the AI to access only the business capabilities it requires.

4. Design the MySQL Database Schema

To support this application, we can create a simple relational database containing five main tables: customers, products, orders, order_items, and support_tickets.



For example, an order can belong to a customer, contain multiple order items, and each order item can reference a product. A support ticket can also be associated with both the customer and the relevant order. This relational structure provides enough business data to demonstrate how MRS can expose controlled REST APIs to the AI assistant.

Here is a shorter, polished version with a practical code example:

5. Identify the Operations the AI Actually Needs

Before creating REST APIs, we should identify exactly what the AI agent needs to do. For example, in the ShopSphere customer-support application, the AI may need to check an order, list a customer’s orders, view order items, and create or update support tickets. Instead of giving the AI direct database access, we expose only these approved business operations through MRS.

For example, if a customer asks, “What is the status of order ORD10025?”, the AI can call an approved endpoint such as:

GET /orders/ORD10025

The API can return only the required information:


Similarly, the AI can use endpoints such as:

GET /customers/101/orders
GET /orders/ORD10025/items
POST /support-tickets
PATCH /support-tickets/501

However, operations such as DROP TABLE, ALTER TABLE, arbitrary SQL execution, employee data access, password data, or payment information should not be exposed to the AI. This follows an important security principle: “Expose capabilities, not database power.” The API should represent the application's intended business operations rather than exposing the entire database.

This keeps the section practical while clearly showing the AI → approved API → database security boundary.

6. Configure MySQL REST Service

The next step is to expose the selected MySQL resources through MySQL REST Service (MRS).

The exact MRS configuration depends on the MRS version and deployment environment. However, the architectural principle remains the same: expose only the resources and operations that the AI agent actually needs.

For our customer-support application, we could expose a small set of controlled APIs:

  • GET /orders/{order_id} — retrieve order information

  • GET /customers/{customer_id}/orders — retrieve a customer's orders

  • GET /orders/{order_id}/items — retrieve items belonging to an order

  • POST /support-tickets — create a support ticket

The AI agent therefore interacts with REST APIs rather than directly executing SQL against MySQL.

Instead of giving the AI broad database access, the application exposes only the business capabilities required by the support workflow.

7. Example GET Operation

Suppose a customer asks:

“What is the status of order ORD10025?”

The AI agent determines that it needs to retrieve the order information. It invokes the appropriate REST operation:

GET /orders/ORD10025

A possible response is:


The AI can then convert the structured API response into a natural-language answer:

“Your order ORD10025 has been shipped and is currently in transit.”

Notice what the AI does not need to do.

It does not generate SQL such as:


Instead, the database operation is hidden behind the REST API.

This provides a cleaner separation of responsibilities:

  • AI agent — understands the customer's request.

  • REST API — defines the permitted operation.

  • MRS — exposes the database resource through the API.

  • MySQL — performs the underlying data operation.

This approach also reduces the amount of database access that needs to be granted to the AI layer.

8. Example Create Operation

REST APIs can also allow the AI agent to perform controlled business actions, not just retrieve information.

Suppose a customer says:

“My product arrived damaged. Please create a support ticket.”

The AI agent identifies that a support ticket needs to be created and invokes:

POST /support-tickets

The request could contain:

Content-Type: application/json

The API might return:


The AI can then respond to the customer:

“I created support ticket #5012 for your damaged product.”

The important security principle is that the AI has not been given unrestricted INSERT access to the database.

This allows the application to control which fields can be submitted, which business rules are enforced, and which database operation can be performed.

Therefore, even when the AI performs a real database modification, the operation remains explicitly defined, limited, and governed by the application's API layer.

9. Authentication and Authorization

A REST API should not be considered secure simply because it exists. Before an AI agent or application can access a protected resource, the system must establish who is making the request and what that caller is permitted to do.

Authentication answers:

Who is calling the API?

Authorization answers:

What is that caller allowed to do?

For example, the customer-support application could use different permissions for different types of clients.

Customer-Support Agent

The support AI may be allowed to:

  • GET /orders/{order_id}

  • GET /customers/{customer_id}/orders

  • GET /orders/{order_id}/items

  • POST /support-tickets

However, it should not be allowed to:

  • Delete customer records

  • Update product prices

  • Retrieve employee payroll information

  • Retrieve payment-card information

  • Perform arbitrary SQL operations

Administrative Application

An administrative application may require additional capabilities, such as managing customers, products, orders, or support operations.

The important point is that different clients should receive only the permissions required for their specific responsibilities.

This creates a least-privilege security model at the API layer.

For an AI application, this is particularly important because an AI agent may dynamically decide which API operation to call. The authorization layer should therefore remain independent of the AI's decision-making.

In other words:

AI decides what it wants to do
↓
Authorization determines whether it is allowed
↓
MRS executes only an authorized operation

The AI should never be treated as inherently trusted simply because it is part of the application.

Authentication establishes the caller's identity, while authorization enforces the boundaries of what that caller can access or modify.

9. Authentication and Authorization

A REST API should not be considered secure simply because it exists. Authentication determines who is calling the API, while authorization determines what that caller is permitted to do. In a secure architecture, the user first authenticates through an Identity Provider, after which the AI application obtains and uses an authentication token to communicate with MySQL REST Service (MRS). MRS then validates the caller's identity and applies the appropriate authorization rules before allowing access to the underlying MySQL resource. For example, a customer-support agent may be permitted to retrieve order details, customer orders, and order items, and create support tickets, while being denied access to operations such as deleting customers, changing product prices, viewing employee payroll information, or retrieving payment-card data. An administrative application, on the other hand, may receive additional permissions based on its role and business requirements. This separation ensures that every API request is authenticated and authorized before reaching the database, implementing the principle of least privilege at the API layer and preventing the AI agent from gaining broader access than the business process requires.

11. AI Agent with Controlled Tools

Now we can introduce the agentic AI architecture. Instead of giving the AI agent a MySQL username and password or unrestricted SQL access, we define a collection of approved tools that represent specific business capabilities. For example, the agent may have tools such as get_order, list_customer_orders, get_order_items, and create_support_ticket. The AI agent decides which approved tool is required based on the user's request, while each tool maps to a specific REST API exposed through MySQL REST Service (MRS). For example, when a customer says, “Create a ticket for order 10025 because the product arrived damaged,” the AI agent can invoke create_support_ticket(), which calls POST /support-tickets. MRS then processes the request and performs the corresponding operation against MySQL, ultimately creating the support ticket. The key security principle is that the model does not receive unrestricted SQL privileges. It can only use the tools that the application explicitly provides, creating a controlled boundary between the AI agent and the database.

12. Example Client Code

A web application, mobile application, chatbot, or AI agent can consume the MRS API using a standard HTTP client. The client does not need to know how the underlying MySQL tables are implemented; it only needs to understand the published REST endpoints and the required request and response formats. For example, a Python client can retrieve an order using an authenticated GET request:

import requests

url = "https://api.example.com/orders/10025"

headers = {
    "Authorization": "Bearer ACCESS_TOKEN",
    "Accept": "application/json"
}

response = requests.get(
    url,
    headers=headers,
    timeout=10
)

response.raise_for_status()

order = response.json()

print(order)

Similarly, the client can create a support ticket by sending a POST request with a JSON payload:

import requests

url = "https://api.example.com/support-tickets"




In a production application, the access token should be obtained and managed securely rather than hard-coded into the source code. The client should also handle authentication failures, authorization errors, validation errors, timeouts, and other API failures appropriately.

The same REST APIs can be consumed by many different types of clients, including AI agents, web applications, mobile applications, chatbots, internal enterprise applications, and integration services. This makes the MRS layer a reusable integration boundary between applications and MySQL. Instead of building separate database-access logic for every client, multiple applications can use the same governed REST APIs while the underlying database remains protected behind the API layer.

13. Security Boundary: MRS vs Direct SQL

The most important architectural comparison is between direct database access and controlled API access. In a direct SQL architecture, the user interacts with an AI agent, which generates SQL and executes it using database credentials. This approach can introduce risks such as excessive database privileges, unexpected SQL statements, access to unrelated tables, accidental updates, destructive operations, and actions triggered by prompt injection. In contrast, an MRS-based architecture places an explicit API boundary between the AI agent and MySQL. The user interacts with the AI agent, which selects an approved tool, the tool calls an MRS REST endpoint, and MRS applies authentication and authorization before accessing the permitted MySQL resource. For example, if the AI agent is provided only with GET /orders/{id} and POST /support-tickets, it cannot simply decide to execute DELETE FROM customers; because that database capability has not been exposed through its available tools. This creates a clearer capability boundary and limits the operations available to the AI agent. However, MRS should not be treated as a complete defense against prompt injection or malicious requests. The API layer must still enforce strong authentication, authorization, input validation, business rules, rate limiting where appropriate, and secure handling of sensitive information. The goal is not merely to prevent the AI from generating dangerous SQL, but to ensure that every action the AI can perform is explicitly defined, authorized, and constrained by the application architecture.

14. Bonus: Combining REST APIs with JSON Duality Views

A particularly interesting architecture is to combine Oracle JSON Relational Duality Views with a REST API layer when the application needs to expose relational data as application-oriented JSON. JSON Relational Duality Views are an Oracle Database capability that allow related relational tables to be represented as hierarchical JSON documents while maintaining the underlying relational model. For example, a traditional schema may contain CUSTOMERS, ORDERS, ORDER_ITEMS, and PRODUCTS tables. Instead of requiring an application to join these tables and construct the JSON structure itself, a Duality View can provide a document-oriented representation containing the customer, orders, and order items in a single logical document.

For example, the underlying relational model could be represented to the application as:


A simplified Oracle JSON Relational Duality View could conceptually look like:


The exact syntax and available features should be aligned with the Oracle Database version being used, but the architectural idea is important: relational tables remain the source of truth while the application receives a document-oriented JSON representation.

This approach can reduce the impedance mismatch between normalized relational data and the JSON structures commonly consumed by modern applications and AI systems. Instead of requiring every client to understand multiple tables, joins, and relationships, the API can expose a meaningful business document. For AI applications in particular, this can make structured business information easier to consume while keeping the underlying relational model, constraints, and database governance intact.

Important distinction: in the MySQL MRS architecture discussed in this article, MRS provides the REST interface to MySQL resources. JSON Relational Duality Views are an Oracle Database feature, so combining both concepts should be understood as a broader architectural pattern rather than implying that MySQL MRS directly provides Oracle Duality Views.

15. When Should Developers Use MRS?

MySQL REST Service (MRS) is particularly useful when an application needs to expose MySQL data and operations through REST APIs without requiring developers to build a large amount of custom CRUD middleware. Common use cases include mobile application backends, web applications, product catalogs, customer portals, internal enterprise applications, operational dashboards, AI assistants, chatbots, controlled AI-agent tools, and CRUD-oriented business applications. For example, a mobile application that needs to retrieve customer and order information can communicate with an MRS endpoint such as GET /customers/101/orders, allowing the request to flow through the REST layer to MySQL without the mobile application connecting directly to the database.

For example, the application could request:

GET /customers/101/orders
Authorization: Bearer ACCESS_TOKEN
Accept: application/json

The API could return:


However, MRS should not be considered a replacement for every application-service architecture. When an operation contains substantial business logic, a conventional application-service layer may be more appropriate. For example, a refund operation might require checking the order status, validating the refund amount, updating multiple database records, calling an external payment provider, creating an audit record, publishing an event, and notifying the customer.

MRS is therefore most effective when the required operations are relatively well-defined and can be safely exposed as REST resources. Complex workflows, multiple transactions, external service integrations, advanced authorization, event processing, payment processing, and extensive domain logic may benefit from an additional application-service layer. The key is to choose the architecture according to the complexity and security requirements of the business operation rather than treating MRS as a universal replacement for application middleware.

The database can contain multiple business domains such as:

MySQL Database
├── Customers
├── Orders
├── Order Items
├── Products
└── Support Tickets

The AI agent does not receive unrestricted access to these tables. Instead, it receives only the tools and API operations that the application intentionally exposes. This creates a clear security boundary between the AI and the database while allowing applications to consume MySQL data through standard REST interfaces.

The overall principle is simple:

Expose capabilities, not unrestricted database access.

By combining MRS with strong authentication, authorization, input validation, business rules, and carefully designed AI tools, developers can build applications where AI can interact with real business data and perform useful actions while keeping database access controlled and auditable.

Conclusion

Building an AI-enabled application does not necessarily mean giving an AI agent direct access to the database. A more secure approach is to provide the AI with the smallest set of capabilities required to complete its task. MySQL REST Service (MRS) can help establish this boundary by placing a controlled REST API between the AI agent and MySQL. The architecture can be summarized as User Request → AI Agent → Approved API Operation → MySQL REST Service → Authentication and Authorization → MySQL. This approach can reduce the need for custom CRUD middleware while providing a reusable REST interface for web applications, mobile applications, chatbots, and AI agents. For a customer-support assistant, the available capabilities might be limited to get_order(), list_customer_orders(), get_order_items(), and create_support_ticket() rather than unrestricted SQL access. By exposing only these operations, the application can reduce the risk of excessive database privileges, unexpected model-generated SQL, accidental access to unrelated data, and actions outside the intended workflow. However, MRS should be considered one component of a broader defense-in-depth strategy; secure AI applications still require authentication, authorization, input validation, business-rule enforcement, auditing, sensitive-data protection, and defenses against prompt injection. The combination of MySQL relational data, REST-based access, and controlled AI tools provides a practical architecture for building modern AI-enabled applications without turning the AI model into an unrestricted database administrator. The key architectural question is therefore not “Can my AI agent access MySQL?”, but rather “What is the smallest, safest, and most useful set of MySQL capabilities that my AI agent should be allowed to access?” That is where a deliberately designed REST API becomes valuable: it transforms database access from an unrestricted capability into a set of controlled business operations.

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications