Securing Data practice questions

Create a standard SQL view that filters rows and omits the sensitive column, then grant access to the view.

Why it fails: Creating separate views introduces data proliferation and maintenance overhead. Users can often bypass views if they retain direct SELECT privileges on the underlying base table, making this approach unreliable for strict enterprise security compliance and auditing.

Configure a table-level access control list (ACL) to restrict the marketing team from querying the credit_card column directly.

Why it fails: Table-level ACLs in Unity Catalog only permit granting or revoking privileges on entire tables or schemas. They do not support column-level restrictions or row-level filtering natively without the use of specialized masking functions.

Set up table access properties in the Hive metastore using traditional GRANT and REVOKE statements for column security.

Why it fails: The legacy Hive metastore lacks native, dynamic column masking and row filtering capabilities. Relying on the legacy metastore for modern governance is discouraged and fails to provide secure, centralized auditing across workspaces.

Create a static view filtered by region and grant the regional team SELECT permission on that view instead of the base table.

Why it fails: Static views require maintaining multiple redundant objects for every regional variation, leading to administrative overhead and significant data duplication. Unity Catalog dynamic views or native row filters provide a far superior, scalable approach for handling multi-tenant security requirements without managing separate tables.

Export the subset of regional data to a separate storage container and provide the regional team with direct read access via cloud IAM roles.

Why it fails: Bypassing Unity Catalog governance to provision separate cloud storage containers creates severe security blind spots and audit compliance risks. Managing access strictly through cloud IAM roles fails to enforce fine-grained row and column filtering natively required within the Databricks analytics platform.

Revoke read permissions on the schema level and rely entirely on cluster-level access control lists to filter out unauthorized rows.

Why it fails: Cluster access control lists manage who can attach to or manage compute resources, not what data users can query within those resources. Relying on cluster boundaries for data security fails because any user with cluster access could still write queries to inspect unmasked sensitive tables.

Execute SELECT queries against any table located inside the schemas of this catalog.

Why it fails: USE CATALOG grants only traversal of the catalog namespace; it does not confer SELECT on tables inside its schemas, which needs SELECT at table, schema, or catalog level. Querying data is what SELECT privileges are for, granted when an analyst is given read access to specific tables or schemas.

Grant the USE SCHEMA privilege on child schemas to other analysts within the same business unit.

Why it fails: Granting privileges requires MANAGE or ownership on the securable plus the GRANT OPTION; USE CATALOG alone conveys no authority to delegate USE SCHEMA to others. This option is tempting because catalog owners routinely grant schema privileges, but that flows from ownership, not from the USE CATALOG privilege itself.

Create new tables and views directly within the default schema of the targeted catalog.

Why it fails: USE CATALOG only permits navigating the catalog and referencing its schemas; it confers no CREATE TABLE or CREATE VIEW rights, which require CREATE plus USE SCHEMA on the target schema. Creating objects is the purpose of schema-level CREATE privileges, granted when a user owns or is delegated write access to a specific schema.

Apply a Workspace-level ACL to the entire database.

Why it fails: Workspace-level access control lists are too broad and do not support column-level granularity. Applying them would grant or deny access to all tables within a schema, failing to provide the specific restriction needed to hide sensitive PII while still allowing access to non-sensitive columns in the same table.

Move the sensitive columns into a separate, restricted table.

Why it fails: While physically separating sensitive columns into a restricted table is technically possible, it is administratively inefficient and breaks existing data pipelines. It requires complex join logic for authorized users and significantly increases maintenance overhead. Unity Catalog features are designed to handle these security requirements without requiring physical schema changes.

The user is missing the 'CATALOG_USER' role.

Why it fails: Unity Catalog does not use a 'CATALOG_USER' role for basic table access. Permissions are granted via SQL GRANT statements directly to users or groups. The absence of a non-existent role would not cause a UI visibility issue, as permissions are evaluated against the user's explicit assignments and group memberships.

The user lacks the CREATE permission on the schema.

Why it fails: SELECT access is sufficient for viewing tables and querying data. The CREATE permission is only required if the user needs to add, modify, or drop objects within the schema. Lacking this permission would not prevent a user from seeing an existing table in the Data Explorer interface.

The user is not in the 'finance_team' group.

Why it fails: If the user is not a member of the group 'finance_team', they will not inherit the permissions granted to that group. Unity Catalog access is strictly enforced based on the identity of the user. If the user's identity is not linked to the authorized group, they will see no data.

Create a dynamic view containing a WHERE clause filtering the region column by the current user identifier and grant SELECT on the view.

Why it fails: Dynamic views are an older Hive metastore pattern and create maintenance overhead by requiring duplicate object structures. Unity Catalog prefers native table row filters applied directly to the base table to prevent data sprawl and ensure consistent security across all downstream queries.

Configure a table-level access control list granting SELECT only when the analyst uses a specific cluster configured with an instance profile.

Why it fails: Cluster-level instance profiles control cloud resource permissions rather than filtering data rows inside tables. Relying solely on compute configurations provides all-or-nothing access to the entire table and cannot restrict visibility down to individual rows based on user identity.

Execute an ALTER TABLE command to drop rows from the table that do not match the analyst's assigned regional territory before they run queries.

Why it fails: Dropping rows permanently destroys data for other business units, violating core data governance and multi-tenant isolation principles. Security controls must never mutate the underlying dataset to enforce read-time visibility restrictions for specific operational personas.

Export the table to CSV files, manually redact the sensitive columns using a script, and upload the redacted files to an external cloud bucket for the recipient.

Why it fails: Manual export workflows introduce severe data staleness, security risks, and administrative overhead by bypassing native governance controls. Delta Sharing provides secure, live access directly from Delta tables while maintaining complete auditability and eliminating manual file transfers.

Revoke read permissions on the entire table and share a derived view created using the CREATE VIEW statement in the recipient's metastore.

Why it fails: Delta Sharing operates across distinct Databricks accounts and metastores where local views cannot reference tables outside the secure sharing boundary. Delta Sharing relies on native sharing entities rather than cross-account view definitions to manage external access securely.

Set up an Apache Spark caching job that overwrites the original table with null values in sensitive columns whenever an external recipient runs a query.

Why it fails: Overwriting the source table with nulls destroys the data for all consumers and is not a sharing control; Delta Sharing recipients query the shared table read-only. Column-level security via dynamic views or column masks on the share is required. Caching jobs serve query performance, not data redaction.

SELECT on the table 'main.clinical.patients'

Why it fails: Granting SELECT on the table would allow the nurses to read all columns, not just 'patient_id'. Unity Catalog does not support column-level privileges; SELECT is table-level. This would violate the principle of least privilege and expose sensitive data such as diagnoses and treatment notes.

SELECT on the column 'main.clinical.patients.patient_id'

Why it fails: Unity Catalog does not support granting privileges directly on columns. Privileges such as SELECT are granted at the table, schema, or catalog level. To restrict access to specific columns, you must use a dynamic view or a row filter/column mask, not a column-level grant.

Grant USAGE on the column 'patient_id'

Why it fails: USAGE is not a valid privilege for columns in Unity Catalog. USAGE applies to catalogs and schemas to allow object traversal, not to columns. Granting USAGE on a column would fail because column-level grants are not supported.

Grant SELECT on the base table to the group but instruct them to use aggregation in their queries.

Why it fails: Granting SELECT on the base table allows users to query individual rows directly, bypassing any aggregation. Instructions are not enforceable. This approach does not prevent users from seeing sensitive row-level data, so it fails the requirement.

Grant MODIFY on the base table to the group so they can create their own aggregated tables.

Why it fails: Granting MODIFY allows users to insert, update, and delete data in the base table, which is a security risk and does not restrict them to aggregated results. They could still query the base table if they have SELECT, but MODIFY alone does not grant SELECT. However, this does not achieve the goal and introduces unnecessary privileges.

Use dynamic views with row-level filters to mask individual rows.

Why it fails: Dynamic views with row-level filters are used for row-level security, not aggregation. They can filter rows based on user attributes, but they still return individual rows (a subset). This does not achieve the goal of only showing aggregated results. The requirement is aggregation, not filtering.

Grant the recipient SELECT on the table 'main.sales.transactions'.

Why it fails: Granting SELECT on the table would allow the recipient to query all columns, including 'credit_card_number'. Delta Sharing shares are separate from Unity Catalog privileges; the recipient does not need direct SELECT on the table. Instead, the share itself defines what data is exposed.

Use the Delta Sharing REST API to define a partition filter on the share.

Why it fails: Partition filters can restrict rows, not columns. The requirement is to hide a specific column, not filter rows. Partition filters are used to share subsets of data based on partition values, but they cannot exclude a column from the shared schema.

Apply a column mask on 'credit_card_number' to redact values for the recipient.

Why it fails: Column masks are applied within Unity Catalog and are evaluated based on the querying user's identity. However, Delta Sharing recipients are external and do not have Unity Catalog identities, so column masks do not apply to shared data. The mask would not affect the shared table.

Grant SELECT on the table to all users and rely on the 'finance' group membership to restrict access.

Why it fails: Granting SELECT to all users would allow anyone to query the table, regardless of group membership. Group membership does not restrict access if the privilege is granted broadly. This approach would expose sensitive data to unauthorized users.

Create a view that filters rows based on the user's group and grant SELECT on the view to all users.

Why it fails: While a dynamic view with row filters can restrict rows, the requirement is to restrict access to only the finance group, not filter rows for all users. Granting SELECT on the view to all users would allow non-finance users to query the view, potentially seeing some data. The requirement is to restrict access entirely to the finance group.

Grant USAGE on the schema to all users and SELECT on the table to the 'finance' group.

Why it fails: Granting USAGE on the schema allows all users to navigate the schema, but without SELECT on the table, they cannot query it. However, this does not revoke existing SELECT privileges that other users might have. If other users already have SELECT, they would retain access. The approach does not ensure that only the finance group can query the table.

ALL PRIVILEGES

Why it fails: `ALL PRIVILEGES` on the schema would grant all applicable privileges, including `CREATE TABLE` and `USE SCHEMA`, but it would also grant other privileges like `CREATE FUNCTION`, `CREATE MODEL`, etc., which may exceed the requirement. The principle of least privilege suggests granting only what is needed. Since the question asks for two specific privileges, `ALL PRIVILEGES` is too broad and not the intended answer.

MODIFY

Why it fails: `MODIFY` is a table-level privilege that allows inserting, updating, and deleting data in a table. It does not apply at the schema level and does not grant the ability to create new tables. Granting `MODIFY` on the schema is not valid; it would need to be granted on specific tables, but that does not address the requirement to create tables.

SELECT

Why it fails: `SELECT` on the schema does not grant the ability to query tables; `SELECT` must be granted on the tables themselves. Granting `SELECT` on the schema is not a valid privilege in Unity Catalog. The requirement is to allow querying tables, which requires `SELECT` on each table, not on the schema. Therefore, this privilege does not meet the need.

Grant `SELECT` on the underlying table to all users who need access, and also grant `SELECT` on the view.

Why it fails: Granting `SELECT` on the underlying table would allow users to bypass the view and see all rows directly, defeating row-level security. Instead, users should be granted `SELECT` only on the view, not the base table. The base table's `SELECT` should be revoked from regular users. This ensures they must go through the view's filtering logic.

Create a row filter function using `CREATE FUNCTION` and apply it to the table with `ALTER TABLE ... SET ROW FILTER`.

Why it fails: Row filter functions are a valid Unity Catalog feature, but the question specifies using dynamic view functions. While row filters are more modern, the scenario explicitly mentions 'dynamic view functions', which implies a view-based approach. Using row filters would be an alternative, but not the required two steps for this implementation. Thus, this option is not part of the correct solution.

Use `GRANT SELECT ON TABLE main.hr.employees TO hr_admins` and rely on the view's WHERE clause to filter for others.

Why it fails: Granting `SELECT` on the base table to `hr_admins` is acceptable if they need full access, but this option alone does not address other users. The requirement is to implement row-level security for all users, so we must also ensure non-admins have no direct table access. This step is incomplete and could leave a security hole if non-admins also have table access.

`deleteTable` with `statusCode` 403

Why it fails: `deleteTable` events pertain to dropping tables. A 403 status would indicate a failed attempt to delete a table, not to read it. Unauthorized read attempts would be logged under access events like `getTable`, not deletion events. Therefore, this is not the correct event to look for.

`queryTable` with `statusCode` 200

Why it fails: A `queryTable` event with status code 200 indicates a successful query. It does not represent an unauthorized attempt. The analyst is looking for failed attempts, so a successful query is not relevant. Additionally, `queryTable` might not be the exact event name; Unity Catalog logs table access under different event types.

`createTable` with `statusCode` 403

Why it fails: `createTable` events relate to creating tables, not reading them. A 403 status on `createTable` would indicate a failed attempt to create a table, which is not the same as an unauthorized read attempt. The scenario is about reading data, so this event is not applicable.

SELECT on the table only

Why it fails: SELECT on the table allows a user to query rows and columns, but it does not authorize attaching a masking function that rewrites column values for other users. Applying a column mask is a governance operation on the table object, so a plain SELECT privilege is insufficient here. The analyst needs a privilege that permits altering the table's security metadata, not merely reading its data.

MODIFY on the table

Why it fails: MODIFY lets a user insert, update, or delete data rows in the table, which is about changing table contents rather than changing how values are presented to readers. A column mask is part of the table's governance definition, so MODIFY does not grant the authority to attach the masking function. This privilege is commonly confused with ownership-level control, but it does not cover security policy changes.

MANAGE on the table

Why it fails: MANAGE grants the ability to control privileges, transfer ownership, and manage certain table-level settings, but applying a column mask to a table requires the OWNER privilege because the mask changes the table's security definition. MANAGE is about administering access rather than redefining row or column presentation rules. This distractor is plausible because both deal with governance, but only ownership authorizes the mask.

Create a static clone of the table, drop the restricted column, and grant SELECT permission on the clone to the marketing group.

Why it fails: Static cloning creates an expensive duplicate copy of the data that immediately drifts from the source table. Maintaining synchronized updates requires complex engineering pipelines, violating the single source of truth principle within the Unity Catalog architecture.

Configure an access control list on the parent catalog to deny SELECT access on specific columns for the marketing group.

Why it fails: Unity Catalog access control lists operate at the catalog, schema, table, and view levels. They do not support denying access to individual columns or filtering specific rows natively through catalog permissions alone.

Export the filtered subset of data into CSV files and upload them to a secured volume inside the marketing team's schema.

Why it fails: Exporting data to flat files introduces severe security risks, including unmanaged data proliferation, lack of audit trails, and stale data snapshots. It circumvents the centralized governance and security auditing capabilities provided by Unity Catalog.

GRANT ALL PRIVILEGES ON SCHEMA sales_schema TO `colleague@company.com`;

Why it fails: Granting ALL PRIVILEGES on sales_schema gives read, write and manage rights over every table in that schema, not just quarterly_sales. It tempts because schema-level grants are the standard way to give a team access to a whole subject area.

GRANT READ ACCESS ON CATALOG main TO `colleague@company.com`;

Why it fails: Granting READ ACCESS on the catalog main confers read rights across every schema and table within it, exposing far more than quarterly_sales. The tempting appeal is breadth: catalog-level grants suit onboarding analysts who legitimately need the whole catalog, not a single table.

GRANT USE TABLE ON quarterly_sales TO `colleague@company.com`;

Why it fails: Unity Catalog has no USE TABLE privilege; table access is granted via SELECT, and USE TABLE is not valid syntax. It is tempting because USE SCHEMA and USE CATALOG exist, and would be correct when granting a user the ability to reference a schema or catalog.

Local workspace-level user management.

Why it fails: Local workspace-level management creates fragmented identities that are incompatible with Unity Catalog's centralized governance model. Relying on local users prevents the effective application of consistent security policies across multiple workspaces, as identity mappings would not be synchronized or recognized by the global Unity Catalog metastore configuration.

Database-level authentication via JDBC drivers.

Why it fails: JDBC authentication is a legacy method for connecting external tools to Databricks clusters and does not manage internal user identities. Unity Catalog relies on the Databricks platform's internal identity model, which is managed via Account Console or SCIM, not through database connection strings or traditional external database authentication protocols.

Private Link connectivity for all users.

Why it fails: Private Link handles network security and traffic isolation between your VPC and Databricks. While important for enterprise networking, it does not define or manage the identity lifecycle required for Unity Catalog. You can have Private Link without Unity Catalog, and you need account-level identities regardless of network configuration.

Delete the sensitive rows from the table manually.

Why it fails: Manually deleting rows is a destructive action that results in permanent data loss. Data security should be achieved through access control policies, not by destroying the data itself. This approach prevents legitimate business use cases that require the full dataset for reporting, auditing, or machine learning model training.

Rotate the workspace passwords weekly.

Why it fails: Password rotation is a credential management policy, not a data security policy. While good for security hygiene, it does not restrict access to specific tables within Databricks. Access to data is governed by Unity Catalog permissions, regardless of the password policy applied to the user's account authentication.

Disable the table for all users except the owner.

Why it fails: Disabling a table is not a supported administrative action in Databricks. You manage access via permissions. Even if possible, it would be impractical for real-world scenarios where multiple teams need access to different levels of data. Security should be managed through granular permissions rather than binary enable/disable switches.

To encrypt files stored in the S3 bucket.

Why it fails: Storage Credentials are used for authentication and authorization to access cloud storage, not for data encryption. Encryption is handled by the cloud provider (e.g., S3 server-side encryption) using keys managed in services like KMS. The storage credential simply proves to the cloud provider that Databricks has permission to read files.

To manage user passwords for the Databricks workspace.

Why it fails: Storage credentials manage access to data in external cloud storage, not the authentication of users into the Databricks environment. User authentication is managed by the identity provider (IdP) linked to your Databricks account, such as Okta, Microsoft Entra ID, or Google Workspace, and is completely separate from cloud storage IAM roles.

To define table schema definitions for external tables.

Why it fails: Table schemas are defined using Data Definition Language (DDL) within the Databricks SQL environment. Storage credentials have nothing to do with defining columns, data types, or partitioning strategies. They are strictly infrastructure-level security objects that authorize data access, ensuring that the platform can read the underlying files associated with tables.