SQL Server databases are one of the most commonly used data storage technologies for K2 applications. SQL server is a very widely used database platform and K2 provides out of the box integration capabilities that means you can very quickly start using existing or new SQL databases with your K2 solutions.
SQL Server offers 2 ways for users or systems to authenticate when connecting to a database *:
- Windows Authentication – windows credentials are used to authenticate
- SQL Server Authentication – a “SQL Login” with user name and password is used to authenticate
* SQL Server Authentication is the only option available for SQL Azure
When setting up the connection to a SQL database from K2 (i.e. creating the “Service Instance”) you are presented with a few different authentication options:
- Service Account – The K2 Service Account windows credentials are used to authenticate using Windows Authentication
- Impersonate – The currenly logged on user’s windows credentials are used to authenticate using Windows Authentication
- Static – A user name and password is stored (encrypted) in the K2 system database and used to authenticate using SQL Server Authentication
The “Service Account” mode is very commonly used for SQL Server Service Instance as it only requires the K2 service account be granted the necessary permissions against the SQL server database. This means that any user of a K2 application that connects to the database does not need to have their own “permissions” to connect and read/write data, as from the SQL servers perspective this is all happening as a single account – the K2 service account.
This works well, but what happens when you want to know the identity of the user executing a query against the database so that you can do things like store that user’s details (e.g. store information about who created a new record) or restrict access to certain data (e.g. only return records that user is permitted to see)? The answer lies in a specific “Service Key” that can be set when configuring the SQL Server Service Instance called “Set the impersonation user on the DB ContextInfo ”
In this article we will experiment with using this service key setting to reproduce some functionality described in the K2 KB Article Pass Identity Information to Microsoft SQL Server for Security and Auditing, and then use the Context_Info variable to leverage Row Level Security (RLS) in SQL in a simple example, and then a more complex one.
By setting the “Set the impersonation user on the DB ContextInfo” service key for a SQL Server Service Instance to true, the current user’s (e.g. a user opening a K2 SmartForm) Fully Qualified Name (FQN), (e.g. “K2:Denallix\Bob”) is used to set the Context_Info variable in SQL Server.
Microsoft defines Context_Info as variable that “Associates up to 128 bytes of binary information with the current session or connection”. This implies a limit of 128 characters to the FQN that the SQL Broker will pass to SQL. For more information about the Context_Info variable see here.
Without using the Context_Info variable it is still possible to pass the current user’s details to SQL from K2. For example, the current users name (obtained from the K2 Context Browser) could be passed as a parameter to a SmartObject method calling a stored procedure, or as an input mapping for a table based SmartObject create method. This however opens up potential security risks, as it is possible for tools like fiddler to be used to intercept and modify web requests (see Securing K2 Solutions for more details).
With the Context_Info variable set to the FQN of the current user it allows a SQL Developer to access it and leverage other SQL functionality, without having to pass that user information through the SmartObject layer.
For the initial investigation into using ContextInfo we created a table “UsingDBContextInfo” and a stored procedure “UsingDBContextInfo_Create” to replace the default create method for the table’s ServiceObject. Note that our table has a “Creator” column to store the name of the user who created the record, and that the stored procedure is utilising the Context_Info variable to populate the “Creator” column value.
CREATE TABLE [dbo].[UsingDBContextInfo]( [ID] [uniqueidentifier] NOT NULL, [Created] [datetime2](7) NOT NULL, [Creator] [nvarchar](200) NOT NULL, [Information] [nvarchar](max) NOT NULL, CONSTRAINT [PK_UsingDBContextInfo] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[UsingDBContextInfo] ADD CONSTRAINT [DF_UsingDBContextInfo_ID] DEFAULT (newid()) FOR [ID] GO Stored Procedure CREATE PROCEDURE [dbo].[UsingDBContextInfo_Create] @Information nvarchar(max), @ID uniqueidentifier = null OUT AS BEGIN SET NOCOUNT ON; SET @ID = NEWID() INSERT INTO [dbo].[UsingDBContextInfo] (ID ,[Created] ,[Creator] ,[Information]) VALUES (@ID ,Getdate() ,REPLACE(CAST(CONTEXT_INFO() AS nvarchar(128)),nchar(0),'' COLLATE Latin1_General_100_BIN2) ,@Information) END GO
Next, we registered a new SQL Service Instance, remembering to set the ContextInfo Service Key to true. Authentication can be performed as the service account since we intend to use the Context_Info variable to identifiy the K2 authenticated user.
If you have a version of K2 that you believe should have this functionality, but you cannot see the service key, refresh the service type (we were not able to do this from the Management Site, but it can still be done using the ServiceObject Tester).
During testing using service key values “Native SQL” as true and “on different SQL server” as false, the Context_Info variable was not populated and did not work with list methods. Setting “on different SQL server” as true and “Use Native SQL Execution” as false enabled list methods to work.
A SmartObject was then created and the create method was modified to use the purpose-built stored procedure instead of the default create method for the table.
Executing and Testing
To test the create method we opened K2 Management, located our SmartObject in the Category browser, selected the Create method and passed in some text into the “Information” property.
As the results show, the “Creator” value was set to the name of the user executing the SmartObject method (in this case Administrator).
In summary, we have created a SmartObject that does not rely on passing the Authenticated user from the User Interface to SQL, but instead relies on the K2 service broker to set the Context_Info session variable and use this instead.
Using ContextInfo for Row Level Security
Row Level Security (RLS) is a SQL Server feature that enables control over access to rows in a database table based on the user who is executing the query. For a more information on RLS, see here.
For the next example we will utilise RLS to restrict access to rows in a table so that a user can only see records where they are the “Creator”. We are going to create a table and stored proc like the above example. Then we will apply RLS to it using the technique described in the above article. It is important to note that instead of using the authenticated user, which will be the K2 service account, we will use Context_Info session variable.
To enable RLS we created a table valued function that accepts the “Creator” parameter as input and returns a result that determines whether a user can access to specific rows in a table.
This table valued function is then applied to the table as a security policy to filter what data should be returned during normal select statements. When applying the security policy, we specify the column/s from the table being secured that need to be passed to the table valued function.
CREATE TABLE [dbo].[UsingRowLevelSecurity]( [ID] [uniqueidentifier] NOT NULL, [Created] [datetime2](7) NOT NULL, [Creator] [nvarchar](128) NOT NULL, [Information] [nvarchar](max) NOT NULL, CONSTRAINT [PK_UsingRowLevelSecurity] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[UsingRowLevelSecurity] ADD CONSTRAINT [DF_UsingRowLevelSecurity_ID] DEFAULT (newid()) FOR [ID] GO Stored Procedure CREATE PROCEDURE [dbo].[UsingRowLevelSecurity_Create] @Information nvarchar(max), @ID uniqueidentifier = null OUT AS BEGIN SET NOCOUNT ON; SET @ID = NEWID() INSERT INTO [dbo].[UsingRowLevelSecurity] ([ID] ,[Created] ,[Creator] ,[Information]) VALUES (@ID ,GETDATE() ,REPLACE(CAST(CONTEXT_INFO() AS nvarchar(128)),nchar(0),'' COLLATE Latin1_General_100_BIN2) ,@Information) END GO Table Valued function (Security Predicate) CREATE FUNCTION [dbo].[UsingRowLevelSecuritySecurityPredicate] ( @Creator nvarchar(128) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS SecurityPredicateResult WHERE REPLACE(CAST(CONTEXT_INFO() AS nvarchar(128)),nchar(0),'' COLLATE Latin1_General_100_BIN2) = @Creator GO Apply the table valued function as a security policy CREATE SECURITY POLICY [dbo].[UsingRowLevelSecurityFilter] ADD FILTER PREDICATE [dbo].[UsingRowLevelSecuritySecurityPredicate]([Creator]) ON [dbo].[UsingRowLevelSecurity] WITH (STATE = ON, SCHEMABINDING = ON) GO
A SmartObject can now be created in the same manner as the previous test and executed. Subsequent default list methods will only return results that are allowed by the security policy.
Testing with a view
Below is a side by side comparison for two users
Remember that the default List method is used without any filters. No security had to be applied in K2. Only the K2 Service account is used for authentication.
SQL Server Management Studio View
Using SSMS we cannot see the data as the security policy is filtering out everything as the Context Info variable is not set in this session. We can view the data by disabling the security policy, or setting Context_Info session variable in your connection or query.
This will also affect what is returned through the SmartObject Layer
Enabling the security policy will restore the filter.
Using Context_Info – Advanced
Next we will test a more advanced scenario. For this test we have users, companies, and information for a company captured by a user. A User could be a global admin requiring to see all data in the Information table. Users need to be assigned to a company via the CompanyUser table, at which point they can be made company administrators. Company administrators can view all information for a company. A user that is added to a company, but is not an administrator for that compny, can only view their own captured information for the company. The database schema looks like this:
In this example we will:
- Protect the information in the Company table so that
- Users assigned GloablAdmin can see all
- Other users can only see companies they are assigned to via the CompanyUser table
- Protect the information in the Information table so that
- Global Admin can see all
- Users assigned admin on the CompanyUser table can see all for that company
- Users assigned through the CompanyUser table can see only their own entries for a company
A Script to create this DB and the associated security Predicates and policies is included.
A K2 Solution to demonstrate the example is also included.
Once you have installed the example, open the following form:
Create yourself as Global Admin and some other users. Your logged in FQN is at the top of the User Tab.
Add some companies:
Now open the Information Form at:
Accessing the information form, our user cannot see any companies, as the security policy does not allow a user to see a company till they are associated with it through the CompanyUser table.
Logged in as George Harrison
After adding the user to some companies the filter allows those records via the standard list method.
Logged in as George Harrison
The user can create and view their own information.
Logged in as George Harrison
Let us associate a second user with the same company as above, this time with company admin selected.
What does the new user see?
Logged in as John Lennon
The policy applied to the companies table allows a single company row to be viewed, and the policy applied to the Information table allows all record through since the “Is Admin” option is true.
This user adds information to the table:
Logged in as John Lennon
The first user cannot see it as they do not have the “Is Admin” option as true.
Logged in as George Harrison
The Global Admin User can see everything.
In these examples we have been using the “K2 Service Account” as the authentication mode for the SQL Server Service Instance. As mentioned above, when using SQL Azure databases, the “SQL Server Authentication” method is the only available authentication method. This means that you must use the “Static” authentication mode for the service instance. We tested the above scenarios using “Static” credentials and observed the same results with no other changes.
In this article we have demonstrated that by using the “Set the impersonation user on the DB Context_Info” serivce key to true for a SQL Server Service instance we can:
- Obtain the FQN of the user executing a query from the Context_Info SQL variable without explicitly passing that value via the SmartObject layer (i.e. as a SmartObject property)
- Use the Context_Info variable to store the user’s FQN when creating new records in tables
- Use the Context_Info variable to secure SQL data using Row Level Security
Using the methods described above in this article present you with an opportunity to make your K2 applications that use SQL data more secure, ensuring you have full data auditability (record who created/modified/deleted data) and control over data access (e.g. restrict access to table rows via RLS).