Provisioning Access

Create role, user, warehouse

🚧
Schema-level grants are preferred because if other roles have future grants on schemas, tables in those schemas may not appear in Metaplane. This is due to schema-level grants taking precedence in Snowflake:

When future grants are defined at both the database and schema level, the schema level grants take precedence over the database level grants, and the database level grants are ignored.

If no other roles have future grants on schemas, permissions can be granted to Metaplane on the database-level.

As a super user, execute the following SQL commands to create a read-only role, a user assigned to that role, and a warehouse for that role.

Metaplane supports both password based and key-pair based authentication to Snowflake. The script below assumes password auth, but can be simply changed too swap out the user's password for the public key generated following this guide

Make sure to generate a secure password and store it securely — you'll save it into Metaplane later.

-- Create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'METAPLANE_ROLE';
set user_name = 'METAPLANE_USER';
set user_password = 'changethispassword'; -- Change this password
set warehouse_name = 'METAPLANE_WH';
set database_name = 'DATABASE';  -- Change this database name to your database

-- Change role for user / role steps
use role accountadmin;

-- Create role for metaplane
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- Create a user for metaplane
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;

grant role identifier($role_name) to user identifier($user_name);

-- Create a warehouse for metaplane
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 5
auto_resume = true
initially_suspended = true
max_concurrency_level = 30
statement_timeout_in_seconds = 300
statement_queued_timeout_in_seconds = 1200;

-- Grant metaplane role access to warehouse
grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);

-- Grant the ability to monitor task and pipe history in schemas for which the role has USAGE access
grant MONITOR EXECUTION on account to role identifier($role_name);

-- Grant access to query history
grant imported privileges on database "SNOWFLAKE" to role identifier($role_name);

Provide access to tables/views

Option 1. Provide read-only privileges to all schemas in a database (preferred)

The following stored procedure iterates through each schema in a given database, then grants access to each table in a given schema. This stored procedure should be run as follows each time a new schema is created. Be sure to replace DB_NAME and METAPLANE_ROLE with the relevant database and role names.

create or replace procedure grantFutureAccess(databaseName string, roleName string)
  returns string not null
  language javascript
  as
  $$
  var schemaResultSet = snowflake.execute({ sqlText: 'SELECT SCHEMA_NAME FROM ' + '"' + DATABASENAME + '"' + ".INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA';"});
    
  var numberOfSchemasGranted = 0;
  while (schemaResultSet.next()) {
    numberOfSchemasGranted += 1;
    var schemaAndRoleSuffix = ' in schema "' + DATABASENAME + '"."' + 
    schemaResultSet.getColumnValue('SCHEMA_NAME') + '" to role ' + ROLENAME + ';'

    snowflake.execute({ sqlText: 'grant USAGE on schema "' + DATABASENAME + '"."' +  
    schemaResultSet.getColumnValue('SCHEMA_NAME') + '" to role ' + ROLENAME + ';'});
    snowflake.execute({ sqlText: 'grant SELECT on all tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all event tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all external tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all dynamic tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future event tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future external tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future dynamic tables' + schemaAndRoleSuffix});
  }
  
  return 'Granted access to ' + numberOfSchemasGranted + ' schemas';
  $$
;

grant USAGE on database identifier($database_name) to role identifier($role_name);
call grantFutureAccess('DB_NAME', 'METAPLANE_ROLE');

Option 2. Provide read-only privileges to specific schemas

Alternatively, execute the following query for each relevant schema:

-- Read-only access to specific schemas (CHANGE THIS)
set schema_name = 'DATABASE_NAME.SCHEMA_NAME';
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all views in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future views in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all external tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future external tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all event tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future event tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all dynamic tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future dynamic tables in schema identifier($schema_name) to role identifier($role_name);

Alternative option: Provide read-only privileges to entire database

🚧
Schema-level grants are preferred because if other roles have future grants on schemas, tables in those schemas may not appear in Metaplane. This is due to schema-level grants taking precedence in Snowflake:

When future grants are defined at both the database and schema level, the schema level grants take precedence over the database level grants, and the database level grants are ignored.

-- Read-only access to database
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on all schemas in database identifier($database_name) to role identifier($role_name);
grant USAGE on future schemas in database identifier($database_name) to role identifier($role_name);
grant SELECT on all tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on all views in database identifier($database_name) to role identifier($role_name);
grant SELECT on future views in database identifier($database_name) to role identifier($role_name);
grant SELECT on all external tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future external tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on all event tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future event tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on all dynamic tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future dynamic tables in database identifier($database_name) to role identifier($role_name);

External Data Share

External data share tables work slightly differently than normal tables. Privileges can be granted with the following command:

grant imported privileges on database identifier($database_name) to role identifier($role_name);

Snowpipes and Tasks

Metaplane supports both Snowpipes and Tasks! These require separate grants to provide access. Unfortunately, Snowflake doesn't allow bulk granting access to Snowpipes, so the easiest way to grant these permissions is at the account level:

grant MONITOR EXECUTION on account to role identifier($role_name);

This grants access to only the Snowpipes and Tasks that are in a database and schema which the given role has USAGE privileges on - i.e. the databases and schemas to which you granted the Metaplane role access in the previous steps.

Finer-grained pipe and task access control

If you prefer finer-grained control over the pipe access given to Metaplane, you can grant access to pipes individually:

grant MONITOR on pipe <database>.<schema>.<pipename> to role identifier($role_name);

and you can grant access to Tasks with the following:

grant MONITOR on all tasks in database identifier($database_name) to role identifier($role_name);

Adding Snowflake source

To add a source, navigate to the Connections page and click the Snowflake button. A modal will prompt you for the credentials you just created, then allow you to save the connection for testing:

Finding your Snowflake Account Name

For "Account name", please enter your Snowflake Account Name in the format of <id>.<region>. For example, your information may look something like this: v123abc.us-east1.aws

To get this information, navigate to bottom left in your Snowflake console (as in the picture below) and "Copy Account URL":

The copied URL will look like this:

https://v123abc.us-east1.aws.snowflakecomputing.com

You can use the information here to get the required fields for "Account Name", which in this case would be v123abc.us-east1.aws.

Whitelist Metaplane IPs

If your organization uses a whitelist to manage Snowflake access, Metaplane will only access your Snowflake through the following IPs:

44.197.96.121
34.206.79.174
107.22.42.246

As list: 44.197.96.121, 34.206.79.174, 107.22.42.246

Using SQL

CREATE NETWORK RULE allow_metaplane_ip_access
  MODE = INGRESS
  TYPE = IPV4
  VALUE_LIST = ('44.197.96.121', '34.206.79.174', '107.22.42.246');


CREATE NETWORK POLICY allow_metaplane_ip_policy
  ALLOWED_NETWORK_RULE_LIST = ('allow_metaplane_ip_access');

Private Link

Metaplane also supports either Azure Private Link or AWS PrivateLink in addition to IP whitelisting for your Snowflake instance.