Snowflake Exam ADA-C01 Topic 1 Question 19 Discussion

Actual exam question for Snowflake's ADA-C01 exam

Question #: 19
Topic #: 1

Which masking policy will mask a column whenever it is queried through a view owned by a role named MASKED_VIEW_ROLE?

Acreate or replace masking policy maskstring as (val string) returns string ->
case
when is_role_in_session ('MASKED_VIEW_ROLE') then ' **
else val
end;
*,

Bcreate or replace masking policy maskString as (val string) returns string ->
case
when array_contains ('MASKED_VIEW_ROLE' :: variant, parse_json (current_available_roles ())) then '*
else val
end;
** '

Ccreate or replace masking policy maskstring as (val string) returns string ->
case
when invoker_role() in ('MASKED_VIEW_ROLE') then
else val
end;
' **

Dcreate or replace masking policy maskString as (val string) returns string ->
case
when current_role() in ('MASKED_VIEW_ROLE') then ' ********* '
else val
end;

Show Suggested Answer

Suggested Answer: A

A masking policy is a SQL expression that transforms the data in a column based on the role that queries the column1. The is_role_in_session function returns true if the specified role is in the current session2. Therefore, the masking policy in option A will mask the column data with asterisks whenever it is queried through a view owned by the MASKED_VIEW_ROLE3. The other options use different functions that do not check the ownership of the view, but rather the current role, the invoker role, or the available roles in the session45. These functions may not return the desired result if the role that owns the view is different from the role that queries the view.

by Ming at Jul 18, 2024, 04:11 PM

Limited Time Offer

25%

Off

Get Premium ADA-C01 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Quinn

4 months ago

Option D all the way! Masking with a bunch of asterisks is both functional and aesthetically pleasing.

upvoted 0 times

...

Merlyn

4 months ago

I think option A is the way to go. It's straightforward and does the job of masking the column for the 'MASKED_VIEW_ROLE'.

upvoted 0 times

Tracey

2 months ago

Option D might be a bit too complicated, I prefer the simplicity of option A for this scenario.

upvoted 0 times

...

Rochell

2 months ago

I think option B could also work, it checks if the role is in the current session before masking the column.

upvoted 0 times

...

Franklyn

3 months ago

I agree, option A seems like the most direct way to mask the column for the 'MASKED_VIEW_ROLE'.

upvoted 0 times

...

Precious

4 months ago

Hmm, option C seems a bit off. Checking the invoker_role() instead of the current_role() might not work as expected.

upvoted 0 times

...

Evangelina

4 months ago

I'll go with option B, it uses the array_contains function to check if the current session role is in the 'MASKED_VIEW_ROLE' list.

upvoted 0 times

Ronald

2 months ago

Let's go with option B then.

upvoted 0 times

...

Rueben

2 months ago

That's a good point, it ensures the column is masked when queried through the view owned by MASKED_VIEW_ROLE.

upvoted 0 times

...

Paris

2 months ago

Yeah, option B uses the array_contains function to check the current session role.

upvoted 0 times

...

Frank

3 months ago

I think option B is the best choice.

upvoted 0 times

...

Jenifer

4 months ago

Hmm, I see your point. I might reconsider my answer then.

upvoted 0 times

...

Gussie

4 months ago

I agree with Inocencia, option A seems like the most logical choice.

upvoted 0 times

...

Inocencia

4 months ago

But option A seems to be checking if the role is in session, which makes more sense to me.

upvoted 0 times

...

Micaela

4 months ago

Option D looks good, it checks if the current role is in the 'MASKED_VIEW_ROLE' and masks the value accordingly.

upvoted 0 times

Ling

4 months ago

D) create or replace masking policy maskString as (val string) returns string ->\ncase\nwhen current_role() in ('MASKED_VIEW_ROLE') then ' ********* '\nelse val\nend;

upvoted 0 times

...

Jenifer

5 months ago

I believe it's actually option B.

upvoted 0 times

...

Inocencia

5 months ago

I think the correct answer is A.

upvoted 0 times

...