Best practices for migration from single-factor authentication¶

Evaluation logic for healthy users¶

Sample queries for lists of risky users¶

The following query lists the latest risky users and why they are risky:

SELECT DISTINCT
  f.value:entity_id::VARCHAR AS entity_id,
  f.value:entity_name::VARCHAR AS entity_name,
  f.value:entity_detail:user_type::VARCHAR as user_type,
  f.value:entity_detail:has_password::VARCHAR as has_password,
  f.value:entity_detail:has_rsa_public_key::VARCHAR as has_rsa_public_key,
  f.value:entity_detail:mfa_enabled::VARCHAR as mfa_enabled,
  f.value:entity_detail:account_auth_policy_name::VARCHAR as Account_level_Auth_policy,
  f.value:entity_detail:account_auth_policy_requires_mfa::VARCHAR as Account_level_EnforceMFA_policy,
  f.value:entity_detail:user_auth_policy_name::VARCHAR as User_level_Auth_policy,
  f.value:entity_detail:user_auth_policy_requires_mfa::VARCHAR as User_level_EnforceMFA_policy,
  f.value:entity_detail:account_network_policy_name::VARCHAR as Account_level_Net_policy_Name,
  f.value:entity_detail:account_network_policy_Allowlist::VARCHAR as Account_level_Net_policy_Allowlist,
  f.value:entity_detail:user_network_policy_name::VARCHAR as User_level_Net_policy_Name,
  f.value:entity_detail:user_network_policy_allowlist::VARCHAR as User_level_Net_policy_Allowlist,
FROM SNOWFLAKE.TRUST_CENTER.FINDINGS,
  LATERAL FLATTEN(input => at_risk_entities) AS f
WHERE EVENT_ID =
  (SELECT event_id FROM snowflake.trust_center.findings WHERE SCANNER_PACKAGE_NAME = 'Threat Intelligence'
   ORDER BY event_id DESC LIMIT 1);

Snowflake recently added two new scanners: one for person users and one for service users. This allows customers to have separate lists of users using single-factor password sign-on, depending on the user type.

You can return a list of risky users for each type.

SELECT *
  FROM SNOWFLAKE.TRUST_CENTER.FINDINGS
  WHERE event_id =
    (SELECT event_id FROM SNOWFLAKE.TRUST_CENTER.FINDINGS
    WHERE scanner_id = 'THREAT_INTELLIGENCE_NON_MFA_PERSON_USERS'
    ORDER BY event_id DESC LIMIT 1) AND total_at_risk_count != 0
;

SELECT *
  FROM SNOWFLAKE.TRUST_CENTER.FINDINGS
  WHERE event_id =
    (SELECT event_id FROM SNOWFLAKE.TRUST_CENTER.FINDINGS
    WHERE scanner_id = 'THREAT_INTELLIGENCE_PASSWORD_SERVICE_USERS'
    ORDER BY event_id DESC LIMIT 1 ) and total_at_risk_count != 0
;

User distribution across TYPE and authentication method used¶

In addition to the previous query, it is beneficial to list user distribution across user types and used authentication methods. This helps customers strategize on user migration to stronger authentication methods such as SAML and OAuth. For instance, if a user is showing as risky (because they have a password in Snowflake) but that user is only using SAML authentication, it is advisable to remove that password from Snowflake as soon as possible.

WITH users AS (
  SELECT DISTINCT
       user_id
      , name
      , login_name
      , type
      , email
  FROM
      SNOWFLAKE.ACCOUNT_USAGE.USERS
  WHERE
      DELETED_ON IS NULL)
SELECT
    u.user_id
    , a.event_timestamp
    , a.user_name
    , u.type
    , a.reported_client_type
    , a.first_authentication_factor
    , a.second_authentication_factor
  FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY AS a
    JOIN USERS u ON a.user_name = u.name
;

Migration considerations¶

Here are a few considerations to keep in mind when you plan your migration:

• First, set user types (this can be automated via SCIM).

• Second, check what authentication methods your applications support: Snowflake supports a variety of authentication methods, such as OAuth, SAML, key-pair, MFA, and so on. However, the application that connects to Snowflake also needs to support strong authentication methods. There are two use cases:

  • The app already supports SAML and/or OAuth. You are encouraged to migrate to that authentication as soon as possible.

  • The app is legacy and does not support strong authentication methods such as SAML or OAuth; it supports passwords only. In this case, you are advised to update your legacy application. In the meantime, you can leverage network policies, password rotation, and leaked password protection features until you update their application.

• Next, customers should consider the following for local users (created manually in Snowflake who are not SAML or OAuth enabled):

  • For an application that supports SSO, switch the local user to SSO-enabled users and consider downtime when switching users.

  • To switch local users to SSO-enabled, make sure such users exist in your IdP and then sre provisioned in Snowflake manually, or preferably automatically, via SCIM.

  • Disable unused local users.

• Snowflake supports authentication policies, network policies, and password policies at both the user level and account level. Consider user-level policies first to migrate gradually (user-level policies take priority over account-level policies):

  • Snowflake recommends user-level network policies for applications that use service users (TYPE = SERVICE or LEGACY_SERVICE).

  • For human users (TYPE = PERSON or NULL), you can start with user-level network policies and then apply network policies at the account level to protect all user populations that do not have user-level-specific policies.

  • The same concept with MFA policies starts with user-level policies first.

• If you have a legacy application that does not support single sign-on, Snowflake recommends leveraging programmatic access tokens (PATs), which by default are tied to specific roles, require a network policy, and are time-bound.

Set user type¶

The first step in the protection phase is to set the user types automatically via SCIM or manually:

ALTER USER svc_user1 SET TYPE = SERVICE;
ALTER USER user1@example.COM SET TYPE = PERSON;
-- LEGACY APPLICATION ONLY

In addition, you can now set the admin user type when they create an account:

CREATE ACCOUNT <name> [ ADMIN_USER_TYPE = PERSON | SERVICE | LEGACY_SERVICE | NULL ];

Remove local passwords when they are not needed¶

Leverage the queries under User Distribution Across TYPE and AuthN Method Used and List of Risky Users, based on trust center findings, to start cleaning any password in Snowflake for a user who is already using SAML, OAuth, or keypair exclusively.

Create authentication policies for service users¶

Snowflake recommends using OAuth for programmatic service users, and you can enforce that with authentication policies:

CREATE AUTHENTICATION POLICY PROGRAMMATIC_ACCESS_USER_AUTH
  CLIENT_TYPES = ('DRIVERS', 'SNOWSQL')
  AUTHENTICATION_METHODS = ('OAUTH')
  SECURITY_INTEGRATIONS = ('<OAUTH SECURITY INTEGRATIONS>');

ALTER USER <SERVICE_USER> SET AUTHENTICATION POLICY PROGRAMMATIC_ACCESS_USER_AUTH;

Customers can still use keypair for authentication with SERVICE users, but they should combine that with network policies and regularly rotate their keys.

Create authentication policies to enforce MFA on human users¶

Snowflake recommends that customers use their own SAML identity providers (IdPs) with any MFA solution that their IdP supports. The following authentication policy example helps customers to:

• Enforce Snowflake native MFA for any human users who use Snowflake native passwords.

• Rely on the customer SAML IdP to enforce MFA for single sign-on users.

CREATE AUTHENTICATION POLICY HUMAN_ACCESS_ACCOUNT_ENFORCE_MFA
  AUTHENTICATION_METHODS = ('SAML', 'PASSWORD')
  SECURITY_INTEGRATIONS = ('<SAML SECURITY INTEGRATIONS>')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD'); -- enforce Snowflake MFA for native passwords only
  MFA_ENROLLMENT = 'REQUIRED';

Customers should consider a break-glass situation in case the customer IdP is offline so their account admins can still log in to their Snowflake accounts.

CREATE AUTHENTICATION POLICY ACCOUNTADMIN_BREAKGLASS_MFA
  AUTHENTICATION_METHODS = ('PASSWORD')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD') -- enforce Snowflake MFA for native passwords only
  MFA_ENROLLMENT = 'REQUIRED';

CREATE AUTHENTICATION POLICY ACCOUNTADMIN_DOUBLE_MFA
  AUTHENTICATION_METHODS = ('PASSWORD', 'SAML')
  SECURITY_INTEGRATIONS = ('<SAML SECURITY INTEGRATIONS>')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD', 'SAML') -- double MFA
  MFA_ENROLLMENT = 'REQUIRED';

Create password policy¶

In case you have a legacy system or a break-glass situation where you must use Snowflake native passwords, please leverage Snowflake password policies to match organization password policies, if they are different from the default policies.

CREATE PASSWORD POLICY password_policy_account
  PASSWORD_MIN_LENGTH = 32
  -- PASSWORD_MAX_LENGTH = <integer>
  PASSWORD_MIN_UPPER_CASE_CHARS = 6
  PASSWORD_MIN_LOWER_CASE_CHARS = 6
  PASSWORD_MIN_NUMERIC_CHARS = 4
  PASSWORD_MIN_SPECIAL_CHARS = 8
  PASSWORD_MIN_AGE_DAYS = 10
  PASSWORD_MAX_AGE_DAYS = 30
  PASSWORD_MAX_RETRIES = 3
  PASSWORD_LOCKOUT_TIME_MINS = 30
  PASSWORD_HISTORY = 24
  COMMENT = '<string_literal>';

Create session policy¶

Snowflake recommends that you create session policies to enforce reauthentication after a specific period of inactivity. This is just an example; you can customize policies at the individual user level.

CREATE SESSION POLICY session_policy_account
  SESSION_IDLE_TIMEOUT_MINS = 240 -- Snowflake clients and programmatic clients
  SESSION_UI_IDLE_TIMEOUT_MINS = 20 -- For the Snowflake web interface
  COMMENT = '<string_literal>';

Create network policies for service users¶

In general, service or programmatic access users should come from authorized IP addresses (or VPCE ID, LinkID, and so on, in case of private connectivity).

Snowflake recommends creating service user-level network policies to restrict access to those programmatic access users from authorized and trusted sources. Customers should consider enforcing network rules at internal stages as well.

Connections can come from private or public networks, depending if the customer is using private connectivity or not. Keep in mind that you can allow both public and private connectivity at the same time by adding multiple network rules that include the relevant IPs (public or private) and/or CSP tags (such as VPCE ID, LinkIDs).

-- ACCESS FROM PUBLIC IP ADDRESSES
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_PUBLIC
  TYPE = IPV4
  VALUE_LIST = ( 'PUBLIC IP1' , 'XX.XX.XX.XX/24' [ , ... ] )
  MODE =  INGRESS
;
-- ACCESS FROM PRIVATE NETWORK
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_PL
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1931' , 'VPCE-123ABC3420C1932' )
  MODE =  INGRESS
;
-- RESTRICT ACCESS TO INTERNAL STAGE USING VPCE ID
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_INTERNAL_STAGE
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1933' )
  MODE =  INTERNAL_STAGE
;
CREATE NETWORK POLICY PROGRAMMATIC_ACCESS_USER_NET_POLICY
  ALLOWED_NETWORK_RULE_LIST =
    (
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_PUBLIC',
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_PL',
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_INTERNAL_STAGE'
    )
  --BLOCKED_NETWORK_RULE_LIST = ( 'OPTIONAL BLOCKED LIST OF IPS' )
;

Create network policies at the account level¶

Account-level policies work as default security network policies for users who do not have network policies applied directly to them. The best practice is to keep these policies as restrictive and as short as possible while using user-level policies to handle specific user needs.

Snowflake does not recommend building huge account-level network policies to satisfy all the organization’s needs; instead, enables user-level policies to have more granular controls.

Similar to user-level network policies, connections can come from private or public networks, depending on whether the customer is using private connectivity. Keep in mind that you can allow both public and private connectivity at the same time by adding multiple network rules that include the relevant IPs (public or private) and/or CSP tags (such as VPCE ID, LinkIDs).

-- ACCESS FROM PUBLIC IP ADDRESSES
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_PUBLIC
  TYPE = IPV4
  VALUE_LIST = ( 'PUBLIC IP1' , 'XX.XX.XX.XX/24' [ , ... ] )
  MODE =  INGRESS
;
-- ACCESS FROM PRIVATE NETWORK
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_PL
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1934' , 'VPCE-123ABC3420C1936' )
  MODE =  INGRESS
;
-- RESTRICT ACCESS TO INTERNAL STAGE USING VPCE ID
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_INTERNAL_STAGE
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1937')
  MODE =  INTERNAL_STAGE
;
CREATE NETWORK POLICY ACCOUNT_LEVEL_NET_POLICY
  ALLOWED_NETWORK_RULE_LIST =
   (
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_PUBLIC',
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_PL',
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_INTERNAL_STAGE'
   )
   --BLOCKED_NETWORK_RULE_LIST = ( 'OPTIONAL BLOCKED LIST OF IPS' )
;

Protect service users¶

Users with TYPE=SERVICE are exempted from account-level MFA enforcement policies and have restrictions to improve the security posture of noninteractive use cases. Notably, SERVICE-type users cannot log in using a password or SAML SSO. See the caution below.

-- FOR EVERY SERVICE PROGRAMMATIC ACCESS USER
ALTER USER SERVICE_USER_1 SET
TYPE = SERVICE
NETWORK_POLICY = PROGRAMMATIC_ACCESS_USER_NET_POLICY
AUTHENTICATION POLICY = PROGRAMMATIC_ACCESS_USER_AUTH;

Apply password and session policies at the account level¶

The Snowflake security admin should apply baseline password and session policies at the account level and then override these policies with user-level policies as needed.

ALTER ACCOUNT SET
SESSION POLICY = SESSION_POLICY_ACCOUNT;
PASSWORD POLICY = PASSWORD_POLICY_ACCOUNT;

Test service users¶

At this stage, the account has password and session policies applied; the service programmatic users are exempted from MFA; and they have their own specific authentication policies and network policies that make sure the connections are established from trusted sources and use the recommended authentication methods, such as OAuth or key-pair.

The security admin should test a few service users to ensure smooth operations and to confirm that the connections are coming from trusted sources and using the proper authentication methods. Admins should use LOGIN_HISTORY, in addition to the Trust Center, to confirm that the service users are protected by network policies.

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY(TIME_RANGE_START =>
    DATEADD('HOURS',-1,CURRENT_TIMESTAMP()),CURRENT_TIMESTAMP()))
  ORDER BY EVENT_TIMESTAMP;

Protect the account with MFA enforcement¶

To enforce MFA for all non-SERVICE type users, apply the MFA enforcement policy at the account level.

These policies help ensure that all human interactive users enable MFA either from their own IdP or from the native Snowflake MFA.

ALTER ACCOUNT SET
AUTHENTICATION POLICY = HUMAN_ACCESS_ACCOUNT_ENFORCE_MFA;

If you want to enforce double MFA on your highly privileged users, such as account admins, enforce MFA at this user level. However, you need to strike a balance between break-glass procedures in case your IdP is down and your security requirements.

ALTER USER SUPER_PROTECTED_ACCOUNTADMIN_1
AUTHENTICATION POLICY = ACCOUNTADMIN_DOUBLE_MFA;

For a break-glass situation:

ALTER USER BREAKGLASS_ACCOUNTADMIN_1
AUTHENTICATION POLICY = ACCOUNTADMIN_BREAKGLASS_MFA;

Apply account-level network policies¶

Finally, it is time to apply network policies at the account level to protect all other users that do not have explicit network policies.

ALTER ACCOUNT SET
NETWORK_POLICY = ACCOUNT_LEVEL_NET_POLICY;

Disable unused users¶

The Trust Center CIS scanner (1.8) will monitor users who have not been active in the past 90 days. As shown in the following diagram, you can click Open a Worksheet to show the query that lists the inactive users:

SELECT
  F.VALUE:ENTITY_ID::VARCHAR AS ENTITY_ID,
  F.VALUE:ENTITY_NAME::VARCHAR AS ENTITY_NAME,
  F.VALUE:ENTITY_OBJECT_TYPE::VARCHAR AS ENTITY_OBJECT_TYPE,
  F.VALUE:ENTITY_DETAIL AS ENTITY_DETAIL
FROM
  SNOWFLAKE.TRUST_CENTER.FINDINGS,
  LATERAL FLATTEN(INPUT => AT_RISK_ENTITIES) AS F;

Snowflake highly recommends that you disable those users.