8.5.3.2 Using MySQL Enterprise Dynamic Data Masking Policies

Dynamic data masking enables you to conditionally mask column data for specific users or roles. This masking is defined by data masking policies.

A masking policy is a rule, or set of rules, which determines how a column's data is transformed for access control. A masking policy consists of the following:

• A gatekeeper function: criteria which determine whether data is masked.

  The following gatekeeper functions are supported:

  • CURRENT_ROLE_IN(role1,role2,...): supports a comma-separated list of roles.

  • CURRENT_USER_IN(user1,user2,...): supports a comma-separated list of usernames.

• A masking function: logic which transforms the data. This is a CASE expression.

The following example, when applied to a table column, enables members of the admin group to view the contents of the column, while masking the contents for all other users with a SHA2-256 hash function:

CREATE MASKING POLICY mask_password1(pass_column)
CASE WHEN CURRENT_ROLE_IN('admin')
     THEN pass_column
     ELSE sha2(pass_column, 256)
END;

• Creating Masking Policies

• Deleting Masking Policies

• Show Masking Policies

• Applying or Removing Masking Policies

• Compatible Types

• Masking Policy Restrictions

Creating Masking Policies

Create a masking policy using CREATE MASKING POLICY.

Deleting Masking Policies

Delete a masking policy using DROP MASKING POLICY. This deletes the masking policy from the mysql.column_masking_policies table.

To remove a masking policy from a column, use one of the DROP MASKING POLICY clauses, as described in Applying or Removing Masking Policies.

Show Masking Policies

Show a masking policy using SHOW CREATE MASKING POLICY.

Applying or Removing Masking Policies

Masking policies can be applied with the following:

• CREATE TABLE

  For example:

  CREATE TABLE user_data(
    id INT NOT NULL,
    ssn INT NOT NULL MASKING POLICY mask_ssn
  );

• ALTER TABLE ... ADD COLUMN

  For example:

  ALTER TABLE t1 ADD COLUMN j VARCHAR(256) MASKING POLICY mask_ssn;

• ALTER TABLE ... MODIFY | CHANGE COLUMN

  For example:

  ALTER TABLE t1 MODIFY COLUMN j VARCHAR(256) MASKING POLICY mask_ssn;

  ALTER TABLE t1 CHANGE COLUMN j j VARCHAR(256) MASKING POLICY mask_ssn;

Note

It is possible to reference a non-existent masking policy in column definitions.

Masking policies can be removed with the following:

• ALTER TABLE ... ALTER COLUMN

  For example, the following removes the masking policy from the column, ssn:

  ALTER TABLE user_data ALTER COLUMN ssn DROP MASKING POLICY;

Note

Omitting the MASKING POLICY attribute in a MODIFY or CHANGE operation also removes the masking policy from the column. To retain, or set a masking policy, you must specify the attribute and name of the policy.

When resolving queries, references to masked columns are replaced with the CASE expression of their respective masking policy. If the user running the query does not have the privileges to read the masked data, an error is returned. Similarly, if the query is part of a view, stored function, or procedure with SQL SECURITY INVOKER, the privileges of the invoking user are considered.

Note

In the case of CREATE TABLE... AS SELECT, the privileges of the user are considered and an error is returned if they do not have the required privileges on the masked column of the originating table. If the user has the required privileges, the masked column's data is copied to the new table, but the new column does not inherit the masking policy defined on the original. To mask the data in the new column, you must add the masking policy.

Compatible Types

MASKING POLICY can be applied to a column only if the type of the column is compatible with the type returned by the CASE expression in the policy. Types are considered compatible if one of these conditions is met:

• Both are integer types.

• Both are string types with identical collation.

• Both are DECIMAL types with the same scale. Precision can differ.

• Both are REAL types with identical precision. Both FLOAT or both DOUBLE.

• Both are TIME types with identical fractional seconds precision (FSP).

• Both are DATETIME/TIMESTAMP types with identical fractional seconds precision (FSP).

• Both have the same enum_field_type, as returned by Item::data_type(), and are not integer, string, DECIMAL, or REAL types.

Masking Policy Restrictions

Masking policies have the following restrictions:

• A masking policy cannot be assigned to a GENERATED (VIRTUAL or STORED) column.

• A masking policy cannot be assigned to a column if any GENERATED column refers to that column.

• A masking policy cannot be assigned to an indexed column.

• A masking policy cannot be assigned to a column with a histogram.

• A masking policy can be assigned to a column that has a DEFAULT expression, including non-literal defaults. An error is returned if the DEFAULT expression references a column that has a masking policy.

• A DEFAULT expression cannot reference a column that has a masking policy.

• A masking policy cannot be assigned to a column that is referenced in any CHECK constraint defined on the table.

• A masking policy cannot be assigned to a column that is part of the table’s partitioning key, including subpartitioning keys.