SnowPro Advanced: Architect — Question 154

An Architect is working with a healthcare company’s Enterprise data governance team to review how company-sensitive data is protected within Snowflake. Physicians in the company network can run queries against views. There are two views, one shows mental health information, and the other shows physical health information:

create view mental_health_view as select * from patients where category = 'MentalHealth';

create view physical_health_view as select * from patients where category = 'PhysicalHealth';

Most physicians do not have direct access to the table. Instead, they are assigned one of two roles:

1. MentalHealth, which has privileges to read from mental_health_view. or
2. PhysicalHealth, which has privileges to read from physical_health_view.

A physician with the PhysicalHealth role wants to know whether there are any mental health patients in the table, and used the following query:

select * from physical_health_view where 1/iff(category = 'MentalHealth', 0, 1) = 1;

How will this query affect the sensitive data?

Answer options

• A. Snowflake will ensure that for any scenario, the underlying patient sensitive information will not be revealed directly or indirectly in any manner.
• B. If the physician is also assigned SECURITYADMIN or ACCOUNTADMIN roles, they will be able to view the mental health data, despite not being named explicitly in that role.
• C. If the physician is also assigned the SYSADMIN role and SYSADMIN is the owner of the patient table, the physician will be able to view the mental health data.
• D. It depends on what choices the query optimizer has made, and how the views are written. The physician with physical_health_view privileges will not see any rows for patients with mental health issues, but can deduce that there is at least one patient in the mental health category.

Correct answer: D

Explanation

The correct answer is D because the query is structured in a way that does not provide access to the mental health data while also allowing the physician to infer its existence. Options A, B, and C are incorrect as they suggest potential access to sensitive information that is not available to the physician with only the PhysicalHealth role.