Data Governance Study Guide — DEA-C02

Security

This domain tests your ability to design and implement authentication, authorization, network controls, and data governance features in Snowflake, specifically in the context of data engineering pipelines and ETL workflows. One of the smaller topic areas on the SnowPro Advanced: Data Engineer (DEA-C02) exam under the current (carried-over, unverified) blueprint weighting, questions typically require you to select the correct Snowflake feature, write or interpret SQL for security configurations, or reason through least-privilege role designs. Expect scenario-based questions that require you to distinguish between similar features and know exact syntax, defaults, and tier requirements.

Service Account Authentication: Key-Pair Auth, Key Rotation, and OAuth

Key-Pair Authentication

Key-pair authentication replaces password-based login for service accounts and programmatic access. It uses an RSA private/public key pair where the public key is registered on the Snowflake user and the private key is stored in the client application or secrets manager.

Setup steps:

1. Generate RSA key pair (2048-bit minimum; 4096-bit recommended)
2. Register the public key: ALTER USER svc_etl SET RSA_PUBLIC_KEY = '<public_key>';
3. Client connects using the private key (no password sent over the wire)

A single user can hold two active public keys (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) simultaneously, enabling zero-downtime key rotation.

Key Rotation

Key rotation is the process of replacing an expiring or compromised key without service interruption:

1. Generate a new key pair
2. Register new public key in the unused slot (RSA_PUBLIC_KEY_2)
3. Update the application to use the new private key
4. Remove the old public key: ALTER USER svc_etl UNSET RSA_PUBLIC_KEY;

Common traps:

• Exam may ask "how many public keys can a user have active?" — answer is 2, not 1 or unlimited
• Key rotation does NOT require creating a new user
• Forgetting to unset the old key does not break authentication but leaves a security gap

OAuth for Programmatic Access

External OAuth is the correct answer when the question involves an existing enterprise identity provider. You create a Security Integration of type EXTERNAL_OAUTH.

Common traps:

• OAuth tokens have a configurable TTL; after expiry the service must re-authenticate — not a key-pair concern
• External OAuth does not automatically create Snowflake users; user must already exist or SCIM must provision them
• OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE is a default that must not be changed in production

Role Hierarchy for Pipelines: Dedicated ETL Roles and Least Privilege

Designing ETL Role Hierarchies

The principle of least privilege requires that each service account or pipeline component holds only the permissions it needs. Snowflake role hierarchies are additive: a role inherits all privileges of roles granted to it.

Recommended pattern for a data engineering pipeline:

Bold caption: ETL role hierarchy showing separation of loading and transformation responsibilities.

Key design rules:

• SYSADMIN owns objects; SECURITYADMIN manages grants
• Never grant ACCOUNTADMIN to service accounts
• Create dedicated roles per pipeline function (load, transform, orchestrate)
• Use USERADMIN only for creating users; grant it sparingly

Privilege Reference for Pipeline Roles

Common traps:

• USAGE on a database alone does not grant access to tables — you also need USAGE on the schema
• OWNERSHIP transfers all privileges; never grant OWNERSHIP of production tables to ETL service accounts
• ACCOUNTADMIN is required for certain account-level operations (network policies, SCIM) — do not conflate with pipeline roles