Skip to main content

Privacera Documentation

Connect Snowflake to PrivaceraCloud

This topic describes how to connect the Snowflake application to the PrivaceraCloud using the AWS and Azure platforms.

Snowflake PolicySync prerequisites: users, roles, warehouse, permissions, and UDFs

Before configuring Snowflake, you must first manually create the Snowflake warehouse, database, users, and roles required by PolicySync. All of this can be accomplished by manually executing SQL queries.

Note

Log in to Snowflake as a user with ACCOUNTADMIN privileges.

Creating PolicySync role

The PRIVACERA_POLICYSYNC_ROLE role, which we will create in this step, will be used in the SNOWFLAKE_ROLE_TO_USE property when configuring Snowflake with Privacera Manager.

  1. Drop a role.

    DROP ROLE IF EXISTS "PRIVACERA_POLICYSYNC_ROLE";

  2. Create a role.

    CREATE ROLE IF NOT EXISTS "PRIVACERA_POLICYSYNC_ROLE";

  3. Grant this role permission to users to create/update/delete roles.

    GRANT ROLE USERADMIN TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

  4. Grant this permission to the role, allowing them to provide grants/revokes privileges on user/roles to create warehouse/database on account.

    GRANT ROLE SYSADMIN TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

  5. Grant this permission to the role so that it can manage grants for snowflake resources.

    GRANT MANAGE GRANTS ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

  6. Grant this permission to the role so that it can create native Masking policies.

    GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

  7. Grant this permission to the role so that it can create native row filter policies.

    GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Creating a warehouse

The PRIVACERA_POLICYSYNC_WH warehouse, which we will create in this step, will be used in the SNOWFLAKE_WAREHOUSE_TO_USE property when configuring Snowflake with Privacera Manager.

Create a warehouse for PolicySync. Change the warehouse size according to deployment.

CREATE WAREHOUSE IF NOT EXISTS "PRIVACERA_POLICYSYNC_WH" WITH WAREHOUSE_SIZE='XSMALL'WAREHOUSE_TYPE='STANDARD'AUTO_SUSPEND=600AUTO_RESUME= TRUE  MIN_CLUSTER_COUNT=1MAX_CLUSTER_COUNT=1SCALING_POLICY='ECONOMY';

Granting role permission to read access audits

To get read access audit permission on the Snowflake database, follow the steps below.

  1. Grant warehouse usage access so we can query the snowflake database and get the Access Audits.

    GRANT USAGE ON WAREHOUSE "PRIVACERA_POLICYSYNC_WH" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

  2. Grant our role PRIVACERA_POLICYSYNC_ROLE to read Access Audits in the snowflake database.

    GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Creating database for Privacera UDFs

The database name PRIVACERA_DB will be used in the SNOWFLAKE_JDBC_DB property when configuring Snowflake with Privacera Manager.

  1. This step is optional. If you already have the database and want to use it, you can skip this step.

    CREATE DATABASE IF NOT EXISTS "PRIVACERA_DB";

  2. Grant our role PRIVACERA_POLICYSYNC_ROLE database access so that we can create UDFs in the database.

    GRANT ALL ON DATABASE "PRIVACERA_DB" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

GRANT ALL ON ALL SCHEMAS IN DATABASE "PRIVACERA_DB" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Creating user

The user which we will create in this step will be used in the SNOWFLAKE_JDBC_USERNAME and SNOWFLAKE_JDBC_PASSWORD properties when configuring Snowflake with Privacera Manager.

  1. Create a user

    CREATE USER IF NOT EXISTS "PRIVACERA_POLICYSYNC_USER"PASSWORD='<PLEASE_CHANGE>'MUST_CHANGE_PASSWORD=FALSE DEFAULT_WAREHOUSE="PRIVACERA_POLICYSYNC_WH"DEFAULT_ROLE="PRIVACERA_POLICYSYNC_ROLE";

  2. Grant the user the PRIVACERA_POLICYSYNC_ROLE role.

    GRANT ROLE "PRIVACERA_POLICYSYNC_ROLE" TO USER "PRIVACERA_POLICYSYNC_USER";

Masking and row level filtering

To run the Masking and Row Level Filter, the following permissions must be granted to each database managed by PolicySync. <DATABASE_NAME> must be replaced with the specific value.

GRANT ALL ON DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON ALL SCHEMAS IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON FUTURE SCHEMAS IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON ALL TABLES IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON FUTURE TABLES IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON ALL VIEWS IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT ALL ON FUTURE VIEWS IN DATABASE "<DATABASE_NAME>" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Using reduced permissions for existing PolicySync

If Privacera PolicySync is currently configured with ACCOUNTADMIN privileges, the steps below must be completed as an ACCOUNTADMIN in order for PolicySync to work with the reduced permissions specified in the previous sections.

  1. Drop UDFs.

    DROP FUNCTION IF EXISTS "<DATABASE_NAME>"."PUBLIC".ThrowColumnAccessException(string);

    Note

    • For PolicySync versions 4.7 or earlier,

      <DATABASE_NAME> must be replaced with the value provided in configuration jdbc.db.

    • For PolicySync versions 5.0 or later:

      <DATABASE_NAME> must be replaced with the value provided in configuration ranger.policysync.connector.snowflake.masking.functions.db.name.

  2. Drop row level filter access policies.

    DROP ROW ACCESS POLICY IF EXISTS "<DATABASE_NAME>"."<SCHEMA_NAME>"."<ROW_ACCESS_POLICY_NAME>";

    Note

    • For PolicySync version 4.7:

      Row Level Filter access policies must be deleted in all databases and schemas managed by PolicySync.

      The following is the format of a Row Level Filter access policy name: :

      {database}_{schema}_{table}_row_filter_policy.

      For example, "db1_sch1_tbl1_row_filter_policy"

    • For PolicySync versions 5.0 or later:

      If PolicySync is configured to create Row Level Filter access policies in a specific database and schema (see below), Row Level Filter access policies must be deleted from the specified database and schema.

      • ranger.policysync.connector.snowflake.row.filter.policy.db.name

      • ranger.policysync.connector.snowflake.row.filter.policy.schema.name

      Or else, Row Level Filter access policies in all databases and schemas managed by PolicySync must be deleted.

      The following is the format of a Row Level Filter access policy name: :

      {database}{separator}{schema}{separator}{table}.

      For example, "db1_PRIV_sch1_PRIV_tbl1".

    Use the following command to list Row Level Filter access policies:

    SHOW ROW ACCESS POLICIES;

  3. Drop masking policies.

    DROP MASKING POLICY IF EXISTS "<DATABASE_NAME>"."<SCHEMA_NAME>"."<MASKING_POLICY_NAME>";

    • The following is the format of a Masking policy name:

      {table}{separator}{column}.

      For example, "tbl1_priv_col1"

    •  

      If PolicySync is configured to create Masking policies in a specific database and schema (see below), Masking policies must be deleted from the specified database and schema.

      • ranger.policysync.connector.snowflake.masking.policy.db.name

      • ranger.policysync.connector.snowflake.masking.policy.schema.name

      Or else, Masking policies in all databases and schemas managed by PolicySync must be deleted.

      The following is the format of a Masking policy name:

      {database}{separator}{schema}{separator}{table}{separator}{column}.

      For example, "db1_PRIV_sch1_PRIV_tbl1_PRIV_col1".

    Use the following command to list all masking policies:

    SHOW MASKING POLICIES;
Procedure to connect Snowflake to PrivaceraCloud

  1. Go to Settings > Applications.

  2. On the Applications screen, select Snowflake.

  3. Select the platform type (AWS or Azure) on which you want to configure the Snowflake application.

  4. Enter the application Name and Description, and then click Save.

    You can see Privacera Access Management and Data Discovery with toggle buttons.

    Note

    If you don't see Data Discovery in your application, enable it in Settings > Account > Discovery. For more information, see About the Account page on PrivaceraCloud.

Enable Privacera Access Management for Snowflake

  1. Click the toggle button to enable the Privacera Access Management for your application.

  2. On the BASIC tab, enter the values in the given fields and click Save. For property details and description, see table below:

    Note

    Make sure that the other properties are advanced and should be modified in consultation with Privacera.

    Basic fields

    Table 31. Basic fields

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

    Enable Use Key Pair Authentication

    boolean

    false

    Yes

    Specifies whether PolicySync uses key-pair authentication.

    Enable this setting to true to enable key pair authentication.

    Snowflake JDBC private key

    string

    No

    Specifies the contents of the private key file to use with Snowflake. For example:

    -----BEGIN ENCRYPTED PRIVATE KEY----- MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyCppzOwECAggABIIEyLiGSpeeGSe3xHP1wHLjfCYycUPennlX2bd8yX8xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lMWXbL... -----END ENCRYPTED PRIVATE KEY-----

    Snowflake JDBC private key password

    string

    No

    Specifies the password for the private key. If the private key does not have a password, do not specify this setting.

    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.

    Snowflake Resource Owner

    string

    No

    Specifies the role that owns the resources managed by PolicySync. You must ensure that this user exists as PolicySync does not create this user.

    • 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

    Warehouses to set access control policies

    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. Names are case-sensitive.

    An example list of warehouses might resemble the following:

    testdb1warehouse,testdb2warehouse, sales_dbwarehouse*

    Databases to set access control policies

    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. Names are case-sensitive.

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

    If specified, Databases to be ignored by access policy takes precedence over this setting.

    Default password for new snowflake user

    string

    Yes

    Specifies the password to use when PolicySync creates new users.

    Enable policy enforcements and user/group/role management

    boolean

    true

    No

    Specifies whether PolicySync performs grants and revokes for access control and creates, updates, and deletes queries for users, groups, and roles. The default value is true.

    Database name where masking function for column access control will be created

    string

    No

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

    Enable access audits

    boolean

    true

    Yes

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

    Enable simple audits

    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 Enable advance audits.

    Enable advance audits

    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 Enable simple audits.



    Advanced fields

    Table 32. Advanced fields

    Field name

    Type

    Default

    Required

    Description

    Schemas to set access control policies

    string

    No

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

    Use the following format when specifying a schema:

    <DATABASE_NAME>.<SCHEMA_NAME>

    If specified, Schemas to be ignored by access policy 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.

    Tables to set access control policies

    string

    No

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

    Use the following format when specifying a table:

    <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

    If specified, 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.

    Stream to set access control policies

    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.

    Functions to set access control policies

    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.

    Procedures to set access control policies

    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.

    Sequences to set access control policies

    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.

    FileFormat to set access control policies

    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.

    Pipes to set access control policies

    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.

    ExternalStage to set access control policies

    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.

    InternalStage to set access control policies

    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.

    Warehouses to be ignored by access policy

    string

    No

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

    This setting supersedes any values specified by Warehouses to set access control policies.

    Databases to be ignored by access policy

    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. Names are case-sensitive. If not specified, all databases are subject to access control.

    For example:

    testdb1,testdb2,sales_db*

    This setting supersedes any values specified by Databases to set access control policies.

    Schemas to be ignored by access policy

    string

    *.INFORMATION_SCHEMA

    No

    Specifies a comma-separated list of schema names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. 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 Schemas to set access control policies.

    Create user in snowflake by policysync

    boolean

    true

    No

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

    Create user role in snowflake by policysync

    boolean

    true

    No

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

    Enable use of email as login for snowflake

    boolean

    false

    No

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

    Prefix of snowflake roles for portal users

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

    Prefix of snowflake roles for portal groups

    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.

    Prefix of snowflake roles for portal roles

    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.

    Manage users form portal

    boolean

    No

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

    Manage group form portal

    boolean

    No

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

    Manage role form portal

    boolean

    No

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

    Users to set access control policy

    string

    No

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

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

    If specified, Users to be ignored by access control policy takes precedence over this setting.

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

    Groups to set access control policy

    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. Names are case-sensitive.

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

    If specified, Groups to be ignored by access control policy takes precedence over this setting.

    Roles to set access control policy

    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. Names are case-sensitive.

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

    If specified, Roles to be ignored by access control policy takes precedence over this setting.

    Users to be ignored by access control policy

    string

    No

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

    This setting supersedes any values specified by Users to set access control policy.

    Groups to be ignored by access control policy

    string

    No

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

    This setting supersedes any values specified by Groups to set access control policy.

    Roles to be ignored by access control policy

    string

    No

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

    This setting supersedes any values specified by Roles to set access control policy.

    Regex to find special characters in user names

    string

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

    No

    Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the String to replace with the special characters found in user names setting.

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

    String to replace with the special characters found in user names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in user names setting.

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

    Regex to find special characters in group names

    string

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

    No

    Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the String to replace with the special characters found in group names setting.

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

    String to replace with the special characters found in group names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in group names setting.

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

    Regex to find special characters in role names

    string

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

    No

    Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the String to replace with the special characters found in role names setting.

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

    String to replace with the special characters found in role names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in role names setting.

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

    Persist case sensitivity of user names

    boolean

    false

    No

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

    Persist case sensitivity of group names

    boolean

    false

    No

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

    Persist case sensitivity of role names

    boolean

    false

    No

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

    Set access control policies only on the users from managed groups

    boolean

    false

    No

    Specifies whether to manage only the users that are members of groups specified by Groups to set access control policy. The default value is false.

    Set access control policies only on the users/groups from managed roles

    boolean

    false

    No

    Specifies whether to manage only users that are members of the roles specified by Roles to set access control policy. The default value is false.

    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 Enforce Snowflake Native Masking to true.

    Enforce Snowflake Native Masking

    boolean

    true

    No

    Specifies whether PolicySync enables native masking policy creation functionality.

    Enforce Snowflake Native 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.

    Enforce row filter policies using secure views

    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.

    Enforce masking policies using secure views

    boolean

    false

    No

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

    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.

    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.

    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.

    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.

    Create secure view for all tables/views

    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.

    Default masked value for numeric datatype columns

    integer

    0

    No

    Specifies the default masking value for numeric column types.

    Default masked value for text/varchar datatype columns

    string

    <MASKED>

    No

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



    Custom fields

    Table 33. Custom fields

    Canonical name

    Type

    Default

    Description

    jdbc.maximum.pool.size

    integer

    15

    Specifies the maximum size for the JDBC connection pool.

    jdbc.min.idle.connection

    integer

    3

    Specifies the minimum size of the JDBC connection pool.

    jdbc.leak.detection.threshold

    string

    900000L

    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.

    handle.pipe.ownership

    boolean

    false

    Specifies whether PolicySync changes the ownership of a pipe to the role specified by Snowflake Resource Owner.

    ignore.table.list

    string

    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. Names are case-sensitive. Specify tables using the following format:

    <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

    This setting supersedes any values specified by Tables to set access control policies.

    ignore.stream.list

    string

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

    This setting supersedes any values specified by Stream to set access control policies.

    ignore.function.list

    string

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

    This setting supersedes any values specified by Functions to set access control policies.

    ignore.procedure.list

    string

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

    This setting supersedes any values specified by Procedures to set access control policies.

    ignore.sequence.list

    string

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

    This setting supersedes any values specified by Sequences to set access control policies.

    ignore.file_format.list

    string

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

    This setting supersedes any values specified by FileFormat to set access control policies.

    ignore.pipe.list

    string

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

    This setting supersedes any values specified by Pipes to set access control policies.

    ignore.external_stage.list

    string

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

    This setting supersedes any values specified by ExternalStage to set access control policies.

    ignore.internal_stage.list

    string

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

    This setting supersedes any values specified by InternalStage to set access control policies.

    user.name.case.conversion

    string

    lower

    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 Persist case sensitivity of user names is set to true.

    group.name.case.conversion

    string

    lower

    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 Persist case sensitivity of group names is set to true.

    role.name.case.conversion

    string

    lower

    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 Persist case sensitivity of role names is set to true.

    user.filter.with.email

    boolean

    false

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

    User.role.use.upper.case

    boolean

    false

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

    Group.role.use.upper.case

    boolean

    false

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

    Role.role.use.upper.case

    boolean

    false

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

    perform.grant.updates.batch

    string

    Specifies whether PolicySync applies grants and revokes in batches. If enabled, this behavior improves overall performance of applying permission changes.

    perform.grant.updates.max.retry.attempts

    integer

    2

    Specifies the maximum number of attempts that PolicySync makes to execute a grant query if it is unable to do so successfully. The default value is 2.

    enable.privileges.batching

    boolean

    false

    Specifies whether PolicySync applies privileges described in Access Manager policies.

    masking.policy.db.name

    string

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

    masking.policy.schema.name

    string

    PUBLIC

    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.

    masking.policy.name.template

    string

    {database}{separator}{schema}{separator}{table}

    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}

    row.filter.policy.db.name

    string

    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.

    row.filter.policy.schema.name

    string

    PUBLIC

    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.

    row.filter.policy.name.template

    string

    {database}{separator}{schema}{separator}{table}

    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>

    secure.view.schema.name.remove.suffix.list

    string

    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.

    secure.view.name.remove.suffix.list

    string

    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.

    secure.view.database.name.prefix

    string

    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.

    secure.view.database.name.postfix

    string

    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.

    secure.view.database.name.remove.suffix.list

    string

    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.

    policy.name.separator

    string

    _PRIV_

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

    row.filter.alias.token

    string

    obj

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

    masked.double.value

    integer

    0

    Specifies the default masking value for DOUBLE column types.

    masked.date.value

    string

    Specifies the default masking value for date column types.

    peg.functions.db.name

    string

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

    peg.functions.schema.name

    string

    public

    Specifies the schema name where the PEG encryption functions reside.

    load.roles

    string

    load_md

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

    load_md: Use metadata queries

    load.users

    string

    load_md

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

    • load

    • load_db

    load.resources

    string

    load_md_from_account_columns

    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.

    load.policies

    string

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

    load_md: Use metadata queries

    load.audits

    string

    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.enable.resource.filter

    boolean

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

    audit.initial.pull.min

    string

    30

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

    custom.audit.db.name

    string

    PRIVACERA_ACCESS_LOGS_DB

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

    sync.interval.sec

    integer

    60

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

    sync.serviceuser.interval.sec

    integer

    420

    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.

    sync.servicepolicy.interval.sec

    integer

    60

    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.

    audit.interval.sec

    integer

    30

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

    jdbc.application

    string

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



  3. On the ADVANCED tab, you can add custom properties.

  4. Using the IMPORT PROPERTIES button, you can browse and import application properties.

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

Enable Data Discovery for Snowflake

Click the toggle button to enable the Data Discovery for your application.

  1. On the BASIC tab, enter values in the following fields.

    • JDBC URL

    • JDBC Username 

    • JDBC Password

  2. On the ADVANCED tab, you can add custom properties.

    You need to configure some advanced properties for the application where all the data to be scanned are stored. For more information, see General process for configuring an application.

  3. Using the IMPORT PROPERTIES button, you can browse and import application properties.

  4. Click the TEST CONNECTION button to check if the connection is successful, and then click Save.

Add Data Source

To add a resources using this connection as Privacera Discovery targets, see Privacera Discovery scan targets.