Unpacking Snowflake's New User Type Property

What is the User Type Property?

When creating or altering a user in Snowflake, there are dozens of properties and parameters that can be set, as explained in Snowflake’s docs. One Object Property that can be set for users is the type property, which can be set to person, service, legacy_service or null.

This property was recently introduced by Snowflake in Q3 2024 to provide a means of tracking the purpose of each user. I believe the new property is also a subtle way for Snowflake to encourage customers to use key-pair authentication for service accounts, as we will discuss below.

Defining Each Type

• person is obviously meant for human users. These users typically log in to Snowflake via SSO (SAML) or simple User / Password authentication. Snowflake's MFA is always recommended for users of type person.
• service type is meant for service accounts. This type is unique in that it cannot have user / password authentication or MFA. It must use Snowflake's key-pair authentication; therefore common commands and properties related to MFA and Passwords are not allowed. Additionally, users of type service cannot have First, Middle, and Last Name.

• legacy_service is a service account that can authenticate using a Password or SAML.

What are the Advantages?

Account Hardening

By differentiating between service and legacy_service, we are keeping account security top of mind. Key-pair authentication is widely accepted as more secure than password authentication; flagging each account that is using the less secure approach is good housekeeping, and perhaps a to-do list for future cleanup.

Analyzing Query History

Another benefit of maintaining the Type property for all users is that you can leverage this information for analysis. For example, imagine you want to see query counts against a table split by service type using the Snowflake query history:

1select
2    users.type = 'PERSON' as is_person,
3    count(*)
4from snowflake.account_usage.query_history
5left join snowflake.account_usage.users
6    on query_history.user_name=users.name
7where true
8    and query_history.query_text ilike '%analytics.public.orders%'
9    and start_time > current_date - 30
10group by 1

Example Code: Setting the Type Property

Setting the User Type when creating a New User

Setting the type property when creating a user is easy:

1create user if not exists my_service_user 
2    type = service 
3    rsa_public_key = <string>;

Updating the User Type for an Existing User

Imagine we want to set type=service for an existing service account that currently uses Password Auth. When updating an existing user, you could do the bare minimum:

1alter user my_service_user set
2    type = service
3    rsa_public_key = <string>
4;

In this case we did not unset the password or other incompatible properties; the incompatible properties will be stored but disabled. Setting the user type to legacy_service , person, or null would re-enable those properties.

For good housekeeping, I would suggest the following:

1alter user my_service_user unset password;
2alter user my_service_user unset first_name;
3alter user my_service_user unset middle_name;
4alter user my_service_user unset last_name;
5alter user my_service_user set disable_mfa = true;

Limitations

As of now (September 2024), the type property is not displayed in Snowsight UI and can only be set with code. Also, the show users command does not display a column for user type, but hopefully it will soon.

The only way to assess a user’s type is with the describe user <username> command, or by querying the snowflake.account_usage.users table.

Conclusion and Recommendations

The new type property in Snowflake is a great way to track user types in your Snowflake account and can act as a means of account hardening in that it forces you to acknowledge where you have service accounts with legacy authentication.

I recommend reviewing every service account in your Snowflake account; set each one as type = service or type = legacy_service. For the legacy_service accounts, create an action plan on how you will update them to type service in the near future.