Snowflake permissions configuration

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'ARIZE_FILE_IMPORTER_ROLE';
set user_name = 'ARIZE_FILE_IMPORTER';
set warehouse_name = '[PROVISIONED_WAREHOUSE_FOR_ARIZE]';

-- change role to securityadmin for user / role steps
use role securityadmin;

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

-- create  a user for arize
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';

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

-- set user public key
ALTER USER identifier($user_name) SET RSA_PUBLIC_KEY='[ARIZE_PUBLIC_KEY]';

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

CREATE TABLE IF NOT EXISTS [DATABASE_NAME].arize.arize_ingestion_keys(
    object_name string, 
    object_type string, 
    tag_name string, 
    tag_value string
); 

-- Assign challenge key to table to prove ownership (idempotently)
MERGE INTO [DATABASE_NAME].arize.arize_ingestion_keys t
USING (SELECT
    '[TABLE_NAME]' as object_name,
    'table' as object_type,
    'arize-ingestion-key' as tag_name,
    '[ARIZE_INGESTION_KEY]' as tag_value
) s
ON t.object_name = s.object_name
WHEN MATCHED THEN UPDATE SET tag_value = '[ARIZE_INGESTION_KEY]'
WHEN NOT MATCHED THEN
INSERT VALUES ('[TABLE_NAME]', 'table', 'arize-ingestion-key', '[ARIZE_INGESTION_KEY]');

-- Grant permissions to Arize role
set role_name = 'ARIZE_FILE_IMPORTER_ROLE';
set DATABASE_NAME = '[DATABASE_NAME]';

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

grant USAGE
on schema [DATABASE_NAME].[SCHEMA_NAME]
to role identifier($role_name);

grant SELECT
on table [DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME]
to role identifier($role_name);

grant USAGE
on schema [DATABASE_NAME].arize
to role identifier($role_name);

grant SELECT
on table [DATABASE_NAME].arize.arize_ingestion_keys
to role identifier($role_name);