An AI Agent With Tools, Not SQL: MySQL REST Service for a Bank Support Agent, Measured

 

Tested on: MySQL Enterprise Server 26.7.0, MySQL Router 26.7.0 with MySQL REST Service, MySQL Shell 26.7.0, Oracle Linux 9 on OCI | Level: Intermediate

In my previous post a bank support AI agent queried MySQL directly, and Dynamic Data Masking hid card numbers and emails from it. This post goes one step further: what if the agent never gets SQL access at all? I exposed the same bank data through MySQL REST Service (MRS) as exactly two approved operations, and gave a Python agent an MRS login instead of database credentials. The agent could look up a customer in 2.1 ms and add a support note in 12.2 ms. Everything else it tried returned 401, 403 or 404, and masking still applied behind the API.

Architecture

The path is the one the MySQL team suggests for agentic AI: User request → AI agent → approved tool → MySQL REST Service → MySQL. The agent holds an MRS user and a short-lived bearer token, never a MySQL account. MySQL Router serves the REST API over HTTPS and checks authentication, REST roles, endpoints and HTTP verbs before any SQL runs. Inside MySQL, masking policies, a stored procedure and constraints apply a second set of rules.

Results at a glance

Agent request	Result
GET customer summary, no token	401 Unauthorized
Login (MRS native SCRAM authentication)	Bearer token, password never sent
GET customer summary 42	200 in 2.1 to 2.8 ms, card and email masked
POST add support note	200 in 12.2 ms, note stored
Note containing a card number / unknown customer	Rejected by the database (500 to the client)
DELETE, PUT or POST on the customer view	403 Forbidden
A table that is not exposed (/cards)	404 Not Found
Filter on a real email address	0 results (masking applies behind the API)
Path 42 OR 1=1	Returned customer 42 only

Why an API instead of SQL?

A database account is a very wide interface. Even read-only, it lets an agent write any SELECT it can imagine, join any table it can see and scan as much data as it wants. A REST API built for the agent is narrow by design: a fixed set of resources, fixed verbs and fixed parameters. The model can still make bad decisions, but it can only make them through doors you built.

Step 1: set up MySQL REST Service

MRS needs three pieces: REST metadata in the server, MySQL Shell to define the API, and MySQL Router to serve it. All three are in the MySQL Enterprise 26.7 bundle.

-- MySQL Shell, SQL mode
CONFIGURE REST METADATA ENABLED UPDATE IF AVAILABLE;

Two practical lessons from a single-server lab on 26.7:

• Router bootstrap needs InnoDB Cluster or ReplicaSet metadata. On a standalone server, bootstrap failed with "Expected MySQL Server to contain the metadata of MySQL InnoDB Cluster". A single-node InnoDB ReplicaSet fixed it in two commands (dba.configureReplicaSetInstance(), a restart, then dba.createReplicaSet()).
• The --mrs bootstrap options from older guides no longer exist. I added a [mysql_rest_service] section to mysqlrouter.conf, granted the mysql_rest_service_meta_provider and mysql_rest_service_data_provider roles to the Router account, and stored a token-signing secret with mysqlrouter_keyring set ... rest-user jwt_secret .... Without that secret the plugin stopped with "Could not fetch value for 'rest-user' from the keyring".

[mysql_rest_service]
mysql_read_write_route=bootstrap_rw
mysql_read_only_route=bootstrap_ro
mysql_user=mysql_router1_xxxxxxx
router_id=1

After a restart the Router log showed each endpoint being published:

mrs_endpoint INFO Endpoint(path='/bank/support/customerSummary') changed state to 'public'
mrs_endpoint INFO Endpoint(path='/bank/support/addSupportNote') changed state to 'public'

Step 2: design the API around the agent's job

The agent needs to answer "why was my card declined?" and record what it told the customer. That is two capabilities, so the API has two objects.

A read view that is a JSON document, not a table. MRS turns the normalized customers and cards tables into one nested JSON resource (JSON/relational duality), so the agent gets exactly the shape it needs in one call:

CREATE OR REPLACE REST SERVICE /bank COMMENT "Bank support API for AI agents" PUBLISHED;
CREATE OR REPLACE REST AUTH APP "MRS" VENDOR MRS;
ALTER REST SERVICE /bank ADD AUTH APP "MRS";
CREATE OR REPLACE REST SCHEMA /support ON SERVICE /bank FROM `bank` AUTHENTICATION REQUIRED;

CREATE OR REPLACE REST VIEW /customerSummary ON SERVICE /bank SCHEMA /support
AS `bank`.`customers` {
    customerId: customer_id @SORTABLE,
    fullName: full_name,
    email: email,
    city: city,
    segment: segment,
    cards: bank.cards {
        cardId: card_id,
        pan: pan,
        cardStatus: card_status,
        lastDecline: last_decline
    }
}
AUTHENTICATION REQUIRED;

Phone and national ID are simply not in the document. The best way to protect a field from an agent is not to expose it.

A write operation with business rules, not raw INSERT. The agent can add a note only through a stored procedure that validates it:

CREATE PROCEDURE bank.add_support_note (IN p_customer_id INT, IN p_note VARCHAR(400))
BEGIN
  IF p_note IS NULL OR LENGTH(p_note) < 5 OR LENGTH(p_note) > 400 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Note must be 5-400 characters';
  END IF;
  IF p_note REGEXP '[0-9]{12,}' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Note must not contain card or account numbers';
  END IF;
  INSERT INTO bank.support_notes (customer_id, note) VALUES (p_customer_id, p_note);
  SELECT LAST_INSERT_ID() AS note_id;
END;

CREATE OR REPLACE REST PROCEDURE /addSupportNote ON SERVICE /bank SCHEMA /support
AS bank.add_support_note AUTHENTICATION REQUIRED;

Step 3: authentication and authorization

CREATE OR REPLACE REST USER "support_agent"@"MRS" IDENTIFIED BY "...";
CREATE OR REPLACE REST ROLE "SupportAgentRole" ON SERVICE /bank;
USE REST SERVICE /bank;
GRANT REST READ ON SERVICE /bank SCHEMA /support OBJECT /customerSummary TO "SupportAgentRole";
GRANT REST CREATE, READ, UPDATE ON SERVICE /bank SCHEMA /support OBJECT /addSupportNote TO "SupportAgentRole";
GRANT REST ROLE "SupportAgentRole" ON SERVICE /bank TO "support_agent"@"MRS";

Note USE REST SERVICE /bank;: without it, the GRANT statements failed with "No REST SERVICE specified". The resulting grants:

GRANT REST READ ON SERVICE /bank SCHEMA /support OBJECT /customerSummary TO `SupportAgentRole` ON SERVICE /bank
GRANT REST CREATE,READ,UPDATE ON SERVICE /bank SCHEMA /support OBJECT /addSupportNote TO `SupportAgentRole` ON SERVICE /bank

Login. MRS native authentication is a SCRAM-style challenge and response. The client sends its user name and a nonce, the server replies with a salt, iteration count and combined nonce, and the client returns a proof computed from the password. The password itself never crosses the network, and the response is a JWT bearer token. The core of the Python client:

salted     = hashlib.pbkdf2_hmac("sha256", password.encode(), salt, iterations)
client_key = hmac.new(salted, b"Client Key", hashlib.sha256).digest()
stored_key = hashlib.sha256(client_key).digest()
auth_msg   = f"n={user},r={client_nonce},r={server_nonce},s={b64(salt)},i={iterations},r={server_nonce}"
proof      = xor(client_key, hmac.new(stored_key, auth_msg.encode(), hashlib.sha256).digest())
# POST /bank/authentication/login {"clientProof": [...], "nonce": server_nonce, "state": "response"}

One detail cost me a few attempts: the standard SCRAM message includes c=biws, but this MRS version accepted the proof only without it. The official MRS SDKs handle this for you; I wrote it by hand to show what happens on the wire.

Step 4: the agent's tools

The LLM is given two functions, nothing else. Each maps to one REST call:

def get_customer_summary(token, customer_id):
    return http("GET", f"{BASE}/support/customerSummary/{int(customer_id)}", token=token)

def add_support_note(token, customer_id, note):
    return http("POST", f"{BASE}/support/addSupportNote",
                {"pCustomerId": int(customer_id), "pNote": note}, token=token)

The response to get_customer_summary(42), in 2.8 ms (trimmed):

{
  "customerId": 42,
  "fullName": "Customer 000042",
  "email": "c****@example.com",
  "city": "Delhi",
  "segment": "RETAIL",
  "cards": [
    { "cardId": 42, "pan": "XXXXXXXXXXXX0042", "cardStatus": "BLOCKED", "lastDecline": "DAILY_LIMIT" }
  ]
}

The card number and email are masked even though the REST view selects the real columns. MRS runs the query through the Router's own database account, which does not hold the pii_read role from my masking post, so the masking policies apply. API scoping and masking are independent layers, and both held.

Repeated lookups took 2.1 ms end to end over HTTPS, including JSON assembly. Adding a note took 12.2 ms and returned the new ID:

{ "resultSets": [ { "items": [ { "note_id": 1 } ] } ] }

Step 5: the agent tries things outside its tools

An agent can be confused or manipulated by a prompt injection. I made it attempt actions the workflow never needs:

GET    /customerSummary/42      (no token)          401 Unauthorized
DELETE /customerSummary/42                          403 Forbidden
PUT    /customerSummary/42      (change a customer) 403 Forbidden
POST   /customerSummary         (create a customer) 403 Forbidden
PUT    /addSupportNote          (wrong verb)        403 Forbidden
GET    /cards                   (not exposed)       404 Not Found
GET    /customerSummary?q={"email":"customer000042@example.com"}   200, 0 items
GET    /customerSummary/42 OR 1=1                   200, customer 42 only
POST   /addSupportNote  note "card 4111000000000042"   rejected
POST   /addSupportNote  customer 999999             rejected

Only the valid note reached the table:

note_id  customer_id  note
1        42           Customer asked why card was declined: daily limit reached, advised to retry tomorrow.

Honest limitations

• A read endpoint still allows listing. GET /customerSummary without an ID returned a page of customers, masked but still customers the agent did not ask about. Narrow it further with a procedure that takes one customer ID, row-level ownership options, smaller page sizes or a custom authorization procedure.
• Error messages are hidden from the client. Both rejected notes came back as a generic 500 Internal Error, and the reason was not in the Router log at the default level either. Hiding database errors is good security, but an agent needs to know whether to retry, rephrase or give up. Return a structured status from the procedure instead of only raising errors.
• Setup takes some care on 26.7. ReplicaSet metadata, a Router config section and a keyring secret were all needed. None of it is hard, but older tutorials do not match.
• The API does not judge intent. A valid but unhelpful note is still accepted. Business rules in the procedure, rate limits and human review of agent actions remain necessary.

MRS or a conventional service layer?

Use MRS when	Use an application service when
The agent needs well-defined reads and a few controlled writes	Workflows span several systems or need orchestration
Business rules fit in stored procedures and constraints	Rules depend on external services, fraud scores or approvals
You want JSON documents from relational data without writing CRUD code	You need custom error contracts, versioning and caching logic
Fast delivery matters: two endpoints here took a handful of SQL statements	The team already runs an API gateway with its own security model

Direct SQL vs REST API for AI agents

Risk	Agent with a SQL account	Agent with MRS tools
Excessive privileges	Limited by GRANTs, but any SELECT is possible	Two objects, fixed verbs
Unexpected model-generated SQL	Runs as written	No SQL at all, only parameters
Access to unrelated tables	Anything granted	404 for anything not exposed
Prompt-injection actions	Whatever the account allows	Only the two tools, and 403 for the rest
Changes outside the workflow	Possible if any write privilege exists	Only through a validating procedure

Recommendations

• Design tools from the agent's job, not from the schema. Two endpoints were enough here.
• Expose documents, not tables. JSON/relational duality gives the agent one call with only the fields it needs.
• Put writes behind procedures with explicit business rules, and return clear status codes the agent can act on.
• Give every agent its own MRS user and role, and grant per object and per verb.
• Keep database controls underneath. Masking, constraints and audit still apply when the API is bypassed or misconfigured.
• Remove list access where the workflow only needs one record at a time.

Summary

With MySQL REST Service, a bank support AI agent did its job through two purpose-built operations, a customer summary in 2.1 ms and a validated support note in 12.2 ms, without ever holding database credentials or writing SQL. Every action outside those tools was refused with 401, 403 or 404, invalid notes were rejected inside the database, and masking still protected card numbers behind the API. The architecture does not make an agent trustworthy, but it makes its mistakes small and predictable.

References: MySQL REST Service Reference Manual, Quickstart Guide and SQL Reference; Oracle MySQL blog posts "Introducing the MySQL REST Service", "Setting Up REST Service for MySQL HeatWave" and "Introducing JSON/Relational Duality for MRS".

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox with fictional data. 

Tamilselvan Perumal is an Oracle ACE Associate and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.