Databricks Data Engineer Professional Question 213
Select 3A company wants to control access to a Delta table containing employee records based on both user roles and the departments they belong to. Using dynamic views, you need to implement row-level restrictions so that users with the role 'HR_Manager' can see all rows, while users with the role 'Employee' can only see rows where the column department matches their assigned department. Which of the following steps are required to achieve this?
- A
Create a dynamic SQL view that filters rows based on the current user's role and department using the
is_memberfunction and thecurrent_user()function. - B
Assign the created dynamic SQL view to a Databricks table ACL to enforce access control.
- C
Store the user's department and role information in a separate table and join it with the base Delta table within the dynamic view.
- D
Enable Delta Sharing to distribute the filtered data to users based on their roles and departments.
- E
Grant SELECT privileges on the dynamic view to the required user groups using the GRANT statement.
Show answer and explanation
Correct answers: A, C, E
Explanation
To implement row-level restrictions using dynamic views, you must define a view that filters rows based on the user's role and department. This requires using the user's role and department information, typically stored in a separate table, and leveraging functions like is_member and current_user(). Granting SELECT privileges on the view ensures secure access, while table ACLs or Delta Sharing are not relevant for this specific scenario.
- A. Correct.
Correct: Creating a dynamic SQL view is essential to enforce row-level restrictions based on the user's role and department. The
is_memberfunction can check role membership, whilecurrent_user()can identify the current user. - B. Incorrect.
Incorrect: While table ACLs can be used for access control, the dynamic view already enforces row-level restrictions. Additional ACLs at the table level are unnecessary in this scenario.
- C. Correct.
Correct: To filter rows dynamically, you need to map user information (like department and role) stored in a separate table and join it with the Delta table in the dynamic view definition.
- D. Incorrect.
Incorrect: Delta Sharing is used to share datasets between organizations, not for enforcing row-level or column-level security within the same organization.
- E. Correct.
Correct: Granting SELECT privileges on the dynamic view ensures that only authorized users can query the view and access filtered rows.