Service User
Why a service user ?
Indexima for Snowflake requires a Snowflake user to analyze your datamodel and create Dynamic Tables automatically. For fine-grained control and security we recommand you create a dedicated role indexima_role
. You can then grant the indexima_role
either to your personal Snowflake user, or to a dedicated Indexima Service user.
You can change this user at any time in the Indexima settings, and adapt the rights of the indexima_role
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 either :
- with PAT (Programmatic Access Token). See documentation to create a PAT.
- with Key-pair. See documentation to generate the private and public keys.
ℹ️ We recommand using PAT, which is easier to setup than key-pair, and allows fine-grained control on programmatic access.
Creating a service user
Execute the following commands on Snowflake to create a service role and user. You need to replace <warehouse>
with the warehouse you want to use for Dynamic table creation.
ℹ️ You need to have the ACCOUNTADMIN role to create the service role and assign it to a user.
sql
CREATE ROLE indexima_role;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE indexima_role;
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;
CREATE USER indexima_usr TYPE=SERVICE DEFAULT_ROLE=indexima_role DEFAULT_WAREHOUSE=<warehouse>;
GRANT ROLE indexima_role TO USER indexima_usr;
ALTER USER indexima_usr ADD PAT indexima_token DAYS_TO_EXPIRY = 365;
-- Copy the newly generated token_secret and use it as normal password for indexima_usr.
ℹ️ A network policy is mandatory to create a PAT (see documentation).
ℹ️ In case you assign the indexima_role
to your personnal Snowflake user, you can restrict the PAT to only this role with :
sql
ALTER USER ADD PAT indexima_token DAYS_TO_EXPIRY = 365 ROLE_RESTRICTION = 'indexima_role';
Configure the service user
After creating the service user in Snowflake, please update the service user in Indexima settings.