Add User and Roles in Snowflake

Note

Use the ACCOUNTADMIN role to run the steps in the following sections.

Using Shell Script

Execute a shell script to automatically create Snowflake users and roles. Follow the steps below:

1. Start the PM docker shell to setup Snowflake.

   cd ~/privacera/privacera-manager
   ./privacera-manager.sh shell
   

2. Download the Snowflake setup for policysync process.

   cd ../downloads
   curl https://privacera.s3.amazonaws.com/public/pm-demo-data/snowflake/snowflake_setup.sh --output snowflake_setup.sh
   chmod +x snowflake_setup.sh
   

3. Run downloaded script. e.g ./snowflake_setup.sh testsnowflake.prod.us-west-2.aws testadmin AdminPassword PolicySyncPassword false

   ./snowflake_setup.sh ${SNOWFLAKE_ACCOUNT} ${ADMIN_USERNAME} ${ADMIN_PASSWORD} ${POLICYSYNC_USER_PASSWORD} ${ENABLE_ADVANCE_AUDIT}
   

   Where: set ${ENABLE_ADVANCE_AUDIT} to true only if you have private auditing feature enabled from Snowflake.

Using Snowflake CLI

Follow the steps below to create Snowflake users and roles by manually executing SQL queries:

1. Start the PM docker shell to setup Snowflake.

   cd ~/privacera/privacera-manager
   ./privacera-manager.sh shell
   

2. Log into your Snowflake account using your account name and user name. This will start SnowSQL. For more information about the account name and user name, click here.

   snowsql -a <account_name> -u <user_name>
   

3. 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';
   

4. Role used by the Policy Sync.

   CREATE ROLE IF NOT EXISTS PRIVACERA_SYNC_ROLE;
   

5. Role should have permissions to grant/revoke and also to create users and roles.

   GRANT ROLE SECURITYADMIN TO ROLE PRIVACERA_SYNC_ROLE;
   GRANT ROLE ACCOUNTADMIN TO ROLE PRIVACERA_SYNC_ROLE;
   

6. This is the default owner for all user-created resources. By switching the roles to the default role helps in managing the grants/revokes.

   CREATE ROLE IF NOT EXISTS PRIVACERA_DEFAULT_OWNER;
   

7. The database user used by the Policy Sync process. Replace ${POLICYSYNC_USER_PASSWORD} with the appropriaate password.

   CREATE USER IF NOT EXISTS PRIVACERA_SYNC 
   PASSWORD='${POLICYSYNC_USER_PASSWORD}'
   MUST_CHANGE_PASSWORD=FALSE DEFAULT_WAREHOUSE='PRIVACERA_POLICYSYNC_WH'
   DEFAULT_ROLE='PRIVACERA_SYNC_ROLE';    
   

8. Allow the user to assume the PolicySync role.

   GRANT ROLE PRIVACERA_SYNC_ROLE TO USER PRIVACERA_SYNC;
   

9. Create the database to store masking policies.

   CREATE DATABASE IF NOT EXISTS privacera_db;