Row Access Policies in Snowflake with dbt

Column masking hides the value. Row access policies hide the row. Both live in Snowflake’s governance layer, both are invisible to whoever is running the query, and both are the right tool for a specific problem. If a sales rep in EMEA should never see APAC orders – not redacted, not null, completely absent from the result set – that is a row access policy.

The masking guide covered classification tags and column-level policies. This one covers rows. The pattern is similar: a mapping table that records which roles can see which data, a policy that checks that mapping at query time, and a dbt macro that binds the policy wherever it needs to go. No view wrappers, no WHERE clauses buried in application code, no relying on every consumer remembering to filter.

Three things to build.

1. Create the governance schema and mapping table

Run this as SECURITYADMIN. The same dedicated schema from the masking guide works here – governance objects should live apart from your data schemas.

The mapping table is the source of truth. A role maps to one or more values of the column being filtered. Keep it flat: role name, allowed value, one row per pair. Adding a region later means inserting a row, not touching the policy.

USE ROLE SECURITYADMIN;

CREATE DATABASE IF NOT EXISTS master_governance;
CREATE SCHEMA IF NOT EXISTS master_governance.policies;

CREATE TABLE IF NOT EXISTS master_governance.policies.role_region_map (
    role_name  VARCHAR NOT NULL,
    region     VARCHAR NOT NULL
);

INSERT INTO master_governance.policies.role_region_map VALUES
    ('SALES_EMEA', 'EMEA'),
    ('SALES_APAC', 'APAC'),
    ('SALES_AMER', 'AMER');

2. Create the row access policy

The policy is a function. It receives the value of the column you are filtering on and returns a boolean: true means the row is visible, false means it does not exist as far as the querier is concerned.

Use IS_ROLE_IN_SESSION rather than CURRENT_ROLE(). The difference is that IS_ROLE_IN_SESSION checks all active roles in the session, including secondary roles, so a user with USE SECONDARY ROLES ALL does not have their access silently broken.

USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROW ACCESS POLICY master_governance.policies.region_filter
    AS (region VARCHAR) RETURNS BOOLEAN ->
        IS_ROLE_IN_SESSION('SYSADMIN')
        OR IS_ROLE_IN_SESSION('SECURITYADMIN')
        OR EXISTS (
            SELECT 1
            FROM master_governance.policies.role_region_map m
            WHERE IS_ROLE_IN_SESSION(m.role_name)
              AND m.region = region
        );

3. Grant the dbt role permission to apply the policy

Your dbt role needs to read the mapping table and to bind the policy to tables it owns. These are two separate privileges and both are required. Missing either one produces an unhelpful error at run time.

USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE master_governance TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA master_governance.policies TO ROLE TRANSFORMER;
GRANT SELECT ON TABLE master_governance.policies.role_region_map TO ROLE TRANSFORMER;
GRANT APPLY ON ROW ACCESS POLICY master_governance.policies.region_filter TO ROLE TRANSFORMER;

4. Write the dbt macro

Put this in macros/apply_row_access_policy.sql. It queries information_schema.policy_references to find any existing row access policy on the table, drops it, then applies the one you specify. The drop step is what makes re-running the model safe – Snowflake does not allow two row access policies on the same table, and without the check you would get an error on every run after the first.

{% macro apply_row_access_policy(policy_name, column_name) %}
  {% if execute %}
    {% set existing_policies %}
      SELECT policy_name
      FROM TABLE(information_schema.policy_references(
          ref_entity_name   => '{{ this.database }}.{{ this.schema }}.{{ this.identifier }}',
          ref_entity_domain => 'TABLE'
      ))
      WHERE policy_kind = 'ROW_ACCESS_POLICY'
    {% endset %}

    {% for row in run_query(existing_policies) %}
      {% do run_query(
        "ALTER TABLE " ~ this ~ " DROP ROW ACCESS POLICY " ~ row['POLICY_NAME']
      ) %}
    {% endfor %}

    {% do run_query(
      "ALTER TABLE " ~ this
      ~ " ADD ROW ACCESS POLICY " ~ policy_name
      ~ " ON (" ~ column_name ~ ")"
    ) %}
  {% endif %}
{% endmacro %}

5. Apply it to a model

Pass the macro as a post_hook in the model config. Snowflake runs it after the table is created or replaced, so the policy is reapplied on every dbt run and stays in sync with the model.

{{ config(
    post_hook="{{ apply_row_access_policy(
        'master_governance.policies.region_filter',
        'region'
    ) }}"
) }}

SELECT
    order_id,
    customer_id,
    region,
    amount,
    created_at
FROM {{ ref('stg_orders') }}

6. Verify

Switch roles and query the same table. Row counts should differ. If they do not, the most common cause is the role not being in the mapping table, or a case mismatch between the column name in the policy binding and the actual column name in the table.

USE ROLE SALES_EMEA;
SELECT DISTINCT region FROM analytics.sales.orders;
-- EMEA

USE ROLE SYSADMIN;
SELECT DISTINCT region FROM analytics.sales.orders;
-- EMEA, APAC, AMER

The policy runs on every query against that table, from every tool, every BI connection, every notebook. Add a row to the mapping table and the access is there. Delete it and the rows disappear.

Full script

-- 1. Governance schema and mapping table (run as SECURITYADMIN)
CREATE DATABASE IF NOT EXISTS master_governance;
CREATE SCHEMA IF NOT EXISTS master_governance.policies;
CREATE TABLE IF NOT EXISTS master_governance.policies.role_region_map (
role_name  VARCHAR NOT NULL,
region     VARCHAR NOT NULL
);
INSERT INTO master_governance.policies.role_region_map VALUES
(‘SALES_EMEA’, ‘EMEA’),
(‘SALES_APAC’, ‘APAC’),
(‘SALES_AMER’, ‘AMER’);
– 2. Row access policy (run as SECURITYADMIN)
CREATE OR REPLACE ROW ACCESS POLICY master_governance.policies.region_filter
AS (region VARCHAR) RETURNS BOOLEAN ->
IS_ROLE_IN_SESSION(‘SYSADMIN’)
OR IS_ROLE_IN_SESSION(‘SECURITYADMIN’)
OR EXISTS (
SELECT 1
FROM master_governance.policies.role_region_map m
WHERE IS_ROLE_IN_SESSION(m.role_name)
AND m.region = region
);
– 3. Grants (run as SECURITYADMIN)
GRANT USAGE ON DATABASE master_governance TO ROLE TRANSFORMER;
GRANT USAGE ON SCHEMA master_governance.policies TO ROLE TRANSFORMER;
GRANT SELECT ON TABLE master_governance.policies.role_region_map TO ROLE TRANSFORMER;
GRANT APPLY ON ROW ACCESS POLICY master_governance.policies.region_filter TO ROLE TRANSFORMER;
– 4. macros/apply_row_access_policy.sql
{% macro apply_row_access_policy(policy_name, column_name) %}
{% if execute %}
{% set existing_policies %}
SELECT policy_name
FROM TABLE(information_schema.policy_references(
ref_entity_name   => ‘{{ this.database }}.{{ this.schema }}.{{ this.identifier }}’,
ref_entity_domain => ‘TABLE’
))
WHERE policy_kind = ‘ROW_ACCESS_POLICY’
{% endset %}
{% for row in run_query(existing_policies) %}
  {% do run_query(
    "ALTER TABLE " ~ this ~ " DROP ROW ACCESS POLICY " ~ row['POLICY_NAME']
  ) %}
{% endfor %}

{% do run_query(
  "ALTER TABLE " ~ this
  ~ " ADD ROW ACCESS POLICY " ~ policy_name
  ~ " ON (" ~ column_name ~ ")"
) %}

{% endif %}
{% endmacro %}
– 5. models/marts/orders.sql
{{ config(
post_hook="{{ apply_row_access_policy(
‘master_governance.policies.region_filter’,
‘region’
) }}"
) }}
SELECT
order_id,
customer_id,
region,
amount,
created_at
FROM {{ ref(‘stg_orders’) }}
– 6. Verify
USE ROLE SALES_EMEA;
SELECT DISTINCT region FROM analytics.sales.orders;
USE ROLE SYSADMIN;
SELECT DISTINCT region FROM analytics.sales.orders;