Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 3 question 34 discussion

Actual exam question from Microsoft's DP-203

Question #: 34
Topic #: 3

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 that contains a table named Sales.

Sales has row-level security (RLS) applied. RLS uses the following predicate filter.

A user named SalesUser1 is assigned the db_datareader role for Pool1.

Which rows in the Sales table are returned when SalesUser1 queries the table?

A. only the rows for which the value in the User_Name column is SalesUser1
B. all the rows
C. only the rows for which the value in the SalesRep column is Manager
D. only the rows for which the value in the SalesRep column is SalesUser1

Show Suggested Answer

Suggested Answer: D 🗳️

by AHUI at April 5, 2023, 12:23 a.m.

Comments

Submit Cancel

mamahani

Highly Voted 1 year, 8 months ago

Selected Answer: D

here is the same example directly from microsoft docs: |https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16#Typical its definitely D

upvoted 7 times

...

Gikan

Most Recent 11 months, 1 week ago

I am really confused. The function compares the input parameter to logged in user. No filtering in rows in where cause. So if the function called with a fake user it returns nothing. If parameter is the same az logged in user returns all rows. If the parameter is Manager, then returns all rows.

upvoted 3 times

...

kkk5566

1 year, 4 months ago

Selected Answer: D

D is correct , see link https://learn.microsoft.com/en-us/training/modules/secure-data-warehouse-azure-synapse-analytics/6-exercise-manage-authorization-through-column-row-level-security

upvoted 1 times

...

kkk5566

1 year, 4 months ago

https://learn.microsoft.com/en-us/training/modules/secure-data-warehouse-azure-synapse-analytics/6-exercise-manage-authorization-through-column-row-level-security

upvoted 2 times

...

AHUI

1 year, 9 months ago

Ans is C. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager'). ref: https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

upvoted 2 times

zekescookies

1 year, 8 months ago

It's D. It clearly states that the user querying the table is SalesUser1. I feel they should have mentioned it being a manager if it's C.

upvoted 8 times

...

shakes103

1 year, 9 months ago

I have looked it up too. Answer is C

upvoted 2 times

Mal2002

1 year, 5 months ago

If you really looked up then what did you understand from this? EXECUTE AS USER = 'SalesRep1'; SELECT * FROM Sales.Orders; REVERT; EXECUTE AS USER = 'SalesRep2'; SELECT * FROM Sales.Orders; REVERT; EXECUTE AS USER = 'Manager'; SELECT * FROM Sales.Orders; REVERT; The manager should see all six rows. The Sales1 and Sales2 users should only see their own sales. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16#Typical It's clearly D

upvoted 1 times

...

aemilka

1 year, 8 months ago

In the "Scenario for users who authenticate to the database" there is the same code snippet and it's clearly stated that after applying security policy adding the function as a filter predicate "the manager should see all rows. The Sales1 and Sales2 users should only see their own sales." So the answer is D.

upvoted 6 times

...