Snowflake Configuration for PolicySync#

Before connecting Snowflake application to PrivaceraCloud, 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 field when connecting Snowflake application to PrivaceraCloud.

Create a role.

CREATE ROLE IF NOT EXISTS "PRIVACERA_POLICYSYNC_ROLE";

Grant this role permission to users to create/update/delete roles.
```
GRANT ROLE USERADMIN TO ROLE "PRIVACERA_POLICYSYNC_ROLE";
```
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";
```
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";
```
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";
```
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 field when connecting Snowflake application to PrivaceraCloud.

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=600 AUTO_RESUME= TRUE  MIN_CLUSTER_COUNT=1 MAX_CLUSTER_COUNT=1 SCALING_POLICY='ECONOMY';

Granting Role Permission to Read Access Audits#

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

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

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 Database name where masking function for column access control will be created field when connecting Snowflake application to PrivaceraCloud.

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

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 fields when connecting Snowflake application to PrivaceraCloud.

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

Grant the user the PRIVACERA_POLICYSYNC_ROLE role.

GRANT ROLE "PRIVACERA_POLICYSYNC_ROLE" TO USER "PRIVACERA_POLICYSYNC_USER";

Last update: April 7, 2022