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#

Property Mandatory Description Default Value Example
SNOWFLAKE_V2_ENABLE Yes Property to Enable/Disable Snowflake.
Fill-in SNOWFLAKE_MANAGE_WAREHOUSE_LIST and SNOWFLAKE_MANAGE_DATABASE_LIST before enabling this to true. 		  true
SNOWFLAKE_JDBC_URL Yes This property is used to set the JDBC URL, which can be used to connect to the Snowflake server.
JDBC URL must follow below convention: jdbc:snowflake:/example.com/?warehouse=WAREHOUSE TO USE>&role=ROLE TO USE> 		jdbc:snowflake://testsnowflake.snowflakecomputing.com/?warehouse=PRIVACERA_POLICYSYNC_WH&role=PRIVACERA_SYNC_ROLE
SNOWFLAKE_JDBC_DB Yes The database to store masking policies. PRIVACERA_DB
SNOWFLAKE_JDBC_USERNAME Yes This property is used to specify the JDBC username that will be used to connect to Snowflake. PRIVACERA_SYNC
SNOWFLAKE_JDBC_PASSWORD Yes This property is used to specify the JDBC username password that will be used to connect to Snowflake.
SNOWFLAKE_WAREHOUSE_TO_USE Yes This property is used to specify which JDBC warehouse will be used to establish a connection in order to run SQL queries on Snowflake. PRIVACERA_POLICYSYNC_WH
SNOWFLAKE_ROLE_TO_USE Yes This property is used to specify the Snowflake role that will be used to run SQL queries on Snowflake. PRIVACERA_SYNC_ROLE
SNOWFLAKE_DEFAULT_USER_PASSWORD Yes This property is used to specify the password that will be used by any new user created by PolicySync in Snowflake. welcome1
SNOWFLAKE_OWNER_ROLE Yes This property specifies who owns all of the PolicySync-managed resources.
The specified role will become the owner of all managed resources and will have full control over them.
We support changing the owner of a database, schema, tables, and views.
In general, the value must be the same as the SNOWFLAKE_ROLE_TO_USE property value to be used to execute SQL queries.

Note: If the owner role is left blank, an ownership will not change, and users who create tables/views or other objects will be the owner of those objects, and PolicySync will not be able to control access to those objects.

SNOWFLAKE_MANAGE_WAREHOUSE_LIST Yes This property specifies a comma-separated list of warehouses for which PolicySync manages access control.
If you want to manage all warehouses, you can skip this property.
This also works with wildcards.
The ignore warehouses list takes precedence over the manage warehouses list.
For example, testdb1warehouse, testdb2warehouse, sales dbwarehouse*.
 dev_,qa_
SNOWFLAKE_MANAGE_DATABASE_LIST Yes This property specifies a comma-separated list of database names for which PolicySync manages access control.
If you want to manage all databases, you can skip this property.
This also accepts wildcards.
The manage database list takes precedence over the ignore database list.
For example, testdb1, testdb2, sales db*
 customer,sales
SNOWFLAKE_MANAGE_SCHEMA_LIST This property specifies a comma-separated list of schema FQDN for which PolicySync manages access control.
Enter the value for the property in the following:
{database_name}.{schema_name}
If the value is kept blank, then all schemas will be managed.
If the value is none, then no schemas will be managed.
If the value is specified as {database_name}.*, then all schemas will be managed.
 testdb1.schema1,testdb2.schema2,sales_db*.sales*
SNOWFLAKE_MANAGE_TABLE_LIST This property specifies a comma-separated list of table FQDN for which PolicySync manages access control.
Enter the value for the property in the following:
{database_name}.{schema_name}.{table_name}
If the value is kept blank, then all tables will be managed.
If the value is none, then no tables will be managed.
If the value is specified as {database_name}.{schema_name}.*, then all tables will be managed.
 testdb1.schema1.table1,testdb2.schema2.view2,sales_db*.sales*.*
SNOWFLAKE_MANAGE_ENTITIES Yes Enable/Disable Manage User/Group/Role.
Fill-in SNOWFLAKE_MANAGE_WAREHOUSE_LIST and SNOWFLAKE_MANAGE_DATABASE_LIST before enabling this to true 		true
SNOWFLAKE_GRANT_UPDATES Yes This property determines whether actual grant/revoke and create/update/delete queries for user/group/role must be run on snowflake. true true
SNOWFLAKE_AUDIT_ENABLE Yes This property enables access audit retrieval from Snowflake. true  
SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE
SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE

Both the properties are optional. Uncomment them and add values only if required.

Enable the Audit Setup based on your snowflake account settings.

When you enable SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE property, the following expected data is displayed:
- RequestData (query text)
- AccessResult (execute status)
- AccessType (query type)
- User (username)
- ResourcePath (database_name.schema_name)
- EventTime (query time)
- AclEnforcer (connector name)

When you enable SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE property, the following expected data is displayed:
- AccessResult (execute status)
- AccessType (query type)
- User (username)
- ResourcePath (database_name.schema_name.column_names)
- EventTime (query time)
- AclEnforcer (connector name)

SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE: "true"
SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE: "false"
SNOWFLAKE_MANAGE_ENTITY_PREFIX Put the prefix for user/group/roles to be managed, so only user/group/roles with specified prefixes will be managed.
Keep it commented to manage all user/group/roles present in Ranger.
For eg.
Frank user you can set this value as fr_,
Sally user you can set value as sa_ 		fr_*
SNOWFLAKE_ENTITY_ROLE_PREFIX Set the prefixes for roles to be created in the database.
For eg.
Frank user you can set this value as fr_
Sally user you can set value as sa_ 		fr_
SNOWFLAKE_MANAGE_USER_LIST This property specifies a comma-separated list of user names for which PolicySync manages access control.
If you want to manage all users, you can skip this property.
This also works with wildcards.
The ignore users list takes precedence over the manage users list.
For example, user1,user2,dev_user**
 "" user1,user2,dev_user*
SNOWFLAKE_MANAGE_GROUP_LIST This property specifies a comma-separated list of group namesN for which PolicySync manages access control.
If you want to manage all groups, you can skip this property.
This also works with wildcards.
The ignore group list takes precedence over the manage groups list.
For example, group1,group2,dev_group**
 "" group1,group2,dev_group*
SNOWFLAKE_MANAGE_ROLE_LIST This property specifies a comma-separated list of role names for which PolicySync manages access control.
If you want to manage all roles, you can skip this property.
This also accepts wildcards.
The manage role list takes precedence over the ignore role list.
For example, role1,role2,dev_role**
 "" role1,role2,dev_role*
SNOWFLAKE_IGNORE_USER_LIST This property specifies a comma-separated list of user names for which PolicySync does not manage access control.
If you do not want to ignore any users, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of users to manage.
For example, user1,user2,dev_user** 		"" user1,user2,dev_user*
SNOWFLAKE_IGNORE_GROUP_LIST This property specifies a comma-separated list of group names for which PolicySync does not manage access control.
If you do not want to ignore any groups, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of manage groups.
For example, group1,group2,dev_group** 		"" group1,group2,dev_group*
SNOWFLAKE_IGNORE_ROLE_LIST This property specifies a comma-separated list of role names for which PolicySync does not manage access control.
If you do not want to ignore any roles, you can leave this property blank.
This also works with wildcards.
This takes precedence over the manage roles list.
For example, role1,role2,dev role* 		"" role1,role2,dev_role*
SNOWFLAKE_MANAGE_USER_FILTERBY_GROUP Set this property to true if you only want to manage users who belong to the groups specified in the SNOWFLAKE_MANAGE_GROUP_LIST property. false false
SNOWFLAKE_MANAGE_GROUPS This property controls whether PolicySync creates roles in Snowflake for groups retrieved from Ranger. POLICYSYNC_V2_MANAGE_ENTITIES
SNOWFLAKE_ENABLE_ROW_FILTER Set this property to enable row-level filter policy creation functionality in PolicySync. true true
SNOWFLAKE_ENABLE_VIEW_BASED_MASKING Set this property to enable view-level masking policy creation functionality in PolicySync. false false
SNOWFLAKE_LOAD_RESOURCES_KEY This property controls which method to be used to load resources from Snowflake.

  • load_md - It loads resources from Snowflake using a top-down resource approach; that is, it loads the database first, followed by schemas, tables, and columns. This mode is only for development purposes.
  • load_md_from_database_columns - It loads all databases first, then loads all child resources (schema, tables, and views) if the database is managed by PolicySync. Recommended when you want to manage less than 70% of the databases in your Snowflake account, which are specified in the SNOWFLAKE_MANAGE_DATABASE_LIST property.
    For eg.
    Managing 6 databases out of 10.
  • load_md_from_account_columns - It loads resources individually by each resource type, that is, it loads all databases first, then loads all schemas, all tables and their columns successively. This mode is recommended for production purposes since it is much faster than the other modes.
load_md_from_account_columns
SNOWFLAKE_AUDIT_SOURCE_ADVANCE_DB_NAME This property is used to specify the database that will be used to retrieve advanced audits from Snowflake. PRIVACERA_ACCESS_LOGS_DB

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.