How to Configure Snowflake Row Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up snowflake row level security, you link a row filter policy to a table. This policy checks the active role and hides rows that do not match the rule. Data teams use this method so one table can serve many groups at once. Without these rules, broad table permissions let users read every single row of data. A small mistake in role grants can expose private records to the wrong team. How snowflake row level security works with roles and tables Snowflake uses schema objects called row fi

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

To set up snowflake row level security, you link a row filter policy to a table. This policy checks the active role and hides rows that do not match the rule. Data teams use this method so one table can serve many groups at once. Without these rules, broad table permissions let users read every single row of data. A small mistake in role grants can expose private records to the wrong team.

How snowflake row level security works with roles and tables

Snowflake uses schema objects called row filter policies to control which rows return. First, an admin writes a policy that checks the active role or user name. The policy compares that role against a specific column in the table, like region. Next, the admin attaches this policy to one or more tables or views. When a user runs a query, the database engine checks the policy first. It filters the rows before the query logic runs or returns any results.

Teams often pair these policies with mapping tables to make updates much easier. A mapping table lists which roles can read which specific values or regions. The policy checks this mapping table each time a user runs a query. If a user changes teams, an admin updates one row in the mapping table. The admin does not need to rewrite the policy or change the main table. This setup keeps role rules in one central place inside the data warehouse.

What native row filters solve and what they miss

Native row filters stop users from reading rows that belong to other groups. They help teams avoid making separate copies of the same table for each group. One shared table stays clean, and the database handles the filtering on every read. This works well when every person logs in with a unique warehouse role.

Still, row filters only decide which rows a static warehouse role can read. Many modern apps and internal tools log in using one shared service role. When a shared role runs a query, the native filter sees only that role. It does not know which human engineer or support agent triggered the query. Also, row filters do not check or block risky write commands in flight. If a role has permission to read a row, it reads the raw columns. Unless you add separate column rules, emails, phone numbers, and card numbers return in plain text. Managing both row policies and column policies across hundreds of tables takes constant work.

Row-Level Security + Snowflake Access Control: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Keeping mapping tables and role hierarchies clean

To keep your setup fast, you must watch how mapping tables grow over time. When a policy joins a huge mapping table on every query, reads slow down. Keep mapping tables small and only store the exact keys needed for the check. You also need to test how inherited roles behave when they run queries. In Snowflake, a parent role inherits the permissions of the child roles below it. If your policy checks for one exact role name, a parent role might get zero rows. Test your policies with every role in the chain before you go live. Regular audits of role grants help ensure no user gets extra rows by accident.

Add runtime guardrails and masking beside your databases and APIs

Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits inline in the protocol beside the service as an open-source proxy, a sidecar for your database or API. On every production query an agent or human runs, hoop.dev decides whether to pass, mask, block, or escalate it for review.

Before a query runs, hoop.dev guardrails check the statement against your rules, an AI Analyzer you configure can score intent or hold risky calls for human approval, and hoop.dev data masking rewrites sensitive fields in responses in memory. Explore the MIT-licensed project on GitHub.

Frequently asked questions

What happens when a role does not match the row filter policy?

The query still runs without an error, but it returns zero matching rows. The user sees an empty result set and does not know other rows exist.

Can row filters hide sensitive columns inside an allowed row?

No, a row filter either shows the whole row or hides it completely. To rewrite emails or card numbers in results, you need separate masking rules.

Do row filter policies apply to table owners and admins?

Yes, once attached to a table, the policy runs for every role, including owners. If the owner role is not in the rule, the owner sees zero rows.

The query runs. The emails stay hidden.