Understanding Column-level Security

This topic provides a general overview of Column-level Security and describes the features and support that are common to both Dynamic Data Masking and External Tokenization.

To learn more about using a masking policy with a tag, see Tag-based Masking Policies.

What is Column-level Security?

Column-level Security in Snowflake allows the application of a masking policy to a column within a table or view. Currently, Column-level Security includes two features:

  1. Dynamic Data Masking

  2. External Tokenization

Dynamic Data Masking is a Column-level Security feature that uses masking policies to selectively mask plain-text data in table and view columns at query time.

External Tokenization enables accounts to tokenize data before loading it into Snowflake and detokenize the data at query runtime. Tokenization is the process of removing sensitive data by replacing it with an undecipherable token. External Tokenization makes use of masking policies with external functions.

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.

The masking policy admin can apply masking policies to tables and views.

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.

Masking policy results for authorized and unauthorized roles.

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

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.

How Does a Masking Policy Work?

Masking policies for Dynamic Data Masking and External Tokenization adopt the same structure and format with one notable exception: masking policies for External Tokenization require using External Functions in the masking policy body.

The reason for this exception is that External Tokenization requires a third-party tokenization provider to tokenize data before loading data into Snowflake. At query runtime, Snowflake uses the external function to make a REST API call to the tokenization provider, which then evaluates a tokenization policy (that is created outside of Snowflake) to return either tokenized or detokenized data based on the masking policy conditions. Note that role mapping must exist in Snowflake and the tokenization provider to ensure that the correct data can be returned from a given query.

Snowflake supports creating masking policies using CREATE MASKING POLICY. For example:

-- 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;

Where:

employee_ssn_mask

The name of the Dynamic Data Masking policy.

employee_ssn_detokenize

The name of the External Tokenization policy.

as (val string) returns string

Specifies the input and output data types. The data types must match.

->

Separates the policy signature from its body.

case ... end;

Specifies the masking policy body (i.e. SQL expression) conditions.

In these two examples, if the query operator is using the PAYROLL custom role in the current session, the operator sees the unmasked/detokenized value. Otherwise, a fixed masked/tokenized value is seen.

ssn_unprotect

The external function to operate on the tokenized data.

Tip

If you want to update an existing masking policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE MASKING POLICY command.

The masking policy definition can then be updated with the ALTER MASKING POLICY command. This command does not require unsetting a masking policy from a column, if the masking policy is set on a column. So, a column that is protected by a policy remains protected while the policy definition is being updated.

For more details on using masking policies, see:

Using Conditional Columns

Conditional masking uses a masking policy to selectively protect the column data in a table or view based on the values in one or more different columns.

Using a different column to determine whether data in a given column should be protected offers policy administrators (i.e. users with the POLICY_ADMIN custom role) more freedom to create policy conditions.

Note the difference between the two representative policy examples:

Masking policy

This policy can be used for dynamic data masking.

create masking policy email_mask as
(val string) returns string ->
  case
    when current_role() in ('PAYROLL') then val
    else '******'
  end;

This policy specifies only one argument, val, which represents any column that contains string data. This policy can be created once and applied to any column containing string data. Only users whose CURRENT_ROLE is the PAYROLL custom role can see the column data. Otherwise, Snowflake returns a fixed masked value in the query result.

For more information, see CREATE MASKING POLICY.

Conditional masking policy

Note the arguments, (email varchar, visibility string), in this example.

create masking policy email_visibility as
(email varchar, visibility string) returns varchar ->
  case
    when current_role() = 'ADMIN' then email
    when visibility = 'Public' then email
    else '***MASKED***'
  end;

This policy specifies two arguments, email and visibility, and these arguments are column names. The first column always specifies the column to mask. The second column is a conditional column to evaluate whether the first column should be masked. Multiple conditional columns can be specified. In this policy, users whose CURRENT_ROLE is the ADMIN custom role can view the email address. If the email address also has a visibility column value of Public, then the email address is visible in the query result. Otherwise, Snowflake returns a query result with a fixed masked value for the email column.

This policy can be used on multiple tables and views provided that column structure in the table or view matches the columns specified in the policy. For more information, see CREATE MASKING POLICY.

Since the same object type is used for each representative example, the overall behavior of the policies should be similar, including, but not limited to:

  • Query runtime evaluation.

  • Utility (e.g. protecting sensitive data, using Context Functions).

  • Privilege structure.

  • Usage with different management approaches to support segregation of duties (SoD).

Limitations

In addition to the existing masking policy limitations, conditional masking policies have the following limitations:

Considerations

In addition to the existing normal masking policy considerations, evaluate the following points prior to using conditional masking policies:

  • Ensure all columns specified in the CREATE MASKING POLICY statement reside in the same table or view.

  • Minimize the number of column arguments in the policy definition. Snowflake must evaluate each column at query runtime. Specifying fewer columns leads to faster performance overall.

  • Track conditional column usage in a masking policy by calling the Information Schema table function POLICY_REFERENCES.

For more details on setting masking policies with conditional columns, see Apply a Conditional Masking Policy on a Column (in this topic).

Masking Policies at Query Runtime

At runtime, Snowflake rewrites the query to apply the masking policy expression to the columns specified in the masking policy. The masking policy is applied to the column regardless of where in a SQL expression the column is referenced, including:

  • Projections.

  • JOIN predicates.

  • WHERE clause predicates.

  • ORDER BY and GROUP BY clauses.

Important

A masking policy is deliberately applied wherever the relevant column is referenced by a SQL construct to prevent the de-anonymization of data through creative queries to include masked column data.

Therefore, if executing a query results in masked data in one or more columns, the query output may not provide the anticipated value because the masked data prevents evaluating all of the query output data in the desired context.

Masking policies with nested objects:

Snowflake supports nested masking policies, such as a masking policy on a table and a masking policy on a view for the same table. At query runtime, Snowflake evaluates all masking policies that are relevant to a given query in the following sequence:

  1. The masking policy that is applicable to the table is always executed first.

  2. The policy for the view is executed after evaluating the policy for the table.

  3. If nested views exist (e.g. table_1 » view_1 » view_2 » … view_n), the policies are applied in sequential order from left to right.

This pattern continues for however many masking policies exist with respect to the data in the query. The following diagram illustrates the relationship between a query performer, tables, views, and policies.

Masking policies with tables and views.

User queries:

The following example shows a user-submitted query followed by the Snowflake runtime query rewrite in which the masking policy (i.e. sql_expression) applies to the email column only.

SELECT email, city
from customers
where city = 'San Mateo';

SELECT <SQL_expression>(email), city
from customers
where city = 'San Mateo';

Query with a protected column in the WHERE clause predicate (anti-pattern):

The following examples show a user-submitted query followed by the Snowflake runtime query rewrite in which the masking policy (i.e. sql_expression) applies to only one side of a comparison (e.g. the email column but not the string to which the email column is compared. The results of the query are not what the user intended. Masking only one side of a comparison is a common anti-pattern.

SELECT email
from customers
where email = 'user@example.com';

SELECT <SQL_expression>(email)
from customers
where <SQL_expression>(email) = 'user@example.com';

Query with a protected column in the JOIN predicate (anti-pattern):

select b.email, d.city
from
  sf_tuts.public.emp_basic as b
  join sf_tuts.public.emp_details as d on b.email = d.email;

select
  <SQL_expression>(b.email),
  d.city
from
  sf_tuts.public.emp_basic as b
  join sf_tuts.public.emp_details as d on <SQL_expression>(b.email) = <SQL_expression>(d.email);

Query Runtime Considerations

Snowflake recommends considering the following factors when trying to predict the effect of applying a masking policy to a column and whether the query operator sees masked data:

The current session

Masking policy conditions using CURRENT_ROLE.

The executing role

Masking policy conditions using INVOKER_ROLE.

Role hierarchy

Masking policy conditions using IS_ROLE_IN_SESSION or IS_GRANTED_TO_INVOKER_ROLE.

Data sharing

Whether the data is shared using Snowflake Secure Data Sharing. See Limitations (in this topic).

Replication

See Replicated Database Objects.

Subqueries

A masking policy can reference a subquery in the policy definition, however, there are limits to subquery support in Snowflake. For more information, see Working with Subqueries.

UDFs in a masking policy

Ensure the data type of the column, UDF, and masking policy match. For more information, see User-defined Functions in a Masking Policy (in this topic).

The first three items are explained in greater detail in Advanced Column-level Security Topics. Data sharing only applies to Dynamic Data Masking because external functions cannot be invoked in the context of a share.

Ultimately, the specific use case determines whether Dynamic Data Masking or External Tokenization is the best fit.

Choosing Dynamic Data Masking or External Tokenization

To choose the correct feature that best meets the need of your organization, evaluate the major use cases for your data along with relevant considerations and limitations. The following two sections summarize the benefits and limitations between the two features.

Benefits

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

Factor

Dynamic Data Masking

External Tokenization

Notes

Preserve analytical value after de-identification.

Since tokenization provides a unique value for a given set of characters, it is possible to group records by a tokenized value without revealing the sensitive information.

For example, group medical records by diagnosis code with the patient diagnosis code tokenized. Data analysts can then query a view on the diagnosis code to obtain a count of the number of patients with a unique diagnosis code.

Pre-load tokenized Data.

Unauthorized users never see the real data value. Requires third-party tokenization provider.

Pre-load unmasked data.

Only need built-in Snowflake functionality, no third-parties required.

Data Sharing.

External functions cannot be used with Data Sharing, therefore no External Tokenization.

Ease of use and Change management.

Write a policy once and have it apply to thousands of columns across databases and schemas.

Data administration and SoD.

A security or privacy officer decides which columns to protect, not the object owner.

Masking policies are easy to manage and support centralized and decentralized administration models.

Data authorization and governance.

Contextual data access by role or custom entitlements.

Supports data governance as implemented by security or privacy officers and can prohibit privileged users with the ACCOUNTADMIN or SECURITYADMIN system roles from unnecessarily viewing data.

Database Replication.

See: Replication (in this topic).

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.

Limitation

Dynamic Data Masking

External Tokenization

Notes

Shared objects.

For Dynamic Data Masking, if the masking policy on a table or view column references an external function, the table or view cannot be shared. . For External Tokenization, Secure Data Sharing is not applicable because external functions cannot be invoked in the context of a share.

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

A data sharing consumer cannot apply a masking policy to a shared database or table. As a workaround, import the shared database or table and apply the masking policy to a local view on that shared table column.

Materialized views (MV).

For a complete summary, see Materialized Views (in this topic).

DROP MASKING POLICY

Prior to dropping a policy, unset the policy from the table or view column using ALTER TABLE … ALTER COLUMN or ALTER VIEW.

DROP DATABASE and DROP SCHEMA

Dropping a database or schema requires the masking policy and its mappings to be self-contained within the database or schema.

For example, database_1 contains policy_1 and policy_1 is only used in database_1.

Virtual columns.

Masking policies cannot be applied to virtual columns. Apply the policy to the source table column or view column.

External tables.

An external table cannot be referenced as a lookup table (i.e. in a subquery) to determine whether column values should be masked. For more information, see External Tables (in this topic)

Different data types in the input and output of a policy definition.

A masking policy definition must have the same data type for the input and output. In other words, as a representative example, you cannot define the input datatype as a timestamp and return a string.

Masking policy change management.

You can optionally store and track masking policy changes in a version control system of your choice.

Future grants.

Future grants of privileges on masking policies are not supported.

As a workaround, grant the APPLY MASKING POLICY privilege to a custom role to allow that role to apply masking policies on table or view columns.

Subqueries.

A subquery in the policy body that joins a column from a different table or view is not supported.

Certain subqueries with conditional masking policies can result in errors. The following are two representative examples:

  • Mask or tokenize a column in one table based on the column value in a different table or view (i.e. mapping table).

  • Mask or tokenize a column in one table based on joining a second column to another column in a different table or view.

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

Using Column-level Security on Tables and Views

Snowflake supports masking policies with tables and views. The following describes how masking policies affect tables and views in Snowflake.

Tip

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.

Apply Masking Policies to Columns

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;

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;

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:

  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:

    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;
    
  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:

    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);
    

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.

Materialized Views

Snowflake allows setting 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.

  2. For an existing materialized view, execute an ALTER VIEW … MODIFY COLUMN statement on the materialized view.

For a new materialized view, execute the following statement:

create or replace materialized view user_info_mv (ssn_number masking policy ssn_mask) as select ssn_number from user_info;

For an existing materialized view, see the view example 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 or view if a materialized view is already created from the underlying table or view. 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>.
    
  2. If a masking policy is set on an underlying table or view column and a materialized view is created from that table or view, 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'.
    

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>'));

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 apply 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 apply the masking policy to the external table VALUE column by executing an ALTER TABLE … ALTER COLUMN statement on the external table.

ALTER TABLE <name> MODIFY COLUMN VALUE SET MASKING POLICY <policy_name> ;

A policy cannot be set on any other external table column, including user-added virtual columns. If these columns need to be protected, create a view on the external table and apply a policy to the view 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.

Important

Snowflake does not support using an external table as a lookup table (i.e. in a subquery) in a masking policy. While cloning a database, Snowflake clones the masking policy, but not the external table. Therefore, the policy in the cloned database refers to a table that is not present in the cloned database.

If the data in the external table is necessary for the policy, consider moving the external table data to a dedicated schema within the database in which the masking policy exists prior to completing a clone operation. Update the masking policy to reference the fully qualified table name to ensure the policy refers to a table in the cloned database.

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 when accessing a cloned object.

  • Cloning an individual masking policy object is not supported.

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

  • A cloned table maps to the same masking policies as the source table.

    • When a table is cloned in the context of its parent schema cloning, if the source table has a reference to a masking policy in the same parent schema (i.e. a local reference), the cloned table will have a reference to the cloned masking policy.

    • If the source table refers to a masking 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;
    
  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;
    
  4. Execute a query on the view column.

    use role ANALYST;
    select count(DISTINCT SSN) from <view>;
    

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.

Masking Policies with Shared Tables and Views

Snowflake allows data sharing providers and consumers to add masking policies to database objects. Snowflake returns an error if either of the following are true:

  • The policy assigned to a shared table or view column is updated with an ALTER MASKING POLICY statement to call an external function.

  • The policy calls an external function and you attempt to assign the policy to a shared table column using an ALTER TABLE … ALTER COLUMN statement or assign the policy to a view column using an ALTER VIEW statement.

If a masking policy assigned to a shared table or view calls the CURRENT_ROLE or CURRENT_USER function, or calls a secure UDF, Snowflake returns a NULL value for the function or the UDF.

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

For more information about data sharing and masking policies, see the Limitations sections (in this topic).

Replication

Policy-protected objects can be replicated if they are in the same database as the policy.

The individual masking policies can be replicated.

The replication operation fails if either of the following conditions are 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 reference to a masking policy in another database.

    Note

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

    For more information, see Database Replication and Failover/Failback.

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"
        ]
      }
    ]
  ]
}

Unloading 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.

Managing Column-level Security

This section provides information useful for determining your overall management approach to masking policies, describes the privileges required to manage Column-level Security, and lists supported DDL commands.

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.

Policy Action

Centralized Management

Hybrid Management

Decentralized Management

Create policies

Security officer

Security officer

Individual teams

Apply policies to columns

Security officer

Individual teams

Individual teams

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.

Privilege

Usage

CREATE

Enables creating a new masking policy in a schema.

APPLY

Enables executing the unset and set operations for a masking policy on a column.

Note that granting the global APPLY MASKING POLICY privilege (i.e. APPLY MASKING POLICY on ACCOUNT) enables executing the DESCRIBE operation on tables and views.

For syntax examples, see Masking Policy Privileges.

OWNERSHIP

Grants full control over the masking policy. Required to alter most properties of a masking policy. Only a single role can hold this privilege on a specific object at a time.

Note

Operating on a masking policy also requires the USAGE privilege on the parent database and 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 <db_name.schema_name> to role security_officer;

grant apply masking policy on account to role security_officer;

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 <db_name.schema_name> to role security_officer;

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;

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 <db_name.schema_name> to role support;
grant create masking policy on schema <db_name.schema_name> to role finance;

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;

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;

For more information on masking policy privileges, see:

Masking Policy DDL

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

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

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

Operation

Privilege

Create masking policy

A role with the CREATE MASKING POLICY on SCHEMA privilege.

Alter masking policy

The masking policy owner (i.e. the role with the OWNERSHIP privilege on the masking policy).

Drop masking policy

The masking policy owner (i.e. the role with the OWNERSHIP privilege on the masking policy).

Show masking policies

One of the following: . A role with the global APPLY MASKING POLICY privilege, or . The masking policy owner (i.e. the role with the OWNERSHIP privilege on the masking policy) or . A role with the APPLY privilege on the masking policy.

Describe masking policies

One of the following: . A role with the global APPLY MASKING POLICY privilege or . The masking policy owner (i.e. the role with the OWNERSHIP privilege on the masking policy) or . A role with the APPLY privilege on the masking policy.

List of columns having a masking policy

One of the following: . The role with the APPLY MASKING POLICY privilege, or . The role with the APPLY on MASKING POLICY privilege on a given masking policy and has OWNERSHIP on the target object.

Using UDFs in a masking policy

If creating a new or altering an existing masking policy, the policy administrator role must have usage on the UDF, all scalar UDFs in the policy expression should have the same data type, and the UDF must exist.

At the query runtime, Snowflake verifies if the UDF exists; if not, the SQL expression will not resolve and the query fails.

Next Topics:

Back to top