Provisioning Access

Create role, user, warehouse

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.

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 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 future tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future views' + 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);

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.

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

-- 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);

External Tables

External Tables are supported in Metaplane, but the require separate grants. Privileges can be granted with the following command:

GRANT SELECT ON ALL EXTERNAL 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);

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 UI

Click Account > Policies

Click + Create... then enter Name, Allowed IP Addresses, and Comment

Using SQL

CREATE NETWORK POLICY METAPLANE_CONNECTOR
	ALLOWED_IP_LIST=('44.197.96.121', '34.206.79.174', '107.22.42.246')
	BLOCKED_IP_LIST=()
COMMENT='Static IPs used by the Metaplane Connector';

Private Link

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