In this post we are going to cover security, governance and data sharing in Snowflake: roles and grants, future grants and managed access, masking and row access policies, secure data sharing, and how people and services sign in. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where an auditor asks how your Snowflake account is secured and who can see sensitive data.
Security in Snowflake works in layers. Roles and grants decide who can touch which objects, policies decide which columns and rows each person actually sees, shares decide what leaves your account, and authentication and network policies decide who can sign in and from where. Interviewers ask about it because every real project hits these questions early, usually when a new analyst joins, HR asks about salaries, or an auditor shows up.
Part 9 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Question 1: A new analyst needs read access to one schema. How do roles, grants and the role hierarchy work in Snowflake?
- Question 2: Every new table in a schema must be readable by the reporting role automatically. How do future grants and managed access schemas work?
- Question 3: HR wants salaries hidden from most users. How do masking policies work, and how do tags help at scale?
- Question 4: Sales managers must see only their region's rows. How do row access policies work?
- Question 5: A partner company needs live access to your sales data. How does secure data sharing work, and what is a reader account?
- Question 6: Security says passwords alone are no longer allowed. How do MFA, key-pair authentication and network policies work?
- Scenario 7: An auditor asks how your Snowflake account is secured and who can see sensitive data. What do you show?
The short answer
Privileges go to roles, roles go to users, and custom roles roll up to SYSADMIN. Future grants cover new tables, and managed access schemas keep grant decisions in one place. Masking and row access policies protect columns and rows, tags apply them at scale, shares give partners live read access, and sign-in means MFA for people and key pairs for services.
Keep that in mind. Every question below is one piece of it.
Question 1: A new analyst needs read access to one schema. How do roles, grants and the role hierarchy work in Snowflake?
Snowflake uses role-based access control. Privileges are granted to roles, and roles are granted to users. Every object is owned by exactly one role, by default the role that created it.
So for the analyst, we create a read role and grant it four things:
- Usage on the database.
- Usage on the schema.
- Select on all tables in the schema.
- Usage on a warehouse, so they can run queries.
Without the usage grants, select alone does nothing. Here is the whole thing:
-- a read role for one schema
CREATE ROLE analyst_read;
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_read;
GRANT USAGE ON SCHEMA sales_db.reporting
TO ROLE analyst_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.reporting
TO ROLE analyst_read;
GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst_read;
GRANT ROLE analyst_read TO USER new_analyst;
GRANT ROLE analyst_read TO ROLE SYSADMIN;The last line is the hierarchy. Roles can be granted to other roles, and a role inherits the privileges of every role granted to it. Best practice is to grant the top of your custom roles to SYSADMIN, so SYSADMIN can manage all objects.
Now know the system roles, because interviewers ask:
- ACCOUNTADMIN - the top role.
- SECURITYADMIN - manages grants.
- USERADMIN - creates users and roles.
- SYSADMIN - creates warehouses and databases.
- PUBLIC - granted to every user and role.
Granting straight to people, or handing out ACCOUNTADMIN to unblock someone. Grant to roles, and keep ACCOUNTADMIN to a very small group.
Question 2: Every new table in a schema must be readable by the reporting role automatically. How do future grants and managed access schemas work?
A grant on all tables only covers the tables that exist today. A future grant covers what comes next.
So in a real project we run both. The first grant covers existing tables, and the second one covers every table created from now on. Each new table gets the grant at the moment it's created:
-- what exists today
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.reporting
TO ROLE reporting_read;
-- every table created from now on
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales_db.reporting
TO ROLE reporting_read;You can also set future grants at the database level. But when both exist for the same object type, the schema-level future grant wins.
Then managed access. It changes who can grant. Let's compare it with a normal schema:
| Normal schema | Managed access schema | |
|---|---|---|
| WHO CAN GRANT | the role that owns the object | schema owner, or MANAGE GRANTS |
In a normal schema, the role that owns a table can grant access to it. In a managed access schema, only the schema owner, or a role with the MANAGE GRANTS privilege, can grant. Turning it on is one statement:
-- grant decisions in one place
ALTER SCHEMA sales_db.reporting ENABLE MANAGED ACCESS;That's what security teams want: access is decided in one place, not by every developer who creates a table.
A database role granted to a share doesn't support future grants. When you add a new table, add it to the share explicitly.
Question 3: HR wants salaries hidden from most users. How do masking policies work, and how do tags help at scale?
A masking policy is a schema-level object attached to a column, and it's evaluated at query run time. So the same query shows the real salary to HR and a masked value to everyone else. It needs Enterprise edition or higher.
The policy takes the value and returns the same type. Let's look at it:
CREATE MASKING POLICY mask_salary AS (val NUMBER)
RETURNS NUMBER ->
CASE WHEN IS_ROLE_IN_SESSION('HR') THEN val
ELSE NULL END;
ALTER TABLE hr.employee MODIFY COLUMN salary
SET MASKING POLICY mask_salary;
-- at scale: one policy per data type on a tag
CREATE TAG hr.sensitive;
ALTER TAG hr.sensitive SET MASKING POLICY mask_salary;
ALTER TABLE hr.payroll MODIFY COLUMN bonus
SET TAG hr.sensitive = 'pay';Here, if the HR role is in the session, it returns the salary, otherwise null. Use IS_ROLE_IN_SESSION, not CURRENT_ROLE, so role hierarchies work. Same query, two roles, two results:
| Role in session | salary |
|---|---|
| HR | 95000 |
| ANALYST | NULL |
Now at scale. You don't want to attach a policy column by column across hundreds of tables. Instead, attach the masking policy to a tag, one policy per data type, and then tag the columns. Every tagged column of that type is protected. The tag can also sit on a table, a schema or a database. That's the second half of the code above: the bonus column gets the tag, and the policy comes with it.
How do you find those columns in the first place? A classification profile on a database or schema classifies tables in the background and can apply tags, which carry the masking policies.
One masking policy per column, and a policy set directly on a column overrides the tag-based one.
Question 4: Sales managers must see only their region's rows. How do row access policies work?
A row access policy returns true or false for each row. You attach it to a table on one or more columns, and queries only see the rows where it returns true. Like masking, it needs Enterprise edition or higher.
For sales managers, the policy usually looks up a mapping table of role and region. Let's create it:
CREATE ROW ACCESS POLICY sales_region
AS (row_region STRING) RETURNS BOOLEAN ->
IS_ROLE_IN_SESSION('SALES_ADMIN')
OR EXISTS (
SELECT 1 FROM security.region_map m
WHERE m.region = row_region
AND IS_ROLE_IN_SESSION(m.role_name)
);
ALTER TABLE sales.orders
ADD ROW ACCESS POLICY sales_region ON (region);The sales admin role sees everything. Everyone else sees a row only when the mapping table links one of their roles to that row's region. The mapping table looks like this:
| role_name | region |
|---|---|
| SALES_EAST | EAST |
| SALES_WEST | WEST |
Then we add the policy to the orders table on the region column, and the result is:
| Role | Sees |
|---|---|
| SALES_EAST | EAST rows only |
| SALES_ADMIN | every row |
One policy can protect many tables. And when a manager moves region, you update the mapping table, not the policy.
A couple of rules to know. If a table has both kinds of policy, Snowflake evaluates the row access policy first, then masking. And the same column can't be in both a row access policy and a masking policy signature.
Keep the lookup simple, because it runs with every query. And test with a real manager's role, not an admin role that sees every row.
Question 5: A partner company needs live access to your sales data. How does secure data sharing work, and what is a reader account?
A share gives other Snowflake accounts read access to objects you choose, and no data is copied.
Here is the flow, step by step:
- In your account, grant the share usage on the database and schema.
- Grant select on tables or secure views.
- Add the partner's account to the share.
- The partner creates a read-only database from it, sees your live data, and pays for their own compute.
CREATE SHARE sales_share;
GRANT USAGE ON DATABASE sales_db TO SHARE sales_share;
GRANT USAGE ON SCHEMA sales_db.partner
TO SHARE sales_share;
-- v_orders is a secure view
GRANT SELECT ON VIEW sales_db.partner.v_orders
TO SHARE sales_share;
ALTER SHARE sales_share ADD ACCOUNTS = partner_org.acme;If you share a view, it must be a secure view. That's the usual way to show a partner only some columns or rows. Database roles can split one share into groups of objects.
A direct share works within one region. To reach a partner in another region or cloud, you replicate the database there and share from the copy, or you publish a listing with cross-cloud auto-fulfillment.
Now the reader account. If the partner has no Snowflake account, you create a reader account for them. You create it, you own it, and you pay for its compute. It can only query your shares, so it can't do any of this:
- Loading data
- Inserts, updates, deletes
- Shares of its own
Put a resource monitor on the reader account's warehouse, because its credits are on your bill.
Question 6: Security says passwords alone are no longer allowed. How do MFA, key-pair authentication and network policies work?
Snowflake is phasing out single-factor password sign-in. By late 2026, every human user who signs in with a password needs MFA. Service users can't use passwords at all.
Let's split it by user type:
| User type | Signs in with | Password |
|---|---|---|
| PERSON | passkey, authenticator app or Duo; or SSO | only with MFA |
| SERVICE | key pair, access token, OAuth, workload identity | not allowed |
For people, MFA can be a passkey, which Snowflake recommends, an authenticator app, or Duo. With single sign-on, your identity provider enforces MFA.
Service users, like pipelines and BI tools, use key pairs, programmatic access tokens, OAuth, or workload identity federation. Workload identity uses the workload's cloud identity, with no stored secret.
For key-pair, we generate an RSA key of at least 2048 bits, keep the private key in our secret store, and set the public key on the user. Snowflake now recommends named key pairs, which add expiry and easier rotation.
Then network policies, which control where sign-ins come from:
- Network rules hold IP ranges or private endpoint IDs.
- A network policy lists rules as allowed or blocked.
- You activate it on the account, a user or a security integration. The most specific wins, and blocked beats allowed.
Here is a service user with a key and its own network policy:
CREATE USER etl_svc TYPE = SERVICE
DEFAULT_ROLE = loader DEFAULT_WAREHOUSE = load_wh;
ALTER USER etl_svc SET RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
CREATE NETWORK RULE corp_ips TYPE = IPV4
MODE = INGRESS VALUE_LIST = ('203.0.113.0/24');
CREATE NETWORK POLICY etl_policy
ALLOWED_NETWORK_RULE_LIST = ('corp_ips');
ALTER USER etl_svc SET NETWORK_POLICY = etl_policy;Give service users their own network policy. They need one anyway to use a programmatic access token.
Scenario 7: An auditor asks how your Snowflake account is secured and who can see sensitive data. What do you show?
I show evidence in five layers, straight from the account, not slides.
Here is the whole picture first:
| Layer | What I show the auditor |
|---|---|
| SIGN-IN | MFA or SSO for people, key pairs for services, network policies |
| ACCESS | role hierarchy up to SYSADMIN, managed access, few ACCOUNTADMINs |
| SENSITIVE DATA | classification, tag-based masking, row access policies |
| WHO SAW IT | access history: objects, columns, policies per query |
| POSTURE | Trust Center findings; Business Critical extras if needed |
Now let's go through it in order:
- Sign-in. Every human user on MFA or single sign-on, service users on key pairs or workload identity, and network policies on the account and on service users.
- Access. A documented role hierarchy up to SYSADMIN, managed access schemas, and very few ACCOUNTADMIN users. The grants on each role show who has what.
- Sensitive data. Classification profiles that find and tag sensitive columns, masking policies on those tags, and row access policies for regional data.
- Who saw it. Access history in the account usage schema records each query's reads and writes, down to the columns, and the policies that applied.
- Posture. The Trust Center scans the account against CIS benchmarks and lists findings. And on Business Critical, private connectivity and customer-managed keys with Tri-Secret Secure, if the auditor needs them.
The fourth layer is the one auditors care about most. Here is the query:
SELECT query_start_time, user_name,
direct_objects_accessed, policies_referenced
FROM snowflake.account_usage.access_history
WHERE query_start_time >
DATEADD(day, -30, CURRENT_TIMESTAMP())
ORDER BY query_start_time DESC;Together with the role grants, that answers two questions: who can see salaries, and who actually did.
Bring the role grants, the policy lists, the access history and the Trust Center findings. That is what an auditor actually checks.
Key points to remember
- Privileges to roles, roles to users, custom roles up to SYSADMIN
- Future grants for new tables; managed access centralizes grants
- Masking and row access policies; tags apply them at scale
- Shares: live read access; reader accounts for non-customers
- MFA for people, key pairs for services, network policies
Part 10: Real-World Manager Scenarios
Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.