Databricks Data Engineer Professional Question 212
Single answerA data engineering team at your company is tasked with implementing row-level security on a Delta table containing sensitive customer data. They decide to use a dynamic view to enforce access control based on the user's department and role. The table customer_data has columns: customer_id, name, email, department, and sensitive_info. The dynamic view is created using the following query:
CREATE OR REPLACE VIEW customer_data_secure AS
SELECT *
FROM customer_data
WHERE department = CURRENT_USER() OR role = 'admin';
After creating the view, the team reports that users outside the admin role or their own department are still able to access the data. What is the MOST likely issue with the implementation?
- A
The
CURRENT_USER()function returns the user's email, which does not match the department column. - B
The
ORcondition in the WHERE clause allows users who are not admins to access data outside their department. - C
The view is not explicitly granted to users, so they fall back to the underlying table's permissions.
- D
Dynamic views cannot enforce both row-level and column-level security simultaneously.
Show answer and explanation
Correct answer: A
Explanation
The issue lies in the misuse of the CURRENT_USER() function. The CURRENT_USER() function returns the user's identity (e.g., email or username), but the department column in the customer_data table likely contains department names. As a result, the condition department = CURRENT_USER() will fail to filter rows correctly, leading to unintended access. To fix this, the view logic must correctly map the user's identity to their department.
- A. Correct.
The
CURRENT_USER()function returns the current user's identity, which is typically an email or username. If thedepartmentcolumn contains department names and not user identities, this condition will not work as intended. - B. Incorrect.
While the
ORcondition allows access to users with theadminrole, it does not explain why users outside their department are gaining access. The issue lies in the incorrect use ofCURRENT_USER(). - C. Incorrect.
This is unrelated to the issue at hand. The problem is about the logic within the view, not user permissions on the view.
- D. Incorrect.
Dynamic views in Databricks can enforce both row-level and column-level security if implemented correctly. This statement is incorrect.