Data Governance · Topic 6 of 7
Managing Policies with DDL and Best Practices
Manage Snowflake masking and row access policies with DDL: create, alter the body, attach, detach, drop, find references, and follow best practices.
- 5 min
ALTER ... SET BODYchanges logic in place;UNSETdetaches;DROPneeds zero attachments.- Masking:
SET MASKING POLICY ... FORCEreplaces; row access: one policy per table, swap with oneDROP ..., ADD ...statement. POLICY_REFERENCESshows where policies are used.
What it is
Policies are objects, so you manage them with the usual DDL: CREATE, ALTER, DESCRIBE, SHOW, DROP. The part that trips people up is the difference between creating a policy, attaching it to a column or table, and changing one that is already attached. This page covers that lifecycle and the practices that keep it safe.
How it works
Masking policy DDL
| Task | Statement |
|---|---|
| Create | CREATE MASKING POLICY p AS (val STRING) RETURNS STRING -> ... |
| Change the logic | ALTER MASKING POLICY p SET BODY -> ... |
| Rename | ALTER MASKING POLICY p RENAME TO p2 |
| Attach to a column | ALTER TABLE t MODIFY COLUMN c SET MASKING POLICY p |
| Replace the attached policy | ... SET MASKING POLICY p2 FORCE |
| Detach | ALTER TABLE t MODIFY COLUMN c UNSET MASKING POLICY |
| Attach to a tag | ALTER TAG tg SET MASKING POLICY p |
| List and inspect | SHOW MASKING POLICIES, DESCRIBE MASKING POLICY p |
| Drop | DROP MASKING POLICY p |
Rules to remember:
SET BODYchanges the logic in place for every column using the policy. You do not need to detach anything.DROPfails while the policy is still attached somewhere. Detach it from every column or table first, then drop it.- A column holds one masking policy. Setting a second one fails unless you add
FORCE. - You cannot change the signature of a policy (the input type), so recreate the policy when the type changes.
ALTER ... SET TAGandSET COMMENTwork on policies, which helps documentation.
Row access policy DDL
| Task | Statement |
|---|---|
| Create | CREATE ROW ACCESS POLICY p AS (c VARCHAR) RETURNS BOOLEAN -> ... |
| Change the logic | ALTER ROW ACCESS POLICY p SET BODY -> ... |
| Attach | ALTER TABLE t ADD ROW ACCESS POLICY p ON (c) |
| Detach | ALTER TABLE t DROP ROW ACCESS POLICY p |
| Swap in one statement | ALTER TABLE t DROP ROW ACCESS POLICY p1, ADD ROW ACCESS POLICY p2 ON (c) |
| Drop | DROP ROW ACCESS POLICY p after detaching it everywhere |
A table can hold only one row access policy, so swapping must be one statement, with a drop and an add together, so there is no moment when the table is unprotected.
Finding where policies are used
| Source | Use |
|---|---|
INFORMATION_SCHEMA.POLICY_REFERENCES table function | Policies on one object, or objects using one policy |
SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES view | Account wide inventory |
ACCOUNT_USAGE.MASKING_POLICIES and ROW_ACCESS_POLICIES views | The policy definitions |
ACCESS_HISTORY | Which queries touched policy protected data |
Check POLICY_REFERENCES before you drop or change anything.
Privileges
| Privilege | Allows |
|---|---|
CREATE MASKING POLICY or CREATE ROW ACCESS POLICY on a schema | Create the policy |
APPLY on a policy | Attach or detach that policy |
APPLY MASKING POLICY or APPLY ROW ACCESS POLICY on the account | Attach or detach any policy of that type |
Give these to a policy administrator role, not to every table owner. Table owners then cannot remove protection by accident or on purpose.
Best practices for masking policies
- Centralize. Keep policies in one governance schema, owned by a policy administrator role.
- Mask by tag. Attach policies to tags so new columns are covered automatically, and one policy per data type serves many columns.
- Pick the masking style by use. Hash if users must join, partial if they need a clue, full or
NULLif they need nothing. - Match the return type to the column exactly, and keep the output length compatible with the column.
- Use
IS_ROLE_IN_SESSIONso roles that inherit an authorized role also see clear values. - Default to masked. Put the clear value in the
WHENbranch for named roles and mask in theELSE, so any new role is protected. - Keep the body simple. A policy runs on every query, so avoid heavy subqueries and large lookups.
- Test with real roles before attaching to production, for example by running the same query under each role through
USE ROLE. - Audit. Review
POLICY_REFERENCESandACCESS_HISTORYon a schedule. - Change with
SET BODY, not by drop and recreate, so the column is never unprotected.
When tag based masking and a direct column policy both exist, the policy set directly on the column takes precedence over the tag policy.
CREATE OR REPLACE on a policy that is already attached to columns is not a safe way to edit it. Use ALTER ... SET BODY, which keeps all attachments.
Common mistakes
Dropping an attached policy. Detach it from every object first.
Editing by drop and recreate. Use SET BODY, so the column keeps protection.
Giving APPLY to table owners. Keep it with a policy administrator.
Defaulting to clear. Write the ELSE branch as the masked value.
What to remember for the exam
ALTER ... SET BODYchanges logic in place;UNSETdetaches;DROPneeds zero attachments.- Masking:
SET MASKING POLICY ... FORCEreplaces; row access: one policy per table, swap with oneDROP ..., ADD ...statement. POLICY_REFERENCESshows where policies are used.- Best practices: central admin role, tag based masking,
IS_ROLE_IN_SESSION, default masked, simple bodies, test per role.
Check yourself
How do you change the logic of a masking policy attached to 50 columns?
Use ALTER MASKING POLICY ... SET BODY, which updates all attachments in place.
Why can't you drop a policy right away?
It is still attached to one or more columns or tables, so it must be detached from all of them first.
Which function shows every object that uses a policy?
POLICY_REFERENCES, as an INFORMATION_SCHEMA table function or an ACCOUNT_USAGE view.
Topic 6 of 7 in Data Governance
Test yourself on Data Governance
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.