How create table inline function for security policy row filter depending on values in configuration table in SQL Server?

Question

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:

1. Query a configuration table (let's call it conf_table) to get the department name of user_id
2. 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!

Answer 1

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