Service User ​

Why a service user ? ​

Indexima for Snowflake requires a Snowflake user to create Dynamic Tables automatically. You can use your personal Snowflake user, but for fine-grained control we recommand you create a dedicated service user. You can change this user at any time in the Indexima settings.

ℹ️ Please note that you can sign-in to the Indexima interface with any valid Snowflake user.

Creating a service user ​

For a specific combo of <warehouse>, <database>, <schema>, execute the following commands on Snowflake to create a service user:

sql
CREATE ROLE indexima_role;
CREATE USER indexima_usr PASSWORD='abc123' DEFAULT_ROLE = indexima_role MUST_CHANGE_PASSWORD = FALSE DEFAULT_WAREHOUSE = <warehouse>;
GRANT ROLE indexima_role TO USER indexima_usr;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE indexima_role;
GRANT USAGE ON DATABASE <database> TO ROLE indexima_role;
GRANT USAGE, CREATE STREAM, CREATE DYNAMIC TABLE ON SCHEMA <database>.<schema> TO ROLE indexima_role;
GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE indexima_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE indexima_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA <database>.<schema> TO ROLE indexima_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database>.<schema> TO ROLE indexima_role;

If you want to grant all schema in a <database> to the service user, you can use the following script to loop through all schema:

sql
CREATE ROLE indexima_role;
CREATE USER indexima_usr PASSWORD='abc123' DEFAULT_ROLE = indexima_role MUST_CHANGE_PASSWORD = FALSE DEFAULT_WAREHOUSE = <warehouse>;
GRANT ROLE indexima_role TO USER indexima_usr;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE indexima_role;
GRANT USAGE ON DATABASE <database> TO ROLE indexima_role;

DECLARE
  c1 CURSOR FOR (select catalog_name,schema_name from <database>.information_schema.schemata where schema_name not in ('INFORMATION_SCHEMA') );
BEGIN
  FOR record IN c1 DO
    EXECUTE IMMEDIATE ('GRANT USAGE, CREATE STREAM, CREATE DYNAMIC TABLE ON SCHEMA "' || record.catalog_name || '"."' || record.schema_name || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON ALL TABLES IN SCHEMA "' || record.catalog_name || '"."' || record.schema_name || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON FUTURE TABLES IN SCHEMA "' || record.catalog_name || '"."' || record.schema_name || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON ALL VIEWS IN SCHEMA "' || record.catalog_name || '"."' || record.schema_name || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON FUTURE VIEWS IN SCHEMA "' || record.catalog_name || '"."' || record.schema_name || '" TO ROLE indexima_role;');   
  END FOR;
END;