In Snowflake, secondary roles are a set of roles that authorize any SQL action other than the execution of CREATE <object> statements. For each user, you can specify the set of secondary roles that are active for the user’s session upon login by setting the DEFAULT_SECONDARY_ROLES parameter. For more information about this parameter and its supported values, see the Snowflake documentation.

You can set secondary roles using a post-hook. Let’s assume you have a user defined in your SOLE configuration:

- user:
    name: JohnDoe
    login_name: "john_doe"
    email: "jd@example.com"
    first_name: "John"
    last_name: "Doe"

You can then alter the JohnDoe user by adding account-level post-hook which would set DEFAULT_SECONDARY_ROLES to the preferred value:

account_level_hooks:
  post_hooks:
    - command: "ALTER USER {{ env.DATABASE_NAME_PREFIX }}_JOHNDOE_{{ env.DATAOPS_ENV_NAME }} set DEFAULT_SECONDARY_ROLES = ();"
      environment: snowflake

If you are using SOLE for Data Products, don’t forget to add the path to your hooks file into the dataops_config.yml file:

hook-paths: khooks.yml]

Once your SOLE job completes in the DataOps.live platform, the DEFAULT_SECONDARY_ROLES value for the JohnDoe user in Snowflake would be set to ().