User management¶

Types of users¶

Some user objects correspond to human users while other user objects correspond to a service or application that interacts with Snowflake programmatically without human interaction. When you create a user object, you specify the type of user to differentiate between humans and services. This distinction is important because human users should enroll in multi-factor authentication (MFA), but services and applications should not because there is no human to use a secondary method of authentication.

The TYPE property of a user object determines the type of user. Possible values of this TYPE property are as follows:

PERSON:

User is a human user who can interact with Snowflake.

NULL:

Functions the same PERSON.

SERVICE:

User is a service or application that interacts with Snowflake without human interaction.

To improve the security posture of non-interactive use cases, users with the TYPE property set to SERVICE have the following characteristics:

• They cannot log in using a password.

• They cannot log in using SAML SSO.

• They cannot enroll in MFA.

• They are not subject to authentication policy MFA enforcement.

• They cannot have the following properties:

  • FIRST_NAME

  • MIDDLE_NAME

  • LAST_NAME

  • PASSWORD

  • MUST_CHANGE_PASSWORD

  • MINS_TO_BYPASS_MFA

• The following commands cannot be used:

  • ALTER USER RESET PASSWORD

  • ALTER USER SET DISABLE_MFA = TRUE

SNOWFLAKE_SERVICE:

User that is created by Snowflake for Snowpark Container Services. Administrators cannot create users of type SNOWFLAKE_SERVICE, nor can they change the type of an existing user to be SNOWFLAKE_SERVICE. For more information about SNOWFLAKE_SERVICE users, see Connecting to Snowflake from inside a container.

LEGACY_SERVICE:

A user with their TYPE property set to LEGACY_SERVICE represents a non-interactive integration. It is similar to SERVICE, but allows password and SAML authentication. In a future release, the LEGACY_SERVICE type will be deprecated, at which time all non-interactive use cases will use the SERVICE type with a secure method of authentication.

LEGACY_SERVICE users have the following characteristics:

• They can log in using password or SAML authentication, unlike SERVICE users.

• They have the properties of a SERVICE user, but have the PASSWORD and MUST_CHANGE_PASSWORD properties available.

• They have the command restrictions of a SERVICE user, but can use the ALTER USER RESET PASSWORD command.

• They are not affected by authentication policy multi-factor authentication (MFA) enforcement. This property lets administrators set account-level authentication policies that require MFA for password authentication without needing to set a user-level authentication policy to prevent LEGACY_SERVICE users from requiring MFA for password authentication.

User roles¶

Snowflake uses roles to control the objects (virtual warehouses, databases, tables, etc.) that users can access:

• Snowflake provides a set of predefined roles, as well as a framework for defining a hierarchy of custom roles.

• All Snowflake users are automatically assigned the predefined PUBLIC role, which enables login to Snowflake and basic object access.

• In addition to the PUBLIC role, each user can be assigned additional roles, with one of these roles designated as their default role. A user’s default role determines the role used in the Snowflake sessions initiated by the user; however, this is only a default. Users can change roles within a session at any time.

• Roles can be assigned at user creation or afterwards.

Privileges required to create and modify users¶

The following roles or privileges are required to manage users in your account:

Create users:

The USERADMIN system role can create users using SQL (CREATE USER).

If you prefer to use a custom role for this purpose, grant the CREATE USER privilege on the account to this role.

Modify users:

Only the role with the OWNERSHIP privilege on a user can modify most user properties using SQL (ALTER USER).

Creating users¶

You can create a user by using the following interfaces.

Disabling or enabling a user¶

Disabling a user prevents the user from logging into Snowflake. You can disable a user through the following interfaces.

Unlocking a user¶

If a user login fails after five consecutive attempts, the user is locked out of their account for a period of time (currently 15 minutes). After the period of time elapses, the system automatically clears the lock and the user can attempt to log in again.

To unlock the user before the time has elapsed, you can reset the timer using the ALTER USER command or the UserResource.create_or_alter Python API.

The following example resets the timer to 0, which immediately unlocks user janesmith:

ALTER USER janesmith SET MINS_TO_UNLOCK= 0;

user_parameters = root.users["janesmith"].fetch()
user_parameters.mins_to_unlock = 0
root.users["janesmith"].create_or_alter(user_parameters)

Altering session parameters for a user¶

• To show the session parameters for a user, use the following SQL syntax:

  SHOW PARAMETERS [ LIKE '<pattern>' ] FOR USER <name>
  

  Copy

• To alter the session parameters for a user, use the following syntax:

  ALTER USER <name> SET <session_param> = <value>
  

  Copy

  For example, allow a user to remain connected to Snowflake indefinitely without timing out:

  ALTER USER janesmith SET CLIENT_SESSION_KEEP_ALIVE = TRUE;
  

  Copy

• To reset a session parameter for a user to the default value, use the following syntax:

  ALTER USER <name> UNSET <session_param>
  

  Copy

Modifying other user properties¶

You can modify all other user properties using the ALTER USER command or the UserResource.create_or_alter Python API. You can modify many of the same user properties using Snowsight.

For example:

• Change the last name for user janesmith to Jones:

  SQL:

  ALTER USER janesmith SET LAST_NAME = 'Jones';
  

  Copy

  Python:

  user_parameters = root.users["janesmith"].fetch()
  user_parameters.last_name = "Jones"
  root.users["janesmith"].create_or_alter(user_parameters)
  

  Copy

  Snowsight:

  1. Sign in to Snowsight.

  2. Select Admin » Users & Roles.

  3. Locate the user that you want to edit and select » Edit.

  4. For the Last Name field, enter Jones.

  5. Select Save User.

• Set or change the default warehouse, namespace, primary role, and secondary roles for user janesmith:

  SQL:

  ALTER USER janesmith SET DEFAULT_WAREHOUSE = mywarehouse DEFAULT_NAMESPACE = mydatabase.myschema DEFAULT_ROLE = myrole DEFAULT_SECONDARY_ROLES = ('ALL');
  

  Copy

  Python:

  user_parameters = root.users["janesmith"].fetch()
  user_parameters.default_warehouse = "mywarehouse"
  user_parameters.default_namespace = "mydatabase.myschema"
  user_parameters.default_role = "myrole"
  user_parameters.default_secondary_roles = "ALL"
  root.users["janesmith"].create_or_alter(user_parameters)
  

  Copy

  Snowsight:

  Note

  You cannot set default secondary roles for a user using Snowsight.

  1. Sign in to Snowsight.

  2. Select Admin » Users & Roles.

  3. Locate the user that you want to edit and select » Edit.

  4. Open the Advanced User Options and enter values in the relevant fields.

  5. Select Save User.

Viewing users¶

You can view information about users using the following interfaces.

DESC USER janeksmith;

my_user = root.users["janesmith"].fetch()
print(my_user.to_dict())

users = root.users.iter(like="jane%")
for user in users:
  print(user.name)

Dropping a user¶

Dropping a user removes the user credentials from Snowflake.

Objects created by the user, such as tables or views, are not dropped because they are owned by the user’s active role when the objects were created. Another user assigned the same role or a higher role in the role hierarchy can manage the objects or transfer ownership to another role.

DROP USER janesmith;

root.users["janesmith"].drop()