Roles and privileges for Supermetrics for Snowflake

This guide contains instructions regarding the required roles and privileges that are required by Supermetrics for Snowflake.



Create DATABASE in Snowflake dedicated to your Supermetrics data transfers

  • Note: You can also use an already existing DATABASE. When doing so, please start from the 3rd point instead (creating a Snowflake SCHEMA for your transfer).


1. Required GLOBAL privilege: “CREATE DATABASE”


2. If you don’t have a role containing this privilege, contact your ACCOUNTADMIN to either grant the role this privilege or create the database for you. 


3. Create a Snowflake SCHEMA for your transfer. A Snowflake schema contains a collection of tables or views under a specific Snowflake databaseYou can either use 1 Snowflake schema within a Destination you created in Supermetrics for all of your transfers or you can set up multiple Snowflake schemas if you prefer to load the data of your transfers into separate Snowflake schemas.


There are two possible “OWNER” scenarios:


The “OWNER” is defined by what “ROLE” you have selected when creating the schema:


If a user creates a non-”PUBLIC” schema with another role (e.g. SYSADMIN) on your behalf, that user needs to grant your role the SCHEMA privileges “USAGE” and “CREATE TABLE”, so you can create a working “Destination” and “Transfer” at team.supermetrics.com:

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#schema-privileges

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html#grant-privileges-to-role


4. Once the schema has been created, you can go to team.supermetrics.com/destinations in order to create the Snowflake destination:

How to configure a data destination »


NOTE: If your Snowflake user has multiple roles to choose from, Supermetrics will use your “DEFAULT” role. Thus, if your user has the default role “PUBLIC” but the Schema to which you want to load your Ad data is not “PUBLIC”, then the connection cannot be established from our Supermetrics product. In this case, you will need to change the default role of your user in Snowflake:

https://docs.snowflake.com/en/sql-reference/sql/alter-user.html



EXAMPLE:


User 1 creates schema “FB_ADS” with role “custom_role_1” under a Snowflake database “SUPERMETRICS_DB” for user 2 who only has the roles “PUBLIC” and “custom_role_2” available. The company policy is to avoid creating “PUBLIC” DB and schemas in Snowflake. The role “PUBLIC” is set as default for user 2 and user 2 is the one who is to set up multiple DESTINATIONS and TRANSFERS at team.supermetrics.com.Here are the required steps user 1 and 2 need to perform before the DESTINATION can be created:


- User 1 needs to make sure user 2 can use the DB “SUPERMETRICS_DB” and create Schemas under that DB, i.e. user 1 needs to grant DB privileges “USAGE” and “CREATE SCHEMA” to user 2’s role “custom role 2”:

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html#syntax


GRANT USAGE, CREATE SCHEMA
ON DATABASE SUPERMETRICS_DB
TO ROLE "custom_role_2"


- The step above is only required if user 2 is meant to create additional schemas under “SUPERMETRICS_DB” in the future. Otherwise, the DB privilege “USAGE” is sufficient


- In order for User 2 to be able to use schema “FB_ADS” as destination, User 1 needs to grant the needed SCHEMA privileges “USAGE” and “CREATE TABLE”:

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html#syntax


GRANT USAGE, CREATE TABLE
ON SCHEMA FB_ADS
TO ROLE "custom_role_2"


- NOTE: User 1 needs to make sure that the correct role “custom_role_1” and DB “SUPERMETRICS_DB” are selected in Snowflake in order to be able to grant the privileges to user 2:



- Once User 2 can see and use DB “SUPERMETRICS_DB”, User 2 needs to create the schema/s that will serve as destination/s for Supermetrics, e.g. User 2 wants to create a schema “GOOGLE_ADS” in addition to the already existing schema “FB_ADS”:

https://docs.snowflake.com/en/sql-reference/sql/create-schema.html


CREATE SCHEMA GOOGLE_ADS


- NOTE: As above, user 2 needs to make sure that the correct role “custom_role_2” and DB “SUPERMETRICS_DB” are selected.


- As the last step in Snowflake, user 2 needs to make sure that his user has the correct role set as default. As it’s currently set at role “PUBLIC”, and the DB and schema privileges were granted to role “custom_role_2”, the destination creation at Supermetrics will FAIL. Thus, user 2 needs to set his default role to “custom_role_2” (as that’s the role with the required privileges for “SUPERMETRICS_DB” and schema “FB_ADS”:

https://docs.snowflake.com/en/sql-reference/sql/alter-user.html


ALTER USER IF EXISTS USER2 SET DEFAULT_ROLE = 'custom_role_2'


- Once the schema/s are ready, user 2 can proceed with creating the destination at team.supermetrics, as described here:

How to configure a data destination »


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.