Masking Policy

Masking policies transform column values at query time. Authorized roles see real data; others see redacted values. Stored data never changes.

To hide entire rows instead of redacting columns, use a Row Access Policy.

When to Use

• Customer support — agents see orders, IDs show as 3201**********1234
• Analytics — email shows as ***@***.com without breaking aggregates
• VARIANT logs — hide JSON keys like secret_key / token from non-admins
• Partial redaction — show last 4 digits of a card for verification

Quick Start

CREATE TABLE user_info (id INT, email STRING NOT NULL);

CREATE MASKING POLICY email_mask
AS (val STRING)
RETURNS STRING ->
CASE
  WHEN is_role_in_session('managers') THEN val
  ELSE '*********'
END;

ALTER TABLE user_info MODIFY COLUMN email SET MASKING POLICY email_mask;

INSERT INTO user_info VALUES (1, 'user@example.com');
SELECT * FROM user_info;

id | email
---|----------
 1 | *********

How it works

• Query-time only — SELECT is masked; INSERT / UPDATE / DELETE use true values
• Column-scoped — one policy per column; reusable across tables
• Prefer is_role_in_session() over current_role() so users cannot bypass with SET ROLE

Examples

Conditional masking (USING)

Mask based on another column:

CREATE MASKING POLICY vip_mask
AS (val STRING, is_vip BOOLEAN)
RETURNS STRING ->
CASE
  WHEN is_vip = true THEN val
  ELSE '*********'
END;

ALTER TABLE user_info
MODIFY COLUMN email SET MASKING POLICY vip_mask USING (email, is_vip);

INSERT INTO user_info (id, email, is_vip) VALUES
  (1, 'vip@example.com', true),
  (2, 'normal@example.com', false);

SELECT * FROM user_info;

id | email           | is_vip
---|-----------------|-------
 1 | vip@example.com | true
 2 | *********       | false

Only add columns to USING when the policy body needs them.

VARIANT sub-field masking

Hide specific JSON keys with object_delete. All access paths honor the mask (subscript, path functions, cast, json_object_keys).

CREATE TABLE events (id INT, data VARIANT);

INSERT INTO events VALUES
  (1, parse_json('{"name":"alice","content":"secret data","secret_key":"sk_123","age":30}')),
  (2, parse_json('{"name":"bob","content":"private info","secret_key":"sk_456","age":25}'));

CREATE ROLE data_admin;
CREATE ROLE data_reader;

CREATE MASKING POLICY mask_variant_sensitive
AS (val VARIANT) RETURNS VARIANT ->
CASE
  WHEN is_role_in_session('data_admin') OR is_role_in_session('account_admin') THEN val
  ELSE object_delete(val, 'content', 'secret_key')
END;

ALTER TABLE events MODIFY COLUMN data SET MASKING POLICY mask_variant_sensitive;

GRANT SELECT ON default.events TO ROLE data_admin;
GRANT SELECT ON default.events TO ROLE data_reader;

SET ROLE data_reader;

SELECT data FROM events;
-- {"age":30,"name":"alice"}

SELECT data['content'] FROM events;                 -- NULL
SELECT data['name'] FROM events;                    -- "alice"
SELECT json_path_query_first(data, '$.content');    -- NULL
SELECT data::STRING FROM events;                    -- {"age":30,"name":"alice"}
SELECT json_object_keys(data) FROM events;          -- ["age","name"]
SELECT * FROM events WHERE data['content'] IS NOT NULL;
-- empty

Nested keys:

ELSE delete_by_keypath(val, 'nested:secret')

Read / Write Behavior

INSERT INTO user_info VALUES (2, 'admin@example.com');  -- stores real email
SELECT * FROM user_info WHERE id = 2;                   -- returns *********

Manage Policies

DESCRIBE MASKING POLICY email_mask;

ALTER TABLE user_info MODIFY COLUMN email UNSET MASKING POLICY;
DROP MASKING POLICY IF EXISTS email_mask;

Unbind every column before DROP MASKING POLICY. Find bindings with POLICY_REFERENCES(POLICY_NAME => 'email_mask').

Masking vs Row Access

A column cannot have both policies at once. Mask when the row should stay visible; use row access when the row should disappear.

Limits

• One masking policy per column
• Return type must match the column type
• Unset policy before altering or dropping the column
• Cannot drop a policy still referenced by any table
• No CREATE OR REPLACE MASKING POLICY — drop and recreate
• Not supported on temporary tables, views, or streams
• Policy names are globally unique across masking and row access policies
• Policy argument names are lowercased at create time

Best Practices

1. Prefer is_role_in_session() over current_role().
2. Keep USING minimal — only columns the body needs.
3. Return type-consistent placeholders (***@***.com for emails) if apps call LENGTH / LIKE.
4. For VARIANT, use object_delete / delete_by_keypath instead of masking the whole value.
5. Unbind before drop; verify with a restricted role after attach.

Privileges & References

• CREATE MASKING POLICY on *.* to create policies (creator gets OWNERSHIP)
• Global APPLY MASKING POLICY or APPLY ON MASKING POLICY <name> to attach/detach
• Audit: SHOW GRANTS ON MASKING POLICY <name>

Also see:

• User & Role
• CREATE MASKING POLICY
• ALTER TABLE
• Masking Policy Commands
• Row Access Policy