Understanding Column-level Security¶

What are masking policies?¶

Snowflake supports masking policies as a schema-level object to protect sensitive data from unauthorized access while allowing authorized users to access sensitive data at query runtime. This means that sensitive data in Snowflake is not modified in an existing table (i.e. no static masking). Rather, when users execute a query in which a masking policy applies, the masking policy conditions determine whether unauthorized users see masked, partially masked, obfuscated, or tokenized data. Masking policies as a schema-level object also provide flexibility in choosing a centralized, decentralized, or hybrid management approach. For more information, see Managing Column-level Security (in this topic).

Masking policies can include conditions and functions to transform the data at query runtime when those conditions are met. The policy-driven approach supports segregation of duties to allow security teams to define policies that can limit sensitive data exposure, even to the owner of an object (i.e. the role with the OWNERSHIP privilege on the object, such as a table or view) who normally have full access to the underlying data.

For example, masking policy administrators can implement a masking policy such that analysts (i.e. users with the custom ANALYST role) can only view the last four digits of a phone number and none of the social security number, while customer support representatives (i.e. users with the custom SUPPORT role) can view the entire phone number and social security number for customer verification use cases.

A masking policy consists of a single data type, one or more conditions, and one or more masking functions.

• You can apply the masking policy to one or more table/view columns with the matching data type. For example, you can define a policy for an email address once and apply it to 1000s of email columns across databases and schemas.

• Masking policy conditions can be expressed using Conditional expression functions and Context functions or by querying a custom entitlement table. You can use the context functions INVOKER_ROLE and INVOKER_SHARE for use with views and shares, respectively.

• Masking functions can be any of the built-in functions (e.g. REGEXP_REPLACE, SHA2 , SHA2_HEX), User-defined functions overview, or Writing external functions (for de-tokenization using an external tokenization provider).

While Snowflake offers secure views to restrict access to sensitive data, secure views present management challenges due to large numbers of views and derived business intelligence (BI) dashboards from each view. Masking policies solve this management challenge by avoiding an explosion of views and dashboards to manage.

Masking policies support segregation of duties (SoD) through the role separation of policy administrators from object owners. Secure views do not have SoD, which is a profound limitation to their utility. This role separation leads to the following default settings:

• Object owners (i.e. the role that has the OWNERSHIP privilege on the object) do not have the privilege to unset masking policies.

• Object owners cannot view column data in which a masking policy applies.

For more information on managing roles and privileges, see Managing Column-level Security (in this topic) and Access control privileges.

-- Dynamic Data Masking

CREATE MASKING POLICY employee_ssn_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN val
    ELSE '******'
  END;

-- External Tokenization

  CREATE MASKING POLICY employee_ssn_detokenize AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN ssn_unprotect(VAL)
    ELSE val -- sees tokenized data
  END;

Benefits¶

The following table compares the benefits of Dynamic Data Masking and External Tokenization.

Limitations¶

The following table describes the current limitations for Column-level Security. A checkmark (i.e. ✔) indicates a limitation or lack of current support for the feature.

Considerations¶

• Use caution when inserting values from a source column that has a masking policy on the source column to a target column without a masking policy on the target column. Since a masking policy is set on the source column, a role that views unmasked column data can insert unmasked data into another column, where any role with sufficient privileges on the table or view can see the value.

• If a role that sees masked data in the source column inserts those values into a target column, the inserted values remain masked. If a masking policy is not set on the target column, then users with sufficient privileges on the table or view may see a combination of masked and unmasked values in the target column. Therefore, as a best practice:

  • Exercise caution when applying masking policies to columns.

  • Verify queries using columns that have masking policies before making tables and views available to users.

  • Determine additional tables and views (i.e. target columns) where the data in the source column may appear.

  • For more information, see Obtain Columns with a Masking Policy (in this topic).

• Use caution when creating the setup script for a Snowflake Native App when the masking policy exists in a versioned schema. For details, see version schema considerations.

Active role hierarchy & mapping tables¶

The policy conditions can evaluate the user’s active primary and secondary roles in a session directly, look up active roles in a mapping table, or do both depending on how the policy administrator wants to write the policy. If the policy contains a mapping table lookup, create a centralized mapping table and store the mapping table in the same database as the protected table. This is particularly important if the policy calls the IS_DATABASE_ROLE_IN_SESSION function. For details, see the function usage notes.

For these use cases, Snowflake recommends writing the policy conditions to call the IS_ROLE_IN_SESSION or the IS_DATABASE_ROLE_IN_SESSION function depending on whether you want to specify an account role or database role. For examples, see:

• Examples section in the IS_ROLE_IN_SESSION function.

• IS_DATABASE_ROLE_IN_SESSION

• Share data protected by a policy

For additional examples using context functions and masking policies, see Advanced Column-level Security topics.

Apply masking policies to columns¶

When a column is not protected by a masking policy, there are two options to apply a masking policy to a column in a table or view:

1. With a new table or view, apply the policy to a table column with a CREATE TABLE statement or a view column with a CREATE VIEW statement.

2. With an existing table or view, apply the policy to a table column with an ALTER TABLE … ALTER COLUMN statement or a view column with an ALTER VIEW statement.

For a new table or view, execute the following statements:

-- table
CREATE OR REPLACE TABLE user_info (ssn string masking policy ssn_mask);

-- view
CREATE OR REPLACE VIEW user_info_v (ssn masking policy ssn_mask_v) AS SELECT * FROM user_info;

Copy

For an existing table or view, execute the following statements:

-- table
ALTER TABLE IF EXISTS user_info MODIFY COLUMN ssn_number SET MASKING POLICY ssn_mask;

-- view
ALTER VIEW user_info_v MODIFY COLUMN ssn_number SET MASKING POLICY ssn_mask_v;

Copy

For more information on syntax and usage, see ALTER TABLE … ALTER COLUMN and ALTER VIEW.

If the masking policy uses a UDF, see User-defined functions in a masking policy (in this topic).

Apply a conditional masking policy on a column¶

After creating a masking policy using conditional columns, there are two options to set a conditional masking policy on a column when the column is not already protected by a masking policy:

1. For a new table or view, apply the policy to a table or view column with the corresponding CREATE statement.

   For more information on syntax and usage, see:

   • CREATE TABLE

   • CREATE VIEW

   • CREATE MATERIALIZED VIEW

   For a new table or view, execute the following statements:

   -- table
   CREATE OR REPLACE TABLE user_info (email string masking policy email_visibility) USING (email, visibility);
   
   --view
   CREATE OR REPLACE VIEW user_info_v (email masking policy email_visibility) USING (email, visibility) AS SELECT * FROM user_info;
   

   Copy

2. For an existing table or view, set the policy on a table or view column with the corresponding ALTER statement.

   For more information on syntax and usage, see:

   • ALTER TABLE

   • ALTER TABLE … ALTER COLUMN

   • ALTER VIEW

   • ALTER MATERIALIZED VIEW

   For an existing table or view, execute the following statements:

   -- table
   ALTER TABLE IF EXISTS user_info MODIFY COLUMN email
   SET MASKING POLICY email_visibility USING (EMAIL, VISIBILITY);
   
   -- VIEW
   ALTER VIEW user_info_v MODIFY COLUMN email
   SET MASKING POLICY email_visibility USING (email, visibility);
   

   Copy

Replace a masking policy on a column¶

Once a masking policy is set on a column, there are two different pathways to replace the masking policy on the column with a different masking policy without having to replace the entire table or view.

These examples use ALTER TABLE commands. The same approach applies to views with the ALTER VIEW command:

• Unset the policy from a table column in one statement and then set a new policy on the column in a different statement:

  ALTER TABLE t1 MODIFY COLUMN c1 UNSET MASKING POLICY;
  
  ALTER TABLE t1 MODIFY COLUMN c1 SET MASKING POLICY p2;
  

  Copy

• Use the FORCE keyword to replace the policy in a single statement:

  ALTER TABLE t1 MODIFY COLUMN c1 SET MASKING POLICY p2 FORCE;
  

  Copy

  Note:

  • The FORCE keyword requires the data type of the policy in the ALTER TABLE statement (i.e. STRING) to match the data type of the masking policy currently set on the column (i.e. STRING).

  • The FORCE keyword can be combined with the USING clause to set a conditional masking policy on column:

    ALTER TABLE t1 MODIFY COLUMN c1 SET MASKING POLICY policy1 USING (c1, c3, c4) FORCE;
    

    Copy

Row access policies¶

A given table or view column can be specified in either a masking policy signature or a row access policy signature. In other words, the same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.

This behavior also applies to column used as conditional columns in a masking policy.

For more information, see CREATE MASKING POLICY and CREATE ROW ACCESS POLICY.

Simulate how a policy will work¶

Call the POLICY_CONTEXT function to simulate a query on a column that is protected by a masking policy, a table or view protected by a row access policy, or both types of policies.

Materialized views¶

Snowflake lets you set a masking policy on a materialized view column. At query runtime, the query plan executes any masking policy that is present prior to creating the materialized view rewrite. Once the materialized view rewrite occurs, masking policies cannot be set on any materialized view columns.

There are two options to set a masking policy on a materialized view column:

1. For a new materialized view, execute a CREATE MATERIALIZED VIEW statement:

   CREATE OR REPLACE MATERIALIZED VIEW user_info_mv
     (ssn_number masking policy ssn_mask)
   AS SELECT ssn_number FROM user_info;
   

   Copy

2. For an existing materialized view, execute an ALTER VIEW … MODIFY COLUMN statement on the materialized view as shown in the Apply Masking Policies to Columns section (in this topic).

Additionally, the following two limitations exist regarding masking policies and materialized views:

1. A masking policy cannot be set on a table column if a materialized view is already created from the underlying table. Snowflake returns the following error message when this attempt is made:

   SQL execution error: One or more materialized views exist on the table. number of mvs=<number>, table name=<table_name>.
   

   Copy

2. If a masking policy is set on an underlying table column and a materialized view is created from that table, the materialized view only contains columns that are not protected by a masking policy. Snowflake also returns the following error message if the attempting to include one or more columns protected by a masking policy:

   Unsupported feature 'CREATE ON MASKING POLICY COLUMN'.
   

   Copy

Dynamic tables¶

You can create a dynamic table with a row access policy, masking policy, and tag. For more information, see:

• CREATE DYNAMIC TABLE

• Additional limitations with incremental refresh

Obtain columns with a masking policy¶

To obtain a list of columns with masking policies, execute the following statement. For more information, see POLICY_REFERENCES.

SELECT * from table(
  INFORMATION_SCHEMA.POLICY_REFERENCES(
    policy_name=>'<policy_name>'
  )
);

Copy

Execute a DESCRIBE TABLE or DESCRIBE VIEW statement to view the masking policy on column in a table or view.

Object Tagging and masking policies¶

For details, see Tag-based masking policies.

Note that a masking policy that is directly assigned to a column takes precedence over a tag-based masking policy.

Hashing, cryptographic, and encryption functions in masking policies¶

Hashing and cryptographic/checksum can be used in masking policies to mask sensitive data.

For a more information, see Advanced Column-level Security topics.

External tables¶

You cannot assign a masking policy to the external table VALUE column when creating the external table with a CREATE EXTERNAL TABLE statement because this column is automatically created by default.

You can assign the masking policy to the external table VALUE column by executing an ALTER TABLE … ALTER COLUMN statement on the external table. The data type of the masking policy that protects the VALUE column must be VARIANT.

ALTER TABLE t1 MODIFY COLUMN VALUE SET MASKING POLICY p1;

Copy

You can assign a masking policy to a virtual column in an external table as follows:

• Set the EXEMPT_OTHER_POLICIES masking policy property to TRUE in the masking policy that protects VALUE column in the external table.

  If this property is not already set, execute a CREATE OR REPLACE statement on the masking policy the protects the VALUE column and specify the EXEMPT_OTHER_POLICIES property. The virtual column inherits the policy that protects the VALUE column, and this property allows the policy on the virtual column to override the inherited policy. For details, see CREATE MASKING POLICY.

• Assign a different masking policy to the virtual column using an ALTER TABLE command. This policy can be less strict than the policy for the VALUE column because the virtual column is less sensitive. The virtual column contains a lesser amount of data than the VALUE column; the VALUE column contains all of the data for each row in the external table.

  The data type in the policy that protects the virtual column depends on the data type of the virtual column.

Regarding conditional columns in a masking policy, a virtual column can be listed as an conditional column argument to determine whether the first column argument should be masked or tokenized. However, a virtual column cannot be specified as the first column to mask or tokenize.

For more information, see CREATE MASKING POLICY.

Streams¶

Masking policies on columns in a table carry over to a stream on the same table.

The result is that unauthorized users see masked data; streams created by authorized users see the data as defined by the masking policy.

Cloned objects¶

The following approach helps to safeguard data from users with the SELECT privilege on the table or view when accessing a cloned object:

• Cloning an individual policy object is not supported.

• Cloning a schema results in the cloning of all policies within the schema.

• A cloned table maps to the same policies as the source table. In other words, if a policy is set on the base table or its columns, the policy is attached to the cloned table or its columns.

  • If a table or view exists in the source schema/database and has references to policies in the same schema/database, the cloned table or view is mapped to the corresponding cloned policy (in the target schema/database) instead of the policy in the source schema/database.

  • If the source table refers to a policy in a different schema (i.e. a foreign reference), then the cloned table retains the foreign reference.

For more information, see CREATE <object> … CLONE.

CREATE TABLE … AS SELECT (CTAS) statements¶

Executing a CREATE TABLE … AS SELECT (CTAS) statement applies any masking policies on columns included in the statement before the data is populated in the new table (i.e. the applicable column data is masked in the new table). This flow is adhered to because a table created using a CTAS statement may have a different set of columns than the source objects, and Snowflake cannot apply masking policies to the new table columns implicitly.

If there is a need to copy unmasked data, use a role authorized for protected data to run the CTAS statement. After creating the new table, transfer ownership of the new table to another role and ask the masking policy administrator to apply the masking policies to the columns of the new table.

For more information, see CREATE TABLE.

Queries using aggregate functions and masked columns¶

It is possible to use Aggregate functions on columns with masked data.

A representative use case is that a data analyst wants to obtain the COUNT for a column of social security numbers without needing to see the actual data. However, if the data analyst runs a query using SELECT on a masked table column, the query returns a fixed masked value. Users with the PAYROLL custom role in the current session see the unmasked data and everyone else sees masked data.

To achieve this outcome:

1. The table owner creates a view on the column that contains the aggregate function.

   CREATE VIEW ssn_count AS SELECT DISTINCT(ssn) FROM table1;
   

   Copy

2. Grant the ANALYST role full privileges on the view. Do not grant the analyst any privileges on the table.

3. Apply a masking policy to the table column. Note that the table policy is always applied before the view policy, if there is a policy on a view column.

   CASE
     WHEN CURRENT_ROLE() IN ('PAYROLL') THEN val
     ELSE '***MASKED***'
   END;
   

   Copy

4. Execute a query on the view column.

   USE ROLE analyst;
   SELECT COUNT(DISTINCT ssn) FROM v1;
   

   Copy

User-defined functions in a masking policy¶

A UDF can be passed into the masking policy conditions.

It is important to ensure that the data type for the table or view column, the UDF, and the masking policy match. If the data types are different, such as having a table column and UDF with data type VARIANT and the masking policy (with this UDF in the policy conditions) returns VARCHAR data type, Snowflake returns an error when making a query on the table column when this masking policy is set on the table column.

For a representative example of matching the data type for a table column, UDF, and masking policy, see the Using JavaScript UDFs on JSON (Variant) example in CREATE MASKING POLICY.

Data Sharing¶

Usage:

• If the provider assigns a policy to a shared table or view and the policy conditions call the CURRENT_ROLE or CURRENT_USER function, or the policy conditions call a secure UDF, Snowflake returns a NULL value for the function or the UDF in the consumer account.

  The reason is that the owner of the data being shared does not typically control the users or roles in the account in which the table or view is being shared. As a workaround, use the CURRENT_ACCOUNT function in the policy conditions.

  Alternatively, as a provider, write the policy conditions to call the IS_DATABASE_ROLE_IN_SESSION function and share the database role. As a consumer, grant the shared database role to an account role. For details, see Share data protected by a policy.

Limitations:

• A data sharing provider cannot create a policy in a reader account.

• Data sharing consumers cannot apply a policy to a shared table or view. As a workaround, import the shared database and create a local view from the shared table or view.

• Data sharing consumers cannot query a shared table or view that references two different providers. For example:

  • rap1 is a row access policy that protects the table named t1, where t1 is in the share named share1 from a provider.

  • The rap1 policy conditions reference a mapping table named t2, where t2 comes from share2 and a different provider.

  • The consumer query on t1 fails.

  • The provider for t1 can query t1.

• External functions:

  Snowflake returns an error if:

  • The policy assigned to a shared table or view is updated to call an external function.

  • The policy calls an external function and you attempt to assign the policy to a shared table or view.

Replication¶

Masking policies and their assignments can be replicated using database replication and replication groups.

For database replication, the replication operation fails if either of the following conditions is true:

• The primary database is in an Enterprise (or higher) account and contains a policy but one or more of the accounts approved for replication are on lower editions.

• A table or view contained in the primary database has a dangling reference to a masking policy in another database.

The dangling reference behavior for database replication can be avoided when replicating multiple databases in a replication group.

Note

If using failover or failback actions, the Snowflake account must be Business Critical Edition or higher.

For more information, see Introduction to replication and failover across multiple accounts.

Query profile¶

When used on a column with a masking policy, the EXPLAIN command output includes the masked data, not the masking policy body.

The following example generates the EXPLAIN plan for a query on a table of employee identification numbers and social security numbers. The command in this example generates the example in JSON format.

The column containing the social security numbers has a masking policy.

EXPLAIN USING JSON SELECT * FROM mydb.public.ssn_record;

{
  "GlobalStats": {
    "partitionsTotal": 0,
    "partitionsAssigned": 0,
    "bytesAssigned": 0
  },
  "Operations": [
    [
      {
        "id": 0,
        "operation": "Result",
        "expressions": [
          "1",
          "'**MASKED**'"
        ]
      },
      {
        "id": 1,
        "parent": 0,
        "operation": "Generator",
        "expressions": [
          "1"
        ]
      }
    ]
  ]
}

Unload data¶

Using the COPY INTO <location> command on a column that has a masking policy results in the masking policy being applied to the data. Therefore, unauthorized users see masked data after executing the command.

Snowflake Native App Framework¶

For details about using masking policies with a Snowflake Native App, see:

• Restrictions on sharing data content that contains policies.

• Define policies on proxy views.

• Blocked context functions.

Choosing a centralized, hybrid, or decentralized approach¶

To manage Dynamic Data Masking and External Tokenization policies effectively, it is helpful to consider whether your approach to masking data in columns should follow a centralized security approach, a decentralized approach, or a hybrid of each of these two approaches.

The following table summarizes some of the considerations with each of these two approaches.

As a best practice, Snowflake recommends that your organization gathers all relevant stakeholders to determine the best management approach for implementing Column-level Security in your environment.

Masking policy privileges¶

This section describes the Column-level Security masking policy privileges and how they apply to a centralized, decentralized, or hybrid management approach.

Snowflake provides the following privileges for Column-level Security masking policies.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

The following examples show how granting privileges apply to different management approaches. After granting the APPLY privilege to a role, the masking policy can be set on a table column using an ALTER TABLE … ALTER COLUMN statement or set on a view column using an ALTER VIEW statement (by a member of the role with the APPLY privilege on the masking policy).

Centralized Management

In a centralized management approach, only the security officer custom role (e.g. security_officer) creates and applies masking policies to columns in tables or views. This approach can provide the most consistency in terms of masking policy management and masking sensitive data.

-- create a security_officer custom role

USE ROLE ACCOUNTADMIN;
CREATE ROLE security_officer;

-- grant CREATE AND APPLY masking policy privileges to the SECURITY_OFFICER custom role.

GRANT CREATE MASKING POLICY ON SCHEMA mydb.mysch TO ROLE security_officer;

GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE security_officer;

Copy

Where:

• schema_name

  Specifies the identifier for the schema; must be unique for the database in which the schema is created.

  In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. “My object”). Identifiers enclosed in double quotes are also case-sensitive.

  For more details, see Identifier requirements.

Hybrid Management

In a hybrid management approach, the security officer custom role (e.g. security_officer) creates masking policies and individual teams (e.g. finance, payroll, human resources) apply the masking policies to columns in tables or views owned by the teams. This approach can lead to consistent policy creation and maintenance but requires individual teams to have the increased responsibility to mask sensitive data.

USE ROLE ACCOUNTADMIN;
CREATE ROLE security_officer;
GRANT CREATE MASKING POLICY ON SCHEMA mydb.mysch TO ROLE security_officer;

Copy

The SECURITY_OFFICER custom role grants the APPLY privilege to the human resources team (i.e. users with the HUMAN_RESOURCES custom role) to mask social security numbers (e.g. masking policy: ssn_mask) in columns for objects owned by the HUMAN_RESOURCES custom role.

USE ROLE security_officer;
GRANT APPLY ON MASKING POLICY ssn_mask TO ROLE human_resources;

Copy

Where:

• grant apply on masking policy policy_name to role role_name;

  Used by a policy owner to decentralize the unset and set operations of a given masking policy on columns to the object owners.

  This privilege supports discretionary access control where object owners are also considered data stewards.

Decentralized Approach

In a decentralized management approach, individual teams create and apply masking policies to columns in tables or views. This approach can lead to inconsistent policy management, with the possibility of sensitive data not being masked properly, since individual teams assume all responsibility for managing masking policies and masking sensitive data.

In this representative example, the support team (i.e. users with the custom role SUPPORT) and the finance team (i.e. users with the custom role FINANCE) can create masking policies. Note that these custom roles may not include the SECURITY_OFFICER custom role.

USE ROLE ACCOUNTADMIN;
GRANT CREATE MASKING POLICY ON SCHEMA mydb.mysch TO ROLE support;
GRANT CREATE MASKING POLICY ON SCHEMA <DB_NAME.SCHEMA_NAME> TO ROLE FINANCE;

Copy

The support team grants the APPLY privilege to the human resources team (i.e. users with the custom role HUMAN_RESOURCES) to mask social security numbers (e.g. masking policy: ssn_mask) in columns for objects owned by the HUMAN_RESOURCES custom role.

USE ROLE support;
GRANT APPLY ON MASKING POLICY ssn_mask TO ROLE human_resources;

Copy

The finance team grants the APPLY privilege to the internal audit team (i.e. users with the custom role AUDIT_INTERNAL) to mask cash flow data (e.g. masking policy: cash_flow_mask) in columns for objects owned by the AUDIT_INTERNAL custom role.

USE ROLE finance;
GRANT APPLY ON MASKING POLICY cash_flow_mask TO ROLE audit_internal;

Copy

For more information on masking policy privileges, see:

• Using Dynamic Data Masking

• Using External Tokenization

Masking policy DDL¶

Snowflake provides the following set of commands to manage Column-level Security masking policies.

• CREATE MASKING POLICY

• ALTER MASKING POLICY (see also: ALTER TABLE, ALTER TABLE … ALTER COLUMN, and ALTER VIEW)

• DROP MASKING POLICY

• SHOW MASKING POLICIES

• DESCRIBE MASKING POLICY

The following table summarizes the relationship between the Column-level Security masking policy DDL operations and their necessary privileges.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Discover masking policies¶

You can use the MASKING_POLICIES view in the Account Usage schema of the shared SNOWFLAKE database. This view is a catalog for all masking policies in your Snowflake account. For example:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
ORDER BY POLICY_NAME;

Copy

Identify assignments¶

Snowflake supports different options to identify masking policy assignments, depending on whether the query needs to target the account or a specific database.

• Account-level query:

  Use the Account Usage POLICY_REFERENCES view to determine all of the columns that have a masking policy. For example:

  SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
  ORDER BY POLICY_NAME, REF_COLUMN_NAME;
  

  Copy

• Database-level query:

  Every Snowflake database includes an Snowflake Information Schema. Use the Information Schema table function POLICY_REFERENCES to determine all of the masking policies on columns for a given table:

  SELECT *
  FROM TABLE(
    my_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
      'my_table',
      'table'
    )
  );
  

  Copy

  You can also use this function to query by the name of the masking policy to find the objects that are associated with a given masking policy.

Accessing the Governance area in Snowsight¶

To access the Governance area, your Snowflake account must be Enterprise Edition or higher. Additionally, you must do either of the following:

• Use the ACCOUNTADMIN role.

• Use an account role that is directly granted the GOVERNANCE_VIEWER and OBJECT_VIEWER database roles.

  You must use an account role with these database role grants. Currently, Snowsight does not evaluate role hierarchies and user-defined database roles that have access to tables, views, data access policies, and tags.

  To determine if your account role is granted these two database roles, use a SHOW GRANTS command:

  SHOW GRANTS LIKE '%VIEWER%' TO ROLE data_engineer;
  

  Copy

  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  | created_on                    | privilege | granted_on    | name                        | granted_to | grantee_name    | grant_option | granted_by |
  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  | 2024-01-24 17:12:26.984 +0000 | USAGE     | DATABASE_ROLE | SNOWFLAKE.GOVERNANCE_VIEWER | ROLE       | DATA_ENGINEER   | false        |            |
  | 2024-01-24 17:12:47.967 +0000 | USAGE     | DATABASE_ROLE | SNOWFLAKE.OBJECT_VIEWER     | ROLE       | DATA_ENGINEER   | false        |            |
  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  

  
  

  If your account role is not granted either or both of these database roles, use the GRANT DATABASE ROLE command and run the SHOW GRANTS command again to confirm the grants:

  USE ROLE ACCOUNTADMIN;
  GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE data_engineer;
  GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE data_engineer;
  SHOW GRANTS LIKE '%VIEWER%' TO ROLE data_engineer;
  

  Copy

  For details about these database roles, see SNOWFLAKE database roles.

Dashboard¶

As a data administrator, you can use the Dashboard interface to monitor tag and policy usage in the following ways.

• Coverage: specifies the count and percentage based on whether a table, view, or column has a policy or tag.

• Prevalence: lists and counts the most frequently used policies and tags.

The coverage and prevalence provide a snapshot as to how well the data is protected and tagged.

When you select a count number, percentage, policy name, or tag name, the Tagged Objects interface opens. The Tagged Objects interface updates the filters automatically based on your selection in the Dashboard.

The monitoring information is an alternative or complement to running complex and query-intensive operations on multiple Account Usage views.

These views might include, but are not limited to, the COLUMNS, POLICY_REFERENCES, TABLES, TAG_REFERENCES, and VIEWS views.

Tagged Objects¶

As a data administrator, you can use this table to associate the coverage and prevalence in the Dashboard to a list of specific tables, view, or columns quickly. You can also filter the table results manually as follows.

• Choose Tables or Columns.

• For tags, you can filter with tags, without tags, or by a specific tag.

• For policies, you can filter with policies, without policies, or by a specific policy.

When you select a row in the table, the Table Details or Columns tab in Data » Databases opens. You can edit the tag and policy assignments as needed.

For additional details, refer to Tag and policy discovery