Accounts and Security practice questions

Apply a network policy that forces MFA for all IP ranges.

Why it fails: Network policies are designed to restrict access based on IP address ranges or virtual networks. They do not have built-in capabilities to trigger or enforce Multi-Factor Authentication events. MFA settings are managed within the user profile or via external Identity Provider integrations, not through network-level access control rules.

Set the global parameter MFA_ENFORCE to TRUE.

Why it fails: There is no global parameter named MFA_ENFORCE in Snowflake. MFA is enabled on a per-user basis or through integration with an Identity Provider using SCIM or SAML. While you can mandate MFA for individual users, Snowflake does not provide a single toggle switch for global account-wide MFA enforcement.

Configure the CLIENT_SESSION_KEEP_ALIVE parameter.

Why it fails: The CLIENT_SESSION_KEEP_ALIVE parameter controls how long a session remains active after inactivity before the user is required to re-authenticate. It has no functional relationship with Multi-Factor Authentication. Modifying this setting will not trigger an MFA prompt or enforce any additional security layers beyond standard credential-based session management.

Enable Time Travel on the SNOWFLAKE database.

Why it fails: The SNOWFLAKE database is a managed, read-only system database. Users cannot enable features like Time Travel or modify retention periods for tables within this system schema. Therefore, relying on standard Time Travel mechanisms for the SNOWFLAKE database is not a supported approach for long-term immutable log retention or auditing.

Configure Data Retention Period to 365 days on all tables.

Why it fails: Data Retention Periods apply to user-created tables and standard historical data, not to system-generated views like ACCESS_HISTORY. Configuring this setting globally will not affect the availability of audit logs contained within the ACCOUNT_USAGE schema, which follows its own internal retention policy dictated by the Snowflake service architecture.

Use the AUDIT_LOG_SETTING parameter.

Why it fails: There is no global parameter named AUDIT_LOG_SETTING in Snowflake. Audit logs are automatically captured by the Snowflake platform in the SNOWFLAKE.ACCOUNT_USAGE schema. Architects cannot configure or toggle the existence of these logs; they can only query the existing views or export the data to external storage for long-term analysis.

It determines the authentication method for the SCIM endpoint.

Why it fails: SCIM authentication is typically handled by an OAuth token or a generated bearer token, not by the roles assigned to the provisioner. The provisioner role is strictly for defining the operational permissions allowed during the synchronization process, not for establishing the validity of the identity of the SCIM integration itself.

It enables MFA for users provisioned via SCIM.

Why it fails: The provisioner role has no impact on MFA enforcement. MFA settings are typically defined in the user's profile or via global security settings. The SCIM integration is responsible only for the lifecycle management of the user records, not for the configuration of security features associated with those users upon their successful provisioning.

It encrypts the traffic between the IdP and Snowflake.

Why it fails: The security of the connection between the IdP and Snowflake is handled by TLS/SSL encryption during the HTTPS exchange, not by the role assigned to the SCIM provisioner. The role is a logical construct for permission management within Snowflake and has no bearing on the underlying transport layer's cryptographic security protocols.

Create a Resource Monitor on all stages.

Why it fails: Resource monitors are used to track and control credit consumption, not for enforcing security configuration or preventing the inclusion of credentials in SQL statements. They cannot restrict the parameters used in a `CREATE STAGE` statement, nor can they block the use of embedded credentials within those staging definitions.

Grant the SECURITYADMIN role to all users.

Why it fails: Granting the SECURITYADMIN role to users provides them with excessive, unnecessary privileges that would actually allow them to create stages with embedded credentials without any oversight. This is a severe security violation that undermines the principle of least privilege and significantly expands the potential for unauthorized data exposure and account compromise.

Modify the PUBLIC role to revoke all privileges.

Why it fails: While limiting the PUBLIC role is a good practice, it does not specifically address the issue of users creating stages with embedded credentials if they are granted the `CREATE STAGE` privilege through other custom roles. The solution requires a specific strategy focused on credential management, rather than just revoking broad-reaching permissions.

Manually encrypt all files before uploading.

Why it fails: Snowflake automatically encrypts all data at rest using AES-256. Manually encrypting files adds unnecessary complexity and overhead without providing any additional security benefit. It also hinders Snowflake's ability to optimize data loading and perform internal operations like pruning and clustering, as the service cannot inspect or process encrypted payloads.

Disable all Time Travel features.

Why it fails: Time Travel is a feature designed for data recovery and protection, not a security risk that requires disabling. Disabling it would remove the ability to restore data in the event of accidental deletion or corruption, which would actually be a negative impact on the data protection strategy of the organization.

Set all database tables to read-only mode.

Why it fails: Setting tables to read-only mode is not a supported security feature for protecting data at rest in Snowflake. While it could theoretically limit modifications, it is not a practical approach for any real-world production environment where data needs to be ingested, transformed, and updated as part of the business workflow.

The role has the BROWSE_ALL_DATA privilege.

Why it fails: There is no `BROWSE_ALL_DATA` privilege in Snowflake. Access to data is governed strictly by explicit grant statements and the role hierarchy. Privileges are never implied through a generic browsing permission. If a user can see data, it must be because of specific object-level grants or ownership, not a system-wide browsing permission.

The table has a global tag applied.

Why it fails: Tags in Snowflake are used for data governance and object identification, not for granting or restricting access to data. A tag cannot grant permission to view table contents. Access control is strictly handled through the GRANT/REVOKE commands and the role hierarchy, making tags irrelevant to the user's ability to see data.

The user has access through the PUBLIC role.

Why it fails: While every user is a member of the PUBLIC role, the PUBLIC role does not have SELECT access to tables by default. Unless an explicit grant was made to the PUBLIC role, this would not explain the user's ability to view the table data, as Snowflake's default security model is strictly deny-by-default.

Implement a shared session token.

Why it fails: Snowflake does not provide a mechanism for shared session tokens between different service accounts. Each session is unique to the user or service account and requires its own authentication event. Attempting to share tokens would be a security risk and is not supported by the platform's authentication architecture.

Store the password in a clear-text environment variable.

Why it fails: Storing passwords in clear-text environment variables is a major security vulnerability. Anyone with access to the environment or the process list could easily retrieve the credentials, leading to immediate compromise. This practice is strongly discouraged in all production environments and fails to meet basic regulatory security requirements for data access.

Enable SSO for the service account.

Why it fails: Single Sign-On (SSO) is designed for interactive users who authenticate through a browser-based flow. It is not suitable for non-interactive service accounts or automated applications. Attempting to force an interactive SSO flow for a machine-to-machine integration will fail, as there is no user present to complete the authentication challenge.

SCIM automatically grants ownership of existing databases to newly synced administrative users.

Why it fails: SCIM does not manage object ownership or provide administrative rights over existing databases upon synchronization. Access control and ownership are managed within Snowflake using the GRANT command, ensuring that newly created users follow the established security model rather than gaining automatic, potentially dangerous permissions.

Snowflake stores the user's IdP password in plain text to facilitate SCIM sync operations.

Why it fails: Snowflake never stores external passwords in plain text, as SCIM users typically authenticate via SSO using SAML or OAuth. Storing plain text passwords would be a significant security risk and contradicts modern identity management standards which rely on secure token exchange for authentication and synchronization.

Deactivating a user in the IdP will immediately drop all tables owned by that user.

Why it fails: Deactivating a user via SCIM disables their ability to log in but does not drop the objects they own. Dropping objects automatically would be highly destructive; instead, Snowflake maintains the objects, which should then be reassigned to a new owner by a security administrator using SQL.

The user jsmith can only access objects granted directly to the senior_analyst role.

Why it fails: This statement is incorrect because Snowflake's RBAC model is explicitly designed for hierarchical inheritance, where a parent role automatically gains all the access rights of its children. The senior_analyst role serves as a parent to the analyst role, thus inheriting every privilege that the analyst role possesses.

Role inheritance only works if the analyst role is also granted directly to the user jsmith.

Why it fails: Requiring direct grants for every role in the hierarchy defeats the purpose of RBAC systems and increases the administrative burden of managing user access. Snowflake's architecture is designed to traverse the role graph automatically, so the user only needs the top-level role to access subordinate privileges.

The senior_analyst role must be a system-defined role for the inheritance to function.

Why it fails: Custom roles in Snowflake support the same inheritance capabilities as system roles, allowing architects to build complex, business-aligned permission structures. There is no requirement for a role to be system-defined like SYSADMIN or SECURITYADMIN to participate in a custom hierarchy or to pass privileges to users.

The public key must be encrypted with a passphrase before being uploaded to Snowflake.

Why it fails: While the private key should be encrypted with a passphrase for local storage, the public key itself is not encrypted when assigned to the Snowflake user. The public key is used for verification purposes and must be in the correct format for Snowflake to parse and store it.

The public key must be stored in a Snowflake internal stage before it can be assigned.

Why it fails: Public keys are assigned directly to the user object using the ALTER USER command and are not stored in stages. Stages are meant for data files, whereas security credentials like keys are part of the user's metadata configuration managed by the Cloud Services layer of the architecture.

The public key must be generated using the SHA-256 hashing algorithm exclusively.

Why it fails: Snowflake supports RSA keys for key-pair authentication, and while SHA-256 might be used in the signing process, the key generation itself follows RSA standards. RSA provides the asymmetric structure necessary for the public/private key relationship that underpins this specific authentication method for service accounts.

A public DNS record pointing to the Snowflake account's public URL.

Why it fails: PrivateLink specifically avoids using public DNS records for routing, as the goal is to keep traffic within the private network. Using public URLs would defeat the purpose of the private connection and could potentially lead to traffic being routed over the internet instead of the private link.

A NAT Gateway configured in the Snowflake VPC to allow outbound responses.

Why it fails: Snowflake's internal VPC architecture is managed by Snowflake, and customers do not configure NAT Gateways within it. The communication flow for PrivateLink is handled via the AWS backbone and endpoint services, making a NAT Gateway unnecessary for the establishment of a private connection between the VPCs.

A dedicated hardware security module (HSM) installed in the customer data center.

Why it fails: While HSMs can be used for key management, they are not a requirement for establishing an AWS PrivateLink connection to Snowflake. PrivateLink focuses on network-level isolation and routing rather than the physical storage of cryptographic keys used for data encryption at rest or in transit.

User-defined roles and their associated privilege grants.

Why it fails: Roles and grants are fundamental to the security model and are included in account replication to ensure that the secondary account has the same access control environment. Replicating roles allows for a seamless failover where users can log in and have the same permissions they had on the primary.

Network policies assigned to the account or specific users.

Why it fails: Network policies are considered account-level metadata and are replicated to maintain the security perimeter across regions or accounts. This ensures that the same IP restrictions and access controls are enforced on the secondary account, preventing unauthorized access during or after a failover event.

Users and their associated authentication metadata like public keys.

Why it fails: Replicating users is essential for maintaining business continuity, as it allows identities and their authentication methods to persist in the secondary account. This includes key-pair information and other user-specific settings, ensuring that automated processes and human users can authenticate successfully after a failover occurs.

Enable MFA for service accounts used by automated ETL processes.

Why it fails: Service accounts should typically use key-pair authentication or OAuth instead of MFA because MFA requires human interaction to approve a push notification. Enabling MFA on an automated service account would cause the process to fail unless a human was available to manually verify every single connection.

Use the 'MINS_TO_BYPASS_MFA' property to permanently disable MFA for remote users.

Why it fails: The bypass property is intended for temporary emergency access and should never be used as a permanent solution. Disabling MFA for remote users would significantly weaken the account's security posture and expose it to credential-based attacks, especially for users accessing the system from untrusted networks.

Require users to change their MFA device every 30 days for maximum security.

Why it fails: Forcing users to change devices frequently is impractical and does not materially improve security. It would lead to significant frustration and potential lockout issues; instead, security is better served by maintaining a single, secure device and following proper decommissioning procedures when a user's device is lost.

The actual email address stored in the table.

Why it fails: The policy explicitly checks if the current role is 'ANALYST' to reveal the data. Since the user is using the 'DEVELOPER' role, they do not meet the condition required to see the unmasked values, resulting in the data being obscured according to the policy's ELSE clause.

A NULL value for every row in the column.

Why it fails: The masking policy is defined to return a string of asterisks, not a NULL value. While masking policies can be configured to return NULL, this specific policy's logic dictates that the string '*********' will be displayed to unauthorized users, as specified in the CASE statement's return value.

An error message stating they have insufficient privileges.

Why it fails: Masking policies do not block access to the row or return a permission error; they modify the data returned by the query. The user can still execute the SELECT statement and see other columns, but the protected column's content is transformed based on the policy logic.

Objects created in the session are owned by the active secondary role with the most privileges.

Why it fails: Object ownership is always determined by the primary role in the session, regardless of which secondary roles are active. This ensures that there is a single, clear owner for every new object, maintaining the integrity of the RBAC model and preventing confusion over object management.

Secondary roles allow a user to perform actions that require the ACCOUNTADMIN role.

Why it fails: While secondary roles provide the privileges of the granted roles, certain high-level administrative actions still require the primary role to be set to that specific administrative role. This prevents accidental execution of powerful commands and ensures that administrative actions are intentional and tracked properly.

The privileges of secondary roles are only available for SELECT statements.

Why it fails: Secondary roles provide full privileges for any operation the role is authorized to perform, including DML operations like INSERT, UPDATE, or DELETE. They are not restricted to read-only access, making them a powerful tool for users who need to manage data across multiple functional areas.

It prevents any user from deleting rows in the sales table.

Why it fails: Row access policies control the visibility of rows during SELECT, UPDATE, and DELETE operations, but they do not specifically act as a 'deny delete' constraint. Their primary function is data filtering rather than preventing specific DML actions, although they do limit which rows a DML action can affect.

It masks the sales_region column for unauthorized users.

Why it fails: This is a Row Access Policy, which filters entire rows from the result set, whereas a Masking Policy would obscure the values within a specific column. A user who does not meet the criteria for this policy will see zero rows, rather than seeing rows with masked column values.

It optimizes query performance by partitioning the table by sales_region.

Why it fails: Row access policies are security features and do not directly influence the physical partitioning or clustering of data in Snowflake. While they may improve performance by reducing the volume of data processed, their intended purpose is strictly to enforce security and compliance through row-level filtering.

It can view and modify data in any table across all accounts in the organization.

Why it fails: The ORGADMIN role is focused on organization management and does not have inherent permissions to access data within individual accounts. Data access is still controlled by the ACCOUNTADMIN and other roles within each specific account, maintaining the principle of separation of duties and account-level isolation.

It replaces the ACCOUNTADMIN role for security management within each account.

Why it fails: The ORGADMIN role exists alongside the ACCOUNTADMIN role and does not replace it; ACCOUNTADMIN remains the highest authority within a single account. The ORGADMIN manages the account lifecycle and organization-wide settings, while the ACCOUNTADMIN handles the internal security and configuration of the account itself.

It must be manually granted to a user by Snowflake Support.

Why it fails: While Snowflake initially enables the ORGADMIN role for an account, it can then be managed and granted to other users by an existing ORGADMIN within the organization. It does not require a support ticket for every assignment, allowing the organization to manage its own administrative hierarchy.

DATA_RETENTION_TIME_IN_DAYS

Why it fails: This parameter controls the Time Travel feature, which determines how long Snowflake retains historical data for recovery or analysis. It has no impact on user session management or timeouts, as it is strictly related to data lifecycle management and protection against accidental data loss.

NETWORK_POLICY_BYPASS_TIMEOUT

Why it fails: There is no such parameter in Snowflake; network policies are enforced continuously and do not have a bypass timeout setting. Access is either allowed or denied based on the IP address at the time of connection, and this check is not subject to a temporal bypass mechanism.

MAX_CONCURRENT_QUERIES

Why it fails: This parameter is used to manage warehouse workload and resource allocation rather than session security or timeouts. It limits the number of queries that can run simultaneously on a warehouse but does not affect how long a user's session remains valid or when it should be terminated.

Set the user's password to NULL.

Why it fails: While it is possible to leave a password empty, it is not a requirement for Key-Pair authentication. A user can have both a password and a public key assigned to their account simultaneously, allowing for flexibility in how the user authenticates depending on the client application configuration.

Store the private key in a Snowflake stage.

Why it fails: Storing a private key in a Snowflake stage is a significant security risk and is not a part of the authentication configuration. The private key must reside securely on the client machine or in a secure secret management system, never accessible to the database server itself.

Enable MFA for the service user.

Why it fails: MFA is typically incompatible with non-interactive service users because it requires manual intervention. Key-Pair authentication is designed specifically to allow programmatic, non-interactive authentication. Enabling MFA on a service user would break the automated process and is not a requirement for configuring Key-Pair authentication within Snowflake.

Enable Always Encrypted on the driver.

Why it fails: Snowflake handles data encryption at rest and in transit automatically. 'Always Encrypted' is a concept specific to other database platforms, such as Microsoft SQL Server, and is not a native feature or requirement for implementing row-level security or masking in Snowflake's architecture.

Grant the PUBLIC role ownership of the policy.

Why it fails: Granting ownership of security policies to the PUBLIC role is a major security vulnerability. Every user in the account belongs to the PUBLIC role; giving it ownership would allow any user to modify or drop security policies, completely bypassing the intended access controls and compromising the entire platform security.