Tested on: MySQL Enterprise Server 26.7.0 (Commercial), Oracle Linux 9 on OCI | Level: Intermediate
AI agents are getting direct access to enterprise databases. That raises a simple question every bank must answer: just because an AI agent can query the customer table, should it see every card number, email and ID it returns? I built a bank support scenario with 200,000 fictional customers and tested MySQL Dynamic Data Masking on MySQL Enterprise 26.7. The AI agent's account got XXXXXXXXXXXX0042 where a fraud analyst got the real card number, from the same SQL. Filters on masked columns could not be used to guess hidden digits, every attempt by the agent to escape its limits was blocked and recorded by Enterprise Audit, and a single-customer lookup still took 0.000 s.
Architecture
The flow is the one the MySQL team describes for agentic access: End user → AI application → agent → restricted DB account → MySQL security controls → enterprise data. The agent never connects as a DBA. It uses a service account, ai_support_agent, and three controls inside the server decide what that account can do, what it can see, and what is recorded.
Results at a glance
| Test | Result |
|---|---|
| Same query, AI agent vs fraud analyst | Agent: masked card, email, phone, ID. Analyst with role pii_read: clear values |
Same query as root (DBA, no pii_read) | Masked |
| JSON context the agent would send to the LLM | No real card, email, phone or ID |
WHERE pan = '<real number>' as the agent | Empty set: filters run on the masked value |
| Agent tries SET ROLE, UPDATE, copy table, OUTFILE, read mysql.user, DROP POLICY | All blocked (errors 3530, 1142, 1227) and recorded by Enterprise Audit |
| Full scan, 200,000 rows, 3 masked columns | 0.059 s without policy, 0.063 s clear, 0.276 s masked |
| Single customer lookup | 0.000 s for every identity |
The scenario
A customer asks the bank's support assistant: "Why was my card declined?" To answer, the agent needs the customer, the card status and the decline reason. It does not need the full card number, the full email address, the phone number or the national ID. Those values should never reach the LLM prompt, its logs or the chat history.
The lab uses two fictional tables, bank.customers (200,000 rows: name, email, phone, national ID, city, segment) and bank.cards (card number, status, last decline reason), and three identities:
ai_support_agent: the agent's service account, rolesupport_ro(SELECT on the two tables only).fraud_analyst: a human investigator, rolessupport_roandpii_read.masking_admin: the only non-root account allowed to manage masking policies.
Step 1: enable Dynamic Data Masking
Dynamic Data Masking became generally available in MySQL Enterprise 9.7 LTS. I used the current developer download, 26.7.0, and enabled three components:
-- as an administrative user, from the shell:
-- mysql -uroot -p -D mysql < /usr/share/mysql-26.7/install_component_object_policy.sql
INSTALL COMPONENT 'file://component_masking';
INSTALL COMPONENT 'file://component_masking_functions';
SELECT component_urn FROM mysql.component;
file://component_masking
file://component_masking_functions
file://component_object_policy
The object policy component stores policies in mysql.column_masking_policies. Note the path: on this version the install script lives in /usr/share/mysql-26.7/, not /usr/share/mysql/.
Step 2: roles and least privilege
CREATE ROLE support_ro, pii_read;
GRANT SELECT ON bank.customers TO support_ro;
GRANT SELECT ON bank.cards TO support_ro;
-- pii_read has no table privileges: it only unlocks clear values
CREATE USER 'ai_support_agent'@'localhost' IDENTIFIED BY '...';
CREATE USER 'fraud_analyst'@'localhost' IDENTIFIED BY '...';
CREATE USER 'masking_admin'@'localhost' IDENTIFIED BY '...';
GRANT support_ro TO 'ai_support_agent'@'localhost';
GRANT support_ro, pii_read TO 'fraud_analyst'@'localhost';
SET DEFAULT ROLE ALL TO 'ai_support_agent'@'localhost', 'fraud_analyst'@'localhost';
GRANT MANAGE_DATA_MASKING_POLICY ON *.* TO 'masking_admin'@'localhost';
Step 3: masking policies
A policy is a CASE expression with a gatekeeper function. Here, only sessions with the pii_read role active see clear values:
CREATE MASKING POLICY mask_pan_pol(p)
CASE WHEN CURRENT_ROLE_IN('pii_read') THEN p ELSE mask_pan(p) END;
CREATE MASKING POLICY mask_email_pol(e)
CASE WHEN CURRENT_ROLE_IN('pii_read') THEN e
ELSE CONCAT(LEFT(e, 1), '****@', SUBSTRING_INDEX(e, '@', -1)) END;
CREATE MASKING POLICY mask_phone_pol(ph)
CASE WHEN CURRENT_ROLE_IN('pii_read') THEN ph ELSE mask_inner(ph, 4, 2) END;
CREATE MASKING POLICY mask_id_pol(i)
CASE WHEN CURRENT_ROLE_IN('pii_read') THEN i ELSE mask_inner(i, 0, 4) END;
ALTER TABLE bank.customers
ALTER COLUMN email SET MASKING POLICY mask_email_pol,
ALTER COLUMN phone SET MASKING POLICY mask_phone_pol,
ALTER COLUMN national_id SET MASKING POLICY mask_id_pol;
ALTER TABLE bank.cards
ALTER COLUMN pan SET MASKING POLICY mask_pan_pol;
The policy becomes part of the table definition, so it travels with the column rather than living in application code:
`pan` varchar(19) MASKING POLICY `mask_pan_pol` DEFAULT NULL,
Step 4: one question, three identities
SELECT c.customer_id, c.full_name, c.email, c.phone, c.national_id,
k.pan, k.card_status, k.last_decline
FROM bank.customers c JOIN bank.cards k ON k.customer_id = c.customer_id
WHERE c.customer_id = 42;
-- ai_support_agent (role support_ro)
email phone national_id pan card_status last_decline
c****@example.com +91-XXXXXXXXX42 XXXXXXXX0042 XXXXXXXXXXXX0042 BLOCKED DAILY_LIMIT
-- fraud_analyst (roles pii_read, support_ro)
email phone national_id pan card_status last_decline
customer000042@example.com +91-90000-00042 ID0000000042 4111000000000042 BLOCKED DAILY_LIMIT
-- root (DBA, no pii_read role active)
email phone national_id pan card_status last_decline
c****@example.com +91-XXXXXXXXX42 XXXXXXXX0042 XXXXXXXXXXXX0042 BLOCKED DAILY_LIMIT
The agent has everything it needs to answer: the card is BLOCKED and the last decline was DAILY_LIMIT. Even root saw masked values, because masking follows the active role, not the account's power. That is useful, but root can still grant itself the role or change policies, which is why the audit layer below matters.
Step 5: what reaches the LLM prompt
Agents usually turn query results into a context block for the model. Built by the agent's own account, that context contains no sensitive values:
{
"card": "XXXXXXXXXXXX0042",
"name": "Customer 000042",
"email": "c****@example.com",
"phone": "+91-XXXXXXXXX42",
"card_status": "BLOCKED",
"customer_id": 42,
"national_id": "XXXXXXXX0042",
"last_decline": "DAILY_LIMIT"
}
This is the main benefit for AI applications: sensitive data is removed before it enters the prompt, so it cannot leak through model output, prompt logs, tracing tools or a third-party model provider.
Step 6: is the agent still useful?
- Lookup by customer ID or name: works.
- Card status and decline reasons: works, these columns are not masked.
- Statistics such as
GROUP BY last_decline: works. - "My card ends in 0042":
WHERE pan LIKE '%0042'works because the last four digits survive masking. It returned five customers with cards ending 0042, so the agent must combine it with the customer ID, exactly as a human agent would.
Step 7: what if the AI generates an unexpected query?
LLMs generate SQL you did not write. I tested what a curious or manipulated agent could do with its own account.
Probing hidden values with filters. This was the test I cared about most. If filters ran on the real value, an agent could guess a card number digit by digit with WHERE clauses. They do not:
SELECT customer_id, pan FROM bank.cards WHERE pan = '4111000000000042'; -- Empty set
SELECT COUNT(*) FROM bank.cards WHERE pan LIKE '4111%'; -- 0
SELECT customer_id FROM bank.customers WHERE email = 'customer000042@example.com'; -- Empty set
SELECT SUBSTRING(pan, 1, 6) FROM bank.cards WHERE customer_id = 42; -- XXXXXX
Filters and functions see the masked value. That closes the inference route, but it has a practical side effect: an agent that searches by full email or card number silently gets "not found". Design agent tools to search by customer ID, name or last four digits, and treat an empty result as "not visible", not "does not exist".
Escaping its limits. Every attempt failed:
SET ROLE pii_read ERROR 3530: `pii_read`@`%` is not granted to `ai_support_agent`@`localhost`
UPDATE bank.cards ... ERROR 1142: UPDATE command denied
CREATE TABLE bank.pan_copy AS SELECT ERROR 1142: INSERT, CREATE command denied
SELECT ... INTO OUTFILE ERROR 1227: you need the FILE privilege
SELECT user, host FROM mysql.user ERROR 1142: SELECT command denied
DROP MASKING POLICY mask_pan_pol ERROR 1227: you need the MANAGE_DATA_MASKING_POLICY privilege
Step 8: defence in depth with Enterprise Audit
Masking controls what the agent sees. Audit proves what it did. I installed the Enterprise Audit component and logged everything for the agent's account only:
-- mysql -uroot -p -D mysql < /usr/share/mysql-26.7/audit_log_component_filter_install.sql
SELECT audit_log_filter_set_filter('log_agent', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('ai_support_agent@localhost', 'log_agent');
The JSON audit log recorded the normal reads and every blocked attempt, with the error status (trimmed):
"class": "table_access", "event": "read", "table": "customers", "sql_command": "select"
"sql_command": "set_role", "status": 3530, "query": "SET ROLE pii_read"
"sql_command": "update", "status": 1142, "query": "UPDATE bank.cards SET card_status = 'ACTIVE' WHERE customer_id = 42"
"sql_command": "drop_masking_policy", "status": 1227, "query": "DROP MASKING POLICY mask_pan_pol"
A spike of non-zero statuses from an agent account is exactly the signal a security team wants: the agent is attempting things outside its workflow, perhaps because of a prompt injection. One caution: the audit log stores the full SQL text. If an agent ever puts a real card number into a WHERE clause, that literal lands in the audit file, so protect and rotate audit logs like any other sensitive data.
Step 9: the cost
A full scan of 200,000 customers reading three masked columns took 0.276 s for the agent, against 0.059 s with no policy, about 0.36 µs per masked value. For a reader with pii_read the policy check itself added very little (0.063 s). A single-customer lookup, which is what a support agent does, took 0.000 s for every identity. Masking cost scales with the number of rows and masked columns returned, which is one more reason to keep agent queries narrow.
Database controls vs application and model controls
| Layer | What it does well | What it cannot do |
|---|---|---|
| Prompt and model guardrails | Steer behaviour, refuse risky requests, filter output | Cannot guarantee anything: a manipulated prompt can bypass them |
| Application / API layer | Expose only approved tools, validate inputs, rate limits | Every new tool or code path must repeat the rules |
| Roles and least privilege | Decide which tables and actions the agent can use at all | Cannot hide individual columns' values from a permitted SELECT |
| Dynamic Data Masking | Enforced in the server for every query, including SQL the AI invents | Does not stop the agent reading rows it is allowed to read; masking rules must fit the business |
| Enterprise Audit | Evidence of every query and every blocked attempt | Detects after the fact; logs need protection themselves |
Recommendations for AI applications on enterprise data
- Give every agent its own database account. Never reuse an application or DBA account. Masking and audit both depend on knowing who is asking.
- Grant clear access through a role such as
pii_read, never by default. Masking follows the active role. - Mask by default, unmask by exception. Keep the last four digits or the email domain when they help the task.
- Design agent tools around IDs, not secrets. Filters see masked values, so searching by full card number or email will not work for the agent.
- Separate policy administration. Grant
MANAGE_DATA_MASKING_POLICYto a dedicated account, not to the agent or the application. - Audit agent accounts and alert on non-zero statuses such as 1142, 1227 and 3530.
- Remember what masking is not: it does not encrypt storage, limit which rows an agent reads, or replace network security. Combine it with encryption at rest, row-level restrictions where needed, and options such as Enterprise Firewall or MySQL REST Service for a narrower interface.
Summary
MySQL Dynamic Data Masking let a bank support AI agent do its job, finding the customer, the card status and the decline reason, while card numbers, emails, phones and IDs stayed masked in every result and in the LLM prompt. Because masking runs inside the server, it applied to SQL the AI generated on its own, and filters could not be used to probe hidden digits. Roles kept the agent read-only, every attempt to escape was blocked, and Enterprise Audit recorded it all. Masking cost about 0.36 µs per value on a full scan and nothing measurable on a single lookup. Database-level controls do not replace application guardrails, but they are the layer an AI cannot talk its way around.
References: Oracle MySQL blog posts "Dynamic Data Masking in MySQL: Protect Sensitive Data Without App Changes" and "Dynamic Data Masking (DDM) with MySQL Enterprise Edition 9.7: Reduce your sensitive data exposure", and the MySQL Reference Manual chapters on data masking, dynamic masking policies and gatekeeper functions.
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.
