MySQL data masking: Enterprise functions, views, generated columns and a proxy

MySQL Community has no data masking functions. The masking functions (mask_inner, mask_pan, …) and the 9.7 masking policies are part of MySQL Enterprise Edition, a commercial product. On Community, mask with a view plus column privileges, with generated columns, or at a proxy in front of the database.

What data masking is, and when you need it

Data masking returns a modified value instead of the real one: a***@example.com for an email, ****4242 for a card number. The row is still there and the query still works; the sensitive part is not in the result.

You need it in MySQL when someone or something must query a table without seeing every column:

  • PII and payment data: emails, phone numbers, card numbers, national IDs.
  • Compliance: GDPR, PCI DSS and HIPAA programmes ask you to limit who sees personal and card data, and to show that you do.
  • Analysts and support: they need the rows, not the full card number.
  • AI agents: a SELECT email, card_number FROM customers is harmless for the database, and still puts personal data into an LLM's context.

Masking at read time (dynamic masking) leaves the stored data unchanged. Rewriting the stored data (static masking) is a different job, for copies such as test databases.

MySQL Enterprise Data Masking (Enterprise Edition only)

The 8.4 manual is explicit: "MySQL Enterprise Data Masking and De-Identification is an extension included in MySQL Enterprise Edition, a commercial product" (MySQL 8.4 docs).

  • It started as a plugin (data_masking.so) in MySQL 8.0.13; since 8.0.33 it is also available as components (components vs. plugin). In 9.7 the manual renames it "MySQL Enterprise Data Masking".
  • On 8.4 you install the components with a script that creates the masking_dictionaries table and installs component_masking and component_masking_functions. Remove the plugin before, to avoid conflicts.
# MySQL Enterprise Edition 8.4: installs component_masking and component_masking_functions
mysql -u root -p -D mysql < [path/]masking_functions_install.sql

From the docs, not tested here: MySQL 8.4: installing the data masking components

The functions mask a value (mask_inner(str, margin1, margin2 [, mask_char]), mask_outer, mask_pan, mask_pan_relaxed, mask_ssn, mask_iban, mask_uuid…) or generate fake data (gen_rnd_email, gen_rnd_pan, gen_range, gen_dictionary…). The full list is in the function reference. The docs' examples:

-- MySQL Enterprise Edition only. The values are the docs' examples;
-- gen_rnd_pan() returns a random card number.
SELECT mask_inner('abcdef', 1, 2);       -- aXXXef
SELECT mask_inner('abcdef', 0, 5);       -- Xbcdef
SELECT mask_pan(gen_rnd_pan());          -- XXXXXXXXXXXX9102
SELECT mask_pan_relaxed(gen_rnd_pan());  -- 551279XXXXXX3108
SELECT mask_ssn('909-63-6922');          -- ***-**-6922

From the docs, not tested here: MySQL 8.4: data masking component function descriptions

You call them in queries or views: masking is something you write into the SQL, not a property of the column. On the Community image none of it exists; this is what happens if you try:

INSTALL COMPONENT 'file://component_masking';
INSTALL PLUGIN data_masking SONAME 'data_masking.so';
USE shop;
SELECT mask_inner('4111111111114242', 0, 4);
ERROR 1126 (HY000) at line 1: Can't open shared library '/usr/lib64/mysql/plugin/component_masking.so' (errno: 11 /usr/lib64/mysql/plugin/component_masking.so: cannot open shared object file: No such file or directory)
ERROR 1126 (HY000) at line 2: Can't open shared library '/usr/lib64/mysql/plugin/data_masking.so' (errno: 11 /usr/lib64/mysql/plugin/data_masking.so: cannot open shared object file: No such file or directory)
ERROR 1305 (42000) at line 2: FUNCTION shop.mask_inner does not exist

Tested on MySQL 8.4.13 Community (mysql:8.4), 2026-11-14.

MySQL 9.7: dynamic data masking policies

MySQL 9.7 adds a Dynamic Data Masking Policy component: "a MySQL Enterprise Edition feature which enables you to define masking expressions as policies on columns in a table" (9.7 docs). It is not in the 8.4 manual.

-- MySQL 9.7 Enterprise Edition, after install_component_object_policy.sql
CREATE MASKING POLICY mask_password1(pass_column)
  CASE WHEN CURRENT_ROLE_IN('admin') THEN pass_column ELSE sha2(pass_column, 256) END;

-- A column takes a policy in CREATE TABLE or ALTER TABLE … ADD/MODIFY/CHANGE COLUMN:
--   ssn INT NOT NULL MASKING POLICY mask_ssn
ALTER TABLE user_data ALTER COLUMN ssn DROP MASKING POLICY;

From the docs, not tested here: MySQL 9.7: using masking policies

Documented restrictions: no policy on generated columns or columns a generated column references, on indexed columns, columns with a histogram, columns in a CHECK or partitioning key; and the column type must be compatible with the CASE result (for strings, the same collation).

HeatWave: the MySQL HeatWave manual lists 10 supported masking and de-identification functions (gen_range, gen_rnd_email, gen_rnd_pan, gen_rnd_ssn, gen_rnd_us_phone, mask_inner, mask_outer, mask_pan, mask_pan_relaxed, mask_ssn), with no dictionary functions and no masking policies (HeatWave docs).

On Community: a masking view and column privileges

The pattern that works on every MySQL edition: the user can read only the harmless columns of the table, plus a view that returns the sensitive ones masked. MySQL supports privileges on single columns (SELECT, INSERT, UPDATE, REFERENCES), and a view runs with its definer's privileges by default (SQL SECURITY DEFINER).

CREATE DATABASE shop; USE shop;
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL,
  card_number VARCHAR(19) NOT NULL, ssn VARCHAR(11), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
INSERT INTO customers (id, name, email, card_number, ssn) VALUES
  (1,'Ana','ana@example.com','4111111111114242','123-45-6789'),
  (2,'Bruno','bruno@example.org','5500000000001881','987-65-4321');

CREATE USER 'analyst'@'%' IDENTIFIED BY 'analyst-pw';
-- The analyst may read two columns of the table…
GRANT SELECT (id, name) ON shop.customers TO 'analyst'@'%';
-- …and the masked view, which runs with the definer's privileges.
CREATE SQL SECURITY DEFINER VIEW customers_masked AS
SELECT id, name,
       CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
       CONCAT('****', RIGHT(card_number, 4))                         AS card_number,
       CONCAT('XXX-XX-', RIGHT(ssn, 4))                               AS ssn
FROM customers;
GRANT SELECT ON shop.customers_masked TO 'analyst'@'%';

Tested on MySQL 8.4.13 Community (mysql:8.4), 2026-11-14.

As analyst, the base columns are refused and the view returns masked values:

-- Connected as analyst:
SELECT email FROM customers;
SELECT * FROM customers;
SELECT * FROM customers_masked;
ERROR 1143 (42000) at line 3: SELECT command denied to user 'analyst'@'localhost' for column 'email' in table 'customers'
ERROR 1142 (42000) at line 4: SELECT command denied to user 'analyst'@'localhost' for table 'customers'
+----+-------+------------------+-------------+-------------+
| id | name  | email            | card_number | ssn         |
+----+-------+------------------+-------------+-------------+
|  1 | Ana   | a***@example.com | ****4242    | XXX-XX-6789 |
|  2 | Bruno | b***@example.org | ****1881    | XXX-XX-4321 |
+----+-------+------------------+-------------+-------------+

Tested on MySQL 8.4.13 Community (mysql:8.4), 2026-11-14.

  • Pros: built in, any edition, no extra component.
  • Cons: every client must query the view, not the table; one view per audience; you maintain the masking expressions by hand; a user with SELECT on the table bypasses it.
  • A SQL SECURITY INVOKER view checks the caller's privileges on the base table, so it would fail for analyst: keep DEFINER for masking views.

On Community: generated columns

A generated column computes a masked copy next to the real one. A VIRTUAL column is not stored: it is evaluated when rows are read. Grant the generated column, not the original:

ALTER TABLE customers ADD COLUMN card_last4 VARCHAR(4)
  GENERATED ALWAYS AS (RIGHT(card_number, 4)) VIRTUAL;
ALTER TABLE customers ADD COLUMN email_masked VARCHAR(255)
  GENERATED ALWAYS AS (CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1))) VIRTUAL;
CREATE USER 'support'@'%' IDENTIFIED BY 'support-pw';
GRANT SELECT (id, name, card_last4, email_masked) ON shop.customers TO 'support'@'%';

-- Connected as support:
SELECT id, name, card_last4, email_masked FROM customers;
SELECT card_number FROM customers WHERE id = 1;
|  1 | Ana   | 4242       | a***@example.com |
|  2 | Bruno | 1881       | b***@example.org |
ERROR 1143 (42000) at line 2: SELECT command denied to user 'support'@'localhost' for column 'card_number' in table 'customers'

Tested on MySQL 8.4.13 Community (mysql:8.4), 2026-11-14.

It keeps the masked value in the same table, so applications need no view. The trade-off: it changes the schema of the table, and every new masked column is a migration.

Native options vs. masking at the proxy

OptionEditionApp changesPer-agent policyAudit of masked reads
Enterprise masking functionsEnterprise Edition (commercial)Queries or views must call the functionsPer user, through views and grantsEnterprise Audit, if you use it
9.7 masking policiesEnterprise Edition, 9.7None for queries; DDL to attach policiesPer role, inside the policy's CASENot part of the feature
View + column privilegesAnyClients query the viewPer user or roleNot part of the feature
Generated columnsAnySchema change; clients read the new columnPer user or roleNot part of the feature
Vericto TCP proxyAny, including Community, Aurora MySQL and MariaDBConnection string onlyOne proxy per agent path, plus agent allowlistsEvery masked read in the audit trail, with the column

Performance: the database functions and views run inside MySQL; the proxy adds a network hop and an AST evaluation per statement. We publish what we measured, with the method, in the proxy latency benchmark; measure with your own traffic.

Licensing: MySQL Enterprise Edition is commercial; views, grants and generated columns are part of MySQL Community (GPL); the Vericto proxy is AGPL-3.0-only.

Mask columns with Vericto

Vericto masks at the TCP proxy, between the client and MySQL: no change to the database, no change to the application, and no MySQL Enterprise Edition needed. The proxy speaks the MySQL wire protocol (VERICTO_WIRE_PROTOCOL=mysql; MariaDB and Aurora MySQL go through the same protocol). You point the connection string at the proxy, with the same user and password.

How it works

  1. In the dashboard, under Databases → Sensitive columns, you tag columns: table, column, a policy (block, flag or mask) and, for mask, a style: full ([redacted]), last4, email or hash (SHA-256).
  2. For each query, the engine reads the AST and finds which tagged columns the query returns. Every decision is reported as rule VERICTO-085; when a query reads several tagged columns, the strictest policy wins (block > mask > flag).
  3. Under mask, the proxy forwards a rewritten query in the same COM_QUERY or COM_STMT_PREPARE, never the original. The ? placeholders keep their number and order, so prepared statements run unchanged. The database computes the masked values; the clear value never reaches the client.
  4. Under block, the query never reaches the database: the client gets ERROR 1142.

This is the real rewrite of vericto-engine 5.0.0 for a query that reads two masked columns, and the rows it returned on MySQL 8.4 and MariaDB 11.4:

-- The query the agent or the app sends (MySQL or MariaDB):
SELECT id, name, email, card_number FROM customers WHERE id = 42
-- FLAGGED VERICTO-085: SensitiveColumn > customers.card_number, customers.email (mask)
-- The query the proxy forwards instead (rewritten_query):
SELECT id, name, CASE WHEN CHAR_LENGTH((CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin)) = 0 THEN (CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin) WHEN LOCATE('@', (CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin), 2) > 0 THEN CONCAT(LEFT((CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin), 1), '***', SUBSTRING((CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin), LOCATE('@', (CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin), 2))) ELSE CONCAT(LEFT((CONVERT((email) USING utf8mb4) COLLATE utf8mb4_bin), 1), '***') END AS `email`, CASE WHEN CHAR_LENGTH((CONVERT((card_number) USING utf8mb4) COLLATE utf8mb4_bin)) <= 8 THEN '[redacted]' ELSE CONCAT('****', RIGHT((CONVERT((card_number) USING utf8mb4) COLLATE utf8mb4_bin), 4)) END AS `card_number` FROM customers WHERE id = 42

Real output: vericto-engine 5.0.0, evaluate() on MySQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.

-- The rows the masked rewrite returns:
+----+------+------------------+-------------+
| id | name | email            | card_number |
+----+------+------------------+-------------+
| 42 | Ana  | a***@example.com | ****4242    |
+----+------+------------------+-------------+

Tested on MySQL 8.4.13 Community (mysql:8.4) and MariaDB 11.4.13 (mariadb:11.4), 2026-11-16.

The masks use only functions present in MySQL 5.7, 8.0, Aurora MySQL and MariaDB (no REGEXP_REPLACE), and give the same values as on PostgreSQL. A SELECT * over a table with tagged columns is blocked, with a message asking to list the columns:

SELECT * FROM customers
-- BLOCKED VERICTO-085: SensitiveColumn > * over customers (block): list the columns explicitly
-- Fix: List the columns explicitly: `*`, `t.*`, whole-row references and `COPY table TO` read every tagged column

Real output: vericto-engine 5.0.0, evaluate() on PostgreSQL with customers.email (mask, email), customers.card_number (mask, last4) and customers.ssn (block), 2026-11-14.

What to know

  • Versions: on MySQL, mask rewrites from vericto-proxy 4.7.0 and vericto-eval 2.3.0; with older versions a mask tag blocks the query.
  • The rewrite is printed from the parsed statement, so comments and formatting are not kept. A query the engine cannot reproduce faithfully (optimizer hints /*+ … */, SQL_CALC_FOUND_ROWS and the other SELECT modifiers, bit literals) is blocked rather than rewritten. Text that MySQL and the engine would read differently is blocked by VERICTO-086.
  • A column used only in WHERE, JOIN, GROUP BY or ORDER BY is not a read, so an agent could infer a value by filtering on it. For agents, add an agent-access allowlist, where every reference counts.
  • Vericto decides by column name, not content; a renamed column loses its tag, and views or functions over a tagged table are not covered unless tagged too.
  • Only the TCP proxy enforces. The runtime API returns rewritten_query for the application to run, MCP returns it as the only approved_query, and the CLI shows it as a suggestion. vericto-proxy mcp is PostgreSQL-only in 5.0.1, so for a MySQL agent use the TCP proxy.
  • Sensitive columns need a Vericto workspace: the masking page lists where the feature is available. The proxy is open source under AGPL-3.0-only.

Keep column privileges and views as the base layer; the proxy adds per-database policies, the audit trail of every masked read and one place to manage them. Full reference: sensitive columns docs.

Frequently asked questions

Sources

The official documentation this guide cites, checked on 2026-11-14: