Account Management and Data Governance practice questions

It is the only role that can manage data in every database.

Why it fails: While ACCOUNTADMIN has implicit access, it is not the only role that can manage data. Other roles with appropriate grants can manage databases. Best practice dictates that data management should be delegated to functional roles rather than using the highest-privileged account for routine data operations.

It should be used for daily data warehouse maintenance.

Why it fails: Using ACCOUNTADMIN for daily maintenance is a security risk. Best practices require using the Principle of Least Privilege. Daily tasks should be performed by roles specifically assigned for those operations, such as SYSADMIN, to prevent accidental modification of security settings or deletion of critical objects.

It can bypass all Row Access Policies by default.

Why it fails: Even the ACCOUNTADMIN role is subject to Row Access Policies. If a policy is applied to a table, the ACCOUNTADMIN will see the filtered data based on the policy definition. This ensures that even system administrators are governed by the security rules defined by the organization.

Implementing a custom SSL certificate on the client.

Why it fails: Snowflake handles SSL termination natively and securely. Requiring custom client-side certificates is not a standard configuration for connecting to Snowflake and is not required for ensuring transit encryption. This adds unnecessary complexity to the client setup without enhancing the security of the connection beyond standard TLS.

Disabling the use of standard drivers.

Why it fails: Using standard Snowflake drivers is the recommended way to ensure encryption. Disabling them would force the use of custom or unsupported methods, which could potentially bypass built-in security features, leading to unencrypted or poorly secured connections that violate data governance and transit security requirements.

Inside the Account Configuration settings.

Why it fails: While account-level settings exist, MFA is primarily an authentication-level setting managed per-user via the Duo integration. There is no single 'MFA toggle' in the account settings; rather, it is enforced through user-specific settings or forced by identity provider (IdP) integration with Snowflake.

Using a Network Policy.

Why it fails: Network Policies control access by IP address (allowed/blocked lists). They do not directly enforce MFA. MFA is enforced by requiring users to be enrolled in Duo Security. While an IdP can enforce MFA before reaching Snowflake, Network Policies are strictly for network-level access control.

Through a database-level GRANT.

Why it fails: GRANT statements are used for Role-Based Access Control (RBAC) to manage privileges on objects like tables, views, and warehouses. Authentication methods, including MFA, are system-level configurations and cannot be assigned through standard SQL GRANT statements, which are reserved for data access permissions.

It can view account-wide usage history.

Why it fails: Account-wide usage history is sensitive information and is not accessible to the PUBLIC role. Access to these views is restricted to roles with higher administrative privileges, such as ACCOUNTADMIN, to ensure that usage patterns and costs are not exposed to general users.

It can drop tables in any database.

Why it fails: Dropping tables is a highly destructive action that requires specific ownership or management privileges. The PUBLIC role does not have these rights by default. Granting the ability to drop tables to every user in the account would lead to massive data loss risks and governance failures.

SECURITYADMIN is a child of SYSADMIN.

Why it fails: The two roles are independent and intended to be separate to maintain Segregation of Duties. Making one a child of the other would allow a user in the parent role to perform both data management and security management, which is a major violation of governance best practices.

SYSADMIN can create roles, but not grant them.

Why it fails: SYSADMIN does not have the authority to create or manage roles. That is the exclusive responsibility of the SECURITYADMIN role. SYSADMIN focuses purely on the creation and management of objects like databases, schemas, and warehouses. Allowing SYSADMIN to create roles would bypass the security controls enforced by SECURITYADMIN.

They must be merged into one role for simplicity.

Why it fails: Merging these roles into one creates a high-risk security environment. The principle of Segregation of Duties dictates that these roles must remain separate so that the person creating data objects cannot also assign access rights to them, which prevents fraud and accidental unauthorized access to sensitive information.

The INFORMATION_SCHEMA.QUERY_HISTORY view.

Why it fails: While this view shows recent query activity, it only retains data for a limited time (usually 14 days) and is scoped to the current database. It is not suitable for the long-term, account-wide auditing required for comprehensive security and governance compliance across an entire enterprise deployment.

The MASKING_POLICY_HISTORY table.

Why it fails: This object is used specifically to track changes to masking policies themselves, not the actual queries executed by users. While useful for auditing policy management, it provides no information regarding the data access patterns or the SQL statements run by individual users in the system.

The ROLE_HISTORY function.

Why it fails: There is no 'ROLE_HISTORY' function in Snowflake. Information about roles is found in the 'ROLES' view within the ACCOUNT_USAGE schema. This provides details about role hierarchy and assignments but does not log the specific SQL queries performed by users who have been assigned those roles.

Query the QUERY_HISTORY view and parse the SQL text.

Why it fails: Parsing SQL text from query logs is error-prone and inefficient. It does not reliably track access, as the same table might be accessed via different SQL aliases, views, or stored procedures. ACCESS_HISTORY is purpose-built to track object-level access regardless of the SQL syntax used to reach it.

Enable object-level auditing in the UI.

Why it fails: There is no single 'object-level auditing' switch in the Snowflake UI that replaces the need to query system views. While Snowflake provides audit capabilities, they are surfaced through the ACCOUNT_USAGE views rather than a simple configuration toggle in the web interface that users can enable manually.

Review the login history for all users.

Why it fails: Login history only tracks authentication events, not the actions taken after a user has logged in. Knowing that a user authenticated successfully tells you nothing about which tables they queried, making it useless for tracking data access or ensuring compliance with data privacy regulations.

Multi-region replication.

Why it fails: Multi-region replication is designed for disaster recovery across geographically distant regions, not for high availability within a single region. While it protects against regional outages, it introduces higher latency and cost, and is not the mechanism that handles local availability zone failures for standard operations.

Data clustering on Micro-partitions.

Why it fails: Data clustering is a performance optimization technique used to organize data within micro-partitions to improve query pruning speed. It has no role in ensuring high availability or data durability during infrastructure failures; it is strictly a query execution and storage performance management feature for large datasets.

Virtual Warehouse auto-suspend.

Why it fails: Auto-suspend is a cost-management feature that shuts down compute resources after a specified period of inactivity. It does not affect data availability or resilience. If a compute cluster is suspended, it simply means no compute resources are active, not that the underlying data or service is unavailable.

Use a Row Access Policy with a hardcoded list of users.

Why it fails: Hardcoding lists of users into policies is a maintenance nightmare that does not scale. As personnel change, the policy requires manual updates, increasing the risk of human error or security gaps. Using tags and roles is a dynamic and automated way to manage access without constant manual intervention.

Implement column-level GRANT permissions.

Why it fails: Column-level GRANTs in Snowflake are restrictive but do not support the logic needed for tag-based, conditional access. They only allow granting SELECT on a specific set of columns. They cannot perform dynamic checks based on tag values, making them insufficient for complex governance requirements involving metadata-driven access.

Manually recreate the table for each role.

Why it fails: Creating multiple copies of a table for different roles is a major security and management anti-pattern. It leads to data synchronization issues, unnecessary storage costs, and a high risk of sensitive data leaking if one copy of the table is misconfigured or lacks the appropriate security protections.

Performance optimizations are always disabled.

Why it fails: Not all optimizations are disabled. Snowflake disables specific optimizations that could leak information, such as certain types of query rewrite or constant folding that might expose underlying data values. However, standard query performance optimizations are still applied to ensure that secure views do not suffer from severe performance degradation.

They automatically apply data masking to all columns.

Why it fails: Secure Views do not automatically mask columns. Masking must be implemented using separate Dynamic Data Masking policies. While views can include masking logic, the 'SECURE' designation is strictly for protecting metadata and the view definition from inspection, not for performing the actual data obfuscation itself.

They allow any user to see the data.

Why it fails: Secure Views respect the standard Snowflake RBAC model. A user still requires the necessary SELECT privilege on the view to access it. The 'SECURE' property adds an extra layer of protection regarding metadata and plan visibility, but it does not bypass the fundamental requirement for proper role-based access authorization.

Use a Dynamic Data Masking policy.

Why it fails: Masking policies are designed to obfuscate column values, not to filter rows based on location. Using masking for geo-fencing would result in users seeing all rows, but with some columns obscured, which does not satisfy the requirement to restrict access to the rows themselves based on geographic location.

Create separate databases for each region.

Why it fails: Creating separate databases for each region creates immense operational overhead and hinders cross-regional reporting. It is a highly inefficient approach that complicates auditing and does not provide the centralized, policy-driven governance that Snowflake's Row Access Policies offer natively within a single, unified data environment.

Use data masking with a CASE statement.

Why it fails: A CASE statement in a masking policy can hide data, but it cannot remove rows from the result set. If a user is not authorized for a region, they should not see the records for that region at all. Masking would still allow the user to see the rows, just with obscured values.

A standard user role with 'AUDIT' permissions.

Why it fails: While roles can have permissions to view logs, a role is not an 'object' designed for centralized management of security policies. It is an identity for authorization. Centralized monitoring is better achieved through Snowsight dashboards or the Account Usage shared database, not by assigning specialized roles alone.

A stored procedure that runs periodically.

Why it fails: Stored procedures are for executing logic, not for centralizing the storage or management of security policies. Using a stored procedure for auditing is fragile and requires manual coding. Snowflake provides built-in, managed objects like the Account Usage database that are more reliable and efficient for these tasks.

A resource monitor attached to the account.

Why it fails: Resource monitors are strictly used to control credit consumption and prevent budget overruns. They have no capabilities regarding access control, policy management, or security auditing. Confusing resource management with data governance is a common mistake when managing account-level administrative objects.

It is automatically assigned to all new users created in the account.

Why it fails: New users are not assigned the ACCOUNTADMIN role by default. Following the principle of least privilege, users should be granted only the specific roles they need for their daily tasks. Assigning ACCOUNTADMIN to every user would create a massive security vulnerability across the organization's entire data estate.

It can be renamed to a custom name like 'SUPERUSER'.

Why it fails: The system-defined roles like ACCOUNTADMIN, SECURITYADMIN, and SYSADMIN are immutable. They cannot be renamed, dropped, or modified by the user. This immutability ensures that these core administrative identifiers remain consistent across all Snowflake accounts, which is necessary for system predictability and internal security auditing processes.

It is the only role that can create databases.

Why it fails: The SYSADMIN role is also capable of creating databases, schemas, and tables. In fact, standard practice involves using SYSADMIN for object management. ACCOUNTADMIN is not the exclusive role for these tasks; it is primarily intended for operations that affect the account's configuration, security settings, and billing parameters.

Direct database credentials sharing.

Why it fails: Sharing database credentials is a severe security violation. Snowflake enforces unique credentials for every user account to ensure proper auditing and accountability. Encouraging credential sharing would break the audit trail and make it impossible to identify which individual performed a specific action within the system.

Hard-coding passwords in source code.

Why it fails: Hard-coding passwords in source code is an extremely insecure practice that poses a massive risk of credential exposure. Snowflake provides secure alternatives like key-pair authentication and secrets management services, which should always be used in place of plaintext credentials to ensure the integrity of the data environment.

The drop operation is blocked until policies are detached.

Why it fails: Snowflake does not block a DROP DATABASE command based on the presence of security policies. The system follows the explicit command, and the destruction of objects is absolute. It is the user's responsibility to verify the dependencies before executing such a destructive action on the account's data structure.

Masking policies are automatically deleted.

Why it fails: Masking policies are separate objects from the tables they protect. Dropping a table or database does not automatically delete the policy object itself. The policy will remain in the account metadata until an administrator explicitly drops it, which can lead to orphaned policy objects if not managed carefully.

The user receives a warning that policies exist.

Why it fails: Snowflake does not provide warning prompts or interactive confirmations for DDL operations like dropping a database. Once a DROP command is executed with sufficient privileges, the action is irreversible (unless Time Travel is used), reflecting Snowflake's design philosophy of fast, direct command execution for administrative operations.

The policy must grant the PAYROLL_ADMIN role the MASKING EXEMPT privilege so that role bypasses all masking policies on the SALARY column.

Why it fails: This is incorrect because Snowflake does not have a MASKING EXEMPT privilege. Masking policies are enforced based on the policy logic itself, not through a privilege that exempts a role. The administrator must define the exemption within the policy using conditionals such as CURRENT_ROLE().

The policy must use IS_ROLE_IN_SESSION() to check if PAYROLL_ADMIN is active, returning the unmasked value if true and a masked value otherwise.

Why it fails: This is incorrect because while IS_ROLE_IN_SESSION() checks role activation, it is not the standard function for role-based masking conditions. CURRENT_ROLE() is the appropriate function. IS_ROLE_IN_SESSION() is used for other contexts like row access policies, not masking policies.

The policy must be applied only to the PAYROLL_ADMIN role using an ALTER MASKING POLICY command, so that only that role sees unmasked data.

Why it fails: This is incorrect because masking policies are applied to columns, not to roles. The ALTER MASKING POLICY command modifies the policy definition but does not target specific roles. Role-based conditions must be written inside the policy body using functions like CURRENT_ROLE().

Object tagging with allowed values

Why it fails: Object tagging allows you to assign tags to objects, and you can define allowed values for a tag. However, it does not automatically enforce that a tag must be applied to new tables. Enforcement of tag application requires a policy that acts on table creation, not just allowed values.

Tag propagation

Why it fails: Tag propagation automatically assigns tags from a source object to a target object, such as from a schema to a table. It does not enforce that a specific tag must be present; it only copies existing tags. It also does not prevent table creation without the tag.

Tag-based masking policy

Why it fails: Tag-based masking policies are used to dynamically mask column data based on the tag assigned to a column. They do not enforce that tags must be applied to new tables. This feature is for data protection, not for tag propagation or enforcement of tag assignment.

SYSADMIN

Why it fails: SYSADMIN is responsible for creating and managing warehouses, databases, and other objects, but it does not have the privilege to create or manage users and roles. Those tasks fall under SECURITYADMIN or ACCOUNTADMIN. Therefore, SYSADMIN would not provide the required capabilities for user and role management.

ACCOUNTADMIN

Why it fails: ACCOUNTADMIN is the top-level role that encompasses all privileges, including managing users and roles, but it also grants full control over the entire account, including billing and all data. Granting this role violates the principle of least privilege because the security team member only needs user and role management, not complete account control. A more limited role is appropriate.

USERADMIN

Why it fails: USERADMIN is a predefined role that can create and manage users and roles, but it does not include the MANAGE GRANTS privilege, which allows granting privileges on objects. The question specifies the need to 'grant the privilege to create and manage users and roles', which implies the ability to grant those privileges to others. USERADMIN alone cannot grant privileges, so it is insufficient.

Tag-based masking policies automatically mask data for all users, including those with the ACCOUNTADMIN role.

Why it fails: Tag-based masking policies do not automatically mask data for all users. The masking policy can be defined to exempt certain roles, such as ACCOUNTADMIN, from masking. By default, ACCOUNTADMIN can see unmasked data unless the policy explicitly restricts it. This option incorrectly assumes universal masking.

A tag-based masking policy can be applied to a table to mask all columns at once.

Why it fails: Tag-based masking policies are applied at the column level, not the table level. You cannot apply a single masking policy to a table to mask all columns. Each column must have the tag assigned to it for the masking policy to take effect. This option misrepresents the scope of tag-based masking.

Tag-based masking policies can be used to enforce row-level security.

Why it fails: Tag-based masking policies are for column-level masking, not row-level security. Row-level security is enforced through row access policies. Tag-based masking policies do not filter rows; they mask column values based on tags. This option confuses the two governance features.

Row access policy

Why it fails: A row access policy filters rows based on conditions, not columns. It would not prevent users from seeing the SSN or CREDIT_CARD columns; it only restricts which rows are returned. Since the requirement is to hide specific columns entirely, a row access policy does not meet the need. It is used for row-level security, not column-level projection.

Column-level masking policy

Why it fails: A masking policy replaces the actual data in a column with a masked value, but it does not prevent the column from being selected. Users running SELECT * would still see the masked column, and they could not be blocked from accessing it. Masking is for obfuscation, not for restricting column visibility. In this scenario, the requirement is to hide entire columns, not just alter their values.

Object tagging with a sensitivity tag

Why it fails: Tags are metadata used for classification, tracking, and policy enforcement, but they do not by themselves restrict column access. A tag can be used with a masking policy to automate masking, but it does not provide column projection. Without an associated policy, tagging alone does not prevent users from querying sensitive columns. Thus, it does not solve the scenario.