Automate Least Privilege in Snowflake

security
snowflake

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:

CREATE ROLE ON ACCOUNT

MANAGE GRANTS ON ACCOUNT

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.

Limitations

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.

Notes

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.

Designing a safer authorization integration

Integrating with Snowflake

Building custom privileges

Limitations

Notes

Provide privileged access in under 5 minutes