Skip to main content

Grants

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 (SOLE).

We've designed SOLE's access management 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 to answer 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

Role hierarchy​

It is essential to understand the Snowflake role hierarchy implemented by SOLE.

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.

Grants​

You can grant one or more access privileges on objects to roles in the Snowflake object configuration files. Based on these configurations, SOLE generates all Snowflake object grants and revokes. As SOLE follows a declarative method for defining Snowflake objects, it checks the configuration and updates objects accordingly.

When your pipeline runs, SOLE applies the grants you define in the configuration and removes any other existing grants, no matter where they are defined, and regardless of the value of manage mode. Therefore, only the access privileges you specify in the configuration are granted to their related roles.

When your pipeline runs:

  • SOLE applies the grants you define in the configuration and removes any other existing grants, no matter where they are defined, and regardless of the value of manage mode. Only the access privileges you specify in the configuration are granted to their related roles.

  • SOLE creates a revoke to remove all privileges not specified in the configuration file or those defined with an empty list [].

tip

The implicit revoke is initiated if you use the keyword grants in the object configuration. If you want to define access to Snowflake objects outside SOLE, for example, don't define the keyword grants in any configuration and at any level. When no grants are specified anywhere, SOLE manages only the object properties.

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
databases:
  DATABASE_1:
    comment: "database 1"
    grants:
      MODIFY:
        - ROLE_1
  DATABASE_2:
    comment: "database 2"
  DATABASE_3:
    comment: "database 3"
    grants:
      MODIFY: []

Also, based on this configuration:

  • SOLE generates revokes on the privileges MONITOR, USAGE, CREATE SCHEMA, and IMPORTED PRIVILEGES for the database DATABASE_1.
  • SOLE does not perform any revokes for DATABASE_2 as no grants section is defined in its configuration.
  • SOLE generates revokes on the privilege MODIFY for DATABASE_3 as it is defined with an empty list. SOLE will also create revokes for the database's other missing privileges (MONITOR, USAGE, CREATE SCHEMA, and IMPORTED PRIVILEGES).

See Supported database grants to roles for the list of supported grants.

Enabling roles to grant privileges to other roles​

It is possible to allow specific roles to grant the same privileges to other roles by setting with_grant_option to true, which is set to false by default.

In the following example:

  • ROLE_1 can grant USAGE to other roles as with_grant_option is set to true.
  • ROLE_2, ROLE_3, and ROLE_4 cannot grant USAGE to other roles as with_grant_option is set to false explicitly or by default.
  • ROLE_5 can grant the privileges MODIFY, MONITOR, USAGE, CREATE SCHEMA, and IMPORTED PRIVILEGES to other roles as with_grant_option is set to true.
dataops/snowflake/database.yml
databases:
  DATABASE_3:
    comment: DATABASE 3
    grants:
      USAGE:
        - ROLE_1:
            with_grant_option: true # the role can grant the privilege to other roles
        - ROLE_2:
            with_grant_option: false # the role can not grant the privilege to other roles
        - ROLE_3: # the role can not grant the privilege to other roles
        - ROLE_4: # the role can not grant the privilege to other roles
      ALL PRIVILEGES:
        - ROLE_5: # the role can grant all the privileges to other roles
            with_grant_option: true

Revoking all privileges​

To manage revokes on all the privileges of a Snowflake object, use an empty grants in the Snowflake object's configuration, as the following example shows:

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

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

Revoking privileges on objects shared in multiple environments​

If a managed resource is shared between environments (no environment-specific suffix added, see namespacing), then grants on the object would be reset in each pipeline as per the environment.

roles:
  ROLE_1:
    namespacing: suffix
    comment: Role 1
warehouses:
  WAREHOUSE_1:
    comment: Warehouse 1
    size: MEDIUM
    namespacing: prefix
    grants:
      MONITOR:
        - ROLE_1

In the above example, one role and one warehouse are managed by SOLE. The role ROLE_1 is created in each environment, whereas the warehouse WAREHOUSE_1 is created once and shared between environments.

If the pipeline runs in the PROD environment, then MONITOR on WAREHOUSE_1 would be granted to ROLE_1_PROD. When the same pipeline runs in the QA environment, the grant to ROLE_1_PROD is revoked and MONITOR is granted to ROLE_1_QA.

Implicit grant of USAGE privilege on parent objects​

If you grant a privilege 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 can't access this database object. You must manually grant USAGE on the parent object for the role to access its child object.

To reduce the number of configurations you must manage, SOLE implicitly grants USAGE on the parent, and grandparent, objects for such roles.

The following example shows this principle:

  • SELECT is granted to roles ROLE_1, ROLE_2 and SYSADMIN, and INSERT is granted to ROLE_2 on TABLE_1,
  • As USAGE is not granted to any of the roles in the schema SCHEMA_1 and the database DATABASE_5, these roles cannot access 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
databases:
  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)

Preventing implicit grant of USAGE​

To prevent SOLE from implicitly granting USAGE privilege on parent objects, set the DATAOPS_SOLE_DISABLE_IMPLICIT_GRANTS variable to any value. The variable is set to false by default.

Handling ALL PRIVILEGES privilege in SOLE​

By assigning specific privileges to roles within SOLE, you control who can access and perform operations on objects in Snowflake. However, it's important to note that managing the ALL PRIVILEGES privilege in SOLE varies from its handling in Snowflake. When you grant ALL PRIVILEGES to a role on a Snowflake object, such as a database, function, masking policy, etc., SOLE does not grant the supported privileges for the specified object all at once as it is the case in Snowflake.

SOLE converts ALL PRIVILEGES into individual privilege grants before resolving them in Snowflake. This approach enhances effective management of revokes and tracking of granted privileges to roles. The privilege conversion before execution means that the privileges applied by Snowflake and those applied by SOLE may differ depending on the privileges known to SOLE at the time through the configuration files.

SOLE grant management​

SOLE grant management provides a complete alternative to the Terraform Snowflake Provider for inspecting, comparing, and executing operations. SOLE grant management is enabled by default, and it:

  • Inspects the existing grants that are part of the organization's Snowflake infrastructure during SOLE's PLAN lifecycle action.
  • Compares the grants defined in the configuration file 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.

However, if you want to deactivate SOLE grant management and revert to using the Terraform provider to manage all object grants, set the variable DONT_USE_SOLE_GRANT_MANAGEMENT to any value. This is evaluated as true and results in Terraform provider managing object grants.

warning

The following two sections only apply if DONT_USE_SOLE_GRANT_MANAGEMENT is either:

  • Not set - this variable is not set by default, which indicates SOLE grant management is enabled
  • Set to 0 - evaluated as false, which indicates SOLE grant management is enabled

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 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 applies and revokes privileges on all environment (DEV, QA, and PROD) roles as per the YAML configuration. Apart from this functionality, it can also revoke all privileges.

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 roles, 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 is only granted or revoked if the role is either DATAOPS_ADMIN or matches regex DATAOPS_*, or the user is either SOLEADMIN or matches regex DATAOPS_*.

Last updated on