Skip to content

Snowflake

This topic covers how you can configure Snowflake PolicySync access control using Privacera Manager.

Prerequisites

Ensure the following:

  • Create a Snowflake account that is accessible from the instance used for Privacera Manager installation.
  • Create the Snowflake warehouse, database, users, and roles required by PolicySync. For more information, see Snowflake Configuration for PolicySync.

CLI Configuration

  1. SSH to the instance where Privacera is installed.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.PolicySync.snowflake.yml config/custom-vars/
    vi config/custom-vars/vars.PolicySync.snowflake.yml
    
  3. Set the properties for your specific installation. For property details and description, see the Configuration Properties section that follows.

    Note

    Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see Snowflake Connector.

  4. Run the following commands.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update
    
  5. Steps to validate the install.

    1. Install snowsql if it's not already installed.

      mkdir -p ~/privacera/downloads
      cd ~/privacera/downloads
      wget https://privacera.s3.amazonaws.com/public/pm-demo-data/snowflake/install_snowsql.sh -O install_snowsql.sh
      chmod +x install_snowsql.sh
      ./install_snowsql.sh
      
    2. Download the script for access check.

      wget https://privacera.s3.amazonaws.com/public/pm-demo-data/snowflake/snowflake_access_check.sh -O snowflake_access_check.sh
      chmod +x snowflake_access_check.sh
      
    3. Run downloaded script. For example,./snowflake_access_check.sh testsnowflake.prod.us-west-2.aws emily welcome123

      ./snowflake_access_check.sh ${SNOWFLAKE_ACCOUNT} ${USERNAME} ${PASSWORD}
      
    4. Verify access/denied results by logging in with the Privacera Portal user credentials.

      Navigate to Privacera Portal > Access Management > Audit. Now, access to Snowflake will be shown as Allowed.

Configuration Properties

JDBC configuration

Name Type Default Required Description
SNOWFLAKE_JDBC_URL string Yes

Specifies the JDBC URL for the Snowflake connector.

SNOWFLAKE_JDBC_USERNAME string Yes

Specifies the JDBC username to use.

SNOWFLAKE_JDBC_PASSWORD string Yes

Specifies the JDBC password to use.

SNOWFLAKE_WAREHOUSE_TO_USE string Yes

Specifies the JDBC warehouse that PolicySync establishes a connection to, which is used to run SQL queries.

SNOWFLAKE_ROLE_TO_USE string Yes

Specifies the role that PolicySync uses when it runs SQL queries.

JDBC_MAX_POOL_SIZE integer 15 No

Specifies the maximum size for the JDBC connection pool.

JDBC_MIN_IDLE_CONNECTION integer 3 No

Specifies the minimum size of the JDBC connection pool.

JDBC_LEAK_DETECTION_THRESHOLD string 900000L No

Specifies the duration in milliseconds that a connection is not part of the connection pool before PolicySync logs a possible connection leak message. If set to 0, leak detection is disabled.

SNOWFLAKE_MANAGE_EXTERNAL_STAGE_LIST This property is used to set comma separated ExternalStage Fqdn for which access control should be managed by policysync. If you want to manage all ExternalStage, then you can skip specifying this property. This property supports wildcards as well.
The ignore ExternalStage list has precedance over manage ExternalStage list.
- testdb1.schema1.ExternalStage1,testdb2.ExternalStage2.FileFormat2,sales_db*.sales*.*.
SNOWFLAKE_MANAGE_INTERNAL_STAGE_LIST This property is used to set comma separated InternalStage Fqdn for which access control should be managed by policysync. If you want to manage all InternalStage, then you can skip specifying this property. This property supports wildcards as well.
The ignore InternalStage list has precedance over manage InternalStage list.
- testdb1.schema1.InternalStage1,testdb2.InternalStage2.FileFormat2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_PIPE_LIST This property is used to set comma separated pipes Fqdn for which access control should be managed by policysync. If you want to manage all pipes, then you can skip specifying this property. This property supports wildcards as well.
The ignore pipes list has precedance over manage pipes list.
- testdb1.schema1.pipe1,testdb2.pipe2.FileFormat2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_FILE_FORMAT_LIST This property is used to set comma separated FileFormats Fqdn for which access control should be managed by policysync. If you want to manage all FileFormats, then you can skip specifying this property. This property supports wildcards as well.
The ignore FileFormats list has precedance over manage FileFormats list.
- testdb1.schema1.FileFormat1,testdb2.schema2.FileFormat2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_STREAM_LIST This property is used to set comma separated streams Fqdn for which access control should be managed by policysync. If you want to manage all streams, then you can skip specifying this property. This property supports wildcards as well.
The ignore streams list has precedance over manage streams list.
- testdb1.schema1.streams1,testdb2.schema2.streams2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_FUNCTION_LIST This property is used to set comma separated functions Fqdn for which access control should be managed by policysync. If you want to manage all functions, then you can skip specifying this property. This supports wildcards as well.
The ignore functions list has precedance over manage functions list.
- testdb1.schema1.functions1,testdb2.schema2.functions2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_SEQUENCE_LIST This property is used to set comma separated sequences Fqdn for which access control should be managed by policysync. If you want to manage all sequences, then you can skip specifying this property. This property supports wildcards as well.
The ignore sequences list has precedance over manage sequences list.
- testdb1.schema1.sequence1,testdb2.schema2.sequence2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_PROCEDURE_LIST This property is used to set comma separated pipes Fqdn for which access control should be managed by policysync. If you want to manage all procedures, then you can skip specifying this property. This property supports wildcards as well.
The ignore procedure list has precedance over manage procedures list.
- testdb1.schema1.pipe1,testdb2.pipe2.FileFormat2,sales_db*.sales*.*

Resource management

Name Type Default Required Description
SNOWFLAKE_OWNER_ROLE string No

Specifies the role that owns the resources managed by PolicySync.

  • If a value is not specified, resources are owned by the creating user. In this case, the owner of the resource will have all access to the resource.

  • If a value is specified, the owner of the resource will be changed to the specified value.

The following resource types are supported:

  • Database
  • Schemas
  • Tables
  • Views
SNOWFLAKE_HANDLE_PIPE_OWNERSHIP boolean false No

Specifies whether PolicySync changes the ownership of a pipe to the role specified by SNOWFLAKE_OWNER_ROLE.

SNOWFLAKE_MANAGE_WAREHOUSE_LIST string No

Specifies a comma-separated list of warehouse names for which PolicySync manages access control. If unset, access control is managed for all warehouses. If specified, use the following format. You can use wildcards.

An example list of warehouses might resemble the following:

testdb1warehouse,testdb2warehouse, sales_dbwarehouse*
SNOWFLAKE_MANAGE_DATABASE_LIST string No

Specifies a comma-separated list of database names for which PolicySync manages access control. If unset, access control is managed for all databases. If specified, use the following format. You can use wildcards.

An example list of databases might resemble the following: testdb1,testdb2,sales db*.

If specified, SNOWFLAKE_IGNORE_DATABASE_LIST takes precedence over this setting.

SNOWFLAKE_MANAGE_SCHEMA_LIST string No

Specifies a comma-separated list of schema names for which PolicySync manages access control. You can use wildcards.

Use the following format when specifying a schema:

<DATABASE_NAME>.<SCHEMA_NAME>

If specified, SNOWFLAKE_IGNORE_SCHEMA_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all schemas are managed:

<DATABASE_NAME>.*

The specified value, if any, is interpreted in the following ways:

  • If unset, access control is managed for all schemas.
  • If set to none no schemas are managed.
SNOWFLAKE_MANAGE_TABLE_LIST string No

Specifies a comma-separated list of table names for which PolicySync manages access control. You can use wildcards.

Use the following format when specifying a table:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

If specified, SNOWFLAKE_IGNORE_TABLE_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all matched tables are managed:

<DATABASE_NAME>.<SCHEMA_NAME>.*

The specified value, if any, is interpreted in the following ways:

  • If unset, access control is managed for all tables.
  • If set to none no tables are managed.
SNOWFLAKE_MANAGE_STREAM_LIST string No

Specifies a comma-separated list of stream names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of streams might resemble the following:

testdb1.schema1.stream1,testdb2.schema2.stream*

If unset, access control is managed for all streams.

SNOWFLAKE_MANAGE_FUNCTION_LIST string No

Specifies a comma-separated list of function names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of functions might resemble the following:

testdb1.schema1.fn1,testdb2.schema2.fn*

If unset, access control is managed for all functions.

SNOWFLAKE_MANAGE_PROCEDURE_LIST string No

Specifies a comma-separated list of procedure names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of procedures might resemble the following:

testdb1.schema1.procedureA,testdb2.schema2.procedure*

If unset, access control is managed for all procedures.

SNOWFLAKE_MANAGE_SEQUENCE_LIST string No

Specifies a comma-separated list of sequence names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of sequences might resemble the following:

testdb1.schema1.seq1,testdb2.schema2.seq*

If unset, access control is managed for all sequences.

SNOWFLAKE_MANAGE_FILE_FORMAT_LIST string No

Specifies a comma-separated list of file format names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of file formats might resemble the following:

testdb1.schema1.fileFmtA,testdb2.schema2.fileFmt*

If unset, access control is managed for all file formats.

SNOWFLAKE_MANAGE_PIPE_LIST string No

Specifies a comma-separated list of pipe names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of pipes might resemble the following:

testdb1.schema1.pipeA,testdb2.schema2.pipe*

If unset, access control is managed for all pipes.

SNOWFLAKE_MANAGE_EXTERNAL_STAGE_LIST string No

Specifies a comma-separated list of external stage names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of external stages might resemble the following:

testdb1.schema1.externalStage1,testdb2.schema2.extStage*

If unset, access control is managed for all external stages.

SNOWFLAKE_MANAGE_INTERNAL_STAGE_LIST string No

Specifies a comma-separated list of internal stages names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

An example list of internal stages might resemble the following:

testdb1.schema1.internalStage1,testdb2.schema2.intStage*

If unset, access control is managed for all internal stages.

SNOWFLAKE_IGNORE_WAREHOUSE_LIST string No

Specifies a comma-separated list of warehouse names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all warehouses are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_WAREHOUSE_LIST.

SNOWFLAKE_IGNORE_DATABASE_LIST string DEMO_DB,SNOWFLAKE,UTIL_DB,SNOWFLAKE_SAMPLE_DATA No

Specifies a comma-separated list of database names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all databases are subject to access control.

For example:

testdb1,testdb2,sales_db*

This setting supersedes any values specified by SNOWFLAKE_MANAGE_DATABASE_LIST.

SNOWFLAKE_IGNORE_SCHEMA_LIST string *.INFORMATION_SCHEMA No

Specifies a comma-separated list of schema names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all schemas are subject to access control.

For example:

testdb1.schema1,testdb2.schema2,sales_db*.sales*

This setting supersedes any values specified by SNOWFLAKE_MANAGE_SCHEMA_LIST.

SNOWFLAKE_IGNORE_TABLE_LIST string No

Specifies a comma-separated list of table names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all tables are subject to access control. Specify tables using the following format:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

This setting supersedes any values specified by SNOWFLAKE_MANAGE_TABLE_LIST.

SNOWFLAKE_IGNORE_STREAM_LIST string No

Specifies a comma-separated list of stream names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all streams are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_STREAM_LIST.

SNOWFLAKE_IGNORE_FUNCTION_LIST string No

Specifies a comma-separated list of functions names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all functions are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_FUNCTION_LIST.

SNOWFLAKE_IGNORE_PROCEDURE_LIST string No

Specifies a comma-separated list of procedures names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all procedures are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_PROCEDURE_LIST.

SNOWFLAKE_IGNORE_SEQUENCE_LIST string No

Specifies a comma-separated list of sequences names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all sequences are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_SEQUENCE_LIST.

SNOWFLAKE_IGNORE_FILE_FORMAT_LIST string No

Specifies a comma-separated list of file format names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all file formats are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_FILE_FORMAT_LIST.

SNOWFLAKE_IGNORE_PIPE_LIST string No

Specifies a comma-separated list of pipes names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all pipes are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_PIPE_LIST.

SNOWFLAKE_IGNORE_EXTERNAL_STAGE_LIST string No

Specifies a comma-separated list of external stage names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all external stages are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_EXTERNAL_STAGE_LIST.

SNOWFLAKE_IGNORE_INTERNAL_STAGE_LIST string No

Specifies a comma-separated list of internal stage names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all internal stages are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_INTERNAL_STAGE_LIST.

User, group, and role creation

Name Type Default Required Description
SNOWFLAKE_CREATE_USER boolean true No

Specifies whether PolicySync creates local users for each user in Privacera.

SNOWFLAKE_CREATE_USER_ROLE boolean true No

Specifies whether PolicySync creates local roles for each role in Privacera.

SNOWFLAKE_USER_LOGIN_NAME_USE_EMAIL boolean false No

Specifies whether PolicySync uses the user email address as the login name when creating a new user in Snowflake.

SNOWFLAKE_DEFAULT_USER_PASSWORD string Yes

Specifies the password to use when PolicySync creates new users.

SNOWFLAKE_ENTITY_ROLE_PREFIX string priv_ No
SNOWFLAKE_USER_ROLE_PREFIX string No

Specifies the prefix that PolicySync uses when creating local users. For example, if you have a user named <USER> defined in Privacera and the role prefix is priv_user_, the local role is named priv_user_<USER>.

SNOWFLAKE_GROUP_ROLE_PREFIX string No

Specifies the prefix that PolicySync uses when creating local roles. For example, if you have a group named etl_users defined in Privacera and the role prefix is prefix_, the local role is named prefix_etl_users.

SNOWFLAKE_ROLE_ROLE_PREFIX string No

Specifies the prefix that PolicySync uses when creating roles from Privacera in the Snowflake data source.

For example, if you have a role in Privacera named finance defined in Privacera and the role prefix is role_prefix_, the local role is named role_prefix_finance.

SNOWFLAKE_MANAGE_ENTITIES boolean true No
SNOWFLAKE_MANAGE_USERS boolean No

Specifies whether PolicySync maintains user membership in roles in the Snowflake data source.

SNOWFLAKE_MANAGE_GROUPS boolean No

Specifies whether PolicySync creates groups from Privacera in the Snowflake data source.

SNOWFLAKE_MANAGE_ROLES boolean No

Specifies whether PolicySync creates roles from Privacera in the Snowflake data source.

SNOWFLAKE_MANAGE_USER_LIST string No

Specifies a comma-separated list of user names for which PolicySync manages access control. You can use wildcards.

If not specified, PolicySync manages access control for all users.

If specified, SNOWFLAKE_IGNORE_USER_LIST takes precedence over this setting.

An example user list might resemble the following: user1,user2,dev_user*.

SNOWFLAKE_MANAGE_GROUP_LIST string No

Specifies a comma-separated list of group names for which PolicySync manages access control. If unset, access control is managed for all groups. If specified, use the following format. You can use wildcards.

An example list of projects might resemble the following: group1,group2,dev_group*.

If specified, SNOWFLAKE_IGNORE_GROUP_LIST takes precedence over this setting.

SNOWFLAKE_MANAGE_ROLE_LIST string No

Specifies a comma-separated list of role names for which PolicySync manages access control. If unset, access control is managed for all roles. If specified, use the following format. You can use wildcards.

An example list of projects might resemble the following: role1,role2,dev_role*.

If specified, SNOWFLAKE_IGNORE_ROLE_LIST takes precedence over this setting.

SNOWFLAKE_IGNORE_USER_LIST string No

Specifies a comma-separated list of user names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all users are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_USER_LIST.

SNOWFLAKE_IGNORE_GROUP_LIST string No

Specifies a comma-separated list of group names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all groups are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_GROUP_LIST.

SNOWFLAKE_IGNORE_ROLE_LIST string No

Specifies a comma-separated list of role names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all roles are subject to access control.

This setting supersedes any values specified by SNOWFLAKE_MANAGE_ROLE_LIST.

SNOWFLAKE_USER_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the SNOWFLAKE_USER_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_USER_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the SNOWFLAKE_USER_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_GROUP_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the SNOWFLAKE_GROUP_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_GROUP_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the SNOWFLAKE_GROUP_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_ROLE_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the SNOWFLAKE_ROLE_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_ROLE_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the SNOWFLAKE_ROLE_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

SNOWFLAKE_USER_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether PolicySync converts user names to lowercase when creating local users. If set to true, case sensitivity is preserved.

SNOWFLAKE_GROUP_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether PolicySync converts group names to lowercase when creating local groups. If set to true, case sensitivity is preserved.

SNOWFLAKE_ROLE_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether PolicySync converts role names to lowercase when creating local roles. If set to true, case sensitivity is preserved.

SNOWFLAKE_USER_NAME_CASE_CONVERSION string lower No

Specifies how user name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if SNOWFLAKE_USER_NAME_PERSIST_CASE_SENSITIVITY is set to true.

SNOWFLAKE_GROUP_NAME_CASE_CONVERSION string lower No

Specifies how group name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if SNOWFLAKE_GROUP_NAME_PERSIST_CASE_SENSITIVITY is set to true.

SNOWFLAKE_ROLE_NAME_CASE_CONVERSION string lower No

Specifies how role name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if SNOWFLAKE_ROLE_NAME_PERSIST_CASE_SENSITIVITY is set to true.

SNOWFLAKE_USER_FILTER_WITH_EMAIL boolean false No

Set this property to true if you only want to manage users who have an email address associated with them in the portal.

SNOWFLAKE_MANAGE_USER_FILTERBY_GROUP boolean false No

Specifies whether to manage only the users that are members of groups specified by SNOWFLAKE_MANAGE_GROUP_LIST. The default value is false.

SNOWFLAKE_MANAGE_USER_FILTERBY_ROLE boolean false No

Specifies whether to manage only users that are members of the roles specified by SNOWFLAKE_MANAGE_ROLE_LIST. The default value is false.

SNOWFLAKE_USER_ROLE_USE_UPPERCASE boolean false No

Specifies whether PolicySync converts a user name to uppercase when performing operations.

SNOWFLAKE_GROUP_ROLE_USE_UPPERCASE boolean false No

Specifies whether PolicySync converts a group name to uppercase when performing operations.

SNOWFLAKE_ROLE_ROLE_USE_UPPERCASE boolean false No

Specifies whether PolicySync converts a role name to uppercase when performing operations.

Grant update

Name Type Default Required Description

Column level access control

Name Type Default Required Description
SNOWFLAKE_ENABLE_COLUMN_ACCESS_EXCEPTION boolean true No

Specifies whether an access denied exception is displayed if a user does not have access to a table column and attempts to access that column.

If enabled, you must set SNOWFLAKE_ENABLE_MASKING to true.

Native masking

Name Type Default Required Description
SNOWFLAKE_ENABLE_MASKING boolean true No

Specifies whether PolicySync enables native masking policy creation functionality.

SNOWFLAKE_MASKING_POLICY_DB_NAME string No

Specifies the name of the database where PolicySync creates custom masking policies.

SNOWFLAKE_MASKING_POLICY_SCHEMA_NAME string PUBLIC No

Specifies the name of the schema where PolicySync creates all native masking policies. If not specified, the resource schema is used as the masking policy schema.

SNOWFLAKE_MASKING_POLICY_NAME_TEMPLATE string {database}{separator}{schema}{separator}{table} No

Specifies a naming template that PolicySync uses when creating native masking policies. For example, given the following values:

  • {database}: customer_db
  • {schema}: customer_schema
  • {table}: customer_data
  • {separator} _priv_

With the default naming template, the following name is used when creating a native masking policy. The {column} field is replaced by the column name.

customer_db_priv_customer_schema_priv_customer_data_{column}

Native row filter

Name Type Default Required Description
SNOWFLAKE_ENABLE_ROW_FILTER boolean true No

Specifies whether to use the data source native row filter functionality. This setting is disabled by default. When enabled, you can create row filters only on tables, but not on views.

SNOWFLAKE_ROW_FILTER_POLICY_DB_NAME string No

Specifies the name of the database where PolicySync creates native row-filter policies. If not specified, the resource database is considered the same as the row-filter policy database.

SNOWFLAKE_ROW_FILTER_POLICY_SCHEMA_NAME string PUBLIC No

Specifies the name of the schema where PolicySync creates all native row-filter policies. If not specified, the resource schema is considered the same as the row-filter policy schema.

SNOWFLAKE_ROW_FILTER_POLICY_NAME_TEMPLATE string {database}{separator}{schema}{separator}{table} No

Specifies a template for the name that PolicySync uses when creating a row filter policy. For example, given a table data from the schema schema that resides in the db database, the row filter policy name might resemble the following:

db_priv_schema_priv_data_<ROW_FILTER_ITEM_NUMBER>

View based masking/row filter

Name Type Default Required Description
SNOWFLAKE_ENABLE_VIEW_BASED_ROW_FILTER boolean false No

Specifies whether to use secure view based row filtering. The default value is false.

While Snowflake supports native filtering, PolicySync provides additional functionality that is not available natively. Enabling this setting is recommended.

SNOWFLAKE_ENABLE_VIEW_BASED_MASKING boolean false No

Specifies whether to use secure view based masking. The default value is false.

SNOWFLAKE_SECURE_VIEW_SCHEMA_NAME_PREFIX string No

Specifies a prefix string to apply to a secure schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view schema name for a schema named example1 is dev_example1.

SNOWFLAKE_SECURE_VIEW_SCHEMA_NAME_POSTFIX string No

Specifies a postfix string to apply to a secure view schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a schema named example1 is example1_dev.

SNOWFLAKE_SECURE_VIEW_NAME_PREFIX string No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view name for a table named example1 is dev_example1.

SNOWFLAKE_SECURE_VIEW_NAME_POSTFIX string _SECURE No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a table named example1 is example1_dev.

SNOWFLAKE_SECURE_VIEW_SCHEMA_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a schema name. For example, if a schema is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

SNOWFLAKE_SECURE_VIEW_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a table or view name. For example, if the table is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

SNOWFLAKE_SECURE_VIEW_CREATE_FOR_ALL boolean false No

Specifies whether to create secure views for all tables and views that are created by users. If enabled, PolicySync creates secure views for resources regardless of whether masking or filtering policies are enabled.

string No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same name as the table database name.

For example, if the prefix is priv_, then the secure view name for a database named example1 is priv_example1.

string No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same name as the table database name.

For example, if the postfix is _sec, then the secure view name for a database named example1 is example1_sec.

string No

Specifies a suffix to remove from a database name. For example, if the database is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

Masking/Row filter policy name separator

Name Type Default Required Description
SNOWFLAKE_POLICY_NAME_SEPARATOR string _PRIV_ No

Specifies a string to use as part of the name of native row filter and masking policies.

SNOWFLAKE_ROW_FILTER_ALIAS_TOKEN string obj No

Specifies an identifier that PolicySync uses to identify columns from the main table and parse each correctly.

Masked Value for Masking

Name Type Default Required Description
SNOWFLAKE_MASKED_NUMBER_VALUE integer 0 No

Specifies the default masking value for numeric column types.

SNOWFLAKE_MASKED_DOUBLE_VALUE integer 0 No

Specifies the default masking value for DOUBLE column types.

SNOWFLAKE_MASKED_TEXT_VALUE string <MASKED> No

Specifies the default masking value for text and string column types.

POLICYSYNC_V2_MASKED_DATE_VALUE string No

Specifies the default masking value for date column types.

PEG integration

Name Type Default Required Description
SNOWFLAKE_PEG_FUNCTION_DB string No

Specifies the name of the database where the PEG encryption functions reside.

SNOWFLAKE_PEG_FUNCTION_SCHEMA string public No

Specifies the schema name where the PEG encryption functions reside.

Load sql queries from system config json file

Name Type Default Required Description
string load_md No

Specifies the method that PolicySync uses to load roles from Snowflake. The following methods are supported:

load_md: Use metadata queries

string load_md No

Specifies how PolicySync loads users from Snowflake. The following values are valid:

  • load
  • load_db
SNOWFLAKE_LOAD_RESOURCES_KEY string load_md_from_account_columns No

Specifies how PolicySync loads resources from Snowflake. The following values are allowed:

  • load_md: Load the resources using metadata queries.

  • load_md_from_account_columns: Load resources by directly running SHOW QUERIES on the account. This mode is preferred when you want to manage an entire Snowflake account.

  • load_md_from_database_columns: Load the resources by directly running SHOW QUERIES only on managed databases. This mode is preferred when you want to manage only a few databases.

string No

Specifies the method that PolicySync uses to load existing grants from Snowflake. The following methods are supported:

load_md: Use metadata queries

string No

Specifies the method that PolicySync uses to load access audit information.

The following values are valid:

  • load: Use SQL queries The following values are valid:

Audit integration

Name Type Default Required Description
SNOWFLAKE_AUDIT_ENABLE boolean true Yes

Specifies whether Privacera fetches access audit data from the data source.

SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE boolean true No

Specifies whether to enable simple auditing. When enabled, PolicySync gathers the following audit information from the database:

  • RequestData (query text)
  • AccessResult (execute status)
  • AccessType (query type)
  • User (username)
  • ResourcePath (database_name.schema_name)
  • EventTime (query time)
  • AclEnforcer (connector name)

If you enabled this setting, do not enable SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE.

SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE boolean false No

Specifies whether to enable advanced auditing. When enabled, PolicySync gathers the following audit information from the database:

  • AccessResult (execute status)
  • AccessType (query type)
  • User (username)
  • ResourcePath (database_name.schema_name.column_names)
  • EventTime (query time)
  • AclEnforcer (connector name)

If you enabled this setting, do not enable SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE.

SNOWFLAKE_AUDIT_ENABLE_RESOURCE_FILTER boolean No

Specifies whether PolicySync filters access audit information by managed resources, such as databases, schemas, and so forth.

SNOWFLAKE_AUDIT_INITIAL_PULL_MINUTES string 30 No

Specifies the initial delay, in minutes, before PolicySync retrieves access audits from Snowflake.

SNOWFLAKE_AUDIT_SOURCE_ADVANCE_DB_NAME string PRIVACERA_ACCESS_LOGS_DB No

Specifies the database that PolicySync retrieves access audits from. This setting applies only if you set SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE to true.

Load intervals

Name Type Default Required Description
SNOWFLAKE_RESOURCE_SYNC_INTERVAL integer 60 No

Specifies the interval in seconds for PolicySync to wait before checking for new resources or changes to existing resources.

SNOWFLAKE_PRINCIPAL_SYNC_INTERVAL integer 420 No

Specifies the interval in seconds for PolicySync to wait before reconciling principals with those in the data source, such as users, groups, and roles. When differences are detected, PolicySync updates the principals in the data source accordingly.

SNOWFLAKE_PERMISSION_SYNC_INTERVAL integer 60 No

Specifies the interval in seconds for PolicySync to wait before reconciling Apache Ranger access control policies with those in the data source. When differences are detected, PolicySync updates the access control permissions on data source accordingly.

SNOWFLAKE_AUDIT_SYNC_INTERVAL integer 30 No

Specifies the interval in seconds to elapse before PolicySync retrieves access audits and saves the data in Privacera.

Miscellaneous

Name Type Default Required Description
string No

Specifies the name of a partner application to connect to through JDBC. This setting is for Snowflake partner use only.

Object Permission Mapping

For more information about object permission mapping, see ​Snowflake Documentation​​.

Object Supported Permissions Description
Global CreateWarehouse
CreateDatabase
Enables creating a new virtual warehouse.
​Enables creating a new database in the system.
Warehouse UseWarehouse
Operate
Monitor
Modify
Enables using a virtual warehouse and, as a result, executing queries on the warehouse.
Enables changing the state of a warehouse (stop, start, suspend, resume).
Enables viewing current and past queries executed on a warehouse as well as usage statistics on that warehouse.
Enables altering any properties of a warehouse, including changing its size.
Database UseDB
CreateSchema
Enables using a database, including returning the database details in the SHOW DATABASES command output.
Enables creating a new schema in a database, including cloning a schema.
Schema UseSchema
CreateTable
CreateProcedure
CreateFunction
CreateStream
CreateSequence
CreateFileFormat
CreateStage
CreatePipe
CreateExternalTable
Enables using a schema, including returning the schema details in the SHOW SCHEMAS command output.
Enables creating a new table in a schema, including cloning a table.
Enables creating a new stored procedure in a schema.
Enables creating a new UDF or external function in a schema.
Enables creating a new stream in a schema, including cloning a stream.
Enables creating a new sequence in a schema, including cloning a sequence.
Enables creating a new file format in a schema, including cloning a file format.
Enables creating a new stage in a schema, including cloning a stage.
Enables creating a new pipe in a schema.
Enables creating a new external table in a schema.
Table Select
Insert
Update
Delete
Truncate
References
Enables executing a SELECT statement on a table.
Enables executing an INSERT command on a table.
Enables executing an UPDATE command on a table..
Enables executing a DELETE command on a table.
Enables executing a TRUNCATE TABLE command on a table.
Enables referencing a table as the unique/primary key table for a foreign key constraint.
View Select Enables executing a SELECT statement on a view.
Procedure Usage Enables calling a stored procedure.
Function Usage Enables calling a function.
Stream Select Enables executing a SELECT statement on a stream.
File_Format Usage Enables using a file format in a SQL statement.
Sequence Usage Enables using a sequence in a SQL statement.
Internal_Stage Read
Write
Enables performing any operations that require reading from an internal stage (GET, LIST, COPY INTO <table>);
Enables performing any operations that require writing to an internal stage (PUT, REMOVE, COPY INTO <location>);
External_Stage Usage Enables using an external stage object in a SQL statement.
Pipe Operate
Monitor
Enables viewing details for the pipe (using DESCRIBE PIPE or SHOW PIPES), pausing or resuming the pipe, and refreshing the pipe.
Enables viewing details for the pipe (using DESCRIBE PIPE or SHOW PIPES).

Changing the Owner of a Table

By default, Privacera’s PolicySync changes the ownership of all resources (databases and tables) to Privacera’s Admin Roles. The reasoning behind this is that there must be a single entity to manage the privileges for the resource. If the owner is not changed, then the user who created the table could also modify the privileges. This could cause inconsistencies in the privileges and even lead to cases where the owner might involuntarily drop security policies like column masking/row-level filtering or provide excessive permissions to unauthorized users.

In Snowflake, there is a limitation in its privilege model, where DROP privileges can’t be given to specific users. Instead, to drop tables, the user must be the owner of the table or must have an Account Admin Role.

When any table is created by a user in Snowflake, the owner defaults to the database role of the user who created it. After the table is created, Privacera will forcefully change the role to the PRIVACERA_POLICYSYNC_ROLE role. And all those users associated with this role will become the owners of the table.

To change the ownership, do the following:

  1. Edit the following file:

    vi config/custom-vars/vars.PolicySync.snowflake.yml
    
  2. Add the SNOWFLAKE_OWNER_ROLE property and enter the PRIVACERA_POLICYSYNC_ROLE role.

    SNOWFLAKE_OWNER_ROLE: "PRIVACERA_POLICYSYNC_ROLE"
    

    If you do not want to change the ownership, leave it blank.

  3. Run the update.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update
    

    Note

    When a new object is created by a managed user/group/role and is detected by PolicySync, the PolicySync will change the ownership of that object, as specified in the SNOWFLAKE_OWNER_ROLE property.