Databricks Data Engineer Professional Question 215
Select 2A company wants to restrict access to sensitive columns and rows in a Delta table containing employee data. They decide to use dynamic views in Databricks. The table employee_data has the following columns: employee_id, name, department, salary, and region. Only users in the HR department should see the salary column, and users should only see rows where their region matches the region assigned to them in the company's user metadata. As a data engineer, how would you implement this using dynamic views?
- A
Create a dynamic view with a CASE statement to mask the
salarycolumn for non-HR users and filter rows based on theregioncolumn. - B
Create a dynamic view with a WHERE clause that checks the user's
regionand uses a conditional expression to hide thesalarycolumn for non-HR users. - C
Use a GRANT statement to provide access to the
salarycolumn only to HR users and filter rows based on theregioncolumn in the Delta table. - D
Use the
is_memberfunction in the dynamic view to check if the user belongs to the HR group and filter rows based on theregioncolumn. - E
Create a static view with filters for
regionandsalaryand grant access to the view only to HR users.
Show answer and explanation
Correct answers: A, D
Explanation
Dynamic views in Databricks allow for fine-grained access control by dynamically filtering rows and masking columns based on the user's context (e.g., group membership or metadata like region). The correct approach involves creating a dynamic view that uses the is_member function to check if the user belongs to the HR group, conditionally displays the salary column, and filters rows based on the region column. Static views and GRANT statements cannot provide the required dynamic behavior, and WHERE clauses alone cannot conditionally mask columns.
- A. Correct.
Correct: Using a CASE statement in the SELECT clause allows you to mask sensitive columns like
salaryfor unauthorized users, and using a filter in the WHERE clause can restrict rows based on theregioncolumn. - B. Incorrect.
Incorrect: Although a WHERE clause can filter rows based on
region, it cannot be used to conditionally hide or mask columns likesalary. - C. Incorrect.
Incorrect: GRANT statements control access at the table or view level, not at the row or column level, which is required for this use case.
- D. Correct.
Correct: The
is_memberfunction can check group membership (e.g., HR users), and it can be combined with filtering logic to enforce both row-level and column-level access control in the dynamic view. - E. Incorrect.
Incorrect: Static views do not allow for dynamic filtering based on the user context (e.g., their region or group membership). This approach would not meet the requirements.