Privilege Inheritance & Escalation Prevention
Understanding how roles and privileges propagate in Snowflake is critical to building a secure Data Warehouse. Without proper controls, unintended privilege inheritance can lead to a severe security risk known as Privilege Escalation.
What is Privilege Inheritance?
In Snowflake's Role-Based Access Control (RBAC) model, roles can be granted to other roles. This creates a role hierarchy.
When ROLE_A is granted to ROLE_B, ROLE_B inherits all privileges that were granted to ROLE_A. Any user assigned to ROLE_B can automatically access the objects ROLE_A can access.
What is Privilege Escalation?
Privilege Escalation occurs when a user or role gains access to data or operations they are not supposed to have. In Snowflake, this usually happens through poor RBAC design, particularly when powerful roles are granted to lower-level roles, or when users misuse the OWNERSHIP privilege.
Common Escalation Paths
- The OWNERSHIP Grant: The role that owns an object has full control over it, including the right to drop it or grant access to others. If a lower-level role creates an object (and thus owns it) inside a shared schema, they might alter it in ways that bypass data governance.
- The MANAGE GRANTS Privilege: This is a powerful account-level privilege that allows a role to grant or revoke privileges on any object in the account. Assigning this to a non-administrative role is a massive security risk.
Hands-On Demonstration: Setup & Inheritance
Let's look at how inheritance works and how an escalation path might accidentally be created.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
-- Step 1: Use the ACCOUNTADMIN role to set up our training environment
USE ROLE ACCOUNTADMIN;
-- Create our training database and schema
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
USE DATABASE BEEMS_TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS SECURITY_LAB;
USE SCHEMA SECURITY_LAB;
-- Create a table for demonstration
CREATE OR REPLACE TABLE RAW_EMPLOYEE_SALARY (
EMP_ID INT,
EMP_NAME STRING,
SALARY NUMBER(10,2),
DEPARTMENT STRING
);
-- Insert sample data
INSERT INTO RAW_EMPLOYEE_SALARY (EMP_ID, EMP_NAME, SALARY, DEPARTMENT)
VALUES
(101, 'Alice Smith', 95000, 'Engineering'),
(102, 'Bob Jones', 82000, 'Marketing'),
(103, 'Charlie Brown', 110000, 'Sales');
2. Creating a Role Hierarchy
We will create two roles: HR_ANALYST_ROLE (lower level) and HR_MANAGER_ROLE (higher level).
-- Create Roles
CREATE OR REPLACE ROLE HR_ANALYST_ROLE;
CREATE OR REPLACE ROLE HR_MANAGER_ROLE;
-- Grant database and schema access to the lower role
GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE HR_ANALYST_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.SECURITY_LAB TO ROLE HR_ANALYST_ROLE;
-- Grant SELECT on the table to the analyst role
GRANT SELECT ON TABLE BEEMS_TRAINING_DB.SECURITY_LAB.RAW_EMPLOYEE_SALARY TO ROLE HR_ANALYST_ROLE;
-- Create the Hierarchy: Manager inherits from Analyst
GRANT ROLE HR_ANALYST_ROLE TO ROLE HR_MANAGER_ROLE;
-- Grant roles to your current user to test (replace <YOUR_USERNAME> with your actual user)
-- GRANT ROLE HR_ANALYST_ROLE TO USER <YOUR_USERNAME>;
-- GRANT ROLE HR_MANAGER_ROLE TO USER <YOUR_USERNAME>;
What happened here?
We never explicitly granted SELECT on the RAW_EMPLOYEE_SALARY table to the HR_MANAGER_ROLE. However, because HR_MANAGER_ROLE inherits from HR_ANALYST_ROLE, the manager can query the table perfectly fine!
3. Demonstrating Privilege Escalation Risk
Imagine a scenario where the Analyst is allowed to create their own tables in the schema.
-- Grant CREATE TABLE to the analyst
GRANT CREATE TABLE ON SCHEMA BEEMS_TRAINING_DB.SECURITY_LAB TO ROLE HR_ANALYST_ROLE;
USE ROLE HR_ANALYST_ROLE;
-- The analyst creates a new table
CREATE TABLE BEEMS_TRAINING_DB.SECURITY_LAB.SECRET_SIDE_PROJECT (
PROJECT_NAME STRING,
BUDGET NUMBER
);
-- Because the analyst created it, HR_ANALYST_ROLE is the OWNER.
-- The analyst can now grant access to ANYONE they want!
GRANT SELECT ON TABLE BEEMS_TRAINING_DB.SECURITY_LAB.SECRET_SIDE_PROJECT TO ROLE PUBLIC;
This is an escalation risk. A lower-level analyst just granted access to a table to the entire PUBLIC role, bypassing the Security Admin!
Design Patterns to Prevent Escalation
-
Separation of Duties (SECURITYADMIN vs SYSADMIN):
SECURITYADMIN(orUSERADMIN) should be the only roles creating users, roles, and managing grants.SYSADMINshould create and own all databases, schemas, and warehouses.- Never mix these. If
SYSADMINcan manage grants, they could grant themselves access to highly sensitive HR data.
-
Managed Access Schemas:
- Centralize grant management. In a Managed Access Schema, object owners lose the ability to grant access to their objects. Only the schema owner (or a role with
MANAGE GRANTS) can do so. (We will cover this in depth in a later lesson).
- Centralize grant management. In a Managed Access Schema, object owners lose the ability to grant access to their objects. Only the schema owner (or a role with
-
Avoid the MANAGE GRANTS Privilege:
- Only
SECURITYADMINandACCOUNTADMINshould have theMANAGE GRANTSprivilege.
- Only
Auditing Inherited Privileges
To check for escalation paths, you can query Snowflake's Account Usage views to audit who has been granted what roles.
USE ROLE ACCOUNTADMIN;
-- View the hierarchy: Which roles are granted to which roles?
SELECT
GRANTEE_NAME AS ROLE_RECEIVING_GRANT,
ROLE AS ROLE_BEING_GRANTED,
GRANTED_BY,
CREATED_ON
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE GRANTED_ON = 'ROLE'
AND DELETED_ON IS NULL
ORDER BY ROLE_RECEIVING_GRANT;
(Note: Data in ACCOUNT_USAGE views may have a latency of up to 2 hours).
Summary
- Inheritance flows upward in the role hierarchy.
- Escalation occurs when lower-level roles gain administrative capabilities (like
OWNERSHIPover objects in shared schemas). - Always enforce Separation of Duties between
SYSADMIN(infrastructure) andSECURITYADMIN(access control).