Snowflake

-- Variables
-- Replace the warehouse name with the one you created in Step 1
set role_name = 'OPENLAYER_FILE_IMPORTER_ROLE';
set user_name = 'OPENLAYER_FILE_IMPORTER';
set warehouse_name = '[YOUR_WAREHOUSE_NAME_HERE]';

-- Use Security Admin role for creating users/roles
use role securityadmin;

-- Create role for Openlayer
create role if not exists identifier($role_name);

-- Create a dedicated service user
create user if not exists identifier($user_name);
alter user identifier($user_name) set default_role = $role_name;
alter user identifier($user_name) set default_warehouse = $warehouse_name;
alter user identifier($user_name) set type = 'SERVICE';

-- Assign the role to the user
grant role identifier($role_name) to user identifier($user_name);

-- Associate a public key with the user for key-based authentication
-- Replace the public key with the one available in the Openlayer UI
ALTER USER identifier($user_name) SET RSA_PUBLIC_KEY='[OPENLAYER_PUBLIC_KEY_HERE]';

-- Variables
-- Replace the warehouse, database, schema, and table names with the ones you want to grant permissions to
set role_name = 'OPENLAYER_FILE_IMPORTER_ROLE';
set user_name = 'OPENLAYER_FILE_IMPORTER';
set warehouse_name = '[YOUR_WAREHOUSE_NAME_HERE]';
set database_name = '[YOUR_DATABASE_NAME_HERE]';
set schema_name = '[YOUR_SCHEMA_NAME_HERE]';
set table_name = '[YOUR_TABLE_NAME_HERE]';

-- Grant usage rights to the warehouse
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);

-- Grant usage rights to the database
grant USAGE
on database identifier($database_name)
to role identifier($role_name);

-- Grant usage rights to the schema
grant USAGE
on schema identifier($database_name).identifier($schema_name)
to role identifier($role_name);

-- Grant select rights to the table
grant SELECT
on table identifier($database_name).identifier($schema_name).identifier($table_name)
to role identifier($role_name);