Data Security and Compliance practice questions

Using the 'SHOW PERMISSIONS' SQL command.

Why it fails: While 'SHOW GRANTS' is a valid command to inspect current permissions on an object, it is not an audit log. It shows the current state of access control, not the history of who accessed the data or when that access occurred, which is necessary for comprehensive security auditing.

Manually checking the user's home folder.

Why it fails: Manually checking files is not an audit process and does not provide a record of activity. Auditing must be centralized, automated, and immutable. Checking individual home folders is inefficient, prone to error, and fails to provide the high-level oversight required for a secure enterprise data platform.

Granting SELECT access on a table.

Why it fails: Granting SELECT access on a table is a standard task for data owners or users with the GRANT permission on that specific object. It does not require metastore-level privileges. Decentralizing this authority is essential for scalability, as it allows teams to manage their own data access independently.

Executing a 'REFRESH TABLE' command.

Why it fails: Refreshing table metadata is a routine operational task that can be performed by any user with permission to modify the table. It does not carry security implications for the metastore itself and should not be blocked by requiring high-level administrative privileges, as this would severely hamper operational efficiency.

Use a public IP for the workspace.

Why it fails: Using a public IP is a security risk, not a solution. It exposes the workspace to the public internet, increasing the attack surface. Security should always start with a private network architecture, using Private Links or VPC endpoints to ensure that traffic stays within the secure network boundary.

Enable Unity Catalog with masking policies.

Why it fails: Unity Catalog masking policies ensure that even if a user has access to the table, they only see the data they are authorized to see. This dynamic approach to security is the most effective way to handle sensitive data, as it provides a single point of control that is automatically applied, regardless of the compute cluster used.

Granting 'ALL PRIVILEGES' to the data team.

Why it fails: Granting excessive privileges is the opposite of a secure design. The principle of least privilege should always be applied. Giving full control to the data team creates an unnecessary security risk, where a single compromised account could lead to a catastrophic data breach across the entire platform.

Disabling audit logs to improve performance.

Why it fails: Audit logs are critical for security and compliance. Disabling them for performance reasons is a major oversight that would leave the organization blind to potential security incidents. Performance should be managed through workload optimization, not by sacrificing the visibility required to maintain a secure and compliant data environment.

Configure a Unity Catalog external location with a network policy that allows only the specified IP addresses.

Why it fails: Unity Catalog external locations do not have a built-in network policy feature. While external locations define storage paths and credentials, they do not support IP-based access control directly. Network restrictions must be applied at the cloud provider level or through other mechanisms, not via external location configuration.

Attach a cluster-scoped init script that configures iptables to block all traffic except from the specified IP addresses.

Why it fails: Cluster-scoped init scripts run on cluster nodes and can modify local firewall rules, but they do not restrict access to the storage credential itself. The credential could still be used from other clusters or external services. This approach also introduces operational complexity and does not provide a centralized security control.

Enable Unity Catalog's IP access list feature on the metastore and add the specified IP addresses to the allow list.

Why it fails: Unity Catalog does not have an IP access list feature at the metastore level for storage credentials. While Databricks workspaces support IP access lists for user access, this does not apply to storage credentials used for external locations. The requirement is to restrict the credential's usage, not workspace access.

The dynamic view was created without specifying the 'WITH GRANT OPTION', so the permissions are not cascading to the base table.

Why it fails: WITH GRANT OPTION is used when granting privileges to allow the grantee to grant those privileges to others. It does not affect whether a view can restrict access to the base table. The problem is that users have direct SELECT on the table, not a missing grant option. WITH GRANT OPTION is irrelevant to column-level security enforcement.

The dynamic view was not created with the SECURITY DEFINER clause, so users inherit their own permissions on the base table.

Why it fails: The SECURITY DEFINER clause is not a valid Unity Catalog syntax for views; Unity Catalog views are always definer's rights by default, meaning the view owner's permissions are used. The issue is not about the view's security mode but about direct table access. Users can query the base table because they have been granted SELECT on it separately, bypassing the view's column filtering.

Unity Catalog does not support column-level security through views; the engineer must use attribute-based access control (ABAC) instead.

Why it fails: Unity Catalog does support column-level security through dynamic views, which can filter rows and mask columns based on user attributes. While ABAC is a newer feature, views are a valid and common method. The issue here is not a lack of support but the presence of direct table grants that bypass the view's restrictions.

ALTER TABLE main.sales.sales_raw OWNER TO `sp-abc`;

Why it fails: Transferring ownership gives the service principal full control over the table, including the ability to drop it, alter it, and grant privileges to others. That is far more than read access and is not appropriate for a read-only integration. Ownership also does not fit the scenario of granting limited access to a service principal.

GRANT SELECT ON CATALOG main TO `sp-abc`;

Why it fails: Granting SELECT at the catalog level would give the service principal read access to every table in every schema in the catalog, which violates the principle of least privilege. The requirement is to grant access to a single table only. This command is far too broad and would expose unrelated data.

GRANT USAGE ON TABLE main.sales.sales_raw TO `sp-abc`;

Why it fails: USAGE is not a valid privilege for tables in Unity Catalog. USAGE applies to catalogs and schemas to allow navigation, but it does not grant data access. Even if it were accepted, it would not allow reading rows. The engineer needs SELECT to read the Delta table, so this command fails to meet the requirement.

A Unity Catalog temporary credential issued through a dedicated access policy

Why it fails: Temporary credentials in Unity Catalog are used for external storage and cloud IAM access, not for governing SQL table reads. They cannot require a written justification or bind an approval workflow to a table query. Using them here would not satisfy the auditor's requirement for recorded, time-bound approval, and would leave the table's SELECT privilege governance unresolved.

Table-level GRANT SELECT with a WITH GRANT OPTION clause

Why it fails: WITH GRANT OPTION lets a grantee re-delegate the privilege to other principals, which broadens access rather than limiting it. It does not enforce a justification, does not expire after eight hours, and creates a permanent privilege that survives beyond the audit window. This expands the attack surface and directly contradicts the requirement to avoid permanent group-level privileges on the PHI table.

Row filter and column mask functions applied to the table

Why it fails: Row filters and column masks restrict which rows or values a user sees, but they do not gate access on an approval or justification. A user with SELECT still executes the query immediately. These features are appropriate for PII minimization, not for enforcing time-bound, auditable approval before any access occurs, so they fail the auditor's control objective here.

Use Unity Catalog's built-in audit logging feature and set the retention period to 7 years in the metastore settings.

Why it fails: Unity Catalog does not have a built-in audit logging feature with configurable retention at the metastore level. Audit logs are generated at the workspace level and must be delivered to a customer-managed storage location for long-term retention. There is no metastore setting for retention period.

Configure Databricks SQL alerts to monitor access to the 'transactions' table and log alerts to a separate system for 7 years.

Why it fails: Databricks SQL alerts are for monitoring query results and triggering notifications, not for auditing data access. They do not capture failed attempts or provide a comprehensive audit trail. This approach would miss many access events and is not suitable for compliance.

Create a Delta table that captures access events by querying the system.access.audit table and set up a scheduled job to archive it for 7 years.

Why it fails: The system.access.audit table in Unity Catalog provides audit logs, but it only retains data for a limited time (typically 90 days). It does not capture failed attempts by default in all cases. Relying on it for 7-year retention is insufficient without an external archival process, and it may not include all necessary details.

Use a Unity Catalog storage credential with a network policy that allows only the specified IP range.

Why it fails: Storage credentials authorize access to cloud storage paths and do not enforce client IP allowlists for query access. Network policies are configured at the workspace or account level, not per table via storage credentials. This approach would not selectively restrict a single table to specific IP addresses.

Create a Unity Catalog row filter on the table that checks the client IP address.

Why it fails: Row filters in Unity Catalog are SQL UDFs that evaluate column values per row, not network metadata. They cannot inspect the client IP address or connection context. Using a row filter here would not restrict access by IP and could accidentally filter data rows based on unrelated column values, failing the requirement entirely.

Apply a Unity Catalog column mask to all columns of the table.

Why it fails: Column masks transform values returned for specific columns based on the invoking user or group, but they do not control network origin. Masking every column would obscure data for all users and still allow connections from any IP, so it does not enforce the IP restriction required for this table.

Set the table property 'delta.columnMapping.mode' to 'name' to enable column masking.

Why it fails: Column mapping is a Delta Lake feature that allows renaming columns without rewriting data. It is not related to security masking and does not enable or enforce column masks. Setting this property would not affect whether users see masked or unmasked values, and it is not a prerequisite for Unity Catalog column masks.

Create a view that excludes the 'ssn' column and grant SELECT on the view to all users instead of the table.

Why it fails: A view can restrict columns, but it does not provide dynamic masking based on group membership. All users of the view would see the same columns, and if the 'ssn' column were included, they would see actual values. This approach does not satisfy the requirement that 'pii_readers' see real values while others see masked values.

Grant SELECT on the table to all users, and grant SELECT on the 'ssn' column only to 'pii_readers'.

Why it fails: Unity Catalog does not support column-level SELECT grants in the way described; privileges are granted at the table level for SELECT. Restricting a column by grant is not the masking mechanism. Column masks are used to control visibility of values, not to restrict column-level privileges, and this approach would not produce masked values for unauthorized users.

Unity Catalog's row-level filtering and column masking to hide personal data.

Why it fails: Row-level filtering and column masking are for access control, not for permanent deletion. They can hide data from users but do not delete it from storage. For GDPR erasure, the data must be physically removed, so these features are insufficient.

Delta Lake's DELETE operation with the VACUUM command to remove old files.

Why it fails: While DELETE and VACUUM can remove data and old files, they do not guarantee immediate and permanent deletion from all storage layers, including backups and caches. VACUUM has a default retention threshold of 7 days, so data may persist. This is not the most direct feature for GDPR erasure in Unity Catalog.

Unity Catalog's data lineage to track and then manually delete all copies of the data.

Why it fails: Data lineage helps track data origins and transformations, but it does not provide a mechanism to delete data. Manually deleting all copies based on lineage is error-prone and does not ensure complete erasure, especially from backups and logs.

Row filters on every table that compare the user's region to a stored region column

Why it fails: Row filters hide rows but do not prevent a user from querying a table in another region's catalog. The user could still run queries, consume compute in the wrong region, and potentially infer data through aggregates or metadata. Residency requires preventing access to the catalog itself, which row filters cannot do, so this approach is incomplete and operationally fragile across many tables.

Delta Lake table constraints that validate a `region` column against the user's home region

Why it fails: Table constraints enforce data quality on writes, such as CHECK constraints, and do not evaluate the querying user's identity or region. They cannot prevent a user in one region from reading a table in another region's catalog. Using constraints here would validate stored values but leave cross-region read access fully open, so it does not satisfy the residency rule.

Storage credentials scoped to each region's cloud storage account

Why it fails: Storage credentials govern how Unity Catalog accesses cloud storage; they do not restrict which workspaces can see which catalogs. A user in the wrong region could still query a catalog whose storage credential points to another region's bucket, so data would be read across regions. Storage credentials are an access mechanism, not a residency boundary, and therefore fail the requirement.

GRANT ALL PRIVILEGES ON SCHEMA analytics TO the engineer

Why it fails: ALL PRIVILEGES on the schema includes CREATE, USAGE, and other rights, but it does not automatically grant SELECT on existing tables, and it grants more than the stated need. It also can confer ownership-like abilities depending on the object. This violates least privilege because it is broader than create-and-read, and it may leave the engineer unable to read tables anyway.

GRANT USAGE ON CATALOG and GRANT CREATE ON CATALOG to the engineer

Why it fails: Granting at the catalog level is far broader than needed and would allow creating schemas and objects across the entire catalog, not just the analytics schema. It also does not grant SELECT on the existing tables in the analytics schema, so it both over-privileges and under-delivers. Least privilege requires scoping grants to the specific schema.

GRANT USAGE ON SCHEMA analytics and GRANT CREATE ON SCHEMA analytics to the engineer

Why it fails: USAGE and CREATE let the engineer create tables in the schema, but they do not grant SELECT on tables that already exist. The engineer could create new tables yet be unable to read the existing analytics tables, so the requirement to read all existing tables is unmet. An additional SELECT grant is required, making this set incomplete.

GRANT CREATE TABLE ON SCHEMA prod.sales TO `service-principal`;

Why it fails: The syntax 'GRANT CREATE TABLE ON SCHEMA' is not valid in Unity Catalog. Privileges like CREATE TABLE are granted on the schema, but the correct privilege name is 'CREATE' or 'CREATE TABLE'? In Unity Catalog, the privilege to create tables is 'CREATE' on the schema, not 'CREATE TABLE'. The correct statement would be 'GRANT CREATE ON SCHEMA prod.sales TO ...'. This option uses incorrect privilege name.

GRANT ALL PRIVILEGES ON SCHEMA prod.sales TO `service-principal`;

Why it fails: Granting ALL PRIVILEGES would give the service principal excessive permissions, including the ability to drop tables, modify data, and grant privileges to others. This violates the principle of least privilege. The requirement is only to create tables, so a more specific grant is needed. ALL PRIVILEGES is too broad and not recommended.

GRANT USAGE ON SCHEMA prod.sales TO `service-principal`;

Why it fails: USAGE on a schema allows the principal to access objects within the schema but does not allow creating new objects. To create tables, the principal needs the CREATE privilege. USAGE alone would not fulfill the requirement. The engineer must grant CREATE in addition to any necessary USAGE on the catalog and schema.

Create separate physical tables for HR and general users.

Why it fails: Creating separate physical tables leads to data duplication, increased storage costs, and significant data synchronization challenges. Maintaining consistency across multiple tables is prone to human error, making it difficult to audit and ensure that both tables reflect the same source of truth for compliance reporting.

Use standard SQL views for every user to filter columns.

Why it fails: While views can restrict column access, they are difficult to manage at scale as the number of users and roles grows. Furthermore, views do not provide the same centralized metadata lineage and access auditing capabilities that Unity Catalog's native column masking and filtering features provide.

Assign the 'SELECT' permission on individual columns in the UI.

Why it fails: Databricks Unity Catalog permissions are primarily managed at the table, schema, or catalog level. While column-level security is supported via masking and filtering, there is no UI checkbox feature to simply grant or revoke SELECT access on individual columns without defining a masking policy or filter.

Assign table ownership to individual users for better tracking.

Why it fails: Assigning ownership to individual users creates a significant security risk when those users leave the organization. If an account is deleted, ownership management becomes complex. Best practice dictates using service principals or dedicated groups to manage ownership to ensure continuity and prevent unauthorized access gaps.

Ensure that the metastore admin has access to all data.

Why it fails: The metastore admin role should be treated with extreme caution and follow the principle of least privilege. While they manage metadata, they should not necessarily have unrestricted access to read sensitive business data. Separating administrative duties from data access duties is a critical component of security compliance.

Public access should be granted to the root catalog.

Why it fails: Granting public access to the root catalog is a severe security vulnerability. It allows any user in the workspace to discover and potentially access data objects. Access control should be strictly restricted to specific users and groups using the principle of least privilege to ensure data security.

To increase the read throughput of Delta tables.

Why it fails: Customer-managed keys are a security feature, not a performance optimization tool. While they provide robust encryption, they do not impact the underlying I/O performance of Delta Lake. Performance improvements are achieved through techniques like Z-Ordering, partitioning, and using efficient file formats, not through key management configurations.

To enable transient data encryption for cluster nodes.

Why it fails: Transient data encryption on cluster nodes is handled by the platform's standard disk encryption. CMK is specifically focused on the long-term encryption of data at rest within the underlying object storage layer, providing a mechanism for regulatory compliance and total data access revocation by the customer.

To bypass the need for Unity Catalog permissions.

Why it fails: Encryption and access control are separate security domains. Even with CMK, Unity Catalog permissions are still strictly required to govern who can query the data. CMK protects the data files in storage; Unity Catalog governs the SQL-level access for users and service principals within the workspace.

Unity Catalog access controls.

Why it fails: Unity Catalog governs data access within the platform, not the underlying network connection to the workspace. While it is essential for fine-grained security, it does not provide the capability to filter incoming traffic based on source IP addresses. That is a function of the workspace-level network configuration.

Cluster-level Spark configurations.

Why it fails: Spark configurations are used for performance tuning and environment variables, not for network perimeter security. Attempting to manage network access via Spark configs is ineffective and unsupported, as network traffic is handled by the cloud infrastructure layer and workspace entry points before the Spark cluster is even involved.

Workspace-level SSO integration.

Why it fails: SSO (Single Sign-On) handles user authentication and identity management, ensuring that users have the correct credentials. However, it does not inherently provide IP filtering. While some IdPs support conditional access based on location, Databricks IP Access Lists provide the direct enforcement mechanism required for workspace-level network security.

It allows users to bypass multi-factor authentication.

Why it fails: PATs do not bypass MFA; they are a secondary authentication method for automated tools. The initial authentication required to generate a token usually happens through the workspace SSO, which should enforce MFA. Once the token exists, it bypasses the interactive login process, which is why it is sensitive.

It is intended for end-user dashboard access.

Why it fails: PATs are meant for programmatic API interactions, not for end-user dashboard access. Users should access dashboards through the Databricks UI using their standard SSO identities. Exposing PATs to users for casual browsing is a major security violation that could lead to unauthorized API misuse and data exfiltration.

It encrypts data stored in the workspace.

Why it fails: PATs have no role in data encryption. They are strictly authentication artifacts. Data encryption is handled by the cloud provider's KMS (Key Management Service) or Databricks-managed encryption at rest. Confusing authentication tokens with encryption mechanisms is a dangerous misunderstanding of the Databricks security architecture.

It shows which users accessed the data.

Why it fails: Data Lineage tracks the movement of data between tables and files, not the individual users who queried it. User access information is captured in audit logs. Confusing lineage (the path of data) with auditing (the history of access) is a common error in designing security and compliance monitoring systems.

It manually triggers an alert when PII is detected.

Why it fails: Lineage is a descriptive feature, not a detection or alerting feature. It shows you the history of data movement. While it can help an engineer manually identify the flow of sensitive information, it does not contain built-in logic to trigger alerts based on the detection of PII.

It permanently stores the raw data content.

Why it fails: Lineage metadata tracks relationships and transformations, but it does not store the underlying data records themselves. Storing raw data in metadata logs would be a major privacy risk and would violate most data retention and compliance policies, as metadata should be lightweight and focused on provenance.