DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Create a database role, add users to it, and use a security policy with an inline table-valued function that filters rows based on the user's region.

  Why this is correct

  This is the correct approach for implementing row-level security in Azure Synapse Analytics dedicated SQL pool. You create an inline table-valued function that returns 1 when a user's region matches the row's region, then bind it to a security policy on the target table. Users are assigned to database roles, and the predicate is applied automatically to all queries, regardless of the client tool, ensuring consistent enforcement.

• ✗

  Create a view that filters rows based on the CURRENT_USER function, and grant users SELECT permission only on the view.

  Why it's wrong here

  While a view can provide row filtering, it does not enforce security at the engine level. Users with direct table access could bypass the view and query the base table. Additionally, views must be maintained for each user or role, and they do not automatically apply to ad-hoc queries. This approach lacks the robust, centralized enforcement that row-level security provides.

• ✗

  Use dynamic data masking on the region column with a masking rule that shows only the user's region and masks others.

  Why it's wrong here

  Dynamic data masking is designed to obfuscate sensitive data in query results but does not filter rows. It can mask the region column for unauthorized users, but all rows would still be returned, potentially exposing other data. Masking does not enforce row-level access control and can be bypassed by users with UNMASK permission, so it does not satisfy the security requirement.

• ✗

  Implement column-level encryption on the region column and provide each sales representative with the encryption key for their region.

  Why it's wrong here

  Column-level encryption protects data at rest but does not filter rows at query time. Users would still see all rows, though the region column would be encrypted. Managing separate keys per region adds complexity and does not prevent access to other regions' data if the key is compromised. This does not meet the requirement of row-level filtering based on user identity.

Go deeper

Related to this question