Security and Governance

Overview#

One of the major areas that the DataOps Modelling and Transformation Engine (MATE) extends on top of dbt is the application of GRANTS, TAGS, ROW ACCESS POLICIES, and DYNAMIC MASKING POLICIES.

All of these are achieved via simple additions to the yml file associated with the sql file defining the model. All the remaining examples on this page will use an example model:

con_cust_lifetime_value.sql

{{ config(alias='CUSTOMER_LIFETIME_VALUE'        ) }}
SELECT  CUSTOMERID AS "Customer ID",  Name AS "Customer Name",  DATEDIFF(min,MAX(ORDERDATE),CURRENT_DATE()) AS "Mins since last order",  MIN(TOTALDUE) AS "Minimum Order Size",  SUM(TOTALDUE) AS "Total lifetime value",  AVG(TOTALDUE) AS "Average order size",  MAX(TOTALDUE) AS "Maximum Order Size"FROM {{ref('cal_fact_salesorder')}}GROUP BY 1,2

Roles and Grants#

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html

Under the meta key, a grants key is created, with the different possible grant privileges and then a list of the roles to which these apply e.g.

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value    meta:      grants:        select:          - DATAOPS_READER          - DATAOPS_WRITER          - DATAOPS_ADMIN        insert:          - DATAOPS_WRITER          - DATAOPS_ADMIN        update:          - DATAOPS_WRITER          - DATAOPS_ADMIN        delete:          - DATAOPS_ADMIN

all privileges can also be used e.g.

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value    meta:      grants:        all privileges:          - DATAOPS_READER          - DATAOPS_WRITER          - DATAOPS_ADMIN

In a MATE job the output of the above would look similar to:

07:14:59 | 1 of 1 START table model customer_consumption.CUSTOMER_LIFETIME_VALUE [RUN]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_READER [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_READER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_READER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_READER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_READER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_READER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges select granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges insert granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges insert granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges insert granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges insert granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges update granted to role  DATAOPS_WRITER [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges update granted to role  DATAOPS_WRITER [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges update granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges update granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption privileges usage granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges delete granted to role  DATAOPS_ADMIN [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE privileges delete granted to role  DATAOPS_ADMIN [ SUCCESS 1 ]07:15:16 | 1 of 1 OK created table model customer_consumption.CUSTOMER_LIFETIME_VALUE [SUCCESS 1 in 16.44s]

Model Level TAGS#

https://docs.snowflake.com/en/user-guide/object-tagging.html

Snowflake TAGs (which should be created using the DataOps Snowflake Object Lifecycle Engine (SOLE)) can be set at the model (assuming these are materialised as tables, incremental tables or views) level e.g.

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value    meta:      tags:        DATAOPS_META.DATAOPS_COST_CENTRE: 'Development'        DATAOPS_META.DATAOPS_PIPELINE_ID: "{{ env_var('CI_PIPELINE_ID') }}"        DATAOPS_META.DATAOPS_JOB_ID: "{{ env_var('CI_JOB_NAME') }}"        DATAOPS_META.ANALYST_PII: True        DATAOPS_META.NON_FINANCE: True        DATAOPS_META.OPEN_DEV_ACCESS: True

These TAGs can then be used with a variety of tools that read Snowflake TAGs.

In a MATE job the output of the above would look similar to:

07:14:59 | 1 of 1 START table model customer_consumption.CUSTOMER_LIFETIME_VALUE [RUN]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_COST_CENTRE set to Development [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_COST_CENTRE set to Development [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_PIPELINE_ID set to 12345 [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_PIPELINE_ID set to 12345 [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_JOB_ID set to manual [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.DATAOPS_JOB_ID set to manual [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.ANALYST_PII set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.ANALYST_PII set to True [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.NON_FINANCE set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.NON_FINANCE set to True [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.OPEN_DEV_ACCESS set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE tag DATAOPS_DEMO5_DEV.DATAOPS_META.OPEN_DEV_ACCESS set to True [ SUCCESS 1 ]07:15:16 | 1 of 1 OK created table model customer_consumption.CUSTOMER_LIFETIME_VALUE [SUCCESS 1 in 16.44s]

Column Level TAGS#

https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html

Snowflake TAGs (which should be created using the DataOps Snowflake Object Lifecycle Engine (SOLE)) can also be set as the column level of a model (assuming these are materialised as tables, incremental tables or views) e.g.

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value    columns:        - name: "\"Customer ID\""        - name: "\"Customer Name\""          meta:            tags:              DATAOPS_META.pii_name: True              DATAOPS_META.ANALYST_PII: False              DATAOPS_META.NON_FINANCE: 7              DATAOPS_META.OPEN_DEV_ACCESS: "foo"

These TAGs can then be used with a variety of tools that read Snowflake TAGs.

In a MATE job the output of the above would look similar to:

07:14:59 | 1 of 1 START table model customer_consumption.CUSTOMER_LIFETIME_VALUE [RUN]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" set masking policy DATAOPS_DEMO5_DEV."PUBLIC"."MASK_WITH_SHA2" [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" set masking policy DATAOPS_DEMO5_DEV."PUBLIC"."MASK_WITH_SHA2" [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.ANALYST_PII set to False [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.ANALYST_PII set to False [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.NON_FINANCE set to 7 [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.NON_FINANCE set to 7 [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.OPEN_DEV_ACCESS set to foo [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" tag DATAOPS_DEMO5_DEV.DATAOPS_META.OPEN_DEV_ACCESS set to foo [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Total lifetime value" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Total lifetime value" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ SUCCESS 1 ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Mins since last order" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Mins since last order" tag DATAOPS_DEMO5_DEV.DATAOPS_META.pii_name set to True [ SUCCESS 1 ]07:15:16 | 1 of 1 OK created table model customer_consumption.CUSTOMER_LIFETIME_VALUE [SUCCESS 1 in 16.44s]

Row Access Policies#

https://docs.snowflake.com/en/user-guide/security-row-intro.html

Snowflake Row Access Policies (which should be created using the DataOps Snowflake Object Lifecycle Engine (SOLE)) can be set on a model (assuming these are materialised as tables, incremental tables or views) e.g.

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value    meta:      row_access_policies:        '"ROW_ACCESS".random_chance':          - '"Customer ID"'

The Row Access Policy needs to be specified by both Schema and Name (but not Database since this is determined by the DataOps pipeline based on the branch/environment). The column name to which this Row Access Policy applies also needs to be specified.

be aware

As per current Snowflake design, only one Row Access Policy can be set per model.

In a MATE job the output of the above would look similar to:

07:14:59 | 1 of 1 START table model customer_consumption.CUSTOMER_LIFETIME_VALUE [RUN]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE add row access policy DATAOPS_DEMO5_DEV."ROW_ACCESS".random_chance on column "Customer ID" [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE add row access policy DATAOPS_DEMO5_DEV."ROW_ACCESS".random_chance on column "Customer ID" [ SUCCESS 1 ]07:15:16 | 1 of 1 OK created table model customer_consumption.CUSTOMER_LIFETIME_VALUE [SUCCESS 1 in 1.44s]

Dynamic Masking Policies#

https://docs.snowflake.com/en/user-guide/security-column-ddm-intro.html

Snowflake Dynamic Masking Policies (which should be created using the DataOps Snowflake Object Lifecycle Engine (SOLE)) can be set on the column of a model (assuming these are materialised as tables, incremental tables or views):

con_cust_lifetime_value.yml

version: 2
models:  - name: con_cust_lifetime_value
    columns:        - name: "\"Customer Name\""          meta:            dynamic_masking_policy: '"PUBLIC"."MASK_WITH_SHA2"'

The Row Access Policy needs to be specified by both Schema and Name (but not Database since this is determined by the DataOps pipeline based on the branch/environment). The column is not explicitly stated since it is defined as a property of the column itself.

In a MATE job the output of the above would look similar to:

07:14:59 | 1 of 1 START table model customer_consumption.CUSTOMER_LIFETIME_VALUE [RUN]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE COLUMN "Customer Name" set masking policy DATAOPS_DEMO5_DEV."PUBLIC"."MASK_WITH_SHA2" [ PENDING ]       DATAOPS_DEMO5_DEV.customer_consumption.CUSTOMER_LIFETIME_VALUE "Customer Name" set masking policy DATAOPS_DEMO5_DEV."PUBLIC"."MASK_WITH_SHA2" [ SUCCESS 1 ]07:15:16 | 1 of 1 OK created table model customer_consumption.CUSTOMER_LIFETIME_VALUE [SUCCESS 1 in 16.44s]

Removal of Security and Governance objects#

The Security and Governance functions for MATE are 'add only' - they do not remove any existing GRANTS, TAGS, ROW ACCESS POLICIES, and DYNAMIC MASKING POLICIES. Since most models are created as tables or views, this works fine because the tables or views are rebuilt each time (with none of these) and only the objects defined are added. However if the model is materialised as incremental then it is not recreated each time and therefore objects defined and applied and then removed from the definition are not removed. Please contact support@dataops.live for ways to manage this if required.

Security and Governance

Overview#

Roles and Grants#

READ MORE

Model Level TAGS#

READ MORE

Column Level TAGS#

READ MORE

Row Access Policies#

READ MORE

be aware

Dynamic Masking Policies#

READ MORE

Removal of Security and Governance objects#