Skip to content

Add User and Roles in Snowflake#

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.

    CREATE USER IF NOT EXISTS PRIVACERA_SYNC  PASSWORD='${POLICYSYNC_USER_PASSWORD}'  MUST_CHANGE_PASSWORD=FALSE DEFAULT_WAREHOUSE='PRIVACERA_SYNC_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;
    

Last update: July 23, 2021