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 ​

Execute the following commands on Snowflake to create a service user. You need to replace <warehouse> with the warehouse you want to use for Dynamic table creation, and the RSA_PUBLIC_KEY with the public key created for the service account.

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

sql
CREATE ROLE indexima_role;
GRANT USAGE ON WAREHOUSE <warehouse> TO 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 IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE indexima_role;

DECLARE
  c1 CURSOR FOR (SELECT database_name as dn FROM snowflake.account_usage.databases WHERE deleted is null and type='STANDARD');
BEGIN
  FOR rec IN c1 DO
    EXECUTE IMMEDIATE ('GRANT USAGE  ON DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON ALL TABLES IN DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON FUTURE TABLES IN DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON ALL VIEWS IN DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT SELECT ON FUTURE VIEWS IN DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT USAGE, CREATE STREAM ON ALL SCHEMAS IN DATABASE "' || rec.dn || '" TO ROLE indexima_role;');
    EXECUTE IMMEDIATE ('GRANT CREATE DYNAMIC TABLE ON ALL SCHEMAS IN DATABASE "' || rec.dn || '" 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.