How to ensure that users are enrolled in MFA

Question

I have a large number of users to set up that all need to access to the data warehouse at the same time. A security requirement for our company is that all users must be set up with multi-factor authentication. From what I have read, users must enroll themselves in MFA, so how can we ensure that users are enrolled before they can access the data ? Is there any best practice surrounding this ?

Answer 1

You can list out all the users which have ext_authn_duo=false by running:

show users

This will be the easiest way to get the list and also have those users enroll for DUO.

Expanding on this further, you can build a snowsight (New UI) dashboard and use the following Stored procedure to get the details which can be run using Tasks as detailed here:

/** This is the Stored procedure**/

    create or replace procedure MFA_USERS()
returns string
language javascript
Execute as CALLER
as
$$

var qry = ` show users `;
var qry_rslt = snowflake.execute({sqlText: qry});
var qry_id= qry_rslt.getQueryId();

var qry2 = ` select * from table(result_scan('${qry_id}')) `;
rs = snowflake.execute({sqlText: qry2});

var out = "";
var i = 0;

while (rs.next()) {
    if (i++ > 0) out += ",";
    out += rs.getColumnValue(1);
}

return out;

$$;
  


  /** Create a task that calls the stored procedure every hour**/
    create task my_copy_task
      warehouse = mywh
      schedule = '60 minute'
    as
      call MFA_USERS();

This will give only the list of those users in a list for review from dashboard and avoid the need to run the query manually.

Answer 2

As you said, users must enroll themself into MFA.

One way to ensure that they are not able to access any data in snowflake before this enrollment is using the Role Based Access Control approach. As your user is created, do not grant him any role besides the PUBLIC role which is the default role and should not have any access to real data.

Then you can automate a process that will control using SHOW USERS command which users activated their MFA access (ext_authn_duo and ext_authn_uid columns). Then you can grant an access role to data to this confirmed users.

One other approach could be to let a short time period for recent users to enroll into MFA, automate check and disable their access to Snowflake if not fulfilled using ALTER USER my_user SET disabled = true.

Answer 3

I asked Snowflake support about this and they said that easiest would be to use federated authentication and enforce the requirements on the IdP level.

https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-overview