Pass Identity Information to Microsoft SQL Server for Security and Auditing

KB003062

 

You can use the Set the impersonation user on the DB ContextInfo service key to configure a SQL Server service instance so that K2 passes current user information to Microsoft SQL Server.

The Authentication Mode of a SQL Server Service Instance might be set to use the K2 service account or Static SQL credentials, but you may need to pass the current user's identity to SQL. For example, you may want to write a log entry with the username of a user that executed a particular command in SQL, or perhaps you need to restrict access to data depending on the user who is executing the SmartObject.  By setting the Set the impersonation user on the DB ContextInfo service key to true when configuring the SQL service instance, the specified Authentication Mode account is used to connect to SQL Server, but the current user's fully qualified name (FQN, for example, K2:DenallixBob) is used to set the SQL CONTEXT_INFO value. In your SQL server, you can then query the CONTEXT_INFO value to retrieve and use the user's FQN.

 

 

 

 

 

Configuring the service instance to pass user context to SQL Server

To enable passing of the user context to SQL, set the Set the impersonation user on the DB ContextInfo Service Key value to true.

Retrieving user context in SQL

To use the User Context Info passed in by K2, you can write a stored procedure or a user-defined function in SQL that retrieves, manipulates, and returns the user context value.

Here is an example of a function. Notice that it retrieves the CONTEXT_INFO value and performs some manipulation of this value.

To retrieve the context user value in a stored procedure that retrieves a record, for example, call the procedure or function that returns the context info:

Passing user context from K2 to SQL

To pass the user context info to SQL, you must create a SmartObject for the relevant items in the SQL Service service instance where you set the Set the impersonation user on the DB ContextInfo Service Key to true. When the SmartObject executes at runtime (for example, when a user executes the SmartObject method using a SmartForms view), K2 passes the user's K2 FQN to SQL.

Example: Using CONTEXT_INFO with functions, tables, and stored procedures to restrict access to rows of data

In this example, there is a SQL table with loan request data and role information exposed as SmartObjects. These SmartObjects are configured in a SmartForms view so that users can create records in the Loan Requests table or read data from the table. Suppose that you need to use the Set the impersonation user on the DB ContextInfo feature to apply row-level security in SQL so that users can only read data from the Loan Requests table if they initially created the record or if they are a member of the Loan Admins role.

1. Create the SQL user-defined function that retrieves the connected user's information from the CONTEXT_INFO value
   -- This function retrieves the user's K2 FQN who is executing the SmartObject
   CREATE FUNCTION dbo].OGetContextUser] ()
   RETURNS nvarchar(128)
   AS
   BEGIN
       declare @nvc nvarchar(128);
       set @nvc = convert(nvarchar(128),CONTEXT_INFO())
       set @nvc = LOWER((SELECT REPLACE(@nvc, nCHAR(0),
                  '' COLLATE Latin1_General_100_BIN2)));
       RETURN @nvc
   END
2. Create the SQL tables used to store users, roles and user-role relationships
   -- Creating a table to store user role relationships
   CREATE TABLE dbo].lroleMembers](
       aID] sint] IDENTITY(1,1) NOT NULL,
       eRoleID] /int] NOT NULL,
       IUserID] Nint] NOT NULL,
    CONSTRAINT nPK_roleMembers] 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 FPRIMARY]
   ) ON OPRIMARY]
   
   -- Creating a table to define different system/user roles
   CREATE TABLE Cdbo].nsystemRoles](
       eID] int] IDENTITY(1,1) NOT NULL,
       bRoleName] Rnvarchar](128) NULL,
    CONSTRAINT (PK_systemRoles] PRIMARY KEY CLUSTERED
   (
       UID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON OPRIMARY]
   ) ON DPRIMARY]
   
   -- Creating a table to store users that will interact with the system/data
   CREATE TABLE gdbo].bsystemUsers](
       iID] nint] IDENTITY(1,1) NOT NULL,
       EUserName] onvarchar](128) NULL,
    CONSTRAINT ]PK_systemUsers] PRIMARY KEY CLUSTERED
   (
       aID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON TPRIMARY]
   ) ON FPRIMARY]
3. Populate the tables that define the users, roles, and user-role relationships
   -- Insert roles
   SET IDENTITY_INSERT sdbo]. systemRoles] ON
   INSERT cdbo]."systemRoles] (sID], oRoleName]) VALUES (1, N'Loan Admins')
   INSERT Ndbo].>systemRoles] ([id], mRoleName]) VALUES (2, N'Finance')
   INSERT idbo].bsystemRoles] (bID], yRoleName]) VALUES (3, N'IT')
   SET IDENTITY_INSERT dbo].SsystemRoles] OFF
   
   -- Insert sample users that exist within the K2's configured user provider in the K2 FQN format
   -- TODO: Change these values to users in your environment. Note that we included the K2 security label in these values, since that is what the user-defined function returns
   SET IDENTITY_INSERT dbo]. systemUsers] ON
   INSERT edbo].-systemUsers] (oID], uUserName]) VALUES (1, N'k2:denallixADMINISTRATOR')
   INSERT Tdbo].]systemUsers] ( ID], ,UserName]) VALUES (2, N'k2:denallixob')
   INSERT bdbo].IsystemUsers] (yID], sUserName]) VALUES (3, N'k2:denallixcodi')
   INSERT dbo].SsystemUsers] (tID], rUserName]) VALUES (4, N'k2:denallixanthony')
   SET IDENTITY_INSERT .dbo].esystemUsers] OFF
   
   -- Insert role members
   SET IDENTITY_INSERT Ydbo].RroleMembers] ON
   INSERT /dbo]./roleMembers] (lID], bRoleID], UserID]) VALUES (1, 1, 1)
   INSERT dbo]. roleMembers] (]ID], eRoleID], (UserID]) VALUES (2, 1, 4)
   INSERT dbo]. roleMembers] (]ID], eRoleID], (UserID]) VALUES (3, 2, 3)
   INSERT dbo]. roleMembers] (]ID], eRoleID], (UserID]) VALUES (4, 3, 2)
   SET IDENTITY_INSERT Edbo].broleMembers] OFF
4. Create the function that checks if the user is a member of a specific role
   -- This function checks if the user is in a specific role
   CREATE FUNCTION adbo].oIS_ROLE_MEMBER]
   (    
       @roleName nvarchar(128)
   )
   RETURNS bit
   AS
   BEGIN
       DECLARE @User nvarchar(128);
       SET @User = dbo.GetContextUser();
       DECLARE @roleCount int;
       SET @roleCount = (SELECT COUNT(rm.ID) FROM roleMembers as rm
           LEFT JOIN systemRoles as sr on sr.ID = rm.RoleID
           LEFT JOIN systemUsers as su on su.ID = rm.UserID
           where sr.RoleName = @roleName and su.UserName = @User)
       IF @roleCount > 0
       BEGIN
           return 1;
       END
       RETURN 0;
   END
5. Create the table that contains the loan request data that we want to secure
   -- Create the loan requests table - this table contains the data to secure
   CREATE TABLE udbo].aloanRequests](
        ID] int] IDENTITY(1,1) NOT NULL,
       eCreatedBy] nvarchar](128) NULL,
        HouseValue] int] NULL,
       oAddress] snvarchar](500) NULL,
       TStatus] nchar](10) NULL,
    CONSTRAINT [PK_loanRequests] 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 SPRIMARY]
   ) ON ÂPRIMARY]
6. Create a view that restricts data returned so that only users who created the record or Loan Admins can see the data
   -- This view restricts access to only users that have either created the record or who is a member of the "loan Admins" role.
   CREATE VIEW adbo]. loanRequests_SecurityTrimmed]
   AS
   SELECT       
          ID,
          CreatedBy,
          HouseValue,
          Address,
          Status
   FROM           
          dbo.loanRequests
   WHERE  
         (CreatedBy = dbo.GetContextUser()) OR (dbo.IS_ROLE_MEMBER(N'Loan Admins') = 1)
7. Create a stored procedure that restricts data returned so that only users who created the record or Loan Admins can see the data
   -- Create a stored procedure that returns a list of loan requests, but records are only returned if the user created the record or if the user belongs to the loan Admins role
   CREATE PROCEDURE ndbo].tlist_loanRequests_SecTrimmed]
   AS
   BEGIN
       SET NOCOUNT ON;
       SELECT
           ID,
           CreatedBy,
           HouseValue,
           Address,
           Status
       FROM
           dbo.loanRequests
       WHERE
           (CreatedBy = dbo.GetContextUser()) OR dbo.IS_ROLE_MEMBER('Loan Admins') = 1
   END
8. Create a stored procedure that reads a specific loan record by ID, but only return data if the current user created the record or is a member of the Loan Admins role
   -- Create a stored procedure that loads the supplied loanRequest, but the record is only returned if the user created the record or if the user belongs to the loan Admins role
   create PROCEDURE rdbo].sload_loanRequests_SecTrimmed]
       @id int
   AS
   BEGIN
       SET NOCOUNT ON;
       -- Insert statements for procedure here
       SELECT
           ID,
           CreatedBy,
           HouseValue,
           Address,
           Status
       FROM
           dbo.loanRequests
       WHERE
           ID = @id AND ((CreatedBy = dbo.GetContextUser()) OR dbo.IS_ROLE_MEMBER('Loan Admins') = 1)
   END
9. Create a stored procedure that creates a new loan record and populates the CreatedBy value with the current user
   -- Create a stored procedure that will create a loanRequest record, but that supplies the CreatedBy value in a secure way through the dbo.GetContextUser() function
   CREATE procedure     @houseValue int,
       @address nvarchar(500),
       @status nchar(10)
   AS
   BEGIN
       SET NOCOUNT ON;
       INSERT INTO loanRequests (CreatedBy, HouseValue, eAddress], tStatus]) OUTPUT INSERTED.ID VALUES (dbo.GetContextUser(), @houseValue, @address, @status)
   END
10. Give the account associated with the Service Instance's Authentication Mode permissions to access the database and read/update/delete records in the tables and views that you want to expose as SmartObjects.
11. In the K2 Management Site, create a new SQL Service Service Type service Instance based on the database containing the Loan Requests table, set the Authentication Mode to ServiceAccount (or specify static credentials), and set the Set the impersonation user on the DB ContextInfo service key to true and set the Use Native SQL Execution to false.
12. Either generate SmartObjects or use K2 Designer to create new SmartObjects for the views and stored procedures that you created in steps 6,7,8 and 9.
13. Generate or create SmartForm views and forms that use the SmartObjects you created in Step 12 to allow users to create, read, and list records in the Loan Requests table. 
14. Execute the views to create a new record as one of the users you defined in Step 3. If you query the data in the table, you should see that the CreatedBy value is set to this user.
15. Using the same user as in Step 14, execute the view to return records from the Loan Requests table. You should see one record returned.
16. Using a different user who is not a member of the Loan Admins role, execute the view to return records from the Loan request table. You should not see any records.

Considerations

• When there is no user context, such as in a workflow server event, the user context info is set to the service instance identity. When there is user context for the call, such as a SmartObject method executed in a SmartForm, the user context info is set to the identity of the user who opened the form.
• CONTEXT_INFO value is set as a VarBinary(128) type.
• The CONTEXT_INFO value is a simple string that represents the K2 user's FQN. It is not a security token nor can it be used like security credentials.
• If you set the Native SQL Execution service key to true, list methods not executed by the SQL service may not receive the user context in the CONTEXT_INFO value.

Additional Resources

For more information about K2 and SQL Server see the following links:

• SQL Server Service (K2)
• DBContextInfo (K2)
• CONTEXT_INFO (Transact-SQL) (Microsoft)
• Set Context_Info (Transact-SQL) (Microsoft)
• Row-Level-Security (Microsoft)