Answer-first summary for fast verification
Answer: ```sql CREATE OR REPLACE VIEW policy_view AS SELECT CASE WHEN is_member('compliance') THEN name ELSE '' END AS name, policy_id, age FROM policy; ```
The correct answer uses the `is_member('compliance')` function within a `CASE` statement to check if the current user is a member of the 'compliance' group. If true, the `name` column is displayed; otherwise, an empty string is returned, effectively restricting access. This approach correctly implements dynamic access control with valid syntax. - **Option B** incorrectly uses `member = 'compliance'`, which is invalid syntax since `member` is a function, not a variable. - **Option C** attempts an invalid syntax `name(IF 'compliance' in member)`, which Databricks SQL does not support. - **Option D** uses an incorrect syntax `CREATE VIEW policy_view("name": "compliance") AS`, which is not a valid method for specifying column-level access control in Databricks.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
A data engineer is tasked with restricting access to the name column in a dynamic view definition to only the compliance team. The original view is defined as:
CREATE VIEW policy_view AS
SELECT name, policy_id, age
FROM policy
CREATE VIEW policy_view AS
SELECT name, policy_id, age
FROM policy
Which SQL query correctly implements this access restriction?
A
CREATE OR REPLACE VIEW policy_view AS
SELECT
CASE
WHEN is_member('compliance') THEN name
ELSE ''
END AS name,
policy_id,
age
FROM policy;
CREATE OR REPLACE VIEW policy_view AS
SELECT
CASE
WHEN is_member('compliance') THEN name
ELSE ''
END AS name,
policy_id,
age
FROM policy;
B
CREATE VIEW policy_view AS
SELECT
CASE WHEN
member = 'compliance' THEN name
ELSE ''
END AS name,
policy_id,
age
FROM policy
CREATE VIEW policy_view AS
SELECT
CASE WHEN
member = 'compliance' THEN name
ELSE ''
END AS name,
policy_id,
age
FROM policy
C
CREATE VIEW policy_view AS
SELECT name(IF 'compliance' in member), policy_id, age
FROM policy
CREATE VIEW policy_view AS
SELECT name(IF 'compliance' in member), policy_id, age
FROM policy
D
CREATE VIEW policy_view("name": "compliance") AS
SELECT name, policy_id, age
FROM policy
CREATE VIEW policy_view("name": "compliance") AS
SELECT name, policy_id, age
FROM policy