Snowflake Metadata Access

As outlined in our security documentation, SELECT requires a new user to be created with read only access to your Snowflake account metadata database. In the sections below, we'll walk you through the access commands step by step. Alternatively, you can skip ahead to the full script and run it in one go.

Ensure you have access to run these commands!

In order to run this setup script, your user will need to have access to the accountadmin, sysadmin, and useradmin roles. If you're not sure whether you have those roles, you can run show grants to user <your_username> to check.

Step 1: Create new user & role

First off, create a new user and role that will only be used by SELECT. You'll need to update the PASSWORD_HERE with a randomly generated password ¹. Be sure to save it, since you'll need to enter it into the SELECT web application later on.

We recommend using the useradmin role when creating new users, and the sysadmin role when creating a new warehouse in step 2. If you prefer not to, you can run all these commands with the accountadmin role.

use role useradmin; -- Snowflake best practice
create user if not exists select_user
  password = 'PASSWORD_HERE'
  default_role = select_user
  default_warehouse = select_user
  comment = 'Used by select.dev'
;
create role if not exists select_user comment = 'Used by select.dev';
grant role select_user to role sysadmin; -- Snowflake best practice
grant role select_user to user select_user;

Step 2: Create new warehouse

Next, create a new warehouse for SELECT. SELECT will use this warehouse to query your Snowflake metadata once per day. The cost of running this warehouse will be minimal. If preferred, you can instead grant us access to run the queries from an existing warehouse ².

use role sysadmin; -- Snowflake best practice
create warehouse if not exists select_user
  warehouse_size=xsmall
  auto_suspend=60
  initially_suspended=true
  comment = 'Used by select.dev'
;
grant monitor, operate, usage, modify
  on warehouse select_user to role select_user
;
use role accountadmin;

Step 3: Create a storage integration

In order to export your account's metadata in the fastest and most cost efficient way, we export directly to a secure Google Cloud Storage (GCS) bucket. The commands below create a storage integration, and allow the select_user use it to write the Snowflake metadata to SELECT's GCS bucket.

create storage integration if not exists select_export
    type = external_stage
    storage_provider = 'GCS'
    enabled = true
    storage_allowed_locations = ('*');
grant usage on integration select_export to role select_user;

Optional - Copy through a stage

By default, SELECT attempts to use the storage integration select_export directly. If using the PREVENT_UNLOAD_TO_INLINE_URL parameter, create a stage to represent SELECT's GCS bucket and grant the select_user usage access.

-- change this location and stage name as per your preference
create stage my_db.my_schema.select_export
    -- we will provide this GCS bucket name
    url='gcs://select_bucket_name/'
    storage_integration = select_export;

-- change as needed
grant usage on database my_db to role select_user;
grant usage on schema my_db.my_schema to role select_user;
grant usage on stage my_db.my_schema.select_export to role select_user;

Step 4: Grant user read only access to Snowflake metadata

Next, we'll grant select_user read only access to the Snowflake metadata database. We also give it permission to run show warehouses commands.

-- Allows SELECT to query Snowflake metadata
grant imported privileges on database snowflake to role select_user;
 -- Allows SELECT to monitor warehouses
grant monitor usage on account to role select_user;

Step 5: Grant user ability to modify warehouses

Lastly, we give select_user the ability to adjust warehouse settings. The command below programatically executes the grant modify, ... on warehouse <warehouse_name> to user select_user for each warehouse in your account, to avoid manually typing out those commands.

Running the script below does not enable any SELECT warehouse optimization features. Those must be manually enabled separately in the SELECT UI for each warehouse.

execute immediate $$
declare
  role_name varchar default 'select_user';
  res resultset default (show warehouses);
  cur cursor for res;
begin
  for row_variable in cur do
    execute immediate 'grant modify, monitor, operate on warehouse ' || row_variable."name" || ' to role ' || role_name;
  end for;
  return 'Success!';
end;
$$;

Full Script

After updating the PASSWORD_HERE in the second command, you can run the entire script at once.

-- Step 1: Create new user
use role useradmin;
create user if not exists select_user
  password = 'PASSWORD_HERE'
  default_role = select_user
  default_warehouse = select_user
  comment = 'Used by select.dev'
;
create role if not exists select_user comment = 'Used by select.dev';
grant role select_user to role sysadmin; -- Snowflake best practice
grant role select_user to user select_user;

-- Step 2: Create a new X-SMALL warehouse for SELECT to use when reading Snowflake metadata
use role sysadmin;
create warehouse if not exists select_user
  warehouse_size=xsmall
  auto_suspend=60
  initially_suspended=true
  comment = 'Used by select.dev'
;
grant monitor, operate, usage, modify on warehouse select_user to role select_user;
use role accountadmin;

-- Step 3: Create a storage integration in GCS so SELECT can export metadata required to power cost dashboards
-- Used to power dashboards in select.dev
create storage integration if not exists select_export
    type = external_stage
    storage_provider = 'GCS'
    enabled = true
    storage_allowed_locations = ('*');
grant usage on integration select_export to role select_user;

-- Step 4: Grant SELECT_USER required privileges
-- SELECT will get read only access to Snowflake account metadata database:
-- https://docs.snowflake.com/en/sql-reference/snowflake-db.html

-- Allows us to query Snowflake metadata
grant imported privileges on database snowflake to role select_user;

 -- Allows SELECT to monitor warehouses
grant monitor usage on account to role select_user;

-- Step 5: Give SELECT the ability to modify (i.e. suspend) each warehouse in your account
-- This stored proc removes the need to type out the "grant" command for each warehouse in your account
execute immediate $$
declare
  role_name varchar default 'select_user';
  res resultset default (show warehouses);
  cur cursor for res;
begin
  for row_variable in cur do
    execute immediate 'grant modify, monitor, operate on warehouse ' || row_variable."name" || ' to role ' || role_name;
  end for;
  return 'Success!';
end;
$$;

Next Steps

If your Snowflake account restricts access to certain IP addresses, be sure to update your network policies with our IP address, covered in the next section.

If not, you can skip ahead to connect your Snowflake account.

Notes

¹ We recommend using a tool like 1Password to both randomly generate and save the password. Alternatively, you can use a password generator website and store the password securely elsewhere. ↩

² If you'd prefer to have SELECT run the metadata extracts from an existing warehouse, you can run the commands below instead:

use role sysadmin; -- Snowflake best practice
grant monitor, operate, usage on warehouse YOUR_PREFERED_WAREHOUSE_NAME to role select_user;
alter user select_user set default_warehouse=YOUR_PREFERED_WAREHOUSE_NAME
use role accountadmin;

↩