Service User ​

Why a service user ? ​

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

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

Service user authentication ​

As per the Snowflake security requirements the service user must authenticate with Key-pair. Please follow Snowflake documentation to generate the private and public keys.

Creating a service user ​

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

ℹ️ You need to have the ACCOUNTADMIN role to create the service user.

sql
CREATE ROLE indexima_role;
CREATE USER indexima_usr TYPE=SERVICE DEFAULT_ROLE=indexima_role DEFAULT_WAREHOUSE=<warehouse> RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
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
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;

Configure the service user ​

After creating the service user in Snowflake, please update the service user in Indexima settings.