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
In a nutshell
  • ALTER ... SET BODY changes logic in place; UNSET detaches; DROP needs zero attachments.
  • Masking: SET MASKING POLICY ... FORCE replaces; row access: one policy per table, swap with one DROP ..., ADD ... statement.
  • POLICY_REFERENCES shows 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.

CREATE POLICYobject in a schemaattachALTER TABLESET or ADD policychangeALTER POLICYSET BODYremoveUNSET or DROPdetach first

How it works

Masking policy DDL

TaskStatement
CreateCREATE MASKING POLICY p AS (val STRING) RETURNS STRING -> ...
Change the logicALTER MASKING POLICY p SET BODY -> ...
RenameALTER MASKING POLICY p RENAME TO p2
Attach to a columnALTER TABLE t MODIFY COLUMN c SET MASKING POLICY p
Replace the attached policy... SET MASKING POLICY p2 FORCE
DetachALTER TABLE t MODIFY COLUMN c UNSET MASKING POLICY
Attach to a tagALTER TAG tg SET MASKING POLICY p
List and inspectSHOW MASKING POLICIES, DESCRIBE MASKING POLICY p
DropDROP MASKING POLICY p

Rules to remember:

  • SET BODY changes the logic in place for every column using the policy. You do not need to detach anything.
  • DROP fails 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 TAG and SET COMMENT work on policies, which helps documentation.

Row access policy DDL

TaskStatement
CreateCREATE ROW ACCESS POLICY p AS (c VARCHAR) RETURNS BOOLEAN -> ...
Change the logicALTER ROW ACCESS POLICY p SET BODY -> ...
AttachALTER TABLE t ADD ROW ACCESS POLICY p ON (c)
DetachALTER TABLE t DROP ROW ACCESS POLICY p
Swap in one statementALTER TABLE t DROP ROW ACCESS POLICY p1, ADD ROW ACCESS POLICY p2 ON (c)
DropDROP 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

SourceUse
INFORMATION_SCHEMA.POLICY_REFERENCES table functionPolicies on one object, or objects using one policy
SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES viewAccount wide inventory
ACCOUNT_USAGE.MASKING_POLICIES and ROW_ACCESS_POLICIES viewsThe policy definitions
ACCESS_HISTORYWhich queries touched policy protected data

Check POLICY_REFERENCES before you drop or change anything.

Privileges

PrivilegeAllows
CREATE MASKING POLICY or CREATE ROW ACCESS POLICY on a schemaCreate the policy
APPLY on a policyAttach or detach that policy
APPLY MASKING POLICY or APPLY ROW ACCESS POLICY on the accountAttach 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

  1. Centralize. Keep policies in one governance schema, owned by a policy administrator role.
  2. Mask by tag. Attach policies to tags so new columns are covered automatically, and one policy per data type serves many columns.
  3. Pick the masking style by use. Hash if users must join, partial if they need a clue, full or NULL if they need nothing.
  4. Match the return type to the column exactly, and keep the output length compatible with the column.
  5. Use IS_ROLE_IN_SESSION so roles that inherit an authorized role also see clear values.
  6. Default to masked. Put the clear value in the WHEN branch for named roles and mask in the ELSE, so any new role is protected.
  7. Keep the body simple. A policy runs on every query, so avoid heavy subqueries and large lookups.
  8. Test with real roles before attaching to production, for example by running the same query under each role through USE ROLE.
  9. Audit. Review POLICY_REFERENCES and ACCESS_HISTORY on a schedule.
  10. Change with SET BODY, not by drop and recreate, so the column is never unprotected.
Policy admin rolecentralownsGovernance schemapolicies and tagstag attachedTagged columnsall tablesqueries byTest rolesverified output
Exam tip

When tag based masking and a direct column policy both exist, the policy set directly on the column takes precedence over the tag policy.

Watch out

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 BODY changes logic in place; UNSET detaches; DROP needs zero attachments.
  • Masking: SET MASKING POLICY ... FORCE replaces; row access: one policy per table, swap with one DROP ..., ADD ... statement.
  • POLICY_REFERENCES shows 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.