CODEWITHSUNDEEP
sql-serverasp.net-web-api2row-level-security
Jed
Jed

Reputation: 1074

SQL Server row level security and Microsoft authentication

I know that SQL Server's RLS uses Windows Authentication, but is it possible to use external authentication? Like Google or Microsoft auth?

I am doing testing on it using C# Web API 2 can't figure out how.

Thanks in advance!

Upvotes: 0

Views: 440

Answers (1)

gotqn
gotqn

Reputation: 43636

The Row-Level-Security feature does not use Windows Authentication. You are creating your own function, that is used as filtering predicated. For example, you can use built-in function USER_NAME() (check the official documentation for the full working example):

CREATE SCHEMA Security;  
GO  

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 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

You can use also sp_set_session_context and SESSION_CONTEXT to build more complex logic. For example, you can calculated the security access in your .net code and set session context for the SQL session your are opening. Then, using SESSION_CONTEXT you can check the access in your filtering function.

Upvotes: 2

Related Questions