Categories:

Table, View, & Sequence DDL

ALTER MASKING POLICY

Replaces the existing masking policy rules with new rules or a new comment and allows the renaming of a masking policy.

Any changes made to the policy rules go into effect when the next SQL query that uses the masking policy runs.

Syntax

ALTER MASKING POLICY [ IF EXISTS ] <name> SET BODY -> <expression_ON_VAL> [ COMMENT = '<string_literal>' ]

ALTER MASKING POLICY [ IF EXISTS ] <name> RENAME TO <new_name>

Parameters

name

Identifier for the masking policy; must be unique for your account.

The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

expression_ON_VAL

SQL expression that transforms the data.

The expression can include Conditional Expression Functions to represent conditional logic, built-in functions or UDFs to transform the data.

If a UDF or external function is used inside the masking policy body, the policy owner must have OWNERSHIP on the UDF or external function. Users querying a column that has a masking policy applied to it do not need to have USAGE on the UDF or external function.

RENAME TO new_name

Specifies the new identifier for the role; must be unique for your account.

For more details, see Identifier Requirements.

Usage Notes

  • You cannot change the policy signature (i.e. argument name or input/output data type). If you need to change the signature, execute a DROP MASKING POLICY statement on the policy and create a new one.

  • Before executing an ALTER statement, you can execute a DESCRIBE MASKING POLICY statement to determine the argument name to use for updating the policy.

  • For masking policies that include a subquery in the masking policy body, use EXISTS in the WHEN clause. For a representative example, see the custom entitlement table example in the Examples section in CREATE MASKING POLICY.

Examples

The following example updates the masking policy to use a SHA-512 hash. Users without the ANALYST role see the value as a SHA-512 hash, while users with the ANALYST role see the plain-text value.

DESCRIBE MASKING POLICY email_mask;

-- evaluate output

+-----+------------+---------------+-------------------+-----------------------------------------------------------------------+
| Row | name       | signature     | return_type       | body                                                                  |
+-----+------------+---------------+-------------------+-----------------------------------------------------------------------+
| 1   | EMAIL_MASK | (VAL VARCHAR) | VARCHAR(16777216) | case when current_role() in ('ANALYST') then val else '*********' end |
+-----+------------+---------------+-------------------+-----------------------------------------------------------------------+

ALTER MASKING POLICY email_mask SET BODY ->
  CASE
    WHEN current_role() IN ('ANALYST') THEN VAL
    ELSE sha2(val, 512)
  END;