Protecting Sensitive Data from AI Agents with MySQL Dynamic Data Masking

Article Overview

AI agents now query production databases directly. When a support bot, a reconciliation job, or a fraud pipeline runs a SELECT, the result does not stay inside your application. It is turned into text, placed in a prompt, and sent to a model endpoint that you do not control. From there it may land in logs, caches, or even training data. A grant that is harmless for a careful human becomes a data flow that leaves your network.

This article shows a practical way to solve that. The goal is simple: let an AI agent do real support work — find the customer, read their orders, check the open ticket, write a reply — while the database itself makes sure that no full card number, email address, phone number, or government ID ever reaches the model. It is a hands-on database article, with SQL, Python, and real output captured from a MySQL 8.4 instance.

Table of Contents

1. Why agent access is different
2. The scenario
3. What MySQL actually provides
4. The architecture
5. Building the masking layer
6. The agent in action
7. One customer, four roles
8. When the agent tries something unexpected
9. Where masking stops working
10. Common masking pitfalls
11. Moving to the Enterprise functions
12. Frequently asked questions
13. Production hardening checklist
14. Key takeaways

1. Why Agent Access Is Different

It is easy to treat an AI agent like any other service account. The real difference shows up after the rows come back.

When a human support rep opens a customer record, the values stay in their head for a few minutes. When an AI agent pulls that same record, every column is copied into the prompt, travels to an outside model provider, and may be stored there. The agent does not pick and choose. It uses everything it receives, because it received everything.

The usual fix is a strict system prompt: “do not use sensitive fields.” That is a guardrail — a request made to a system that can be talked out of it. The database fix is to never grant the data in the first place. That is a constraint — it cannot be talked out of anything. Both are useful, but only the database control keeps working if the agent is tricked, has a bug, or gets an unusually clever question.

An AI agent does not filter what it reads — it uses all of it. So the answer is not a careful prompt. The answer is a careful grant.

Two environments, not one

Many teams picture a single environment: the database. Once an agent is involved there are two. The first is the database, where your grants decide what can be returned. The second is the model context, where everything returned ends up at a third party. This article is about the border between those two, and about keeping it clean by design instead of by instruction.

2. The Scenario: Anschoclates, the Support Agent

ACME Retail runs an AI support agent named Anschoclates. When a customer opens a chat, Anschoclates must handle the whole case without a human:

1. Identify the account. The customer writes: “It’s aisha.alqahtani@example.com and my phone ends in 0142.”
2. Find their orders and report status, amount, and date.
3. Read the open ticket so the customer does not have to repeat the story.
4. Confirm the card on file well enough to say “your Visa ending 1111.”
5. Write a reply and confirm where updates will be sent.

Here is what Anschoclates has no use for, and must never be given:

Category	Field	Why the agent must not see it
Payment	payment_methods.pan	Full card number. It is in PCI DSS scope and must never leave the cardholder data environment.
Payment	payment_methods.billing_zip	ZIP code plus last four digits is enough to pass many card checks.
Identity	customers.email	A full address is a login ID and a phishing target.
Identity	customers.phone	A full number helps SIM-swap and phone-scam attacks.
Government ID	customers.gov_id	The most damaging value for identity theft.
Identity	customers.date_of_birth	Often used as a security question.
Address	orders.ship_street, ship_postal	A postal code narrows a person down to a few streets.
Internal	support_tickets.internal_note	Staff-only comments. They must never be repeated to a customer.

One idea drives the whole design: the agent needs to know which record it is working on, not the contents of its sensitive fields. The database’s job is to make that rule mechanically true.

3. What MySQL Actually Provides

“Dynamic data masking” means different things to different vendors. Most project problems start in the gap between the marketing page and the actual CREATE TABLE syntax, so it is worth being precise.

3.1 MySQL Enterprise: a library of masking functions

MySQL Enterprise Edition includes MySQL Enterprise Data Masking and De-Identification . It started as a plugin, and since MySQL 8.0.33 (and in 8.4) it is also available as components. It gives you a set of functions :

Kind	Functions
General purpose	mask_inner(), mask_outer()
Payment card	mask_pan(), mask_pan_relaxed()
Government ID	mask_ssn(), mask_uk_nin(), mask_canada_sin()
Bank account	mask_iban()
Other IDs	mask_uuid()
Synthetic data	gen_rnd_pan(), gen_rnd_email(), gen_rnd_ssn(), gen_rnd_us_phone(), gen_rnd_iban(), gen_rnd_uuid(), gen_range()
Dictionary substitution	gen_dictionary(), gen_blocklist(), masking_dictionary_term_add()

Two facts are often missed:

• Enterprise only. You will not find any of them in MySQL Community Server.
• No masked columns. MySQL has no MASKED clause in CREATE TABLE. These are functions you call, not column settings. The documented pattern is to call them inside a view :

MySQL Enterprise Edition · vendor pattern

CREATE VIEW masked_customer AS
SELECT id, first_name, last_name,
       mask_ssn(CONVERT(ssn USING binary)) AS masked_ssn
FROM customer;

This view pattern is the base of everything that follows.

3.2 Community vs Enterprise

On MySQL Community Server the mask_* and gen_rnd_* functions are simply not there. Calling one returns ERROR 1305 (42000): FUNCTION ... does not exist. This matters because many teams build on Community and deploy on Enterprise, or run Community through a managed cloud service. Check before you design:

Quick check · MySQL

-- Fails with ERROR 1305 on Community, or if the library is not installed
mysql> SELECT mask_inner('test value', 2, 2);
-- Enterprise with masking installed returns:  te******ue

Edition	mask_* / gen_rnd_* functions	Masked-column DDL
MySQL Community Server	Not available	Not available
MySQL Enterprise Edition	Available after installing the plugin or component	Not available in any edition

3.3 What this means for the design

Because no MySQL edition has masked columns, the one approach that works on every edition, every version, and every managed cloud offering is:

Deterministic masking functions + role-scoped views + no access to the base tables.

Together these give you real dynamic masking: the same row looks different depending on who is asking. On Enterprise Edition you later swap the bodies of the custom functions for the built-in mask_* functions (Section 11). Nothing else has to change.

Install the masking library before you rely on it

Even on an Enterprise build, the mask_* and gen_rnd_* functions are not active until the plugin or component is installed. Run SELECT mask_inner('test', 1, 1); first. If you are on Community, there is no built-in replacement — use the custom functions in Section 5.

4. The Architecture

Figure 1: The masking boundary. The agent’s account can reach exactly five objects — four views and one verification function. Sensitive columns are never selected, so they can never enter the model context.

Three ideas in this picture do all the work:

• The boundary is a grant, not code. Anschoclates simply cannot read customers.email. There is no prompt to bypass and no SDK flag to flip.
• Views are the contract. The columns a view selects are the only columns the agent knows about. A column that is not in the view does not exist for the agent — and the server enforces that, not the app.
• Identity checks go through a function, not a query. The agent passes in what the customer typed and gets back one number: a customer ID, or NULL. It can never read the email. It can only ask “does this match?”

4.1 Request flow

Figure 2: One support conversation, step by step. No raw value ever crosses from the database into the agent process.

4.2 The control stack

Figure 3: Defence in depth. Every layer backs up the one above it. The database layer is the only one that the agent’s code and the prompt cannot touch.

A prompt guardrail is something you ask the model to follow, and a model can be persuaded. A REVOKE cannot be persuaded.

5. Building the Masking Layer

The build uses four files: 01_schema_and_seed.sql, 02_masking_layer.sql, 03_verify.sql, and agent_workflow.py.

5.1 The schema — and one important extra column

01_schema_and_seed.sql · MySQL

CREATE TABLE customers (
  customer_id    BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  full_name      VARCHAR(120) NOT NULL,
  email          VARCHAR(190) NOT NULL,
  phone          VARCHAR(32)  NOT NULL,
  gov_id         VARCHAR(11)  NOT NULL,
  date_of_birth  DATE         NOT NULL,
  loyalty_tier   ENUM('basic','silver','gold','platinum') NOT NULL DEFAULT 'basic',
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Hash of the email: lets us match an email without
  -- ever letting the agent read the email column.
  email_lookup   CHAR(64)     NOT NULL,
  UNIQUE KEY uq_customers_email_lookup (email_lookup),
  KEY ix_customers_tier (loyalty_tier)
) ENGINE=InnoDB;

The email_lookup CHAR(64) column is the most important design choice, and it is the step most teams skip.

The simple design shows a masked email in the view and lets the agent search with WHERE email_masked = 'a**************@example.com'. That turns the mask into an oracle: if someone can guess an email, they can prove the guess, since a correct guess brings back a row. Section 9 shows this attack.

The fix is to keep checking a value apart from seeing it. Store a hash of the normalised email, never show it to the agent, and do identity checks inside a SQL SECURITY DEFINER function that only returns a number:

Lookup token

email_lookup = SHA2(LOWER('aisha.alqahtani@example.com'), 256)

Each identity check becomes a fast exact-match lookup on a unique index, and neither the raw email nor its hash is ever visible to the agent.

5.2 The masking functions

02_masking_layer.sql · acme_sec schema (separate from the data)

DELIMITER //

-- aisha.alqahtani@example.com  ->  a**************@example.com
CREATE FUNCTION fn_mask_email(p VARCHAR(190))
RETURNS VARCHAR(190) DETERMINISTIC NO SQL
BEGIN
  DECLARE v_local  VARCHAR(120);
  DECLARE v_domain VARCHAR(120);
  IF p IS NULL OR p = '' THEN RETURN p; END IF;
  SET v_local  = SUBSTRING_INDEX(p, '@', 1);
  SET v_domain = SUBSTRING_INDEX(p, '@', -1);
  RETURN CONCAT(LEFT(v_local, 1),
                REPEAT('*', GREATEST(CHAR_LENGTH(v_local) - 1, 1)),
                '@', v_domain);
END//

-- +966-50-000-0142  ->  +966-**-***-42  (keeps the country code)
CREATE FUNCTION fn_mask_phone(p VARCHAR(32))
RETURNS VARCHAR(32) DETERMINISTIC NO SQL
BEGIN
  IF p IS NULL OR p = '' THEN RETURN p; END IF;
  RETURN CONCAT(LEFT(p, 4), '-**-***-',
                RIGHT(REGEXP_REPLACE(p, '[^0-9]', ''), 2));
END//

-- 900-12-4471  ->  XXX-XX-4471  (same output as mask_ssn())
CREATE FUNCTION fn_mask_gov_id(p VARCHAR(11))
RETURNS VARCHAR(11) DETERMINISTIC NO SQL
BEGIN
  IF p IS NULL OR p = '' THEN RETURN p; END IF;
  RETURN CONCAT('XXX-XX-', RIGHT(p, 4));
END//

-- 4111111111111111  ->  XXXXXXXXXXXX1111  (same output as mask_pan())
CREATE FUNCTION fn_mask_pan(p VARCHAR(19))
RETURNS VARCHAR(19) DETERMINISTIC NO SQL
BEGIN
  IF p IS NULL OR p = '' THEN RETURN p; END IF;
  RETURN CONCAT(REPEAT('X', GREATEST(CHAR_LENGTH(p) - 4, 0)), RIGHT(p, 4));
END//

DELIMITER ;

The script also defines fn_mask_pan_relaxed, fn_mask_name, and fn_mask_address in the same style. All masking functions live in their own acme_sec schema, so the view owner can be given EXECUTE there without touching the data schema. Each function is DETERMINISTIC and NO SQL, so MySQL knows it only transforms its input and touches no tables.

Output captured on a live MySQL 8.4 instance:

Input	Function	Output
aisha.alqahtani@example.com	fn_mask_email	a**************@example.com
+966-50-000-0142	fn_mask_phone	+966-**-***-42
900-12-4471	fn_mask_gov_id	XXX-XX-4471
4111111111111111	fn_mask_pan	XXXXXXXXXXXX1111
5555555555554444	fn_mask_pan_relaxed	555555XXXXXX4444
Aisha Al-Qahtani	fn_mask_name	A**** A*********
1180 Folsom St, Apt 4B	fn_mask_address	1180 F****************

The gov ID and the two card functions return exactly the same output as Enterprise’s mask_ssn(), mask_pan(), and mask_pan_relaxed() . That is the upgrade path: when ACME moves to Enterprise, each wrapper becomes a one-line call to the built-in function.

5.3 Checking identity without revealing anything

02_masking_layer.sql · acme_support schema

DELIMITER //
CREATE FUNCTION sp_verify_customer(p_email VARCHAR(190), p_phone_last4 CHAR(4))
RETURNS BIGINT
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
  DECLARE v_id BIGINT DEFAULT NULL;
  SELECT c.customer_id INTO v_id
  FROM   customers c
  WHERE  c.email_lookup = SHA2(LOWER(TRIM(p_email)), 256)
    AND  RIGHT(REGEXP_REPLACE(c.phone, '[^0-9]', ''), 4) = p_phone_last4
  LIMIT 1;
  RETURN v_id;   -- NULL when the check fails
END//
DELIMITER ;

SQL SECURITY DEFINER is what makes this work. The function runs with its owner’s privileges, so it can read email_lookup and phone even though the agent cannot. It only ever returns one number, so there is no way to ask it for a column value. A wrong answer returns NULL — not an error, and not a “close match” hint.

Verified · MySQL 8.4

mysql> SELECT sp_verify_customer('aisha.alqahtani@example.com','0142') AS id;
+------+
| id   |
+------+
| 1001 |
+------+

mysql> SELECT sp_verify_customer('aisha.alqahtani@example.com','9999') AS id;
+------+
| id   |
+------+
| NULL |
+------+

An attacker cannot tell a wrong last-four from a customer who does not exist. Still, add rate limiting in the application: one NULL tells little, but millions of calls can be used to enumerate accounts.

5.4 Views for each role

02_masking_layer.sql · views as column contracts

-- The agent sees seven columns. Four are masked. internal_note is not there at all.
CREATE SQL SECURITY DEFINER VIEW v_customers_agent AS
SELECT
  c.customer_id,
  acme_sec.fn_mask_name(c.full_name)  AS customer_name,
  acme_sec.fn_mask_email(c.email)     AS email_masked,
  acme_sec.fn_mask_phone(c.phone)     AS phone_masked,
  acme_sec.fn_mask_gov_id(c.gov_id)   AS gov_id_masked,
  c.loyalty_tier,
  YEAR(c.date_of_birth)               AS birth_year   -- year only
FROM customers c;

-- Payment: last4 is already safe, full PAN is masked, billing_zip is left out.
CREATE SQL SECURITY DEFINER VIEW v_payment_agent AS
SELECT p.pm_id, p.customer_id, p.brand, p.last4,
       acme_sec.fn_mask_pan(p.pan) AS pan_masked,
       p.exp_month, p.exp_year
FROM payment_methods p;

-- Orders: house number kept for address confirmation; street and postal masked.
CREATE SQL SECURITY DEFINER VIEW v_orders_agent AS
SELECT o.order_id, o.customer_id, o.status, o.total_amount, o.placed_at,
       acme_sec.fn_mask_address(o.ship_street)  AS ship_street_masked,
       o.ship_city,
       CONCAT('***', RIGHT(o.ship_postal, 2))   AS ship_postal_masked
FROM orders o;

-- Tickets: internal_note is left out, not masked.
CREATE SQL SECURITY DEFINER VIEW v_tickets_agent AS
SELECT t.ticket_id, t.customer_id, t.order_id, t.channel,
       t.summary, t.opened_at, t.status
FROM support_tickets t;

Two choices are worth explaining:

• date_of_birth becomes YEAR(date_of_birth). Masking a date character by character is easy to get wrong — 1986-04-** still leaks the birth month, a common security-question answer. Keeping only the year removes the risky part completely and still supports age-band logic.
• internal_note is removed, not masked. A half-masked block of text still carries meaning. If the column is not in the view, there is nothing for a clever prompt to pull out.

5.5 Roles and least-privilege grants

02_masking_layer.sql · roles, users, and grants

CREATE ROLE IF NOT EXISTS role_ai_agent, role_support_rep, role_analyst, role_dba;

-- Agent: four views and one verification function. Nothing else.
GRANT SELECT ON acme_support.v_customers_agent TO role_ai_agent;
GRANT SELECT ON acme_support.v_payment_agent   TO role_ai_agent;
GRANT SELECT ON acme_support.v_orders_agent    TO role_ai_agent;
GRANT SELECT ON acme_support.v_tickets_agent   TO role_ai_agent;
GRANT EXECUTE ON FUNCTION acme_support.sp_verify_customer TO role_ai_agent;

-- Only the DBA can read base tables. No role gets write access by default.
GRANT ALL PRIVILEGES ON acme_support.* TO role_dba;

-- Bind the role to the login and make it active on connect.
CREATE USER 'ai_agent'@'%' IDENTIFIED BY '<from-secrets-manager>';
GRANT role_ai_agent TO 'ai_agent'@'%';
SET DEFAULT ROLE ALL TO 'ai_agent'@'%';

What is missing matters as much as what is there:

• No SELECT on any base table for the agent, the support rep, or the analyst. The views are the only path to customer data.
• No INSERT, UPDATE, or DELETE for the agent. It is read-only by design, so a prompt injection cannot make it write.
• No schema-wide EXECUTE on acme_sec.*. The agent can run exactly one function.

Check the result with SHOW GRANTS, which lists both table and routine privileges:

Audit · MySQL

SHOW GRANTS FOR role_ai_agent;

-- Table-level grants only (EXECUTE is stored in mysql.procs_priv):
SELECT TABLE_NAME, PRIVILEGE_TYPE
FROM   information_schema.TABLE_PRIVILEGES
WHERE  GRANTEE = "'role_ai_agent'@'%'";

Any line you did not expect in that output is a grant you did not mean to give.

6. The Agent in Action

agent_workflow.py connects as ai_agent and runs a full support conversation. The most important rule is this: the model never writes SQL. It picks a named tool and passes arguments. Every statement sent to the database is written by application code and uses bound parameters, so there is no path from model text into a query string.

agent_workflow.py · tool layer (shortened) · Python

class SupportTools:
    def verify_customer(self, email: str, phone_last4: str) -> dict:
        rows = self._run(
            "SELECT acme_support.sp_verify_customer(%s, %s) AS customer_id",
            (email, phone_last4))
        cid = rows[0]["customer_id"]
        return {"verified": cid is not None, "customer_id": cid}

    def get_customer_summary(self, customer_id: int) -> dict:
        rows = self._run(
            "SELECT customer_name, email_masked, phone_masked, gov_id_masked, "
            "       loyalty_tier, birth_year "
            "FROM v_customers_agent WHERE customer_id = %s", (customer_id,))
        return rows[0] if rows else {}

Even a successful prompt injection cannot turn into arbitrary SQL, because the model’s output is only ever used as a parameter value.

6.1 What the agent collects

agent_workflow.py · output

[1] Verifying the caller
    customer says: "aisha.alqahtani@example.com, last four 0142"
    verify_customer() -> {'verified': True, 'customer_id': 1001}
    wrong last-four   -> {'verified': False, 'customer_id': None}

[2] Gathering permitted context
    customer summary:
      customer_name   A**** A*********
      email_masked    a**************@example.com
      phone_masked    +966-**-***-42
      gov_id_masked   XXX-XX-4471
      loyalty_tier    gold
      birth_year      1986
    orders:
      #50101  shipped    $249.99  2026-09-02  ship=1180 F****************
      #50102  delivered  $ 89.50  2026-08-11  ship=1180 F****************
    open tickets:
      #70001 [open] Package shows delivered but never arrived.
    payment on file:
      visa ****1111  stored_as=XXXXXXXXXXXX1111  exp 04/2028

6.2 The reply — written from masked data only

agent_workflow.py · drafted reply

Hi A.,

I've found your account (ID 1001, gold tier). I can see order #50101
for $249.99, which our system shows as 'shipped' on 02 Sep 2026.

This matches the issue on ticket #70001: "Package shows delivered
but never arrived."

I'm opening a trace with the carrier now. You'll get an update at the
email on file (a**************@example.com) within one business day.

If your details have changed, just reply here and I'll update them.

Regards,
Anschoclates -- ACME Retail Support

This answers the big question: can an agent still be useful when the data is masked? Yes. The reply finds the account, names the exact order and amount, quotes the ticket, promises a next step, and confirms the contact channel — all using values that are worthless to a fraudster. Masking removed the value, not the usefulness, because the design separated what the agent needs to identify a record from what it needs to act on it.

6.3 Proving the payload is clean

A good-looking reply is not proof that nothing leaked. So the workflow builds the exact JSON payload that would go to the model and scans it for every raw sensitive value in the seed data:

agent_workflow.py · egress scanner · Python

RAW_PII_PATTERNS = {
    "full_name":     re.compile(r"\b(Aisha Al-Qahtani|Khalid Al-Ghamdi|Noura Al-Otaibi|"
                               r"Faisal Al-Harbi|Layla Al-Dosari)\b"),
    "email":         re.compile(r"[\w.]+@(?:example\.(?:com|org|net))"),
    "government_id": re.compile(r"\b900-\d{2}-\d{4}\b"),
    "card_number":   re.compile(r"\b(?:4\d{15}|5[1-5]\d{14}|3\d{14})\b"),
    "full_address":  re.compile(r"\b\d+\s+(?:Folsom|Greenwich|Shoreline|Lake Shore|Pike)\b"),
}

Scanner output

sensitivity scan over 1283 chars of payload:
  no raw name, email, government id, card number, or street address found.
  RESULT: payload is safe to send to the model.

In production this scanner is your last check before data leaves. Run it as a CI test against the seed data, and as a live guard that blocks the request if anything matches. If someone accidentally adds an unmasked column to v_customers_agent, the test fails the same day — not six months later during an audit.

7. One Customer, Four Roles

How should different people and agents see the same data? Give each role its own view, and let that view’s column list be the contract.

	role_dba	role_support_rep	role_ai_agent	role_analyst
Object	customers (base)	v_customers_agent	v_customers_agent	v_customers_analyst
Name	Aisha Al-Qahtani	A**** A*********	A**** A*********	C001001 (pseudonym)
Email	aisha.alqahtani@example.com	a*****@example.com	a*****@example.com	not reachable
Phone	+966-50-000-0142	+966-**-***-42	+966-**-***-42	not reachable
Gov ID	900-12-4471	XXX-XX-4471	XXX-XX-4471	not reachable
Birth date	1986-04-11	1986 (year only)	1986 (year only)	1986 (year only)
Card number	4111111111111111	not reachable	XXXXXXXXXXXX1111	not reachable
Street	1180 Folsom St, Apt 4B	not reachable	1180 F*****...	not reachable
internal_note	full text	not reachable	not reachable	not reachable

Table 1: One customer row shown to four roles. Every cell is real output from the running system (long masks shortened to fit).

The privilege layer, checked in information_schema:

Verified · MySQL 8.4

mysql> SELECT TABLE_NAME, PRIVILEGE_TYPE FROM information_schema.TABLE_PRIVILEGES
    -> WHERE GRANTEE = "'role_ai_agent'@'%'";
+-------------------+----------------+
| TABLE_NAME        | PRIVILEGE_TYPE |
+-------------------+----------------+
| v_customers_agent | SELECT         |
| v_orders_agent    | SELECT         |
| v_payment_agent   | SELECT         |
| v_tickets_agent   | SELECT         |
+-------------------+----------------+

Four rows: four views, no base tables, read-only. The server also keeps roles apart:

Verified · MySQL 8.4

mysql> -- the analyst tries the agent's view
mysql> SELECT * FROM v_customers_agent LIMIT 1;
ERROR 1142 (42000): SELECT command denied to user 'analyst'@'localhost'
for table `acme_support`.`v_customers_agent`

Note that the DBA seeing raw data is correct, not a failure. Masking does not mean “nobody ever sees this.” It means “each audience sees the right version.” There should be exactly one unmasked audience, and its access should be logged.

8. When the Agent Tries Something Unexpected

The best way to answer “what if the agent goes rogue?” is to test it. The workflow runs ten hostile queries — the kind a jailbroken, buggy, or cleverly prompted agent might produce:

agent_workflow.py · adversarial probes

[5] Adversarial probes
  BLOCKED  Read full customer PII from the base table
           -> ERR 1142: SELECT command denied ... `acme_support`.`customers`
  BLOCKED  Read the staff-only internal note
           -> ERR 1142: SELECT command denied ... `acme_support`.`support_tickets`
  BLOCKED  Read full card numbers
           -> ERR 1142: SELECT command denied ... `acme_support`.`payment_methods`
  ALLOWED  Rename masked columns with an alias
           -> RETURNED 5 ROW(S)
  BLOCKED  Dump lookup tokens to brute-force an email
           -> ERR 1142: SELECT command denied ... `acme_support`.`customers`
  BLOCKED  Reach raw values through a join
           -> ERR 1142: SELECT command denied ... `acme_support`.`customers`
  BLOCKED  UNION raw rows onto a permitted view
           -> ERR 1142: SELECT command denied ... `acme_support`.`customers`
  BLOCKED  Try to grant itself more privileges
           -> ERR 1142: GRANT command denied ... `acme_support`.`customers`
  ALLOWED  Read the definition of the verification function
           -> RETURNED 1 ROW(S)
  BLOCKED  Update data instead of reading it
           -> ERR 1142: UPDATE command denied ... `acme_support`.`customers`

  8/10 probes refused by the database.

Eight out of ten were refused by the database itself — not by the prompt and not by the application. The UNION attack deserves a special mention. Adding raw rows to a permitted view is the classic way to beat view-based masking. It fails here only because the agent never had SELECT on customers. No query can return a value from a table the agent was never granted, no matter how creative it is.

8.1 The two probes that got through — and why they are the most useful result

Probe 4 — renaming the column. The agent ran SELECT email_masked AS email FROM v_customers_agent and got five rows. The alias changed the label, not the value:

Verified · MySQL 8.4

mysql> SELECT email_masked AS email FROM v_customers_agent WHERE customer_id = 1001;
+-----------------------------+
| email                       |
+-----------------------------+
| a**************@example.com |
+-----------------------------+

This is still a real warning: renaming a masked column does not unmask it, but it can fool tools that check column names. A DLP scanner or query firewall that looks for a column called email will be tricked. Any audit layer on top of masking must check the object and the grant, not the column label.

Probe 9 — reading the function definition. The agent ran SHOW CREATE FUNCTION acme_support.sp_verify_customer. The body came back as NULL (hidden), but the statement succeeded, which confirms the function exists. This happens because the agent holds EXECUTE on it. A role without EXECUTE cannot even see it:

Verified · MySQL 8.4 · analyst role (never granted EXECUTE)

mysql> SHOW CREATE FUNCTION acme_support.sp_verify_customer\G
ERROR 1305 (42000): FUNCTION sp_verify_customer does not exist

mysql> SELECT sp_verify_customer('aisha.alqahtani@example.com','0142');
ERROR 1370 (42000): execute command denied to user 'analyst'@'localhost'
for routine 'acme_support.sp_verify_customer'

The agent needs EXECUTE to do its job, so you cannot simply remove it. The rule to remember: EXECUTE on a routine reveals that it exists. Whether the body is shown depends on the SHOW_ROUTINE privilege (added in MySQL 8.0.20), on global SELECT, or on being the definer — so never grant those to the agent, and test the behaviour on your exact version. As a backup control, make the definer a dedicated account that cannot log in, so even a leaked body gives no extra access.

The probe list is evidence, not proof

Ten hand-written attacks show how enforcement works. The real guarantee comes from the grant list: the agent provably has no SELECT on base tables, and that holds no matter how new the query is. Probes make the guarantee easy to see; grants make it true.

9. Where Masking Stops Working

Masking controls what a query returns. It is strong against direct disclosure, but almost useless against inference — working out a value from clues. Every example below was run against the live system.

9.1 The mask as an oracle

If a masked column can be used in a WHERE clause, it can be used to confirm a guess:

Verified · MySQL 8.4

-- The agent guesses an address and confirms it using only the mask
mysql> SELECT customer_id FROM v_customers_agent
    -> WHERE email_masked = 'a**************@example.com';
+-------------+
| customer_id |
+-------------+
|        1001 |
+-------------+

One bit leaked: “your guess is right.” One bit is enough to confirm who someone is. Partial filters also combine:

Verified · MySQL 8.4

mysql> SELECT customer_id FROM v_customers_agent
    -> WHERE email_masked LIKE 'a%@example.com' AND birth_year = 1986;
+-------------+
| customer_id |
+-------------+
|        1001 |
+-------------+

Searchable masked columns are the most common dynamic-masking mistake. The design looks fine until someone writes a LIKE. The fix is the verification function: a SHA2 match on a hidden token that returns an ID or NULL, never a yes/no on a guess about the mask. In the tool layer, never let the agent filter on masked columns.

9.2 Counting people

Verified · MySQL 8.4

mysql> SELECT COUNT(*) AS n_with_last4_4471 FROM v_customers_agent
    -> WHERE gov_id_masked LIKE '%4471';
+-------------------+
| n_with_last4_4471 |
+-------------------+
|                 1 |
+-------------------+

A COUNT over a masked column shows how many people share the visible part — a standard re-identification trick. Masking the value does nothing to the count. The fix is aggregate-only views with a minimum group size (five is a common floor), not row-level masking.

9.3 Mask length reveals real length

Verified · MySQL 8.4

mysql> SELECT LENGTH(pan_masked) AS mask_len FROM v_payment_agent LIMIT 1;
+----------+
| mask_len |
+----------+
|       16 |
+----------+

pan_masked is 16 characters because the real card number is 16 digits. When length itself gives something away — ID formats differ by country, card lengths hint at the issuer — use fixed-width masks so the output carries no length signal.

9.4 The boundary at a glance

Control	Status
Full card number never returned to the agent	Enforced by privilege
internal_note never returned to the agent	Enforced by leaving the column out
Raw email / phone / gov ID never returned	Enforced by the view
Agent cannot write	No INSERT / UPDATE / DELETE grant
Agent cannot raise its own privileges	No GRANT OPTION; probe denied
Agent cannot read base tables	No SELECT on base tables
Masked values used to confirm a guess	Gap — use the token verification path
Counting people via COUNT / LIKE	Gap — aggregate-only views with a minimum group size
Mask reveals real field length	Gap — fixed-width masks
Raw values in query logs	Gap — log discipline (see Section 10)
Data kept in the model provider’s logs	Gap — contract and DPIA controls

Table 2: Checked against the running system. Each “Gap” row names the extra control that risk needs.

10. Common Masking Pitfalls

Masking protects the query path. Several other paths need their own controls. These are the ones that catch teams most often:

Pitfall	Why it bites, and the fix
Raw values in query logs	The general log records full query text — including the raw email the agent passes to sp_verify_customer. Turn the general log off in production, keep values out of the slow log, and treat audit-log output as sensitive data.
Backups and replicas	A backup of customers holds every raw value. Masking does not protect storage, so encrypt data at rest, encrypt backups, and audit privileges on replicas.
Sorting and aggregates	MIN, MAX, GROUP BY, and window functions over a partly masked column can recover the visible part. Send analytics through views that are fully masked or fully aggregated.
Error messages	A duplicate-key error shows the clashing value in its message (Duplicate entry '...' for key ...). Never put a unique key on a masked expression the agent can trigger, and test what your errors expose.
Searchable masked columns	WHERE email_masked = ... turns the mask into an oracle (Section 9.1). Match on a hidden hash inside a definer function instead.
Trusting column names	Aliases defeat name-based DLP rules (Section 8.1). Audit by object and grant, not by label.
Over-sharing context	Masking cannot stop the model from repeating a masked value it was given, or control what the provider keeps. Send only the fields needed for the task — never a full row “just in case.”

Four habits close most of the remaining gap at the model layer:

1. Send the minimum. Return only the fields the task needs.
2. Check the payload. Run the egress scanner in CI and in production.
3. Log the prompt, not the secret. Store raw tool parameters in a separate, locked-down log.
4. Rate-limit verification. A NULL from sp_verify_customer becomes an enumeration tool at scale.

11. Moving to the Enterprise Functions

The wrapper schema exists so that upgrading is a few function swaps, not a rewrite. On MySQL Enterprise Edition, install the masking library first. With the plugin :

MySQL Enterprise Edition · plugin install

INSTALL PLUGIN data_masking SONAME 'data_masking.so';
CREATE FUNCTION mask_inner       RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_ssn         RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan         RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan_relaxed RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_pan      RETURNS STRING SONAME 'data_masking.so';

On MySQL 8.4 you can use the component instead (INSTALL COMPONENT 'file://component_masking';), which registers all functions at once. Follow the component installation steps in the manual , because it also needs a dictionary table. Pick one method — do not mix the plugin and the component.

Then point each wrapper at the built-in function. MySQL has no CREATE OR REPLACE FUNCTION, so drop and recreate:

Wrapper swap · MySQL

DROP FUNCTION IF EXISTS acme_sec.fn_mask_pan;
CREATE FUNCTION acme_sec.fn_mask_pan(p VARCHAR(19))
RETURNS VARCHAR(19) DETERMINISTIC NO SQL
RETURN mask_pan(p);

Every view, grant, agent tool, and CI test keeps working, because they all call acme_sec.fn_mask_pan, not mask_pan. That indirection is the whole point of the wrapper layer. (Re-grant EXECUTE to the view definer if you dropped and recreated the function.)

Two notes for the move:

• Watch multi-byte input. The vendor docs wrap SSN arguments in CONVERT(ssn USING binary) because mask_ssn() expects an exact byte length [3]. Test any column that may hold non-ASCII data.
• gen_rnd_* is for test data, not masking. Use these functions to fill non-production databases so developers never need a copy of production. That alone is one of the most valuable uses of this feature set.

12. Frequently Asked Questions

Can an AI agent still be useful when the data is masked?

Yes. Section 6.2 shows a complete, specific reply built only from masked data. The agent kept the account ID, order number, amount, ticket, and status — and needed none of the hidden values. Decide for each field whether the agent needs the value or just the identity, then build the view to match.

How is masking different from encryption at rest?

Masking changes what a query returns. Encryption at rest protects the stored bytes if disks or backups are stolen. They solve different problems and you need both: masking does not protect a stolen backup, and encryption does not stop an over-privileged query.

Why use views instead of masking in the table definition?

Because MySQL has no masked-column option in any edition. Views work with roles and SQL SECURITY DEFINER, run on every edition and cloud service, and can be changed without touching the base tables.

Why does the UNION attack fail?

SELECT customer_id FROM v_customers_agent UNION SELECT customer_id FROM customers fails with ERROR 1142 because the agent has no SELECT on customers. The view is not what stops it — the missing grant is. If that grant existed, the UNION would work. That is why the grant is the real control.

Does the model need to know the data is masked?

No, and that is the point. Anschoclates treats A**** A********* and a**************@example.com as normal values and writes its reply with them. Nothing in the design depends on the model knowing or obeying anything about masking.

How should I test this?

Run 03_verify.sql on your exact build to confirm function behaviour. Run agent_workflow.py to repeat the hostile probes. Add the egress scanner to CI so a new unmasked column fails on day one. Re-run everything after each database upgrade, because privilege behaviour can change between releases.

What is the most common mistake?

Letting the agent filter on a masked column — WHERE email_masked = ?. That lets anyone confirm a guessed value. Move identity checks to the hidden SHA2 token inside a definer function (Section 5.3), where the only answer is an ID or nothing.

13. Production Hardening Checklist

Area	Action
Credentials	One account per role. No shared agent login. Rotate passwords through a secrets manager.
Views	SQL SECURITY DEFINER, owned by a dedicated account that cannot log in.
Grants	No SELECT on base tables for agents. No GRANT OPTION. No write grants.
Verification	EXECUTE granted per function, not per schema. Rate-limited, with alerts on spikes.
Egress	Payload scanner in CI and in production. Block the request on any match.
Logging	General log off in production. Slow log without values. Audit output treated as sensitive.
Aggregation	Analytic views with a minimum group size. No masked column in a WHERE clause.
Storage	Encryption at rest, encrypted backups, and privilege audits on replicas.
Build checks	Run 03_verify.sql on the target build before go-live and after every upgrade.
Governance	DPIA and a provider agreement that cover the model path — the masked payload still leaves your network.

14. Key Takeaways

The short version

• Masking is the first line, not the only line. Keeping the sensitive value out of the result set is cheap, works on every edition, and holds even if the agent is fully compromised. Add encryption, log discipline, and contracts on top.
• Enforce outside the agent. A system prompt is a suggestion; a REVOKE is a rule. If your only defense is the model choosing to behave, you have a policy, not a control.
• Give the agent identity, not values. A hidden hash plus a definer function lets the agent find the right record while returning only one number — and it closes the mask-as-oracle hole.
• Test the mask, not just the feature. The two probes that got through in Section 8 taught the most. A ten-minute probe script finds problems that would otherwise show up in production.
• Verify on your exact build. Community has no masking functions. Enterprise has them only after installation. No edition has masked columns. Re-check after every upgrade.

The demo is small — five customers, four tools, one verification function — but it is complete in the way that matters. Every control was enforced by the database engine, not by the model, and it held against every hostile query we tried.

For years, giving an application database access meant choosing which tables it could read and then trusting it with the values. An AI agent breaks that trust model. Its context is serialized, sent to a third party, and possibly cached, logged, or repeated word for word. Once a value enters the context, it has left your network, and the model cannot forget it. Database masking fixes this at the only clean point: before the value is ever selected.

Getting started is easier than it looks. On any existing MySQL schema you need four things: a small library of masking functions, views that use those functions instead of raw columns, a verification function that returns one number, and no base-table access for the agent’s role. That is a few hundred lines of SQL at most, and it can be added to a live system without changing the base tables.

Decide what the agent is allowed to see before you decide what it is supposed to do. The prompt can tell the agent what to do — only the grant can decide what it is allowed to see.

SZ

Syed Zaheer

Service Delivery Director · Techvisions · Cloud, AI & Managed Infrastructure

Writes, speaks, and builds across cloud, AI, enterprise platforms, and digital transformation. The insights shared here are shaped by real-world experience delivering complex technology initiatives, modernizing enterprise environments, and helping organizations accelerate innovation across multi-cloud ecosystems. Published with the hope that they save others time, simplify complexity, and inspire practical solutions.