Skip to main content

Create a Service User with Custom Service User Objects

If you need to use a different service user name or role name (to stay in compliance with your naming convention for example), create a custom service user and grant privileges. We have a specific stored procedure to accomplish this task. If you need help along the way, contact ALTR Support.

To create a service user with custom service user objects and grant privileges:

  1. Create a service user.

    Note

    When creating your service user, the TYPE=LEGACY_SERVICE parameter is required in order for your service user to authenticate with ALTR.

    Starting 09/30/24, all new Snowflake accounts by default require multi-factor authorization (MFA) to log into Snowflake. Set the TYPE parameter to LEGACY_SERVICE on ALTR's service user to ensure ALTR can authenticate with Snowflake. Learn more. ALTR recommends setting a network policy on the service user to prevent unauthorized access. Learn more.

  2. Create a role. For the list of privileges required by the service role, refer to Service User Privilege Requirements.

  3. Create a warehouse.

  4. Create a database. By default, the stored procedure grants access to everything in your Snowflake account. Specify a list of databases to limit what the role can access.

  5. Update the following SnowSQL by setting the the required parameters:

    • SERVICE_USER—The username for the service user. This service user must be created before executing the stored procedure. It defaults to ALTR_SERVICE_USER if it is not supplied. If you created your ALTR account from Snowflake Partner connect and would like to use the default Partner Connect user, set this to PC_ALTR_USER.

    • SERVICE_ROLE—The default role for the service user. This defaults to ALTR_SERVICE_ROLE if not set, and PC_ALTR_ROLE if SERVICE_USER is set to PC_ALTR_USER. The procedure attempts to create this role if it does not already exist.

    • SERVICE_WAREHOUSE—The default warehouse of  the service user. This defaults to ALTR_SERVICE_WH if not set, and PC_ALTR_WH if SERVICE_USER is set to PC_ALTR_USER. The procedure attempts to create this warehouse if it does not already exist.

    • DATABASE_NAMES—An array of databases to which privileges are granted. If left blank or set to an empty array, this defaults to all databases in your Snowflake account. By specifying an array of specific databases, this parameter limits the stored procedure to only grant database, schema and table/view level privileges within the specified databases. This is useful for reducing the amount of time the stored procedure takes to execute for troubleshooting.

  6. Run the SnowSQL in the database to create the ALTR_SETUP_SERVICE_ACCOUNT stored procedure. You should take note of which database you create the procedure in.

  7. Execute the following command to run the stored procedure, setting the parameters as defined above. The stored procedure may take several minutes to run, especially for Snowflake accounts with many databases, schemas and tables. You can reduce this time be specifying a limited number of databases in the relevant argument.

    CALL SETUP_ALTR_SERVICE_ACCOUNT(<SERVICE_USER>,<DATABASE_NAMES>,<SERVICE_ROLE>,<SERVICE_WAREHOUSE>);

Note

ALTR recommends assigning a network policy to the service user to further secure the user and to prevent unauthorized access. Learn more.