Skip to main content

Privacera Platform

Table of Contents

Snowflake configuration for PolicySync

:
Snowflake configuration for PolicySync

Before configuring Snowflake with Privacera Manager, 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";
Creating owner role

By configuring the following property in vars.policysync.snowflake.yml, PolicySync can take ownership of all objects managed by it. PolicySync requires this in order to create row-filtering and column-Masking policies.

SNOWFLAKE_OWNER_ROLE:"PRIVACERA_POLICYSYNC_ROLE"

Note

If PolicySync is not configured to take ownership of all objects managed by PolicySync, keep the property value blank.

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

    Note

    • For PolicySync versions 4.7 or earlier:

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

      The following is the format of a Masking policy name:

      {table}{separator}{column}.

      For example, "tbl1_priv_col1"

    • For PolicySync versions 5.0 or later:

      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;