Automate Least Privilege in Snowflake

Nathan BrahmsNathan Brahms -
  • security
  • snowflake

AI summary
P0 automates least-privilege access for customers by integrating with authorization controls within customer systems. To prevent privilege escalation, P0 integrates with Snowflake using stored procedures to simulate custom privileges, granting only minimum privileges to the integration account.
SEO updates
Nathan Brahms
Aug 23, 2023 08:57 PM
Our product helps cloud security engineers control entitlements for their developers.
May 16, 2023
At P0, we automate least-privilege access for our customers. Automation eliminates identity over-provisioning, while simultaneously removing the toil and operational overhead of manual entitlement management. In this blog post, we’ll dive a bit into how we achieve this automation. And, most importantly, how we can automate least-privilege access securely, without exposing our customers to additional risk.
P0’s automation works by integrating directly with the authorization controls within customer systems. This allows us to tailor each identity’s (or identity group’s) entitlements, containing exactly the permissions they need — and nothing more.
The P0 integrations we offer are powerful, as they give our customers the ability to control access to their systems. However, if an attacker were to gain access, the consequences would be severe. While we prioritize our own internal security, it is impossible to have a perfect security system. Therefore, we must also focus on creating systems that are secure by design.
What if there was a way to limit the power of these integrations, such that an attacker would profit little by gaining integration access? “Least privilege” for least-privilege controls, if you will. In this post you will learn how we build such an integration at P0.

Designing a safer authorization integration

Let’s think through the privileges the P0 integration requires within a system based on role-based authorization controls. Our design must give the integration some minimum privileges:
  • The ability to create roles
  • The ability to assign privileges to roles
  • The ability to grant roles to identities
  • The ability to revoke roles from identities
  • The ability to delete roles
To prevent privilege escalation of the P0 integration account, this integration must also be subject to some constraints:
  • It must not be able to grant roles to its own account
  • It must not be able to assign privileges to its own role(s)
  • It must never be able to add new identities to the system
  • It must never be able to access the system’s data (although it may have access to metadata such as the users, roles, and resources in the system)
For additional safety, we add two more restrictions:
  • The assignable privileges should come from a restricted subset of the system privileges, defined via an allow list
  • Only roles created by this integration may be deleted
Now, we are faced with a new challenge: how can these requirements be implemented in each target customer system? For policy-based systems, such as cloud providers, these restrictions can generally be implemented using some combination of conditional access management and permission boundaries.
But what about systems where privileges are more coarse-grained, and static — say a system using role-based access controls? In the remainder of this post, you will learn how you can use automation within the system to effectively simulate custom privileges, using Snowflake as an example.

Integrating with Snowflake

Data warehouses are some of the most sensitive systems used by our customers. They aggregate many of the most sensitive data held by an organization: application data, those organizations’ own users’ private data, and operational and sales records. Moreover, these warehouses, by their nature, break down the data segmentation that can limit the blast radius of exploits. Therefore, ensuring proper least-privilege access to data warehouses is one of the most effective methods to improve an organization’s security posture. At the same time, organizations must approach integrations into their data warehouses with additional caution.
Now let’s turn our gaze to Snowflake. Snowflake has a novel authorization architecture, combining elements of discretionary access control (DAC) with role-based access control (RBAC). Moreover, Snowflake allows roles within its RBAC model to nest, forming role hierarchies. For the P0 integration we’re building we’ll interact only with Snowflake’s RBAC, and may treat the role hierarchy as completely flat. For the rest of this article we can treat Snowflake as having a traditional RBAC system. (See note 1 at the end for more details).
Within Snowflake’s RBAC, two privileges are necessary to allow our P0 integration to manage access:
Unfortunately, these two privileges are far too broad for our P0 integration to prevent privilege escalation. With these two privileges, it is trivial to grant the integration account read or edit access to all data within the warehouse.
We’re now in a quandary: we need broad privileges for our P0 integration to perform its function — but holding these privileges presents an unacceptable security risk. How are we to solve this dilemma?
The answer is to use automation that runs inside the resource. For Snowflake we make use of stored procedures. These procedures execute with the permissions of the role that created them. The customer can use their administrator privileges to create these stored procedures, then only give our P0 integration privileges to call these procedures.

Building custom privileges

For each action we want our P0 integration to be able to take, we ask our customer to create a stored procedure within their Snowflake instance, then grant our integration privilege to call said procedure.
To begin with, let’s create a role and user for the integration:
USE ROLE ACCOUNTADMIN; CREATE WAREHOUSE integration_wh WITH warehouse_size = xsmall; CREATE DATABASE integration; CREATE SCHEMA custom; USE SCHEMA integration.custom; CREATE ROLE integration_role; CREATE USER integration_user default_role = integration_role rsa_public_key = ... ; GRANT ROLE integration_role TO USER integration_user; GRANT USAGE ON WAREHOUSE integration_wh TO USER integration_user; GRANT USAGE ON DATABASE integration TO USER integration_user; GRANT USAGE ON SCHEMA integration.custom TO USER integration_user;
We’ll start with a procedure that grants or revokes Snowflake user access to or from a role. We’ll use procedures written in JavaScript, for readability:
USE ROLE ACCOUNTADMIN; -- The procedure can execute anything that its creating role can CREATE PROCEDURE alter_user_roles(user text, role text, action text) RETURNS text LANGUAGE javascript AS $$ var USER_DENY_LIST = ["integration_user"]; var ROLE_DENY_LIST = ["accountadmin", "securityadmin", "sysadmin"]; try { // Prevent privilege escalation by filtering out the integration from grants if (USER_DENY_LIST.includes(USER.toLowerCase())) { return "user appears in deny list"; } // Apply a deny-list of roles if (ROLE_DENY_LIST.includes(ROLE.toLowerCase())) { return "role appears in deny list"; } // Prevent SQL injection by matching actions against an allow-list if (ACTION !== "GRANT" && ACTION !== "REVOKE") { return "can only grant or revoke roles"; } var preposition = ACTION === "GRANT" ? "TO" : "FROM" snowflake.execute({sqlText: "GRANT " + ACTION + " IDENTIFIER(?) " + preposition + " USER IDENTIFIER(?)", binds: [ROLE, USER]}); return NULL; } catch (err) { return err.message; } $$ -- But the integration user can _only_ execute this procedure GRANT USAGE ON PROCEDURE integration.custom.alter_user_roles(text, text, text) TO integration_user;
At this point, the P0 integration account can grant roles to user simply by executing:
CALL integration.custom.alter_user_roles('USERNAME', 'ROLENAME', 'GRANT');
but the integration has no ability to directly grant roles (viz., using GRANT ROLE … TO USER …).
We’ve managed to partially implement the requirements of the P0 integration (the ability to grant and revoke roles), without granting the P0 integration excessively broad privileges. We can follow this pattern to also create and delete roles, and to assign privileges to roles.
Fleshing out these additional custom privileges is a matter of writing additional stored procedures.


Simulating custom privileges presents two main limitations:
Deployment. Because this solution relies on automation deployed within the customer environment, we must rely on our customers to execute SQL in their own environment. To do this, we present customers with a pre-written SQL file that they can merely copy-and-paste into their environment. We also make the SQL statement idempotent (viz., using CREATE OR REPLACE statements) so that the SQL can be run multiple times without consequence. Versioning can also be added to the schema name, in order to support integration compatibility as the customer SQL is updated.
Scaling. In this solution, every single custom privilege requires its own hand-written custom procedure. This means that this solution is appropriate when the number of needed privileges is small and relatively static.


Note 1. We explicitly do not gain access using DAC, as doing so would give us access to the customer’s underlying data — access that we do not want. Moreover, we assign an independent role to govern the integration account’s privileges, and never create nor delete nested roles, thus allowing us to treat the Snowflake role hierarchy as flat.

Provide privileged access in under 5 minutes

No credit card needed.