Skip to content

Prerequisites for Access Management for Snowflake Connector

Before integrating Snowflake with Privacera, ensure that you have the necessary Snowflake warehouse, database, users and roles required by PolicySync Connector. Below are the prerequisites for setting up the Snowflake connector with Privacera.

Prerequisites Detail
JDBC URL Get the JDBC URL for the Snowflake cluster.
JDBC username and password Create a Snowflake user with admin privileges which will be used exclusively by the Privacera Connector to manage access permissions. You also have an option to use certificate instead of password also
Snowflake Role for Connector Create a Snowflake role for the connector with the necessary privileges to manage the access policies in Snowflake.
Snowflake warehouse Create a Snowflake warehouse for the connector with the necessary privileges to manage the access policies in Snowflake.
Database, schema and table names The database and schema names which should be managed. Initially you should enable managing access to a test schema with tables. Once you have tried all the use-cases, you can manage all the objects in your Snowflake database cluster.
Users and groups to manage The Privacera users and groups whose permissions in Snowflake will be managed by the connector.

Recommendations to create Snowflake roles and warehouses

For performing below steps, log in to Snowflake as a user with ACCOUNTADMIN privileges

Snowflake Role for Connector

The role created in this step will be used when configuring the Snowflake connector with Privacera. The suggested name for the role is PRIVACERA_POLICYSYNC_ROLE, but you can use a different name if preferred.

Since the Privacera connector manages access policies in Snowflake and can create roles in Snowflake, the role should have the necessary privileges to manage permissions for all tables and views in the database. Additionally, if Privacera is managing users and groups in Snowflake, the role should have the necessary privileges to create, update and delete users and roles in Snowflake.

Privilege Description
CREATE ROLE Allows the role to create roles. This is optional if Privacera is not creating and managing roles
CREATE USER Allows the role to create users .
MANAGE GRANTS Allows the role to manage grants for Snowflake resources.
APPLY MASKING POLICY Allows the role to create native masking policies.
APPLY ROW ACCESS POLICY Allows the role to create native row filter policies.

Here are the steps to create the role and grant the necessary privileges to the role:

  1. Drop the role if it already exists.

    SQL
    DROP ROLE IF EXISTS PRIVACERA_POLICYSYNC_ROLE;
    

  2. Create a new role.

    SQL
    CREATE ROLE PRIVACERA_POLICYSYNC_ROLE;
    

  3. Grant the below privilege to the role allowing it to create, update, delete roles.

    SQL
    GRANT CREATE ROLE ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
    

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

    SQL
    GRANT CREATE USER ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
    

  5. Grant the below privilege to the role allowing it to manage grants for Snowflake resources.

    SQL
    GRANT MANAGE GRANTS ON ACCOUNT TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
    

  6. Grant the below privilege to the role allowing it to create native masking policies.

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

  7. Grant the below privilege to the role allowing it to create native row filter policies.

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

Snowflake warehouse

Privacera's connector requires a warehouse to manage access policies in Snowflake and retrieve audit logs. The warehouse can be of the smallest size, as the connector does not perform any heavy operations. If there are any always-on warehouses, you can use them as well.

The recommended name for the warehouse is PRIVACERA_POLICYSYNC_WH, but you could use a different name if you prefer.

Below is a sample SQL to create a warehouse with the smallest size.

SQL
1
2
3
4
5
6
7
8
9
CREATE WAREHOUSE IF NOT EXISTS "PRIVACERA_POLICYSYNC_WH"
WITH
    WAREHOUSE_SIZE = 'XSMALL',
    WAREHOUSE_TYPE = 'STANDARD',
    AUTO_SUSPEND = 600,
    AUTO_RESUME = TRUE,
    MIN_CLUSTER_COUNT = 1,
    MAX_CLUSTER_COUNT = 1,
    SCALING_POLICY = 'ECONOMY';

Allow the Privacera role to use the warehouse

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

Allow the Privacera role to change the state of the warehouse

SQL
GRANT OPERATE ON WAREHOUSE "PRIVACERA_POLICYSYNC_WH" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Permission to read Access Audits from Snowflake

Privacera's connector requires access to the access audits in Snowflake to retrieve the audit logs. The access audits are stored in the SNOWFLAKE database. Below are the steps to grant the necessary privileges to the role for reading the access audits.

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

Database for Privacera Security Functions

For dynamic masking and row-level filtering, Privacera requires a database to store the UDFs. Below are the steps to create a database and grant the necessary privileges to the role to create UDFs in it.

The suggested name for the database is PRIVACERA_DB, but you could use a different name if you prefer.

SQL
CREATE DATABASE IF NOT EXISTS "PRIVACERA_DB";

Allow the Privacera service role PRIVACERA_POLICYSYNC_ROLE to use the database and the PUBLIC schema.

SQL
GRANT USAGE ON DATABASE "PRIVACERA_DB" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
GRANT USAGE ON SCHEMA "PRIVACERA_DB.PUBLIC" TO ROLE "PRIVACERA_POLICYSYNC_ROLE"

Allow the Privacera service role PRIVACERA_POLICYSYNC_ROLE to create UDFs in the schema.

SQL
GRANT CREATE FUNCTION ON SCHEMA "PRIVACERA_DB.PUBLIC" TO ROLE "PRIVACERA_POLICYSYNC_ROLE";

Database User for Privacera Connector

The Privacera connector requires a user to connect to Snowflake and manage access policies. The user should have the necessary privileges to manage access policies in Snowflake. The suggested name for the user is PRIVACERA_POLICYSYNC_USER, but you can use a different name if preferred. You need to create this database user and grant access to the role PRIVACERA_POLICYSYNC_ROLE.

SQL
1
2
3
4
5
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";

Grant the role PRIVACERA_POLICYSYNC_ROLE to the user.

SQL
GRANT ROLE "PRIVACERA_POLICYSYNC_ROLE" TO USER "PRIVACERA_POLICYSYNC_USER";

Comments