SQL Server row-level security without username in data

Question

I am a student, and we have a portion of an assignment to develop a SQL script in SQL Server to enable row-level security which allows a certain user-profile/group (Sales1) to only see rows within a table (Sales.CreditCards), which exists in the Sales schema, where the credit card type column (CardType) value is 'Vista'.

Database: Microsoft AdventureWorks2017

It is a publicly available data source for education (5.5GB)

Task 6: Add a security policy that allows a sales1 user to only see Vista credit card rows.

EVERY example I can find online of implementing row-level security is built on the basis of tables that include the username or a privilege name within the table data itself. Like, show only columns to Tom belonging to Tom, where Tom's name is within a column of the dataset. I cannot find any examples where you apply a filter to data without the username in the dataset.

Here is the script which I have setup so far for this assignment, but I am receiving multiple errors. Please provide some guidance, or point me in the right direction.

UPDATE: I figured out how to get it to work! Issues resolved!

Once I got past the initial error codes and got the script working, the issue was that I was trying to pass the value 'Vista' into the script to be compared in the WHERE clause, from the Create Security Policy section at the end. However, this was backward logic.

Instead, the variable passed to the Function in the section "Create security Policy" should be the column header you want to filter on, not the expected value". The value could then be defined within the where clause, as shown below.

Errors

Script

USE AdventureWorks2017
GO

GRANT SELECT ON Sales.CreditCard TO Sales1;
GO

/* Setup Row-level security schema */
CREATE SCHEMA Vista;
GO

/*ATTEMPT 5: Create Row-Level security Function */
CREATE FUNCTION Vista.fn_SecurityPredicateEmployee
    (@UserID AS sysname, 
     @CardType AS varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Result
    FROM Sales.CreditCard AS c
    WHERE (@UserID = 'Sales1'
           AND 'Vista' = @CardType);
GO

/* ATTEMPT 5: Apply Security Policy */
CREATE SECURITY POLICY Vista.Sales1Filter
ADD FILTER PREDICATE Vista.fn_SecurityPredicateEmployee(USER_NAME(), CardType)  
ON [Sales].[CreditCard]
WITH (STATE = ON); 
GO

GRANT SELECT ON Vista.fn_SecurityPredicateEmployee TO Sales1;

Validation

USE AdventureWorks2017
GO

EXECUTE AS USER = 'Sales1';
SELECT * FROM [Sales].[CreditCard]

REVERT;
GO

That wouldn't be a userprofile. It would be a group, and you still have to have a way to identify each row that indicates what group/groups can access it. — Ken White
– Ken White, Commented Sep 30, 2022 at 1:08
@KenWhite. I have edited the script. It now runs successfully, the security rule is adopted... However, when I go to validate using the Sales1 user profile it returns no results, instead of the rows with 'CardType' 'Vista'. Any ideas? — Kristopher Penland
– Kristopher Penland, Commented Sep 30, 2022 at 2:02
No idea. I don't have access to your data or any sample data - how can I tell you why you're not getting the results you want? — Ken White
– Ken White, Commented Sep 30, 2022 at 2:20

Kristopher Penland · Accepted Answer · 2022-09-30 03:19:43Z

1

UPDATE: I figured out how to get it to work! Issues resolved!! Once I got past the initial error codes and got the script working, the issue was that I was trying to pass the value 'Vista' into the script to be compared in the WHERE clause, from the Create Security Policy section at the end. However, this was backward logic. Instead, the variable passed to the Function in the section "Create security Policy" should be the column header you want to filter on, not the expected value". The value could then be defined within the where clause, as shown below.

answered Sep 30, 2022 at 3:19

Kristopher Penland

459 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server row-level security without username in data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related