What should you do?

HOTSPOT

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 that contains an external table named Sales. Sales contains sales data. Each row in Sales contains data on a single sale, including the name of the salesperson.

You need to implement row-level security (RLS). The solution must ensure that the salespeople can access only their respective sales.

What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

View Answer

Answer:

Explanation:

Box 1: A security policy for sale

Here are the steps to create a security policy for Sales:

✑ Create a user-defined function that returns the name of the current user:

✑ CREATE FUNCTION dbo.GetCurrentUser()

✑ RETURNS NVARCHAR(128)

✑ AS

✑ BEGIN

✑ RETURN SUSER_SNAME();

✑ END;

✑ Create a security predicate function that filters the Sales table based on the current user:

✑ CREATE FUNCTION dbo.SalesPredicate(@salesperson NVARCHAR(128))

✑ RETURNS TABLE

✑ WITH SCHEMABINDING

✑ AS

✑ RETURN SELECT 1 AS access_result

✑ WHERE @salesperson = SalespersonName;

✑ Create a security policy on the Sales table that uses the SalesPredicate function to filter the data:

✑ CREATE SECURITY POLICY SalesFilter

✑ ADD FILTER PREDICATE dbo.SalesPredicate(dbo.GetCurrentUser()) ON dbo.Sales

✑ WITH (STATE = ON);

By creating a security policy for the Sales table, you ensure that each salesperson can only access their own sales data. The security policy uses a user-defined function to get the name of the current user and a security predicate function to filter the Sales table based on the current user.

Box 2: table-value function

to restrict row access by using row-level security, you need to create a table-valued function that returns a table of values that represent the rows that a user can access. You then use this function in a security policy that applies a predicate on the table.