Snowflake MFA Setup: Enabling & Disabling

Multi-factor authentication (MFA) can be used for free by all Snowflake customers to provide increased security when connecting to Snowflake. Let's talk about how you can get setup with MFA in Snowflake.

How to enable MFA for a Snowflake User?

To enable MFA for your Snowflake user, click on your profile in the Snowsight UI:

At the bottom of your profile card, you'll see a section for Multi-factor authentication. Click the Enroll button:

Snowflake's MFA feature is powered by Cisco Duo. You do not need to separately sign up for Duo, but you will need to download the Duo Mobile Application on your phone.

Once enrolled, you'll see a screen like this the next time you log in.

How to disable MFA in Snowflake?

Disabling MFA can only be done programmatically with SQL by a user with the ACCOUNTADMIN role. Here's the command to disable MFA for a user:

use role accountadmin;
alter user ian set disable_mfa=true;

Is MFA enabled by default?

MFA is not enabled by default in Snowflake. It must be enabled on a per-user basis and users are not automatically enrolled in MFA. Administrators cannot automatically enforce that all users require MFA. They can instead build automations using the queries below to identify users without MFA and take the appropriate action.

Identify all users without MFA enabled

To identify users without MFA enabled, you can query the snowflake.account_usage.users view (details here).

select *
from snowflake.account_usage.users
where
    ext_authn_duo = false
    and deleted_on is null
order by last_success_login desc
;

Note, this view can be up to 2 hours stale. For an up-to-date copy of the data, run the command show users.

The output of this query will likely contain many machine users, for which it doesn't make sense to enable MFA. Instead, it is recommended to setup key-pair authentication for these users, instead of the traditional username-password authentication method.

Identify all PRIVILEGED users without MFA

It's especially important to identify any users without MFA who have privileged access (one of the default SYSADMIN, SECURITYADMIN, or ACCOUNTADMIN roles), as a compromise of one of these users would be much more significant.

The following query identifies all users with access to one of these roles who do not have MFA enabled:

with 
privileged_roles (role_name) as (
    select grantee_name
    from snowflake.account_usage.grants_to_roles
    where
        privilege = 'MANAGE GRANTS'
        or grantee_name in ('SYSADMIN', 'SECURITYADMIN', 'ACCOUNTADMIN')
)
select
    users.name,
    users.has_password,
    timediff(days, users.last_success_login, current_timestamp())|| ' days ago' as last_login,
    timediff(days, users.password_last_set_time, current_timestamp(6)) || ' days ago' as password_age
from snowflake.account_usage.grants_to_users
inner join privileged_roles
    on grants_to_users.role = privileged_roles.role_name
    and grants_to_users.deleted_on is null
inner join snowflake.account_usage.users
    on grants_to_users.grantee_name = users.name
    and users.ext_authn_duo = false
    and users.deleted_on is null
order by users.last_success_login desc
;

What other security measures should you implement?

In addition to enabling MFA for all human users in your Snowflake Account, consider the following additional security measures recommended by Snowflake:

Use key pair authentication for all users without MFA
Rotate passwords frequently
Utilize account-level Network Policies to only allow logins from known IP addresses for all users
Utilize user-level Network to ensure service accounts can be only used from a more specific list of IP's
Use Authentication Policies to ensure service accounts cannot use the UI
Create a custom role for each service account and ensure least-privilege
Consider using Session Policies and Password Policies
Utilize a username naming convention to monitor all SQL closely and have a low threshold for suspicious activities