How create table inline function for security policy row filter depending on values in configuration-CodePudding

I would like to create a row level security policy.

My input is a user_id for users who connect to the database through a middle-tier application.

I would like to:

Query a configuration table (let's call it conf_table) to get the department name of user_id
Depending on value department, I want to filter on another table called customers on type_of_customers.

Example:

conf_table:

user_id	department
toto	sidney

Customers:

customer_no	typ_customer
0001	A
0002	B

Function:

IF conf_table.user_id = 'toto' AND conf_table.department = 'sidney'`
    SELECT * 
    FROM customers 
    WHERE typ_customer = A`

ELSE 
    SELECT * 
    FROM customers 
    WHERE typ_customer = B`

Many thanks in advance for your help!

CodePudding user response：

The simplest way is to do this :

DECLARE @type VARCHAR(1) = 'B'

IF EXISTS(SELECT * FROM conf_table WHERE user_id = 'toto' AND department = 'sidney')
    SET @type = 'A'
    
SELECT * FROM customers WHERE typ_customer = @type

CodePudding user response：

Ideally, each row in conf_table would have a typ_customer associated with it, alternatively you would join to a Departments table to get that value.

But without that, you can just use a CASE expression.

Note the usage of a function parameter to be able to pass in the typ_customer value from the Customers table that is being filtered.

CREATE OR ALTER FUNCTION Security.YourPredicateTVF (@typ_customer AS char(1))  
  RETURNS TABLE WITH SCHEMABINDING  
AS RETURN

SELECT 1 AS tvf_securitypredicate_result
FROM Security.conf_table c
WHERE c.user_id = USER_NAME()
  AND CASE c.department = 'sidney' THEN 'A' ELSE 'B' END = @typ_customer;

GO

Then simply define a security policy, passing in the typ_customer column to the function.

CREATE SECURITY POLICY Security.YourPolicyName
    ADD FILTER PREDICATE
      Security.YourPredicateTVF (typ_customer)
      ON dbo.Customers;

You may want to change FILTER to BLOCK and/or add DML filters also, depending on your use case.

Be aware that Row-Level Security is subject to side-channel attacks, and is therefore not a completely secure feature. For example, triggering a divide-by-zero error could tell the user what is stored in a row without actually seeing it.