Skip to main content

SOLE Access Management

Access to Snowflake objects is an integral part of the DataOps ecosystem. DataOps pipelines cannot (and will not) run successfully without being able to read from and write to Snowflake objects. Therefore, it is imperative that unauthorized users do not gain access to an organization's data for multiple reasons, including:

  • To prevent data loss through theft
  • To prevent data from being held hostage as part of ransomware attacks
  • To maintain confidentiality
  • To protect data integrity

How does DataOps manage access to the organization's Snowflake objects?

The straightforward answer is via the Snowflake Object Lifecycle Engine or SOLE.

SOLE's access management function is designed to control access to all Snowflake objects. In other words, SOLE manages the rules around access control by implementing Snowflake's access control rules.

What is access control?

The Snowflake Access Control webpage has the following to say by way of answering this question:

Access control privileges determine who can access and perform operations on specific objects in Snowflake.

This access control model image describes the SOLE access control model:

snowflake-access-control-model __shadow__

Source: https://docs.snowflake.com/en/user-guide/security-access-control-overview.html

SOLE Role Hierarchy

At the same time, it is essential to understand the Snowflake role hierarchy implemented by SOLE.

To recap, the Snowflake describes roles as follows:

Roles are the entities to which privileges on securable objects can be granted and revoked. Roles are assigned to users to allow them to perform actions required for business functions.

You can also grant roles to other roles; thereby, creating a hierarchy of roles, as the following image shows:

snowflake-role-hierarchy __shadow__

Source: https://docs.snowflake.com/en/user-guide/security-access-control-overview.html

The ACCOUNTADMIN, SECURITYADMIN, SYSADMIN, USERADMIN, and PUBLIC roles are Snowflake system-defined roles. These roles inherit from each other.

For instance, The ACCOUNTADMIN role inherits all the privileges from the SECURITYADMIN and USERADMIN roles. Another way to describe this functionality is that the ACCOUNTADMIN role can create the SECURITYADMIN and USERADMIN roles and grant them the privileges they need to function. The SECURITYADMIN role also inherits all the USERADMIN privileges because it is higher than USERADMIN in the role hierarchy.

The last point to note when looking at this image is that the system-defined role SYSADMIN is responsible for creating several custom roles and that several of these roles inherit from each other.

How does SOLE Manage Access to Snowflake Objects?

Users can define privilege grants to roles in the Snowflake object configuration. Based on these configurations, SOLE generates all Snowflake object grants and revokes. SOLE follows a declarative method for defining Snowflake objects, so it looks at the configuration and updates the objects to match the configuration. Therefore, only the specified privileges in the configuration are granted to their related roles.

In the same way, revokes are added implicitly for all privileges not specified in the configuration. For instance, if SOLE finds a privilege not in the configuration, it will create a revoke to remove this privilege.

tip

The implicit revoke only initiates when the keyword grants is used in the object configuration. If this keyword is not specified, the grants and revokes of privileges are not managed.

The example below shows how the MODIFY privilege to role ROLE_1 is specified in the grants section in the DATABASE_1 configuration. Because it is in the configuration, SOLE will manage this MODIFY grant. And as no grants section is defined for database_2, SOLE will not manage any grants for this object.

dataops/snowflake/database.yml
DATABASE_1:
comment: DATABASE 1
grants:
MODIFY:
- ROLE_1
DATABASE_2:
comment: DATABASE 2

Based on this configuration, SOLE will generate revokes on privileges MONITOR, USAGE, CREATE SCHEMA, IMPORTED PRIVILEGES for the database DATABASE_1. Also, as no grants section is defined in the configuration of DATABASE_2, no revokes for this database are performed.

Revoking all Privileges

In order to manage revokes on all the privileges of a Snowflake Object, an empty grants must be specified in the Snowflake object's configuration, as the following example shows:

dataops/snowflake/database.yml
DATABASE_3:
comment: DATABASE 3
grants:

In this scenario, SOLE will generate revokes for all the privileges (MONITOR, USAGE, CREATE SCHEMA, IMPORTED PRIVILEGES, and MODIFY) on DATABASE_3.

Implicit Grant of USAGE Privilege on Parent Objects

If a privilege is granted to a role on a database object such as a schema, table, or view, but USAGE is not granted on its parent objects, the role will not be able to access this database object. USAGE must be granted on the parent object for the role to access its child object.

However, to reduce the number of configurations that must be managed, SOLE can implicitly grant USAGE on the parent (and grandparent) objects for such roles.

The following example demonstrates this principle:

  • SELECT is granted to roles ROLE_1, ROLE_2 and SYSADMIN, and INSERT is granted to ROLE_2 on the table TABLE_1,
  • As USAGE is not granted to any of the roles in both the schema SCHEMA_1 and the database DATABASE_5, these roles are not able to access the table TABLE_1.

However, because SOLE implicitly grants USAGE to ROLE_1, ROLE_2 and SYSADMIN on both SCHEMA_1 and DATABASE_5, these roles can access TABLE_1.

dataops/snowflake/database.yml
DATABASE_5:
comment: Database 5
grants:
MODIFY:
- ROLE_1
MONITOR:
- ROLE_1
- ROLE_2
schemas:
SCHEMA_1:
comment: Hello
grants:
MODIFY:
- ROLE_1
tables:
TABLE_1:
grants:
SELECT:
- ROLE_1
- ROLE_2
- SYSADMIN
INSERT:
- ROLE_2
comment: Hello Table_1
COLUMNS:
COLUMN_1:
type: VARCHAR(16777216)

SOLE Grant Management

Snowflake Object Lifecycle Engine's Grant Management provides a complete alternative to the Terraform Snowflake Provider for inspect, compare, and execute operations. SOLE Grant Management is enabled by default. Set the variable DONT_USE_SOLE_GRANT_MANAGEMENT to disable this and revert to using the Terraform provider to manage all object grants.

SOLE Grant Management inspects the existing grants that are part of the organization's Snowflake infrastructure during SOLE's PLAN lifecycle action. It compares the grants defined in the configuration with what's in Snowflake and determines what privilege and role grants must be added or revoked. Because this inspection is a run-time inspection, unlike the Terraform provider, no local state is required.

caution

If you set DONT_USE_SOLE_GRANT_MANAGEMENT to any value, the following two sections do not apply.

DATAOPS_SOLE_WAREHOUSE

SOLE Grant Management requires a Snowflake warehouse to perform object inspection. This warehouse must be assigned to the user used to connect to Snowflake and can either be specified in the variable DATAOPS_SOLE_WAREHOUSE or can be saved in the DataOps Vault path SNOWFLAKE.SOLE.WAREHOUSE.

If no warehouse is defined, SOLE Grant Management assumes that a default warehouse is set for the user. You can run the following query in Snowflake to set a default warehouse for the user:

ALTER USER <SOLE_USER> SET DEFAULT_WAREHOUSE = <WAREHOUSE_NAME>

Limiting the Roles and Users operated on

By default, SOLE Grant Management will apply and revoke privileges on all environment (dev, QA, and prod) roles as per the YAML configuration. Apart from this functionality, it can also revoke all privileges.

And, for some use cases, you might want to limit SOLE Grant Management's scope to a well-known set of roles and users. These roles and users can be defined using the top-level keys specified_roles and specified_users, respectively. If present in the configuration, SOLE Grant Management only manages the roles and users that match the specified values. Privileges for roles and users that do not match the patterns will not be altered. In other words, they are expressly not revoked but also not granted to an object.

tip

You can use the role names, user names, or regular expressions in the specified_roles and specified_users keys.

For instance:

specified_roles:
- DATAOPS_ADMIN
- DATAOPS_*

specified_users:
- SOLEADMIN
- DATAOPS_*

With the above configuration, SOLE Grant Management will only grant or revoke privileges if the role is either DATAOPS_ADMIN or matches the DATAOPS_* regex value. Similarly, a role will only be granted or revoked if the role is either DATAOPS_ADMIN or matches regex DATAOPS_*, or the user is either SOLEADMIN or matches regex DATAOPS_*.