Governance and Security practice questions

Table ACLs configured via legacy GRANT commands on Hive metastore tables.

Why it fails: Legacy table ACLs only provide all-or-nothing table-level permissions and do not support dynamic column-level data masking. Relying on legacy Hive metastore ACLs fails modern compliance mandates and lacks integration with Unity Catalog security frameworks.

Dynamic view creation combined with SQL conditional functions like CASE WHEN.

Why it fails: While dynamic views can restrict columns, creating separate views for every masked scenario introduces maintenance overhead and breaks direct table queries. Unity Catalog native column masking allows applying masks directly to base tables without requiring view abstraction.

Cluster-level Spark configuration properties that filter DataFrame schema definitions at runtime.

Why it fails: Spark configuration properties can be easily bypassed by users spinning up custom compute clusters or executing direct SQL queries. Security enforcement must reside in the centralized metadata governance layer rather than transient cluster configurations.

Managed tables do not support Delta Lake ACID transaction guarantees, unlike external tables.

Why it fails: Both managed and external tables in Unity Catalog fully leverage Delta Lake protocols, providing complete ACID transaction guarantees, time travel, and optimized file management regardless of where the underlying files reside.

Dropping a Unity Catalog external table removes the underlying data files from cloud storage immediately.

Why it fails: Dropping an external table only removes the table metadata from the Unity Catalog metastore. The underlying data files stored in the external cloud location remain untouched, preventing accidental data loss of shared storage assets.

External tables can only be queried by users who possess metastore admin privileges across all workspaces.

Why it fails: External tables are governed by standard Unity Catalog access controls like any other table. Users with appropriate GRANT permissions (such as SELECT) can query external tables without requiring high-level metastore administrator rights.

Table Access Control Lists (ACLs)

Why it fails: Table ACLs are a legacy security feature primarily used in non-Unity Catalog workspaces. They lack the fine-grained, dynamic masking capabilities required for conditional column-level security. Unity Catalog uses modern security primitives like views and functions rather than legacy ACLs to manage data access policies across the platform.

Unity Catalog Dynamic Views

Why it fails: Dynamic views leverage SQL functions such as is_account_group_member() to return masked or unmasked data based on the caller's identity. This approach provides the flexibility to hide PII columns from unauthorized users while keeping the raw data intact for authorized HR personnel in a centralized, governed environment.

Row-level filtering only

Why it fails: Row-level filtering restricts the number of records a user can see but does not address column-level masking requirements. If the PII resides in specific columns, row-level filters would fail to protect the sensitive data, whereas a dynamic view can handle both filtering and masking simultaneously.

Credential passthrough

Why it fails: Credential passthrough is an older authentication mechanism that allows users to use their own Microsoft Entra ID or IAM identities to access storage directly. It does not provide the capability to apply complex masking logic or granular field-level transformations that are necessary to satisfy regulatory data privacy requirements.

PATs are recommended for production job automation.

Why it fails: PATs are tied to a specific user's identity and permissions. If the user leaves the organization or the token expires, the production job will fail. This creates operational fragility and security risks. Service principals are the preferred mechanism for production automation as they are independent of individual employee accounts.

PATs have higher security than service principals.

Why it fails: PATs generally have lower security standards because they are tied to user identities that often possess more permissions than the job actually requires. Service principals allow for the principle of least privilege, as they can be assigned only the specific permissions needed for a particular job or automated task.

PATs are easier to audit than service principals.

Why it fails: PATs are inherently easier to audit because they are mapped directly to a single human user. When a PAT is used, the audit logs show exactly which user generated it, which can be beneficial for specific compliance scenarios. However, this ease of audit does not outweigh the operational fragility of PATs.

Enable external locations.

Why it fails: Enabling external locations allows for the registration of data in Unity Catalog, but it does not prevent users from accessing legacy tables in the Hive Metastore. It is a functional component of Unity Catalog, not a governance setting for enforcing a strict, system-wide access policy.

Use the 'Account Admin' role.

Why it fails: The Account Admin role manages the account and users, but it does not govern the access policies to data tables directly. You can be an account admin without having any access to the data, and this role alone does not enforce a 'deny-by-default' policy for the entire platform.

Define a default catalog.

Why it fails: Defining a default catalog is a convenience feature that simplifies query syntax by allowing users to skip specifying the catalog name. It has absolutely nothing to do with security policy or enforcing access controls; it is purely an ease-of-use configuration that does not influence access restrictions or data governance.

SELECT access is denied.

Why it fails: The final command in the sequence is a GRANT. Therefore, the effective permission must be SELECT access. The prior REVOKE command is overridden by the subsequent GRANT statement. This is consistent with how standard SQL access control lists work in most relational database management systems and specifically within Unity Catalog.

The permission is in an inconsistent state.

Why it fails: There is no inconsistent state; permission systems evaluate the final, current state of the ACL. The series of commands clearly defines a sequence that results in a GRANT. The system does not maintain a history of conflicting states; it simply applies the latest defined privilege for the specific user or group.

The grant fails due to a conflict.

Why it fails: There is no conflict that prevents a GRANT after a REVOKE. These operations are valid SQL commands, and the system executes them sequentially as requested. The final state of the ACL is what dictates the actual access control, and the previous commands are simply steps leading to that final, effective state.

Access rights at the table level can be inherited by the parent schema.

Why it fails: Inheritance in Unity Catalog is strictly top-down, not bottom-up. Permissions assigned to a table do not propagate upward to the parent schema or catalog. This unidirectional flow is intentional to prevent accidental escalation of privileges and to maintain a clear, predictable security model for data assets.

Users must have the OWNERSHIP privilege on a catalog to create new tables within it.

Why it fails: While ownership provides full control, it is not a requirement for creating tables. A user only needs the USE CATALOG, USE SCHEMA, and CREATE TABLE privileges to perform this task. Requiring ownership for routine tasks would violate the principle of least privilege and complicate standard data engineering workflows.

Privileges are limited to Databricks SQL and cannot be enforced in notebooks.

Why it fails: Unity Catalog privileges are enforced consistently across the entire Databricks platform, including SQL Warehouses, notebooks, jobs, and Delta Live Tables. This unified security layer ensures that data access is governed identically regardless of the compute engine or interface used to access the underlying data assets in storage.

The users lack the SELECT privilege on the 'main' catalog.

Why it fails: A SELECT privilege is not required at the catalog level. Catalog-level access is managed through the USE CATALOG privilege, which is already present in the commands. Adding SELECT at the catalog level would violate the principle of least privilege, as it would grant broad access to all schemas.

The users require the 'BROWSE' privilege on the catalog.

Why it fails: The BROWSE privilege allows users to see metadata objects in the catalog explorer, but it is not a prerequisite for executing queries on a table. Queries rely on the specific USE CATALOG, USE SCHEMA, and SELECT privileges, which are the fundamental components required for successful data retrieval.

The table 'sales_data' must have its owner changed to the 'data-analyst-group'.

Why it fails: Ownership is not required to query data. Ownership is specifically reserved for managing privileges, modifying table structures, or dropping objects. Standard users or analyst groups only require specific data access privileges, such as SELECT, to read the content of the table, regardless of who owns it.

The group needs the BIND_EXTERNAL_DIRECTORIES privilege on the catalog.

Why it fails: The BIND_EXTERNAL_DIRECTORIES privilege is used for specific governance actions related to external file access and location bindings, not for standard querying of managed or external Delta tables stored in Unity Catalog.

The group needs the EXECUTE privilege on the parent schema to run SQL queries.

Why it fails: The EXECUTE privilege is generally associated with running user-defined functions or models rather than executing standard tabular queries. Standard SELECT operations depend purely on catalog, schema, and table-level access permissions.

The SQL commands are correct, but the user group may not have been assigned the appropriate workspace entitlement to access the workspace.

Why it fails: While catalog, schema, and table privileges are correctly granted, users must also be assigned workspace-level entitlements to log in and use SQL warehouses or clusters. Without workspace access entitlements, users cannot submit queries regardless of UC grants.

GRANT SELECT ON SCHEMA my_schema TO user@example.com

Why it fails: Granting SELECT on the schema level provides read access to all tables currently in the schema and potentially future ones. This violates the principle of least privilege, as the requirement specifically asks for access to a single table, not the entire schema contents and its metadata.

GRANT ALL PRIVILEGES ON TABLE my_table TO user@example.com

Why it fails: Granting ALL PRIVILEGES is dangerous and violates security best practices. It allows the user to perform administrative actions on the table, including dropping it or altering its ownership. The requirement only specifies the ability to query the table, which is satisfied solely by the SELECT privilege.

GRANT USAGE ON TABLE my_table TO user@example.com

Why it fails: The USAGE privilege in Unity Catalog is generally applied to catalogs and schemas to allow users to traverse the object hierarchy. It does not grant the ability to read or query the data within a table. To access the data, the SELECT privilege is explicitly required.

Unity Catalog privileges are subtractive by default.

Why it fails: Unity Catalog privileges are additive, not subtractive. You start with no access to an object and explicitly grant the required permissions. There is no concept of a 'deny' rule that overrides an 'allow' rule, which is a common misconception derived from other cloud IAM provider models.

Permissions granted at the table level can be overridden by schema-level DENY rules.

Why it fails: Unity Catalog does not support explicit DENY rules. Because privileges are strictly additive, it is impossible to override a grant with a deny. If a user is granted access, they have that access unless the permission is explicitly revoked by an administrator using the REVOKE command.

GRANT SELECT ON CATALOG finance TO user@example.com

Why it fails: Granting SELECT on the catalog is too permissive. It would grant the user read access to every table within the entire catalog. The requirement is to grant only the minimum permission necessary to resolve the error, which is the USE CATALOG privilege, not full read access.

GRANT USAGE ON CATALOG finance TO user@example.com

Why it fails: There is no 'USAGE' privilege for catalogs in Unity Catalog. The correct terminology and permission is 'USE CATALOG'. Using incorrect syntax will result in a command failure, failing to resolve the access error experienced by the user while trying to query their target data.

GRANT OWNER TO user@example.com ON CATALOG finance

Why it fails: Granting ownership of the catalog gives the user full control, including the ability to drop the catalog or change permissions for others. This violates the principle of least privilege, as the user only needs access to perform queries, not full administrative control over the entire finance catalog.

GRANT ALL PRIVILEGES ON SCHEMA finance_analytics TO `data-analysts`;

Why it fails: Granting ALL PRIVILEGES provides excessive administrative and destructive capabilities, such as the ability to drop tables or modify schema definitions, which violates the principle of least privilege for read-only analysts.

GRANT USAGE ON SCHEMA finance_analytics TO `data-analysts`;

Why it fails: While USAGE is used for catalogs and shares, granting USAGE alone on a schema does not grant permission to read the data within the tables contained inside that specific schema without explicit table privileges.

GRANT SELECT ON SCHEMA finance_analytics TO `data-analysts`;

Why it fails: Syntax for granting SELECT on an entire schema is invalid in Unity Catalog; privileges must be granted explicitly on tables and views, or inherited through broader namespace privileges like SELECT on all tables.

GRANT SELECT ON CATALOG sales TO analysts;

Why it fails: Granting SELECT on the catalog 'sales' would allow the group to read all tables in all schemas within that catalog, which is broader than required. The scenario specifies access only to the 'sales' schema, not the entire catalog. This option over-privileges the group and violates the principle of least privilege, potentially exposing data in other schemas.

GRANT USAGE ON SCHEMA sales TO analysts;

Why it fails: USAGE on a schema only allows the grantee to access the schema and its objects if they have additional privileges. It does not grant the ability to read table data. Without SELECT on the tables or schema, the group cannot query the tables. This option provides insufficient privileges for reading data, so it fails to meet the requirement.

GRANT SELECT ON TABLE sales.* TO analysts;

Why it fails: Unity Catalog does not support wildcard syntax like 'sales.*' in GRANT statements. To grant on all tables in a schema, you must grant at the schema level. This option uses invalid syntax and would result in an error. Even if it were valid, it might not cover future tables unless explicitly designed to do so, but the syntax itself is incorrect.

GRANT SELECT ON SCHEMA prod.hr TO `data_analysts`;

Why it fails: Granting SELECT on the `hr` schema would explicitly allow the data analysts to read all tables in `hr`, which directly violates the requirement that they must not be able to see or query tables in `hr`. This privilege would over-privilege the group and is the opposite of what is needed. Therefore, it should not be granted.

GRANT USE SCHEMA ON SCHEMA prod.hr TO `data_analysts`;

Why it fails: USE SCHEMA on `hr` would allow the data analysts to see the schema and its objects, and potentially query them if they have SELECT. Even without SELECT, they could see the schema exists, which might violate the requirement to not see tables in `hr`. To strictly prevent any visibility, you should not grant any privileges on `hr`. Thus, this is incorrect.

GRANT SELECT ON CATALOG prod TO `data_analysts`;

Why it fails: Granting SELECT on the entire catalog would give the data analysts read access to all schemas and tables in `prod`, including `hr`. This violates the requirement to restrict access to only the `finance` schema. Such a broad grant is unnecessary and insecure for this scenario, so it should not be used.

The service principal lacks `USE CATALOG` and `USE SCHEMA` privileges on the catalog and schema containing the external location.

Why it fails: `USE CATALOG` and `USE SCHEMA` govern access to securable objects within a catalog and schema, but an external location is a metastore-level object. Reading files directly from an external location path does not require catalog or schema usage privileges, so this is not the cause.

The service principal lacks `USE STORAGE CREDENTIAL` privilege on `s3_cred`.

Why it fails: The scenario states the storage credential was already granted to `etl_sp`. Even if `USE STORAGE CREDENTIAL` were missing, the error would typically surface differently; here the credential is already granted, so this is not the cause of the read failure.

The service principal lacks `CREATE EXTERNAL LOCATION` privilege on the metastore.

Why it fails: `CREATE EXTERNAL LOCATION` is required to create the external location object, not to read from it. The external location already exists, and the service principal only needs `READ FILES` and access to the underlying storage credential. This privilege would not affect read access at query time.

Create a separate table for analysts with masked emails and another for auditors with full emails, then use table aliases to redirect queries based on group.

Why it fails: Duplicating tables introduces data redundancy and maintenance overhead, and table aliases do not automatically redirect based on group in Unity Catalog. This approach also risks inconsistency and does not leverage Unity Catalog's native governance features. A column mask is simpler and ensures consistent enforcement across all consumers.

Create a dynamic view on top of sales.orders that applies a CASE expression based on is_account_group_member(), and grant SELECT on the view to 'analysts' while revoking direct table access.

Why it fails: A dynamic view can mask data, but it requires revoking direct table access and granting on the view, adding administrative overhead. It also does not automatically apply to all query engines if users can still query the base table. Unity Catalog column masks are the native, centralized mechanism designed for this exact scenario, applying consistently without view proliferation.

Use row-level security with a filter that returns different rows for each group, effectively hiding the email column for 'analysts'.

Why it fails: Row-level security filters rows, not column values. It cannot partially mask a column value while preserving the row. Applying a row filter would either hide entire rows or show them unchanged, which does not meet the requirement to mask the email address for analysts while keeping the rest of the row visible.

MODIFY

Why it fails: The `MODIFY` privilege allows users to insert, update, and delete data in the table. The scenario explicitly states that auditors should not modify data. Granting `MODIFY` would violate the requirement. Therefore, this privilege is not appropriate.

USAGE

Why it fails: The `USAGE` privilege is typically granted on schemas or catalogs to allow users to access objects within them. It does not apply directly to tables. While `USAGE` on the schema is necessary to access the table, the question asks for privileges on the table itself. The requirement is to grant privileges on the table to auditors, so `USAGE` is not the correct choice here.

ALL PRIVILEGES

Why it fails: `ALL PRIVILEGES` includes `MODIFY`, `CREATE`, and other privileges that would allow the auditor to alter the table or its data. This exceeds the requirement, which is to allow read-only access and the ability to grant `SELECT`. Granting all privileges would violate the principle of least privilege.

The external location was created without specifying the storage credential, so Unity Catalog is using a default credential that lacks permissions.

Why it fails: If the external location was created without a storage credential, it would fail at creation or when accessed. However, the scenario states the external location was created using the storage credential. The error is more likely due to a trust relationship issue between the IAM role and Databricks, which is a common misconfiguration. The storage credential itself must have a trust policy that allows Databricks to assume the role.

The storage credential must be granted USAGE on the external location, but it was not, so access is denied.

Why it fails: USAGE on an external location is granted to users or groups, not to the storage credential itself. The storage credential is referenced by the external location and is assumed by Unity Catalog. The absence of USAGE grants would affect users, but the error occurs even for users who likely have USAGE. The root cause is the trust relationship.

The S3 bucket policy must explicitly allow the Databricks workspace IAM role, not the storage credential IAM role.

Why it fails: The bucket policy should allow the IAM role specified in the storage credential, as that is the identity Databricks assumes to access S3. The workspace IAM role is not used for Unity Catalog external locations. Therefore, this is not the cause; the bucket policy is correctly configured if it allows the storage credential's IAM role.

Create a single global Unity Catalog metastore and attach it to all workspaces regardless of region.

Why it fails: Unity Catalog metastores are not global; they are regional. A single metastore cannot be attached to workspaces in different regions due to latency and data sovereignty constraints. Attempting to do so is not supported. The correct design is one metastore per region, with each workspace assigned to the metastore in its region.

Create a separate Unity Catalog metastore for each workspace and replicate the data and policies manually.

Why it fails: Creating separate metastores per workspace defeats the purpose of centralized governance. It requires manual replication of data and policies, leading to inconsistency and increased administrative overhead. Unity Catalog is designed to have a single metastore per region that can be shared across multiple workspaces, providing a unified governance layer.

Use a single workspace as the metastore host and have other workspaces connect to it via a remote connection.

Why it fails: Unity Catalog does not support remote connections to a metastore hosted in another region. Metastores are tightly coupled to the region where they are created. Workspaces must be in the same region as the metastore they are assigned to. This option is not a valid configuration and would not meet the requirement for multi-region access.