CODEWITHSUNDEEP
sqlsql-serverrow-level-security
Kristopher Penland
Kristopher Penland

Reputation: 45

SQL Server row-level security without username in data

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

Upvotes: 0

Views: 595

Answers (1)

Kristopher Penland
Kristopher Penland

Reputation: 45

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.

Upvotes: 1

Related Questions