Answer-first summary for fast verification
Answer: only the rows for which the value in the SalesRep column is SalesUser1
## Row-Level Security Analysis ### Understanding the RLS Predicate Function The provided row-level security (RLS) predicate function works as follows: ```sql CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; ``` This function returns rows where either: 1. The `SalesRep` column value equals the current user's name (`USER_NAME()`) 2. The current user is named 'Manager' ### Applying to SalesUser1 When SalesUser1 queries the table: - `USER_NAME()` returns 'SalesUser1' - The predicate evaluates: `@SalesRep = 'SalesUser1' OR 'SalesUser1' = 'Manager'` - Since 'SalesUser1' ≠ 'Manager', only the first condition applies - Therefore, only rows where `SalesRep = 'SalesUser1'` are returned ### Why Option D is Correct **Option D** is correct because the RLS filter predicate specifically allows users to see only rows where they are listed as the SalesRep, unless they are the Manager user who can see all rows. ### Analysis of Other Options - **Option A**: Incorrect - The filter applies to the SalesRep column, not User_Name column - **Option B**: Incorrect - SalesUser1 is not the Manager, so they don't get access to all rows - **Option C**: Incorrect - This would only apply if SalesUser1 were the Manager user ### Best Practice Context This implementation follows the standard RLS pattern in Azure Synapse Analytics where users can only access data they "own" (where they are the assigned representative), while managers have broader access. This maintains data isolation while allowing necessary administrative oversight.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You have an Azure Synapse Analytics dedicated SQL pool named Pool1 containing a table named Sales. Row-level security (RLS) is applied to the Sales table with the following predicate filter:
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
A user named SalesUser1 is assigned the db_datareader role for Pool1.
Which rows from the Sales table will be returned when SalesUser1 executes a query against it?
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