COF-C02 Account Access and Security

Account Access and Security Detailed Explanation

Snowflake security is multi-layered.
It includes:

• Identity & authentication

• Authorization (RBAC)

• Encryption

• Network & session controls

• Data governance & fine-grained security features

Understanding this topic is essential for the exam because Snowflake emphasizes secure, governed access to data at every layer of the architecture.

1. Identity & Authentication

Identity & authentication answer the question:

“Who is trying to access Snowflake, and how do we verify that they are truly who they claim to be?”

This section covers users, login methods, and authentication mechanisms.

1.1 User Management

Each Snowflake user is represented by a user object that contains attributes controlling how they log in and which defaults they use.

1.1.1 User Attributes

Important user properties include:

• LOGIN_NAME
  The username used for authentication.

• DEFAULT_ROLE
  The role automatically activated when the user logs in
  (e.g., ANALYST_ROLE, ETL_ROLE, etc.)

• DEFAULT_WAREHOUSE
  Determines which warehouse is used when queries run without specifying one.

• DEFAULT_NAMESPACE
  The default database + schema a user lands in.

• Email / RSA public key / MFA settings
  Depending on organization requirements.

1.1.2 Password Policies

You can apply password requirements such as:

• Length

• Complexity

• Rotation frequency

• Lockout policies

Snowflake supports reusable password policies that can be bound to multiple users.

1.1.3 Who Can Create Users

Users can be created by:

• SECURITYADMIN (recommended)

• ACCOUNTADMIN (has full power, but should be used carefully)

A typical command:

CREATE USER john 
PASSWORD = 'StrongPassword123' 
DEFAULT_ROLE = analyst 
DEFAULT_WAREHOUSE = bi_wh;

1.2 Authentication Methods

Snowflake supports multiple authentication mechanisms. Understanding when to use each one is frequently tested.

1.2.1 Username / Password

• Most basic method.

• Good for small teams or individual users.

• Not ideal for enterprise security at scale.

1.2.2 Multi-Factor Authentication (MFA)

• Adds a second layer of protection (e.g., Duo push).

• Strongly recommended when using username/password.

1.2.3 Key Pair Authentication

• Uses a private key stored locally and a public key registered in Snowflake.

• Ideal for:

  • Automated scripts

  • CI/CD pipelines

  • Service accounts

It removes the need to store passwords.

1.2.4 OAuth

• Token-based authentication.

• Used by:

  • BI tools

  • Analytic applications

  • Custom client apps

Snowflake supports:

• Snowflake OAuth

• External OAuth

1.2.5 SAML 2.0 / SSO / Federated Authentication

Uses identity providers (IdPs):

• Okta

• Azure AD

• Ping Identity

• Others

Best option for enterprises because:

• Centralized identity lifecycle

• Strong governance

• Automatic deprovisioning

1.2.6 External OAuth

Used typically by:

• Tableau

• Power BI

• External applications that issue OAuth tokens

1.2.7 Exam Expectation

You should know:

• Federated authentication is strongly recommended for enterprise environments

• Key pair authentication is ideal for automated workloads

• MFA is recommended for password-based logins

2. Authorization – RBAC Model

Authorization answers the question:

“What can the user do inside Snowflake?”

Snowflake uses RBAC (Role-Based Access Control), which is one of the cleanest and most powerful security models in modern data platforms.

2.1 Core Concepts

2.1.1 Securable Objects

Securable objects are the things you can grant or deny access to:

• Account

• User

• Warehouse

• Database

• Schema

• Table / View

• Stage

• File format

• Pipe

• Task

• Stream, Materialized View, etc.

2.1.2 Privileges

Privileges define what actions are allowed on an object. Examples:

• USAGE

• SELECT

• INSERT / UPDATE / DELETE

• CREATE TABLE

• MODIFY

• MONITOR

• OPERATE (e.g., for tasks/pipes)

Privileges are always granted to roles, not to users.

2.1.3 Roles (System Roles & Custom Roles)

Roles are containers for privileges.

System roles:

• ORGADMIN
  Highest-level role at the organizational level.

• ACCOUNTADMIN
  Full control over a Snowflake account.

• SECURITYADMIN
  Manages users, roles, and grants.

• SYSADMIN
  Creates/owns warehouses, databases, schemas, tables.

• PUBLIC
  Default role automatically granted to all users.
  Anything granted to PUBLIC is visible to everyone.

Custom roles:

• ETL_ROLE

• ANALYST_ROLE

• BI_ROLE

• DATA_SCIENCE_ROLE

Custom roles support least-privilege access.

2.2 Role Hierarchy

2.2.1 Roles Granted to Roles

Roles can be granted to other roles (role nesting).
This creates a hierarchy.

Example:

ETL_ROLE → DATA_ENGINEERING_ROLE → SYSADMIN

2.2.2 Effective Privileges

A user’s effective privileges =

• Privileges of the active role

• PLUS privileges inherited through that role’s parent roles

Snowflake computes this automatically.

2.3 Best Practices

2.3.1 Least Privilege

Always provide only the minimum required privileges:

• Create task-specific roles

• Grant narrowly scoped privileges

• Do not use extremely powerful roles in daily work

2.3.2 Don’t Use ACCOUNTADMIN or SECURITYADMIN for Daily Tasks

These superuser roles should be used rarely:

• ACCOUNTADMIN: emergency or account-wide configuration

• SECURITYADMIN: user/role administration only

2.3.3 Be Careful with PUBLIC

Anything granted to PUBLIC is visible to all roles and all users.

Avoid granting sensitive permissions to PUBLIC.

3. Network Security

Network security ensures who can connect, from where, and how traffic is protected.

3.1 Network Policies

Network policies allow Snowflake admins to:

• Allow or block specific IP ranges

• Apply policies at:

  • Account level

  • User level

This prevents unauthorized connections from unknown networks.

Example scenarios:

• Allow only office VPN IPs

• Block all public networks

• Restrict sensitive accounts to specific IP ranges

3.2 Private Connectivity

Depending on the cloud provider, private connectivity options include:

• AWS PrivateLink

• Azure Private Link

• GCP Private Service Connect

These connections:

• Avoid the public internet

• Improve security

• Improve governance compliance

Often used by enterprises with strict network controls.

3.3 TLS for Data in Transit

All network communication uses TLS encryption:

• Between users and Snowflake

• Between warehouses and storage

• Between services inside Snowflake

This prevents network-level snooping or tampering.

4. Encryption & Key Management

Encryption protects data both at rest and in transit.

Snowflake uses always-on end-to-end encryption.

4.1 Encryption Coverage

Snowflake encrypts:

• Files in stages

• Micro-partition data in storage

• Backups involved in Time Travel

• Fail-safe storage

• All network traffic

Encryption happens automatically—no user configuration required.

4.2 Hierarchical Key Model

Snowflake uses a hierarchical key model, which gives strong security and easy rotation.

Chain example:

• Account master key
  ↓

• Database key
  ↓

• Table key
  ↓

• Micro-partition file key

Benefits:

• Easy rotation

• Compartmentalized access

• Defense-in-depth

4.3 Key Rotation

Snowflake automatically rotates:

• File keys

• Table/database keys

• Account master key

Enterprises can optionally integrate customer-managed keys via cloud KMS services.

This gives organizations additional control for compliance.

5. Data Governance & Fine-Grained Controls

Beyond RBAC, Snowflake provides several deeper governance features allowing:

• Row-level security

• Column-level masking

• Policy-based access

• Metadata tagging

Essential for industries with strong compliance requirements (GDPR, HIPAA, etc.).

5.1 Secure Views & Secure UDFs

5.1.1 Secure Views

Secure views hide:

• Underlying table structure

• Column definitions

• Sensitive logic

They prevent users from circumventing security by analyzing query plans.

5.1.2 Secure UDFs

Secure UDFs ensure that underlying data or logic is not exposed.

5.2 Row Access Policies

Row Access Policies enforce row-level security.

Example:

A user only sees rows where:

• region = USER_REGION

• department = USER_DEPARTMENT

When applied to a table, Snowflake automatically filters rows based on policy logic.

5.3 Masking Policies

Masking policies hide or transform sensitive values at the column level:

Examples:

• Mask credit cards: **** **** **** 1234

• Mask emails: show only the domain

• Mask phone numbers based on user role

This allows analysts to work with safe versions of PII.

5.4 Tags

Tags let you attach metadata such as:

• “PII”

• “Financial”

• “Confidential”

• “Regulated-Data”

Policies can be applied based on tags, enabling automated governance.

For example:

All columns tagged “PII” must automatically be masked for non-privileged roles.

Account Access and Security (Additional Content)

1. Session Policies

Session policies define and enforce how long user sessions may remain active. They allow administrators to implement strict session-level controls aligned with corporate security standards.

1.1 Purpose of Session Policies

Session policies extend Snowflake’s security model by controlling user session behavior beyond authentication. They allow administrators to enforce consistent inactivity limits across users or groups of users.

1.2 What Session Policies Control

Key capabilities include:

• Maximum session lifetime

• Maximum idle time before automatic termination

• Security requirements at the session level (for example, stricter idle timeout for privileged roles)

These policies directly help organizations comply with security frameworks that require strict session management (such as SOC2, ISO 27001, and PCI DSS).

1.3 Scope of Application

Session policies can be applied:

• At the account level, enforcing global defaults

• At the user level, overriding account defaults for specific users

This dual granularity makes them useful for environments where different teams have distinct security expectations.

1.4 Governance Value

Session policies help ensure:

• Reduction of lingering active sessions

• Lower risk of unauthorized access from stale sessions

• Enforcement of corporate security rules uniformly

2. Access History (Object-Level and Column-Level Audit Logging)

Access History provides detailed visibility into which users accessed which data, including fine-grained column-level lineage.

2.1 Purpose and Scope

Access History records:

• Which objects a user touched

• Which columns were accessed

• The exact query operations performed

It is a full audit trace of data interactions.

2.2 Use Cases

Typical enterprise use cases include:

• Compliance auditing (GDPR, HIPAA, SOX)

• Security incident investigations

• Monitoring high-risk data access patterns

• Verifying masking and row-access policy effectiveness

• Understanding column-level lineage

2.3 Security Monitoring

Access History enables advanced anomaly detection, such as:

• Unusual access of sensitive columns

• Escalation of privileges followed by unexpected data reads

• Lateral movement across schemas

2.4 Where Access History Is Stored

Access history is available through the ACCOUNT_USAGE and ORGANIZATION_USAGE schema views.

3. Object Ownership Model

Snowflake uses a strict ownership model that determines who has ultimate control over every object.

3.1 Ownership Definition

Every object is owned by the role that created it. This is different from privilege-based RBAC inheritance.

3.2 Non-Inherited Ownership

Ownership does not cascade through role hierarchies.
If Role A owns a table and Role B inherits Role A, Role B does not become the owner.

3.3 What Ownership Grants

The owning role can:

• Modify the object

• Drop the object

• Transfer ownership

• Grant privileges on the object

No non-owner role can perform these actions unless explicitly granted privileges.

3.4 Ownership Transfer Rules

Transferring ownership using GRANT OWNERSHIP is a destructive operation because:

• All existing privileges on the object are removed

• After transferring ownership, privileges must be re-granted manually

• This prevents accidental privilege retention across ownership changes

Understanding this behavior is essential for exam questions involving role hierarchies and object lifecycle.

4. Future Grants

Future grants allow administrators to automate privilege assignment for objects created in the future.

4.1 Concepts

Future grants ensure new objects automatically receive the correct privileges without manual intervention.

4.2 Supported Object Types

Future grants apply to:

• Future tables

• Future schemas

• Future views

4.3 Where Future Grants Are Applied

They must be defined at:

• The schema level

• The database level

Exam questions often test whether the student recognizes that future grants cannot be applied directly at the table level.

4.4 Enterprise Utility

Future grants are highly valuable when:

• Data engineering teams continuously create new tables

• Multiple schemas require consistent privilege logic

• Data governance requires predictable access patterns

Proper design of future grants reduces administrative overhead and avoids privilege drift.

5. Database- and Schema-Level Privileges

Understanding container-level privileges is essential because Snowflake uses a hierarchical container model (database → schema → object).

5.1 Key Privileges

Important privileges include:

5.1.1 USAGE

Allows the role to reference the object.
For example:

• USAGE on a database allows access to its schemas

• USAGE on a warehouse allows the role to use the warehouse for queries

Without USAGE, no subordinate object can be accessed.

5.1.2 CREATE

Allows creation of new objects within the container.
Examples:

• CREATE TABLE in a schema

• CREATE SCHEMA in a database

5.1.3 MODIFY

Allows altering objects within the container.
This includes renaming objects, modifying properties, or changing structures.

5.2 Interaction with Role Hierarchy

Exam questions often test scenarios such as:

• A role with USAGE on a database but no USAGE on a schema

• A role with CREATE but lacking USAGE

• Multi-schema environments where roles need both container and object-level privileges

A deep understanding of privilege interplay is essential for RBAC-related questions.

6. Security Integrations

Security integrations provide the connection between Snowflake and external authentication or secure computation systems.

6.1 Purpose

Security integrations define the configuration Snowflake needs to:

• Trust an external identity provider

• Accept tokens from OAuth providers

• Initiate external compute execution

• Support secure communication with external services

6.2 Types of Security Integrations

6.2.1 OAuth Integrations

Used by BI tools, applications, and services that authenticate via OAuth tokens.
Supports both Snowflake OAuth and external OAuth providers.

6.2.2 SAML / SSO Integrations

Enable federated authentication using enterprise identity providers such as Okta, Azure AD, or Ping Identity.
Support fully centralized identity lifecycle management.

6.2.3 External Browser Authentication

Used for browser-based SSO login flows, particularly for tools that launch authentication flows outside Snowflake’s interfaces.

6.2.4 External Functions Security Integrations

Define a secure trust configuration allowing Snowflake to call external APIs from within SQL while ensuring that:

• External endpoints are trusted

• Secrets and credentials are stored securely

• Network traffic is properly controlled

6.3 Benefits

Security integrations ensure:

• Safe and standards-compliant authentication

• Consistent identity governance

• Isolation between Snowflake and external compute

• Enterprise-grade auditability

They are essential when Snowflake must integrate with external identity, federation, BI, or service workloads.