Skip to content

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. You will be prompted for a password, enter it. 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;