Databricks Data Engineer Professional Question 214
Single answerYou are tasked with creating a dynamic view in Databricks to restrict access to specific rows and columns in a table based on a user's role. The table contains customer data with the columns customer_id, email, phone_number, and region. The goal is to allow users with the role sales_rep to view only the customer_id and region columns for customers in their assigned region, while users with the role manager can view all columns but only for customers in the US region. Which of the following SQL statements correctly implements this dynamic view?
- A
CREATE OR REPLACE VIEW customer_view AS SELECT CASE WHEN current_user_role() = 'sales_rep' THEN customer_id, region ELSE email, phone_number, region END FROM customer_data WHERE region = 'US';
- B
CREATE OR REPLACE VIEW customer_view AS SELECT customer_id, region FROM customer_data WHERE current_user_role() = 'sales_rep' AND region IN (SELECT assigned_region FROM user_roles WHERE user = current_user()) UNION SELECT * FROM customer_data WHERE current_user_role() = 'manager' AND region = 'US';
- C
CREATE OR REPLACE VIEW customer_view AS SELECT CASE WHEN current_user_role() = 'sales_rep' THEN customer_id, region ELSE * END FROM customer_data WHERE (current_user_role() = 'sales_rep' AND region = (SELECT assigned_region FROM user_roles WHERE user = current_user())) OR (current_user_role() = 'manager' AND region = 'US');
- D
CREATE OR REPLACE VIEW customer_view AS SELECT customer_id, region FROM customer_data WHERE current_user_role() = 'sales_rep' AND region = (SELECT assigned_region FROM user_roles WHERE user = current_user()) UNION SELECT customer_id, email, phone_number, region FROM customer_data WHERE current_user_role() = 'manager' AND region = 'US';
Show answer and explanation
Correct answer: D
Explanation
Dynamic views in Databricks are used to enforce fine-grained access control by dynamically filtering rows and columns based on runtime conditions such as user roles. The correct option uses UNION to enforce role-based access, ensuring that the sales_rep role only sees the customer_id and region columns for their assigned region and the manager role can view all columns but only for customers in the US region. The other options either misuse SQL constructs or fail to correctly implement the required restrictions.
- A. Incorrect.
This option incorrectly uses CASE statements to determine column visibility, which is not valid for dynamic views in Databricks.
- B. Incorrect.
This option combines UNION logic but does not correctly restrict columns for the
managerrole or filter based on the user's assigned region for thesales_reprole. - C. Incorrect.
This option attempts to use a CASE statement to dynamically choose columns, which is not valid. Additionally, the WHERE clause is overly complex and does not correctly enforce the restrictions for both roles.
- D. Correct.
This option correctly implements a dynamic view by using UNION to handle role-specific access: it restricts
sales_repusers to thecustomer_idandregioncolumns for their assigned regions and allowsmanagerusers to access all columns but only forUSregion customers.