What are the system-defined roles in Snowflake?

`ORGADMIN`, `ACCOUNTADMIN`, `SECURITYADMIN`, `USERADMIN`, `SYSADMIN` and `PUBLIC`. Custom roles should be created under `SYSADMIN` so it can manage every object, and `ACCOUNTADMIN` should be used only for billing and account-level settings, never for daily work.

What is a future grant in Snowflake?

A grant that applies automatically to objects created later — `grant select on future tables in schema x to role y`. Without it, every new table needs a manual grant, which is the usual reason a pipeline breaks the morning after someone adds a model.

What is a masking policy?

A schema-level rule attached to a column that rewrites the value returned based on the querying role. The underlying data is unchanged, so one table can show full email addresses to support staff and hashed values to analysts, with no duplicate copies.

How does Snowflake data sharing work without copying data?

A share grants another account read access to selected objects in your storage. The consumer queries live data with their own warehouse, so there is no copy, no ETL and no lag — and you pay for storage while they pay for compute.

Roles, Masking, and Secure Data Sharing

Snowflake’s access control is role-based and object-scoped: privileges are granted to roles, roles are granted to users and to other roles, and every object has an owner role. The mechanics are simple; the discipline is in designing a hierarchy that does not need a manual grant every time someone adds a table.

The system roles

ACCOUNTADMIN            billing, account settings — a handful of people, MFA required
├── SECURITYADMIN       manages grants globally
│   └── USERADMIN       creates users and roles
└── SYSADMIN            owns all databases and warehouses
    ├── ANALYTICS_ADMIN     custom
    ├── ANALYST             custom
    └── LOADER              custom
PUBLIC                  every user has it — grant almost nothing here

The rule worth following: create custom roles beneath SYSADMIN. If a custom role owns objects but is not granted to SYSADMIN, then SYSADMIN cannot manage them, and you end up using ACCOUNTADMIN for routine work — which is how a warehouse ends up with everybody holding the keys to billing.

Functional roles

use role useradmin;

create role if not exists loader;      -- writes raw data
create role if not exists transformer; -- builds models
create role if not exists analyst;     -- reads marts

use role securityadmin;
grant role loader      to role sysadmin;
grant role transformer to role sysadmin;
grant role analyst     to role transformer;   -- transformer inherits analyst's reads

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Granting analyst to transformer means anything an analyst can read, a transformer can read too. Role hierarchies inherit upward — that is what keeps the number of explicit grants manageable.

use role sysadmin;

grant usage on database bookshop to role analyst;
grant usage on schema bookshop.marts to role analyst;
grant select on all tables in schema bookshop.marts to role analyst;
grant usage on warehouse reporting_wh to role analyst;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

USAGE on the database and the schema is required before any table grant does anything — missing the schema-level USAGE produces a confusing “does not exist or not authorized” error on a table that clearly exists.

Future grants

The grant above covers tables that existed when it ran. Tomorrow’s table is invisible:

use role transformer;
create table bookshop.marts.new_mart as select 1 as x;

use role analyst;
select * from bookshop.marts.new_mart;

002003 (42S02): SQL compilation error:
Object 'BOOKSHOP.MARTS.NEW_MART' does not exist or not authorized.

use role sysadmin;
grant select on future tables in schema bookshop.marts to role analyst;
grant select on future views  in schema bookshop.marts to role analyst;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Now every table created in that schema is readable by analysts on creation. Grant on future schemas at the database level to cover new schemas too. This one setting eliminates most “the dashboard broke overnight” tickets.

Check what a role actually has:

show grants to role analyst;

+-------------------------------+-----------+------------------------+---------------+
| privilege                     | granted_on| name                   | granted_to    |
|-------------------------------+-----------+------------------------+---------------|
| USAGE                         | DATABASE  | BOOKSHOP               | ROLE          |
| USAGE                         | SCHEMA    | BOOKSHOP.MARTS         | ROLE          |
| SELECT                        | TABLE     | BOOKSHOP.MARTS.DAILY_REVENUE | ROLE    |
| USAGE                         | WAREHOUSE | REPORTING_WH           | ROLE          |
+-------------------------------+-----------+------------------------+---------------+
4 Row(s) produced. Time Elapsed: 0.288s

Masking a column

create or replace masking policy email_mask as (val string) returns string ->
    case
        when current_role() in ('SUPPORT', 'ACCOUNTADMIN') then val
        when current_role() = 'ANALYST' then regexp_replace(val, '.+@', '*****@')
        else '***MASKED***'
    end;

alter table customers modify column email set masking policy email_mask;

+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Masking policy EMAIL_MASK successfully created. |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

use role support;   select customer_id, email from customers limit 2;
use role analyst;   select customer_id, email from customers limit 2;
use role loader;    select customer_id, email from customers limit 2;

+-------------+----------------------+
| CUSTOMER_ID | EMAIL                |
|-------------+----------------------|
|           1 | ada@example.com      |
|           2 | grace@example.com    |
+-------------+----------------------+

+-------------+----------------------+
| CUSTOMER_ID | EMAIL                |
|-------------+----------------------|
|           1 | *****@example.com    |
|           2 | *****@example.com    |
+-------------+----------------------+

+-------------+----------------------+
| CUSTOMER_ID | EMAIL                |
|-------------+----------------------|
|           1 | ***MASKED***         |
|           2 | ***MASKED***         |
+-------------+----------------------+

One table, one copy, three views of it. The policy is applied at query time wherever the column is referenced — including through views, joins and SELECT * — so there is no way to route around it by querying something downstream.

Filtering rows

create or replace row access policy country_rows as (country_code string) returns boolean ->
    current_role() in ('ACCOUNTADMIN', 'ANALYTICS_ADMIN')
    or exists (
        select 1 from admin.role_country_map m
        where m.role_name = current_role() and m.country_code = country_code
    );

alter table customers add row access policy country_rows on (country_code);

+------------------------------------------------+
| status                                         |
|------------------------------------------------|
| Row access policy COUNTRY_ROWS successfully created. |
+------------------------------------------------+

use role analyst_gb; select customer_id, country_code from customers;

+-------------+--------------+
| CUSTOMER_ID | COUNTRY_CODE |
|-------------+--------------|
|           1 | GB           |
|           3 | GB           |
+-------------+--------------+
2 Row(s) produced. Time Elapsed: 0.402s

The US rows are not hidden in the UI — they do not exist as far as this session is concerned, including in count(*) and in aggregates. Driving the policy from a mapping table rather than a hard-coded list means adding a region is an INSERT, not a DDL change.

Secure views

An ordinary view can leak data it filters out. The optimiser may push a user-supplied function into the view’s own predicates, and error messages can reveal filtered values. SECURE disables those optimisations:

create or replace secure view marts.customer_summary as
select customer_id, country_code, lifetime_value
from raw.customers
where deleted_at is null;

+-----------------------------------------------------+
| status                                              |
|-----------------------------------------------------|
| View CUSTOMER_SUMMARY successfully created.         |
+-----------------------------------------------------+

It costs some performance, so use it where the view is the security boundary — anything filtering rows by tenant, region or privacy status, and anything included in a share, where it is mandatory.

use role accountadmin;

create or replace share bookshop_partner_share;

grant usage  on database bookshop                     to share bookshop_partner_share;
grant usage  on schema   bookshop.marts               to share bookshop_partner_share;
grant select on view     bookshop.marts.customer_summary to share bookshop_partner_share;

alter share bookshop_partner_share add accounts = ('PARTNER_ORG.AWS_EU_WEST_1');

+---------------------------------------------------+
| status                                            |
|---------------------------------------------------|
| Share BOOKSHOP_PARTNER_SHARE successfully created.|
+---------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.402s

On the consumer’s side:

create database partner_data from share bookshop_org.bookshop_partner_share;
select * from partner_data.marts.customer_summary limit 3;

+-------------+--------------+----------------+
| CUSTOMER_ID | COUNTRY_CODE | LIFETIME_VALUE |
|-------------+--------------+----------------|
|           1 | GB           |          65.50 |
|           2 | US           |          12.00 |
|           3 | GB           |           8.75 |
+-------------+--------------+----------------+
3 Row(s) produced. Time Elapsed: 0.688s

No copy, no pipeline, no lag — the consumer queries your storage with their own warehouse. You pay for storage, they pay for compute, and revoking access is one ALTER SHARE.

Constraints worth knowing: shared databases are strictly read-only, a share cannot be shared onward, only secure views may be included, and the accounts must be in the same cloud region unless replication is set up.

show grants to share bookshop_partner_share;

+-----------+------------+---------------------------------+
| privilege | granted_on | name                            |
|-----------+------------+---------------------------------|
| USAGE     | DATABASE   | BOOKSHOP                        |
| USAGE     | SCHEMA     | BOOKSHOP.MARTS                  |
| SELECT    | VIEW       | BOOKSHOP.MARTS.CUSTOMER_SUMMARY |
+-----------+------------+---------------------------------+
3 Row(s) produced. Time Elapsed: 0.194s

Audit this list before adding an account. A stray grant select on all tables here is a data breach with a friendly interface.

Practice

1. Create a role, grant it read on one schema, and query as that role.

use role analyst;
select count(*) from bookshop.raw.orders;

002003 (42S02): SQL compilation error:
Object 'BOOKSHOP.RAW.ORDERS' does not exist or not authorized.

The analyst has marts but not raw. Note that Snowflake reports “does not exist or not authorized” as one message deliberately — it refuses to confirm that an object exists to someone who cannot see it.

2. Create a table after granting, without a future grant.

002003 (42S02): SQL compilation error:
Object 'BOOKSHOP.MARTS.NEW_MART' does not exist or not authorized.

Then add the future grant and repeat: the next new table is readable immediately. Adding future grants for tables, views and schemas at setup time is a ten-minute job that prevents a recurring class of incident.

3. Apply a masking policy and query as two roles.

-- SUPPORT
| ada@example.com   |
-- ANALYST
| *****@example.com |

Then try to route around it with a view: create view v as select email from customers and query it as ANALYST. It stays masked — policies follow the column wherever it is referenced, which is what makes them trustworthy.

4. Inspect what a share exposes.

show grants to share bookshop_partner_share;

+-----------+------------+---------------------------------+
| privilege | granted_on | name                            |
|-----------+------------+---------------------------------|
| USAGE     | DATABASE   | BOOKSHOP                        |
| USAGE     | SCHEMA     | BOOKSHOP.MARTS                  |
| SELECT    | VIEW       | BOOKSHOP.MARTS.CUSTOMER_SUMMARY |
+-----------+------------+---------------------------------+

Exactly one view. Attempting to add a non-secure view fails outright — Snowflake enforces the rule rather than trusting you to remember it.

Next: query profiling and cost control — finding the query that is spending your budget.