Skip to content

Setting Up a Dedicated Snowflake Service Account For Datameer#

This page guides you to configure a Snowflake account with adequate permissions to be used in conjunction with Datameer. It details account requirements and privileges required for a successful integration of your Snowflake databases and tables during the initial setup.

Creating a dedicated service account for Datameer is recommended when setting up a team environment while a single user can use their existing Snowflake username/ password.

Required Capabilities#

Warehouse

  • Datameer requires the 'Datameer Service Account User' to have a default warehouse and that warehouse to have the 'USAGE' privilege in order to execute queries.
  • At this time, the Datameer application is not starting or stopping the warehouse, so the warehouse should be configured to auto-resume if the warehouse has been configured to auto-suspend.

To set up the default warehouse:

ALTER USER your_user SET DEFAULT_WAREHOUSE = your_warehouse;

Role

  • Datameer requires the role to have 'EXECUTE' and 'MANAGE EXECUTE TASK' privileges in order to schedule materialized tables.
GRANT EXECUTE TASK ON ACCOUNT TO ROLE your_role;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE your_role;

Read-Only Schemas

  • Datameer requires 'USAGE' and 'MONITOR' privileges on the databases and schemas it will be reading from in order to access those databases and schemas and also introspect metadata about them.
  • Datameer requires 'SELECT' privileges on all existing tables and views in the schemas it will be reading from, so users of the Datameer application can read the data from those tables and views.
  • Datameer requires 'FUTURE SELECT' table/view privileges on all schemas it will be reading from to make sure newly added tables and views are discoverable and accessible within the Datameer application.

Read-Write Schemas

  • All of the above privileges required for 'Read-Only' schemas must be fulfilled.
  • Datameer requires 'CREATE TABLE' and 'CREATE VIEW' privileges on a schema in order to publish datasets created through the application.

Configuring Via the UI#

Creating/ Identifying Warehouse and Role#

The Datameer Service Account requires an associated 'Warehouse' and 'Role'. These can be identified or created in the respective 'Warehouse and Account' -> 'Roles' section of the UI.

Creating An Account#

  1. Create the Datameer Service Account under the 'Users' section of the 'Accounts' tab.

  2. Once the 'User' information has been populated, click “Next” to move to the 'Advanced' and then 'Preferences' options. Under the 'Preferences' section of the user creation dialogue, the identified or created warehouse and role can be assigned to the Datameer Service Account.

  3. The Warehouse assigned to the service account must have 'USAGE' privileges for the role assigned to the service account as well as be configured to auto-resume.

Database/ Schema Permissions#

Permissions must be assigned to databases and schemas, as defined above, for read-only and read/ write privileges. These permissions must be assigned on both the database and schema.

Note: Additional permissions for read-only and read/write schemas may require execution of SQL, as they may not be available via the UI. Syntax for these permissions can be found in the scripts below.

Configuring Via Scripts#

These scripts can be executed instead of the UI steps above. Furthermore, some permissions must be granted via the scripts below and cannot be assigned via the UI.

Note that the names given below are examples only. The Datameer application has no requirements on the names given to users, roles etc.

Creating the Service Account User#

If an acceptable role and warehouse are already available for the Datameer Service Account User then the following can be used to create that user:

CREATE USER datameer_service_account
  PASSWORD = 'securepassword'
  EMAIL = '[email protected]'
  MUST_CHANGE_PASSWORD = false
  DEFAULT_ROLE = datameer_application
  DEFAULT_WAREHOUSE = datameer_warehouse
  COMMENT = 'Service Account for the Datameer application';

GRANT USAGE
  ON WAREHOUSE datameer_warehouse
  TO ROLE datameer_application;

GRANT ROLE datameer_application
   TO USER datameer_service_account;

Datameer Application Role and Schema Grants#

First create the role: 

CREATE ROLE datameer_application
  COMMENT = 'Role for the Datameer Application Service Account';

It is best practice to then grant the custom role to the 'SYSADMIN' role of your Snowflake account. In some cases it may mean granting the custom role to another role that has already been granted to 'SYSADMIN'. This is to make sure the system administrators will be able to manage the objects owned by the custom role created for the Datameer application. 

GRANT ROLE datameer_application
   TO ROLE sysadmin;

For each Read-Only schema and Read-Write schema:

GRANT USAGE, MONITOR
  ON DATABASE database_a
  TO ROLE datameer_application;

GRANT USAGE, MONITOR
  ON SCHEMA database_a.schema_1
  TO ROLE datameer_application;

GRANT SELECT
  ON ALL TABLES IN SCHEMA database_a.schema_1
  TO ROLE datameer_application;

GRANT SELECT
  ON FUTURE TABLES IN SCHEMA database_a.schema_1
  TO ROLE datameer_application;

GRANT SELECT
  ON ALL VIEWS IN SCHEMA database_a.schema_1
  TO ROLE datameer_application;

GRANT SELECT
  ON FUTURE VIEWS IN SCHEMA database_a.schema_1
  TO ROLE datameer_application;

Additionally for each Read-Write schema:

GRANT CREATE VIEW
  ON SCHEMA database_a.schema_1
  TO ROLE datameer_application;

GRANT CREATE TABLE
  ON SCHEMA database_a.schema_1
  TO ROLE datameer_application;

If you know all schemas in a database should be accessible etc., then both code block sections above can be optimized via:

GRANT USAGE, MONITOR
  ON DATABASE database_a
  TO ROLE datameer_application;

GRANT USAGE, MONITOR
  ON ALL SCHEMAS IN DATABASE database_a
  TO ROLE datameer_application;

GRANT SELECT
  ON ALL TABLES IN DATABASE database_a
  TO ROLE datameer_application;

GRANT SELECT
  ON ALL VIEWS IN DATABASE database_a
  TO ROLE datameer_application;

GRANT SELECT
  ON FUTURE TABLES IN DATABASE database_a
  TO ROLE datameer_application;

GRANT SELECT
  ON FUTURE VIEWS IN DATABASE database_a
  TO ROLE datameer_application;

And if all the schemas are Read-Write:

GRANT CREATE TABLE
  ON ALL SCHEMAS IN DATABASE database_a
  TO ROLE datameer_application;

GRANT CREATE VIEW
  ON ALL SCHEMAS IN DATABASE database_a
  TO ROLE datameer_application;

Warehouse Grants#

GRANT USAGE
  ON WAREHOUSE datameer_warehouse
  TO ROLE datameer_application;

Also, make sure the warehouse being used for the Datameer application is auto-resume: 

ALTER WAREHOUSE datameer_warehouse SET AUTO_RESUME = TRUE