Pass Identity Information to Microsoft SQL Server for Security and Auditing

  • 15 February 2022
  • 0 replies
  • 265 views

Userlevel 5
Badge +20
 

Pass Identity Information to Microsoft SQL Server for Security and Auditing

KB003062

PRODUCT
K2 Five

 

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.

The KB article KB002942: Sending Identity Information to a Line of Business System describes other ways of passing user context to another system.

 

 

 

 

 

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.

Image

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.

Remember that the username is passed in a format that includes the K2 security label (such as K2:DenallixBob), so you may want to adjust the procedure or function to trim the label name, if necessary.

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

CREATE FUNCTION [dbo].[GetContextUser] ( )
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

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:

DECLARE @User nvarchar(128);
SET @User = dbo.GetContextUser();

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].[GetContextUser] ()
    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].[roleMembers](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [RoleID] [int] NOT NULL,
        [UserID] [int] NOT NULL,
     CONSTRAINT [PK_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 [PRIMARY]
    ) ON [PRIMARY]

    -- Creating a table to define different system/user roles
    CREATE TABLE [dbo].[systemRoles](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [RoleName] [nvarchar](128) NULL,
     CONSTRAINT [PK_systemRoles] 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]

    -- Creating a table to store users that will interact with the system/data
    CREATE TABLE [dbo].[systemUsers](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [UserName] [nvarchar](128) NULL,
     CONSTRAINT [PK_systemUsers] 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]
  3. Populate the tables that define the users, roles, and user-role relationships
    -- Insert roles
    SET IDENTITY_INSERT [dbo].[systemRoles] ON
    INSERT [dbo].[systemRoles] ([ID], [RoleName]) VALUES (1, N'Loan Admins')
    INSERT [dbo].[systemRoles] ([ID], [RoleName]) VALUES (2, N'Finance')
    INSERT [dbo].[systemRoles] ([ID], [RoleName]) VALUES (3, N'IT')
    SET IDENTITY_INSERT [dbo].[systemRoles] 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 [dbo].[systemUsers] ([ID], [UserName]) VALUES (1, N'k2:denallixADMINISTRATOR')
    INSERT [dbo].[systemUsers] ([ID], [UserName]) VALUES (2, N'k2:denallixob')
    INSERT [dbo].[systemUsers] ([ID], [UserName]) VALUES (3, N'k2:denallixcodi')
    INSERT [dbo].[systemUsers] ([ID], [UserName]) VALUES (4, N'k2:denallixanthony')
    SET IDENTITY_INSERT [dbo].[systemUsers] OFF

    -- Insert role members
    SET IDENTITY_INSERT [dbo].[roleMembers] ON
    INSERT [dbo].[roleMembers] ([ID], [RoleID], [UserID]) VALUES (1, 1, 1)
    INSERT [dbo].[roleMembers] ([ID], [RoleID], [UserID]) VALUES (2, 1, 4)
    INSERT [dbo].[roleMembers] ([ID], [RoleID], [UserID]) VALUES (3, 2, 3)
    INSERT [dbo].[roleMembers] ([ID], [RoleID], [UserID]) VALUES (4, 3, 2)
    SET IDENTITY_INSERT [dbo].[roleMembers] 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 [dbo].[IS_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 [dbo].[loanRequests](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [CreatedBy] [nvarchar](128) NULL,
        [HouseValue] [int] NULL,
        [Address] [nvarchar](500) NULL,
        [Status] [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 [PRIMARY]
    ) 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 [dbo].[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 [dbo].[list_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 [dbo].[load_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 [dbo].[create_loanRequest]
        @houseValue int,
        @address nvarchar(500),
        @status nchar(10)
    AS
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO loanRequests (CreatedBy, HouseValue, [Address], [Status]) 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:

 


0 replies

Be the first to reply!

Reply