Skip to main content

🔐 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​

  1. 👤 User

    • A user must authenticate first (e.g., via username/password, SSO, MFA).
  2. 🔑 Role (RBAC)

    • RBAC (Role-Based Access Control) decides which roles the user has.
    • Roles determine object-level privileges (database, schema, table).
  3. 📦 Object Privileges

    • Example:

      GRANT SELECT ON TABLE sales TO ROLE analyst;
    • First Check: Does the role have SELECT privilege on the object?

  4. 🧩 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 SELECT on 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 SYSADMIN has USAGE on a database, so do DBADMIN and ANALYST.
  • If ANALYST has SELECT on a table, DBADMIN and SYSADMIN also 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:

ComponentDescription
RolesLogical containers for privileges (like “analyst”, “engineer”).
UsersIndividuals (or service accounts) that connect to Snowflake.
PrivilegesSpecific actions (like SELECT, INSERT, USAGE).
ObjectsDatabases, 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​

RoleLevelResponsibilities
ORGADMINOrganization-wideManage accounts in the organization. Create/drop accounts.
ACCOUNTADMINAccount-wideFull account control: databases, warehouses, roles, policies.
SECURITYADMINSecurity & rolesManage roles, grants, network policies, and security objects.
USERADMINUser managementCreate, manage users and assign roles.
SYSADMINObject managementCreate/manage databases, schemas, tables, masking policies.
PUBLICBase roleMinimal 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​

  1. analyst_role has SELECT on TABLE employees → Object-Level.
  2. A Row Access Policy restricts rows by department → Row-Level.
  3. A Masking Policy hides SSN column 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.