Production data is invaluable for day-to-day operations—support, troubleshooting, analytics, and development. But when that data contains sensitive fields such as SSNs, emails, phone numbers, or other identifiers, broad read access can quickly become unnecessary exposure. 

Just as importantly, many organizations operate under regulatory and contractual requirements that expect strong controls around access to sensitive data—often including data masking as part of meeting privacy and security obligations (for example, in programs aligned to GDPR, HIPAA, PCI DSS, SOX, and similar internal security standards). 

Dynamic Data Masking (DDM) is now GA in MySQL 9.7 LTS, available in MySQL Enterprise Edition and OCI MySQL HeatWave. With DDM, you attach a masking policy directly to a base-table column so that MySQL returns either the original value or a masked value at query time, based on the executing user or active role—without application changes and without maintaining separate masked copies of data. 

Why Dynamic Data Masking 

Historically, masking in MySQL has often been implemented using SQL functions and/or UDFs combined with views. While effective, that approach typically requires: 

  • Creating and maintaining additional database objects (views)
  • Ensuring users can’t bypass masking by querying base tables directly
  • Ongoing operational work as schemas evolve

Dynamic Data Masking simplifies that model by enforcing masking at the column level in the base table, helping organizations implement least-privilege access and reduce sensitive-data exposure—an important building block for both security posture and compliance programs. (As always, actual compliance depends on your broader controls, processes, and configuration.) 

How it works: masking policies and gatekeeper functions 

A masking policy is defined as a CASE expression with: 

  • A gatekeeper function:
    • CURRENT_USER_IN('...') for user-based matching
    • CURRENT_ROLE_IN('...') for role-based matching (active roles)
  • A masking expression that transforms the value when masking applies (for SSNs, we’ll use MySQL’s built-in mask_ssn())

When a query references a masked column, MySQL evaluates the gatekeeper and returns either: 

  • the original column value (unmasked), or
  • the masked value (computed by the policy)

Because enforcement occurs inside the MySQL server, the behavior is consistent across applications and query paths. 

End-to-end example (copy/paste): mask SSNs for everyone except authorized users/roles 

Below is a minimal, runnable example. It shows both user-based and role-based gating; you can use either (or both) depending on your operational model. 

Note: Some statements below require privileges appropriate for administering users/roles and managing masking policies. 

0) Setup: schema + table + sample data 

CREATE SCHEMA IF NOT EXISTS protected;
USE protected;

DROP TABLE IF EXISTS user_profiles;

CREATE TABLE user_profiles (
  id         INT PRIMARY KEY,
  first_name VARCHAR(32),
  last_name  VARCHAR(32),
  ssn        VARCHAR(16),
  zip_code   VARCHAR(10)
);

INSERT INTO user_profiles VALUES
  (1,'James','Smith','900-01-0001','10001'),
  (2,'Mary','Johnson','900-01-0002','90210'),
  (3,'Robert','Williams','900-01-0003','60601'),
  (4,'Patricia','Brown','900-01-0004','30301');

1) Setup identities: users and a role 

CREATE USER IF NOT EXISTS 'seeall'@'%' IDENTIFIED BY 'Welcome_123!';
CREATE USER IF NOT EXISTS 'noseeall'@'%' IDENTIFIED BY 'Welcome_123!';

GRANT SELECT ON protected.user_profiles TO 'seeall'@'%';
GRANT SELECT ON protected.user_profiles TO 'noseeall'@'%';

CREATE ROLE IF NOT EXISTS 'pii_read'@'%';
GRANT SELECT ON protected.user_profiles TO 'pii_read'@'%';

GRANT 'pii_read'@'%' TO 'seeall'@'%';

2) Create the masking policy (user-based)

CREATE MASKING POLICY mask_ssn_policy(ssn_col)
  CASE WHEN CURRENT_USER_IN('seeall')
       THEN ssn_col
       ELSE mask_ssn(ssn_col)
  END;

3) Apply the policy to the SSN column

ALTER TABLE protected.user_profiles
  ALTER COLUMN ssn
  SET MASKING POLICY mask_ssn_policy;

4) Verify the behavior 

SELECT id, first_name, last_name, ssn, zip_code
FROM protected.user_profiles
ORDER BY id;
  • As noseeall, SSNs are masked (e.g., XXX-XX-0001)
  • As seeall (and/or with the appropriate active role), SSNs are visible

User “noseeall” – data is masked 

mysql> select * from user_profiles;
+----+------------+-----------+-------------+----------+
| id | first_name | last_name | ssn         | zip_code |
+----+------------+-----------+-------------+----------+
|  1 | James      | Smith     | XXX-XX-0001 | 10001    |
|  2 | Mary       | Johnson   | XXX-XX-0002 | 90210    |
|  3 | Robert     | Williams  | XXX-XX-0003 | 60601    |
|  4 | Patricia   | Brown     | XXX-XX-0004 | 30301    |
|  5 | John       | Jones     | XXX-XX-0005 | 75201    |
|  6 | Jennifer   | Garcia    | XXX-XX-0006 | 85001    |
|  7 | Michael    | Miller    | XXX-XX-0007 | 33101    |
|  8 | Linda      | Davis     | XXX-XX-0008 | 19101    |
|  9 | William    | Rodriguez | XXX-XX-0009 | 94101    |
| 10 | Elizabeth  | Martinez  | XXX-XX-0010 | 98101    |
+----+------------+-----------+-------------+----------+
10 rows in set (0.000 sec)

User “seeall” data is not masked.

mysql> select * from user_profiles;
+----+------------+-----------+-------------+----------+
| id | first_name | last_name | ssn         | zip_code |
+----+------------+-----------+-------------+----------+
|  1 | James      | Smith     | 900-01-0001 | 10001    |
|  2 | Mary       | Johnson   | 900-01-0002 | 90210    |
|  3 | Robert     | Williams  | 900-01-0003 | 60601    |
|  4 | Patricia   | Brown     | 900-01-0004 | 30301    |
|  5 | John       | Jones     | 900-01-0005 | 75201    |
|  6 | Jennifer   | Garcia    | 900-01-0006 | 85001    |
|  7 | Michael    | Miller    | 900-01-0007 | 33101    |
|  8 | Linda      | Davis     | 900-01-0008 | 19101    |
|  9 | William    | Rodriguez | 900-01-0009 | 94101    |
| 10 | Elizabeth  | Martinez  | 900-01-0010 | 98101    |
+----+------------+-----------+-------------+----------+
10 rows in set (0.000 sec)

Why server-side enforcement matters 

Proxy-layer masking can be useful, but it typically sits in front of the database. If a user connects directly to MySQL (or otherwise bypasses the proxy), sensitive data may still be exposed. 

With Dynamic Data Masking, masking is enforced within MySQL, at the point where column values are resolved for query execution. This provides consistent enforcement regardless of how queries are issued—interactive SQL, applications, or database objects (with expected invoker/definer semantics). 

Fit for analytics and AI workloads 

Because masking is applied at query time and enforced by the server, Dynamic Data Masking can also help reduce sensitive-data exposure in downstream workflows—such as analytics extracts, reporting, and AI-assisted querying/agents—without requiring every consuming tool to implement and maintain its own masking logic. Teams can use the same datasets and pipelines while MySQL returns values appropriate to the executing identity and active roles. 

Learn more / try it