This document is meant to assist in configuration of a Snowflake account with adequate permissions to be used in conjunction with the Datameer solution. It details account requirements and privileges required for successful integration of Snowflake databases and tables.
Overview
-
Datameer requires a Service Account to access Snowflake
-
The Service Account should have the same Role (if it exists) as the users' who will be working in Datameer
-
The following documents the required privileges in detail for administrators wanting to control what can be seen within the application and where the application can create assets
-
At this time, Datameer only support username/password authentication for the Service Account
In General
Datameer was designed to require the least amount of privileges and be as secure as possible, while still supporting a powerful self-service feature set. Datameer also follows the pattern of “leave it how you found it” and allows users to clean up the assets they are creating (table and views at this time), but never allows them to delete assets created outside of the Datameer application.
A common pattern we have seen at our customers is the team(s) using Datameer have access to production data through read-only schemas and have read-write schemas that act as workspaces for the team(s) to share their own managed datasets. The read-only schemas are where a central IT team (or other data management team) can provide reusable, documented datasets for use within the Datameer application, while the read-write schemas allow for self-service publishing of datasets to be shared across the team(s). The following details the required Snowflake privileges for each type of schema and other privileges required in general for the application.
Note: The following is subject to change as new features are added to the Datameer application. When that happens the minimal required privileges for those features will be documented.
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.
Read-Only Schemas
-
Datameer requires
USAGE
andMONITOR
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
-
Datameer requires
CREATE TABLE
andCREATE VIEW
privileges on a schema in order to publish datasets created through the application
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
-
The Datameer Service Account can be created using the “Create” option under the Users section of the Accounts tab in the Snowflake UI.
-
-
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.
Scripts
These scripts can be executed in lieu of the UI steps above. Furthermore, some permissions must be granted via the scripts below and cannot be assigned via the UI.
Note: The names given are examples. The Datameer application has no requirements on the names given to Users, Roles etc…
Create 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 = 'internaladmin@yourcompany.com'
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.
CREATE ROLE datameer_application
COMMENT = 'Role for the Datameer Application Service Account';
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 the 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
Comments
0 comments
Please sign in to leave a comment.