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:
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:
PUBLIC roles are Snowflake system-defined roles. These roles inherit from each other.
For instance, The ACCOUNTADMIN role inherits all the privileges from the
USERADMIN roles. Another way to describe this functionality is that the ACCOUNTADMIN role can create the
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.
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.
comment: DATABASE 1
comment: DATABASE 2
Based on this configuration, SOLE will generate revokes on privileges
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:
comment: DATABASE 3
In this scenario, SOLE will generate revokes for all the privileges (
IMPORTED PRIVILEGES, and
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:
SELECTis granted to roles
SYSADMIN, and INSERT is granted to
ROLE_2on the table
USAGEis not granted to any of the roles in both the schema
SCHEMA_1and the database
DATABASE_5, these roles are not able to access the table
However, because SOLE implicitly grants
SYSADMIN on both
DATABASE_5, these roles can access
comment: Database 5
comment: Hello Table_1
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.
If you set
DONT_USE_SOLE_GRANT_MANAGEMENT to any value, the following two sections do not apply.
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
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_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.
You can use the role names, user names, or regular expressions in the
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