Authentication & SSO

Snowflake supports multiple authentication mechanisms, ranging from simple username/password to enterprise-grade federated identity via SAML 2.0. This module walks through every method tested on the COF-C02 exam, explains the underlying security model, and shows you exactly which SQL commands to know on exam day.

📖

Key Terms — Authentication & SSO

AUT

Authentication

The process of verifying the identity of a user or service before granting access to Snowflake resources.

Multi-Factor Authentication

MFA

Requires users to present two or more verification factors. Snowflake uses Duo Security for push notifications and TOTP codes.

Security Assertion Markup Language 2.0

SAML 2.0

An XML-based open standard for federated authentication. Allows an external identity provider to authenticate users on behalf of Snowflake.

Identity Provider

IdP

An external system (Okta, Azure AD, ADFS, PingFederate) that stores user credentials and issues SAML assertions to Snowflake.

Single Sign-On

SSO

Allows users to authenticate once with their corporate identity provider and access Snowflake without entering Snowflake-specific credentials.

OAU

OAuth 2.0

An authorisation framework that allows third-party applications (Tableau, Power BI, dbt) to access Snowflake on behalf of a user without sharing passwords.

SEC

Security Integration

A Snowflake object (CREATE SECURITY INTEGRATION) used to configure external authentication mechanisms including SAML2, OAuth, and SCIM.

System for Cross-domain Identity Management

SCIM

An open standard protocol that automates user and group provisioning from an identity provider into Snowflake.

JSON Web Token

JWT

A compact, signed token used with key-pair authentication and External OAuth. Tokens contain claims and carry an expiry time.

KEY

Key-Pair Authentication

An alternative to password login using an RSA 2048-bit public/private key pair. The private key stays on the client; the public key is registered on the Snowflake user object.

SES

Session Policy

A Snowflake policy object that controls session timeout and MFA requirements. Applied at account or user level.

SCI

SCIM Token

A bearer token generated in Snowflake that authorises an IdP to make SCIM API calls for automated provisioning.

Why Authentication Matters for COF-C02

The exam dedicates a meaningful slice of questions to the Security domain, and authentication sits at the foundation of that domain. Expect questions on:

Which authentication method requires a Security Integration object
The commands to configure key-pair authentication on a user
How MFA token caching works and how long it lasts
The difference between Snowflake OAuth and External OAuth
How SCIM relates to user provisioning (not authentication itself)

🎯Exam Domain Weighting

Security, Governance & Data Protection accounts for approximately 15% of the COF-C02 exam. Authentication is one of the most directly testable topics within this domain because the configuration steps map to exact SQL commands.

1. Username and Password Authentication

The default authentication method. Every Snowflake user has a username and password unless an alternative method is configured.

Password Policies

Snowflake allows accounts to enforce password complexity via password policy objects. Only users with the ACCOUNTADMIN role (or a role granted the CREATE PASSWORD POLICY privilege) can create and apply these policies.

Creating and Applying a Password Policy

1-- Create a password policy
2CREATE PASSWORD POLICY corporate_password_policy
3PASSWORD_MIN_LENGTH = 12
4PASSWORD_MAX_LENGTH = 64
5PASSWORD_MIN_UPPER_CASE_CHARS = 1
6PASSWORD_MIN_LOWER_CASE_CHARS = 1
7PASSWORD_MIN_NUMERIC_CHARS = 1
8PASSWORD_MIN_SPECIAL_CHARS = 1
9PASSWORD_MAX_AGE_DAYS = 90
10PASSWORD_MAX_RETRIES = 5
11PASSWORD_LOCKOUT_TIME_MINS = 15
12COMMENT = 'Corporate security standard password policy';
13
14-- Apply to the entire account
15ALTER ACCOUNT SET PASSWORD_POLICY = corporate_password_policy;
16
17-- Apply to a specific user
18ALTER USER jsmith SET PASSWORD_POLICY = corporate_password_policy;
19
20-- Force a user to change password on next login
21ALTER USER jsmith SET MUST_CHANGE_PASSWORD = TRUE;
22
23-- Show existing password policies
24SHOW PASSWORD POLICIES;
25
26-- Describe a specific policy
27DESCRIBE PASSWORD POLICY corporate_password_policy;

💡MUST_CHANGE_PASSWORD

The MUST_CHANGE_PASSWORD = TRUE flag is particularly useful when creating new user accounts. It ensures users set their own password before doing any work, rather than using a temporary password you may have shared. Snowflake prompts for a new password on the very first login.

2. Key-Pair Authentication

Key-pair authentication replaces the password with an RSA public/private key pair. The private key never leaves the client machine. The public key is registered against the Snowflake user. When the client connects, it signs a JWT using the private key; Snowflake validates the signature using the stored public key.

This method is strongly preferred for service accounts, scripts, and CI/CD pipelines because:

No password to rotate or leak
Supports key rotation without downtime (two keys: RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2)
Compatible with SnowSQL, Python Connector, JDBC Driver, ODBC Driver, and Go Snowflake Driver

Key-Pair Authentication Flow

A detailed flow diagram showing the key-pair authentication handshake. On the left side, the Client (laptop or CI/CD server) holds a private key file (rsa_key.p8). An arrow labelled 'Sign JWT with private key' points to a JWT token object in the centre. This JWT is sent over HTTPS to Snowflake Cloud Services on the right. Snowflake Cloud Services holds the RSA_PUBLIC_KEY that was registered on the user object via ALTER USER. An arrow labelled 'Verify JWT signature using public key' points from the JWT back into Cloud Services. If valid, a green tick and 'Session granted' label appear. The entire flow is contained within an encrypted TLS channel. A note at the bottom states: The private key is NEVER transmitted. Only the signed JWT travels over the wire.

Configuring Key-Pair Authentication Step by Step

Generate an RSA private key

Use OpenSSL on your local machine to generate a 2048-bit RSA private key. You can optionally encrypt it with a passphrase using the -aes256 flag for an extra layer of protection.

SQL

# Generate an unencrypted private key (2048-bit RSA)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Or generate with passphrase protection
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out rsa_key.p8

💡Store rsa_key.p8 in a secrets manager (AWS Secrets Manager, Azure Key Vault, HashiCorp Vault) rather than on the filesystem in production.

Extract the public key

Derive the corresponding public key from the private key file. This is the value you will register in Snowflake.

SQL

# Extract the public key in PEM format
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

# View the public key contents
cat rsa_key.pub

Register the public key on the Snowflake user

Copy the public key content (excluding the header/footer lines) and set it on the Snowflake user using ALTER USER. This requires SECURITYADMIN or higher.

SQL

-- Register the public key on a user
ALTER USER service_account
SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...';

-- Verify the key was set (shows fingerprint, not the raw key)
DESC USER service_account;

💡Remove the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines and all newlines before pasting into the SQL statement.

Configure the client to use key-pair auth

Update your SnowSQL config file or Python Connector connection parameters to reference the private key instead of a password.

SQL

# SnowSQL config (~/.snowsql/config)
[connections.myconn]
accountname = myaccount
username = service_account
private_key_path = /path/to/rsa_key.p8

# Python Connector example
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.backends import default_backend
import snowflake.connector

with open('/path/to/rsa_key.p8', 'rb') as f:
  private_key = serialization.load_pem_private_key(
      f.read(), password=None, backend=default_backend()
  )
pkb = private_key.private_bytes(
  encoding=serialization.Encoding.DER,
  format=serialization.PrivateFormat.PKCS8,
  encryption_algorithm=serialization.NoEncryption()
)
conn = snowflake.connector.connect(
  account='myaccount',
  user='service_account',
  private_key=pkb
)

Rotate keys without downtime

Snowflake supports two simultaneous public keys (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2). Register the new key as the second key first, then update your clients, then remove the old key.

SQL

-- Step 1: Register the new key as key 2
ALTER USER service_account
SET RSA_PUBLIC_KEY_2='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEB...';

-- Step 2: Update clients to use new private key
-- Step 3: Remove the old key after confirming clients work
ALTER USER service_account UNSET RSA_PUBLIC_KEY;

💡This zero-downtime rotation is one of the reasons key-pair auth is preferred over passwords for service accounts.

3. Multi-Factor Authentication (MFA)

Snowflake’s MFA integration is powered by Duo Security. Users self-enrol through their Snowsight profile settings. Once enrolled, each login triggers either a Duo push notification to the user’s mobile device or prompts for a TOTP (Time-based One-Time Password) code.

MFA Token Caching

To reduce friction for users who run many short queries in a session, Snowflake supports MFA token caching. After a successful MFA challenge, Snowflake issues a temporary token valid for 20 minutes by default. Subsequent connections within that window do not require a new MFA challenge.

MFA and Session Policy Management

1-- Create a session policy requiring MFA
2CREATE SESSION POLICY require_mfa_policy
3SESSION_IDLE_TIMEOUT_MINS = 30
4SESSION_UI_IDLE_TIMEOUT_MINS = 30
5COMMENT = 'Requires MFA; 30-minute idle timeout';
6
7-- Apply the session policy to the entire account
8ALTER ACCOUNT SET SESSION_POLICY = require_mfa_policy;
9
10-- Apply to a specific user
11ALTER USER analyst01 SET SESSION_POLICY = require_mfa_policy;
12
13-- Apply to a specific role (all users assigned this role inherit it)
14ALTER ROLE data_engineer SET SESSION_POLICY = require_mfa_policy;
15
16-- Show session policies
17SHOW SESSION POLICIES;
18
19-- Describe a session policy
20DESCRIBE SESSION POLICY require_mfa_policy;
21
22-- Remove a session policy from a user
23ALTER USER analyst01 UNSET SESSION_POLICY;

⚠️MFA Cannot Be Enforced via ALTER USER Alone

Simply enabling MFA for your account does not force users to enrol. Administrators use the SESSION_POLICY object with appropriate settings to enforce MFA behaviour at scale. Individual MFA enrolment is still initiated by each user through their profile in Snowsight. Monitor unenrolled users via the LOGIN_HISTORY view.

4. SAML 2.0 / Single Sign-On (SSO)

Federated authentication via SAML 2.0 allows your organisation’s Identity Provider (IdP) to authenticate users. Users log in once with their corporate credentials; the IdP issues a SAML assertion that Snowflake trusts.

How SAML 2.0 SSO Works

SAML 2.0 Federated Authentication Flow

A sequence diagram showing SAML 2.0 SSO between a user's browser, an Identity Provider (IdP), and Snowflake. Step 1: The user navigates to Snowsight login page and clicks 'Sign in with SSO'. Step 2: Snowflake redirects the browser to the IdP (e.g. Okta or Azure AD) with a SAML AuthnRequest. Step 3: The IdP presents a login page; the user enters their corporate credentials (username + password + MFA if configured at IdP level). Step 4: The IdP authenticates the user and generates a signed SAML Assertion XML document containing user attributes (email, name, roles). Step 5: The IdP posts the SAML Response back to Snowflake's Assertion Consumer Service (ACS) URL. Step 6: Snowflake validates the SAML signature using the IdP's X.509 certificate stored in the Security Integration. Step 7: Snowflake maps the SAML subject (usually email) to a Snowflake user account, then grants access and opens a session. Throughout, the user never types a Snowflake password.

Sequence diagram of SAML 2.0 SSO flow: browser to IdP authentication, SAML assertion issued, Snowflake validates and grants session

Supported Identity Providers

Snowflake officially supports:

Okta — most commonly tested in the exam
Microsoft Azure AD / Entra ID
Microsoft ADFS (Active Directory Federation Services)
PingFederate
Any SAML 2.0-compliant IdP

Configuring a SAML 2.0 Security Integration

SAML 2.0 Security Integration Setup

1-- Create a SAML2 Security Integration for Okta
2CREATE SECURITY INTEGRATION okta_sso
3TYPE = SAML2
4ENABLED = TRUE
5SAML2_ISSUER = 'http://www.okta.com/exk9x1234567890'
6SAML2_SSO_URL = 'https://mycompany.okta.com/app/snowflake/exk9x1234567890/sso/saml'
7SAML2_PROVIDER = 'OKTA'
8SAML2_X509_CERT = 'MIIDpDCCAoygAwIBAgIGAW...'
9SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'Okta SSO'
10SAML2_ENABLE_SP_INITIATED = TRUE;
11
12-- View the integration details (includes ACS URL and Entity ID for IdP config)
13DESCRIBE INTEGRATION okta_sso;
14
15-- Show all security integrations
16SHOW INTEGRATIONS;
17
18-- Show only security integrations of a specific type
19SHOW SECURITY INTEGRATIONS;
20
21-- Disable SSO temporarily without deleting
22ALTER SECURITY INTEGRATION okta_sso SET ENABLED = FALSE;
23
24-- Force a specific user to always use SSO (cannot use password)
25ALTER USER sso_user SET DISABLE_LEGACY_BEHAVIOUR = TRUE;

ℹ️What DESCRIBE INTEGRATION Returns

Running DESCRIBE INTEGRATION okta_sso returns the SP Entity ID and SP ACS URL that you need to configure on the IdP side. The SP ACS URL is where Snowflake receives SAML responses — it follows the pattern https://ACCOUNT.snowflakecomputing.com/fed/login.

5. OAuth 2.0 — Delegated Authorisation

OAuth 2.0 is an authorisation framework (not authentication). It allows a tool or application to access Snowflake resources on a user’s behalf without ever receiving the user’s Snowflake password. The user grants consent; the tool gets a scoped access token.

Snowflake OAuth vs External OAuth

Feature

Snowflake OAuth

External OAuth

Purpose

Integrate partner BI tools (Tableau, Power BI, Looker, dbt Cloud) with Snowflake

Use an existing enterprise OAuth IdP (Okta, Azure AD) to issue tokens for Snowflake

Token issuer

Snowflake acts as its own authorisation server

✓External IdP (Okta, Azure AD, Ping) issues the JWT access token

User password exposure

User never shares Snowflake password with the tool

User authenticates via IdP; Snowflake password never needed

Role scope

✓Can restrict which Snowflake roles the tool is allowed to use

Roles mapped via token claims or Snowflake role mapping

Security Integration type

TYPE = OAUTH

TYPE = EXTERNAL_OAUTH

Common use case

Tableau Desktop, Power BI, Sigma Computing connecting as end user

✓Centralised enterprise IAM — all apps use the same OAuth IdP

Refresh token support

✓Supported — users can stay connected without re-authorising

Depends on IdP configuration; typically short-lived access tokens

Snowflake OAuth and External OAuth Security Integrations

1-- Snowflake OAuth for Tableau
2CREATE SECURITY INTEGRATION tableau_oauth
3TYPE = OAUTH
4ENABLED = TRUE
5OAUTH_CLIENT = TABLEAU_DESKTOP
6OAUTH_ISSUE_REFRESH_TOKENS = TRUE
7OAUTH_REFRESH_TOKEN_VALIDITY = 7776000  -- 90 days in seconds
8BLOCKED_ROLES_LIST = ('ACCOUNTADMIN', 'SYSADMIN');
9
10-- Snowflake OAuth for a custom application
11CREATE SECURITY INTEGRATION custom_app_oauth
12TYPE = OAUTH
13ENABLED = TRUE
14OAUTH_CLIENT = CUSTOM
15OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
16OAUTH_REDIRECT_URI = 'https://myapp.example.com/oauth/callback'
17OAUTH_ISSUE_REFRESH_TOKENS = TRUE
18OAUTH_REFRESH_TOKEN_VALIDITY = 86400;
19
20-- External OAuth using Okta as the token issuer
21CREATE SECURITY INTEGRATION okta_external_oauth
22TYPE = EXTERNAL_OAUTH
23ENABLED = TRUE
24EXTERNAL_OAUTH_TYPE = OKTA
25EXTERNAL_OAUTH_ISSUER = 'https://mycompany.okta.com/oauth2/default'
26EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://mycompany.okta.com/oauth2/default/v1/keys'
27EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://myaccount.snowflakecomputing.com')
28EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
29EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';
30
31-- Show details of any integration
32DESCRIBE INTEGRATION tableau_oauth;
33DESCRIBE INTEGRATION okta_external_oauth;

🎯OAuth Exam Trap

The exam distinguishes between Snowflake OAuth and External OAuth. Snowflake OAuth is for partner tools connecting to Snowflake. External OAuth is for when your organisation already has an OAuth 2.0 IdP and you want Snowflake to trust tokens it issues. The TYPE value in CREATE SECURITY INTEGRATION is the key differentiator: OAUTH vs EXTERNAL_OAUTH.

6. SCIM — Automated User Provisioning

SCIM is not an authentication method — it is a provisioning protocol. It automates the creation, updating, and deactivation of Snowflake user accounts and role assignments when users are added or removed in the IdP.

Supported IdPs for SCIM provisioning:

Okta
Azure Active Directory / Microsoft Entra ID

SCIM Integration Configuration

1-- Create a SCIM integration for Okta
2CREATE SECURITY INTEGRATION okta_scim
3TYPE = SCIM
4SCIM_CLIENT = 'OKTA'
5RUN_AS_ROLE = 'OKTA_PROVISIONER';
6
7-- The OKTA_PROVISIONER role must exist and have appropriate grants
8CREATE ROLE okta_provisioner;
9GRANT CREATE USER ON ACCOUNT TO ROLE okta_provisioner;
10GRANT CREATE ROLE ON ACCOUNT TO ROLE okta_provisioner;
11GRANT ROLE okta_provisioner TO USER okta_scim_service_user;
12
13-- Generate the SCIM access token (used by Okta to authenticate to Snowflake SCIM API)
14SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('OKTA_SCIM');
15
16-- Azure AD SCIM integration
17CREATE SECURITY INTEGRATION azure_scim
18TYPE = SCIM
19SCIM_CLIENT = 'AZURE'
20RUN_AS_ROLE = 'AAD_PROVISIONER';

📝SCIM and SAML Are Complementary

SCIM handles provisioning (creating accounts, assigning roles). SAML handles authentication (logging in). You typically use both together: SCIM ensures the Snowflake user account exists before the user tries to log in via SAML SSO. Without SCIM, administrators must manually create Snowflake user accounts even if SAML SSO is configured.

7. JSON Web Tokens (JWT) in Snowflake

JWTs appear in two Snowflake authentication contexts:

Key-pair authentication — when you connect with a private key, the driver generates a short-lived JWT signed with your private key. Snowflake validates it against your registered public key. The JWT typically expires after 60 seconds.
External OAuth — the external IdP issues a JWT access token containing claims about the user. Snowflake validates the JWT signature using the IdP’s public signing keys (retrieved from the JWS_KEYS_URL).

JWTs are self-contained — they carry the user’s identity and expiry time inside the token itself, eliminating the need for Snowflake to call back to the IdP on every request.

8. Session Policies

Session policies control the lifecycle of Snowflake sessions. They can enforce idle timeouts and MFA requirements.

Session Policy Management

1-- Create a session policy with idle timeout
2CREATE SESSION POLICY strict_session_policy
3SESSION_IDLE_TIMEOUT_MINS = 15
4SESSION_UI_IDLE_TIMEOUT_MINS = 15
5COMMENT = 'Strict policy for privileged roles';
6
7-- Apply at account level (affects all users without a specific user/role policy)
8ALTER ACCOUNT SET SESSION_POLICY = strict_session_policy;
9
10-- Override at user level (takes precedence over account-level policy)
11ALTER USER power_user SET SESSION_POLICY = strict_session_policy;
12
13-- Remove a session policy from a user (reverts to account-level)
14ALTER USER power_user UNSET SESSION_POLICY;
15
16-- Drop a session policy (must be detached first)
17ALTER ACCOUNT UNSET SESSION_POLICY;
18DROP SESSION POLICY strict_session_policy;
19
20-- View all session policies
21SHOW SESSION POLICIES;
22
23-- Check which policy is applied to a user
24DESCRIBE USER power_user;

9. Authentication Flow — End to End

Snowflake Authentication Decision Flow

A detailed flowchart showing the complete authentication decision process when a user or application attempts to connect to Snowflake. The flow starts at the top with a 'Connection Request' box. The first diamond decision node asks: 'Network Policy — Is the source IP address allowed?' If No, the flow goes right to a red 'Connection Rejected' terminal. If Yes, the flow continues downward to a second diamond: 'What authentication method is configured for this user?' Four branches emerge: (1) Password — leads to a 'Validate username + password against Snowflake credential store' box; (2) Key-Pair — leads to 'Validate JWT signed with private key against stored public key'; (3) SAML SSO — leads to 'Validate SAML Assertion from IdP using X.509 cert stored in Security Integration'; (4) OAuth Token — leads to 'Validate JWT access token using IdP public keys from JWS_KEYS_URL'. All four paths converge on a diamond: 'Authentication Successful?' If No, red 'Access Denied — Error returned' terminal. If Yes, another diamond: 'Is MFA required by Session Policy?' If Yes: 'Prompt for Duo push or TOTP code' — success leads forward, failure leads to 'Access Denied'. If No (or MFA cached): proceeds to 'Assign Default Role to Session' box, then 'Session Established — Query execution begins' green terminal at the bottom.

Flowchart of Snowflake authentication process: network policy check, auth method selection (password, key-pair, SAML, OAuth), MFA requirement, session establishment

Key Points from the Flow

Network policies are evaluated before any authentication attempt. If your IP is blocked, you never reach the auth step.
Role assignment happens after authentication. The user’s DEFAULT_ROLE is used unless specified in the connection string.
Session policies layer on top of authentication — they can terminate idle sessions and optionally enforce MFA at the session level.

10. Monitoring Authentication Events

Auditing Authentication with LOGIN_HISTORY

1-- View recent login attempts (INFORMATION_SCHEMA — last 7 days)
2SELECT
3  event_timestamp,
4  user_name,
5  client_ip,
6  reported_client_type,
7  first_authentication_factor,
8  second_authentication_factor,
9  is_success,
10  error_message
11FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY(
12  DATEADD('hours', -24, CURRENT_TIMESTAMP()),
13  CURRENT_TIMESTAMP()
14))
15ORDER BY event_timestamp DESC;
16
17-- Find all failed login attempts in the last 24 hours
18SELECT
19  user_name,
20  client_ip,
21  error_message,
22  COUNT(*) AS failed_attempts
23FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY(
24  DATEADD('hours', -24, CURRENT_TIMESTAMP()),
25  CURRENT_TIMESTAMP()
26))
27WHERE is_success = 'NO'
28GROUP BY 1, 2, 3
29ORDER BY failed_attempts DESC;
30
31-- Long-term login history (ACCOUNT_USAGE — up to 365 days, ~2hr latency)
32SELECT
33  event_timestamp,
34  user_name,
35  first_authentication_factor,
36  second_authentication_factor,
37  is_success
38FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
39WHERE event_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
40ORDER BY event_timestamp DESC;
41
42-- Find users who have never used MFA
43SELECT DISTINCT user_name
44FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
45WHERE second_authentication_factor IS NULL
46AND event_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP());

Authentication Monitoring — Information Sources

A two-column diagram comparing the two main sources for authentication monitoring data. Left column header: INFORMATION_SCHEMA.LOGIN_HISTORY (via table function). Below: retention period = last 7 days; latency = near real-time (seconds); access = any user with privileges on the schema; use case = operational monitoring, incident response, live troubleshooting; query syntax = SELECT ... FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY(start_time, end_time)). Right column header: SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY (view). Below: retention period = 365 days; latency = approximately 2 hours; access = requires SNOWFLAKE database access (typically ACCOUNTADMIN or granted role); use case = compliance reporting, trend analysis, long-term auditing, security dashboards. A note at the bottom: Both sources include columns for user_name, client_ip, first_authentication_factor (PASSWORD, KEYPAIR, SAML), second_authentication_factor (DUO_MFA, null), is_success (YES/NO), and error_message.

Comparison diagram of INFORMATION_SCHEMA LOGIN_HISTORY table function versus SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY view, showing retention, latency, and use cases

CheatSheet — Authentication Commands

📋

Quick Reference

Authentication & SSO Command Reference

🔑

Password & User Management

Create password policy

CREATE PASSWORD POLICY p PARAMETERS...— Requires ACCOUNTADMIN or CREATE PASSWORD POLICY privilege

Apply policy to account

ALTER ACCOUNT SET PASSWORD_POLICY = p— Affects all users without a user-level override

Apply policy to user

ALTER USER u SET PASSWORD_POLICY = p— Overrides account-level policy

Force password reset

ALTER USER u SET MUST_CHANGE_PASSWORD = TRUE— User must reset on next login

Show policies

SHOW PASSWORD POLICIES— Lists all password policies in current schema

🗝️

Key-Pair Authentication

Set public key

ALTER USER u SET RSA_PUBLIC_KEY='...'— Paste key without header/footer lines

Set secondary key

ALTER USER u SET RSA_PUBLIC_KEY_2='...'— Enables zero-downtime key rotation

Remove primary key

ALTER USER u UNSET RSA_PUBLIC_KEY— Run after clients switch to key 2

View user key fingerprint

DESC USER u— Shows fingerprint, not raw key

🛡️

Security Integrations

SAML2 integration

CREATE SECURITY INTEGRATION ... TYPE = SAML2— Required for SSO with any IdP

Snowflake OAuth

CREATE SECURITY INTEGRATION ... TYPE = OAUTH— For partner tools (Tableau, Power BI)

External OAuth

CREATE SECURITY INTEGRATION ... TYPE = EXTERNAL_OAUTH— For existing enterprise OAuth IdP

SCIM integration

CREATE SECURITY INTEGRATION ... TYPE = SCIM— For automated user provisioning

Show integrations

SHOW INTEGRATIONS / SHOW SECURITY INTEGRATIONS— Lists all or just security integrations

Describe integration

DESCRIBE INTEGRATION integration_name— Shows ACS URL, client ID, config details

⏱️

Session Policies

Create session policy

CREATE SESSION POLICY p SESSION_IDLE_TIMEOUT_MINS = N— Defaults: 240 mins for UI, 240 mins for client

Apply to account

ALTER ACCOUNT SET SESSION_POLICY = p— Account-wide default

Apply to user

ALTER USER u SET SESSION_POLICY = p— User-level override wins

Remove from user

ALTER USER u UNSET SESSION_POLICY— Reverts to account-level policy

📱

MFA Token Caching

Default cache duration

20 minutes— Re-authentication not required within this window

MFA provider

Duo Security— Push notification or TOTP

User enrolment

Via Snowsight profile page— Self-service; admins cannot enrol on behalf of users

Practice Questions

Authentication

A service account needs to authenticate to Snowflake from a CI/CD pipeline without storing a password. The account must support key rotation with zero downtime. Which authentication method and feature achieves this?

Authentication

A company wants to allow Tableau Desktop users to connect to Snowflake using their corporate identity without sharing their Snowflake passwords with the BI tool. Which Snowflake feature should be configured?

Authentication

An administrator reviews the LOGIN_HISTORY and notices several failed authentication attempts. They want to see login history going back 6 months with details about which authentication factor was used. Which data source should they query?

Flashcards

Authentication

QUESTION

What SQL command registers an RSA public key for key-pair authentication on a Snowflake user?

Click to reveal answer

ANSWER

ALTER USER username SET RSA_PUBLIC_KEY='<public_key_content>'; — The public key content is pasted without the BEGIN/END header lines. A second key slot exists: RSA_PUBLIC_KEY_2, enabling zero-downtime key rotation.

Click to see question

Authentication

QUESTION

How long does Snowflake cache an MFA token by default, and what is the MFA provider?

Click to reveal answer

ANSWER

MFA tokens are cached for 20 minutes by default, meaning a second MFA challenge is not required within that window. Snowflake uses Duo Security as its MFA provider, supporting both push notifications and TOTP codes.

Click to see question

Authentication

QUESTION

What is the difference between a SAML 2.0 Security Integration and a SCIM Security Integration?

Click to reveal answer

ANSWER

SAML 2.0 (TYPE = SAML2) handles authentication — it allows users to log in to Snowflake via an external IdP without entering a Snowflake password. SCIM (TYPE = SCIM) handles provisioning — it automates creating, updating, and deactivating Snowflake user accounts and role memberships when users change in the IdP. They are complementary: SCIM provisions the account, SAML authenticates the login.

Click to see question

Authentication

QUESTION

What are the two types of OAuth supported in Snowflake, and what is the primary use case for each?

Click to reveal answer

ANSWER

(1) Snowflake OAuth (TYPE = OAUTH): designed for partner BI/analytics tools (Tableau, Power BI, Sigma, dbt Cloud) to access Snowflake on behalf of a user. Snowflake acts as its own authorisation server. (2) External OAuth (TYPE = EXTERNAL_OAUTH): designed for organisations that already have an enterprise OAuth IdP (Okta, Azure AD) and want Snowflake to trust JWT tokens that IdP issues. The external system is the authorisation server.

Click to see question

Authentication

QUESTION

Which view or function provides login history for the last 365 days, and what is its approximate data latency?

Click to reveal answer

ANSWER

SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY provides up to 365 days of login history with an approximate latency of 2 hours (data may be up to 2 hours behind real time). For near real-time data within the last 7 days, use the TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY()) table function instead.

Click to see question

🔑Summary — What to Know for the Exam

Password policies are created with CREATE PASSWORD POLICY and applied with ALTER ACCOUNT SET PASSWORD_POLICY or ALTER USER ... SET PASSWORD_POLICY.
Key-pair auth uses ALTER USER ... SET RSA_PUBLIC_KEY. Two key slots (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) enable zero-downtime rotation.
MFA uses Duo Security. Token caching lasts 20 minutes. Enforcement is via Session Policy.
SAML 2.0 SSO requires a Security Integration with TYPE = SAML2. Supported IdPs: Okta, Azure AD, ADFS, PingFederate.
Snowflake OAuth (TYPE = OAUTH) is for partner tools. External OAuth (TYPE = EXTERNAL_OAUTH) is for enterprise IdP token trust.
SCIM (TYPE = SCIM) is provisioning, not authentication. Supported with Okta and Azure AD.
SHOW INTEGRATIONS and DESCRIBE INTEGRATION are the diagnostic commands.
LOGIN_HISTORY: INFORMATION_SCHEMA (7 days, real-time) vs ACCOUNT_USAGE (365 days, ~2hr latency).

Reinforce what you just read

Study the All flashcards with spaced repetition to lock it in.

Study flashcards →