🔐 Security Access Control in Snowflake
Snowflake uses a multi-layered security model to control access to data. It combines Role-Based Access Control (RBAC) with object-level privileges and data-level policies for granular security.
✅ Hierarchy and Checks
-
👤 User
- A user must authenticate first (e.g., via username/password, SSO, MFA).
-
🔑 Role (RBAC)
- RBAC (Role-Based Access Control) decides which roles the user has.
- Roles determine object-level privileges (database, schema, table).
-
📦 Object Privileges
-
Example:
GRANT SELECT ON TABLE sales TO ROLE analyst; -
First Check: Does the role have SELECT privilege on the object?
-
-
🧩 Data-Level Policies
-
After object-level permissions are confirmed, Snowflake applies data policies:
-
Row Access Policy → Row-Level Security (RLS)
Ensure/restricts which rows a user can see.
-
Column-Level Security → Column-Level Security (CLS)
Masks or hides sensitive columns.
-
-
✅ Full Evaluation Flow
- First Check → RBAC (Role access to objects)
- Second Check → Object Privileges (e.g., GRANT
SELECTon a table) - Third Check → Row-Level Security (Row Access Policy)
- Fourth Check → Column-Level Security (Masking Policy)
✅ Hierarchy Visual
Example of a Role Hierarchy
SYSADMIN
└── DBADMIN
└── ANALYST
- If
SYSADMINhasUSAGEon a database, so doDBADMINandANALYST. - If
ANALYSThasSELECTon a table,DBADMINandSYSADMINalso have it.
- ✅ Hierarchy: Roles can inherit from other roles, creating role hierarchies that make management more flexible.
✅ Role-Level Access Control (RBAC)
-
What it is: RBAC is a security framework where permissions are granted to roles, not directly to users. Users are assigned to roles → roles have privileges on objects → users inherit those privileges.
-
Hierarchy: Snowflake allows role hierarchy, so a parent role inherits privileges from its child roles.
-
Key Components:
| Component | Description |
|---|---|
| Roles | Logical containers for privileges (like “analyst”, “engineer”). |
| Users | Individuals (or service accounts) that connect to Snowflake. |
| Privileges | Specific actions (like SELECT, INSERT, USAGE). |
| Objects | Databases, schemas, tables, warehouses, etc. |
-
Why it matters:
- Centralized and scalable security.
- Easier management (you manage roles, not users).
- Supports least privilege principle.
🎯 Simply: RBAC is a security model that uses roles to control access to resources. Instead of assigning privileges directly to users, you assign privileges to roles, and assign roles to users.
- Enforce least privilege by giving users only the permissions they need.
- Simplify privilege management—roles can be assigned and adjusted without touching individual users.
- Enable scalability and consistency in managing access.
✅ Snowflake System-Defined Roles & Hierarchy
🔹 Role Details
| Role | Level | Responsibilities |
|---|---|---|
| ORGADMIN | Organization-wide | Manage accounts in the organization. Create/drop accounts. |
| ACCOUNTADMIN | Account-wide | Full account control: databases, warehouses, roles, policies. |
| SECURITYADMIN | Security & roles | Manage roles, grants, network policies, and security objects. |
| USERADMIN | User management | Create, manage users and assign roles. |
| SYSADMIN | Object management | Create/manage databases, schemas, tables, masking policies. |
| PUBLIC | Base role | Minimal access. Assigned to all users by default. |
🛠️ Setting Up RBAC in Snowflake with Database & Table Operations
1️⃣ Create Roles
CREATE ROLE analyst;
CREATE ROLE data_engineer;
CREATE ROLE dbadmin;
2️⃣ Create Users and Assign Roles
CREATE USER Ranjith PASSWORD = 'MyPass123!';
CREATE USER Bala PASSWORD = 'SecurePass456!';
CREATE USER Saran PASSWORD = 'SecurePass456!';
GRANT ROLE analyst TO USER Ranjith;
GRANT ROLE data_engineer TO USER Bala;
GRANT ROLE dbadmin TO USER Saran;
3️⃣ Create Database and Schema, Set Context
USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS SFTRAINING_EMP_DB;
CREATE SCHEMA IF NOT EXISTS SFTRAINING_EMP_DB.EMP_HR;
USE DATABASE SFTRAINING_EMP_DB;
USE SCHEMA EMP_HR;
GRANT USAGE ON SCHEMA EMP_HR to USER Ranjith;
GRANT USAGE ON SCHEMA EMP_HR to USER Bala;
GRANT USAGE ON SCHEMA EMP_HR to USER Saran;
GRANT USAGE ON WAREHOUSE COMPUTE_WH to USER Ranjith;
GRANT USAGE ON WAREHOUSE COMPUTE_WH to USER Bala;
GRANT USAGE ON WAREHOUSE COMPUTE_WH to USER Saran;
4️⃣ Create a Sample Table (by DB Admin or Data Engineer)
CREATE OR REPLACE TABLE employees (
emp_id INT AUTOINCREMENT,
emp_name STRING,
department STRING,
salary NUMBER(10,2),
hire_date DATE,
PRIMARY KEY(emp_id)
);
5️⃣ Grant Privileges to Roles
-- Allow data engineers to insert data
GRANT INSERT, SELECT ON TABLE employees TO ROLE data_engineer;
-- Allow analysts to select data only
GRANT SELECT ON TABLE employees TO ROLE analyst;
-- Allow DB Admins to modify the table structure
GRANT ALTER, DROP, TRUNCATE ON TABLE employees TO ROLE dbadmin;
6️⃣ Set Default Roles for Users ( Optional )
ALTER USER Bala SET DEFAULT_ROLE = data_engineer;
ALTER USER Ranjith SET DEFAULT_ROLE = analyst;
ALTER USER Saran SET DEFAULT_ROLE = dbadmin;
7️⃣ Insert Sample Records (Data Engineer)
USE ROLE data_engineer;
USE DATABASE SFTRAINING_EMP_DB;
USE SCHEMA EMP_HR;
INSERT INTO employees (emp_name, department, salary, hire_date) VALUES
('John Doe', 'IT', 75000, '2020-05-12'),
('Jane Smith', 'Finance', 85000, '2019-03-18'),
('Mark Wilson', 'HR', 60000, '2021-07-01');
8️⃣ Alter Table Structure (DB Admin)
USE ROLE dbadmin;
USE DATABASE SFTRAINING_EMP_DB;
USE SCHEMA EMP_HR;
ALTER TABLE employees ADD COLUMN email STRING;
🖼️ RBAC Architecture Diagram
- ✅ 9️⃣ Privilege Revocation Example
-- Revoke INSERT privilege from data engineers
REVOKE INSERT ON TABLE EMPLOYEE_DETAILS FROM ROLE data_engineer;
-- Revoke SELECT privilege from analysts
REVOKE SELECT ON TABLE EMPLOYEE_DETAILS FROM ROLE analyst;
✅ Example Flow
analyst_rolehas SELECT on TABLE employees → Object-Level.- A Row Access Policy restricts rows by department → Row-Level.
- A Masking Policy hides
SSNcolumn for that role → Column-Level.
So even if analyst_role can SELECT the table, policies further filter what they see.
🤔 Validate or DIY ?
- ✅ Roles can inherit from other roles, creating role hierarchies 🔗 that make management more flexible and scalable!
🌟 Best Practices for RBAC
- ✅ Use role hierarchies to reduce complexity.
- ✅ Avoid direct grants to users—roles only!
- ✅ Define clear role names and descriptions.
- ✅ Implement least privilege: start small and grow as needed.
- ✅ Regularly review grants in system views (
SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES). - ✅ Combine with masking policies and row access policies for data-level security.
⚡ Key Takeaways
- ✔️ RBAC in Snowflake centralizes and simplifies access control.
- ✔️ Use roles to grant privileges and assign roles to users—no direct grants!
- ✔️ Combine RBAC with masking and classification for full security.