Starting form MS SQL Server 2016, Microsoft added a feature, which is called Row-Level Security. It enables customers to control access to rows in a database table based on the characteristics of the user executing a query (for example, group membership or execution context). In this article I am describing the approach you can take to use Row Level Security with K2 and SQL ServiceBroker, when building K2 applications.
In K2 Five the SQL Server ServiceInstance has a new parameter key – Set the impersonation level on DB ContextInfo. The following article describes the usage of the property
Set the impersonation user on the DB ContextInfo (CONTEXT_INFO). The interesting part about it is that CONTEXT_INFO is set only when the ServiceInstance parameter is set to True. However, K2 sets SESSION_CONTEXT object with the caller’s FQN, when you make a simple LIST call.
In order to illustrate the Row Level Security with K2 and SQL ServiceBroker, let’s imagine, we have the following requirements:
- We have a table of items (e.g. Sales), which contains some data and also a GroupId column.
- We have a Group Table, which contains the mapping of GroupIds and FQN of group members.
- Standard SMO List method must return the items, which are associated with the groups of a user, who is making a call from K2.
Let’s create the following objects in SQL:
Below I will provide some comments and/or explanation, what is going on:
01 – Sample Tables and Data
In the firs part I am creating some sample tables to illustrate the Row Level Security with K2 and SQL ServiceBroker and fill them in with Sample Data. Please, pay your attention:
- K2:DENALLIX\Bob (Group 1) – authorized to see Sales with IDs 1 and 2;
- K2:DENALLIX\Jonno (Group 2) – authorized to see Sales with IDs 3 and 4;
- K2:DENALLIX\Markus (Group 3) – – authorized to see Sales with IDs 5 and 6.
This is important for testing.
02 – K2 Service Account Permissions
Usually, we grant db_owner permissions for K2Service account. However, not in this case. It is required to provide granular permissions to the account, which K2 uses to connect to SQL. In the script I am granting just enough permissions to ‘CRUD’ and view the table definition. Thus, K2 can create all Service Objects.
Also, you can note, that I am not granting any permissions for GroupUsers table. It is not necessary for K2 to see dependent tables, unless you need to get the data from those dependent tables into your applications.
03 – New Schema and Inline Function
Microsoft highly recommends to create a separate schema for the Row Level Security (RLS) objects (predicate function and security policy).
The inline function Security.fn_securitypredicate should contain all the logic, which is used to understand, if a user can or cannot have access to the table row. The row column value will be sent as an input parameter function and the condition will be evaluated for every row.
In the function I am using 2 system SQL functions/objects:
- CONTEXT_INFO – when calling Create/Read/Update/Delete methods;
- SESSION_CONTEXT – when calling List method.
The current approach is working on K2 Five 5.2 FP6. Maybe, the behavior changes in latter versions or after applying some newer FixPacks.
04 – Security Policy
I am creating a security policy that adds this function as a filter predicate. So this is actually the place, where I map the table column to the input parameter of the bound function. It is also possible to add other types of predicates, e.g. if you want to check that a user can create only items with their GroupIds etc.
You need to set state to ON to enable the policy.
Check the screenshots below to see the results
When Initializing the view, I am doing only a simple List call. The filtering is done by SQL, based on the Row Level Security settings.
This is a simple illustration, how to use Row Level Security with K2 and SQL ServiceBroker. You can create a more complex logic to maintain your security and permissions in the Data Layer. However, you need to be careful so that your complex logic does not affect the query performance.
The Spanish version of this article is available here Seguridad a Nivel de Fila (Row Level Security) – K2 & SQL ServiceBroker.