Automate Table Auditing

The National Institute of Standards and Technology (NIST) establish the security audit policies, content and availability of audit results in the event of software changes.  Specifically, AU-2 (Audit Events), AU-3 (Content of Audit Records) and AU-12 (Audit Generation) address when an audit is required, what is included in the audit and the ability to generate audit records.  The remaining controls in this family provide the guidance for ensuring that the system is capable of storing and protecting the audit records, that they are accessible and that they are able to be analyzed by the appropriate personnel.  

However, the benefits of these controls include more than increased security.  Implementing them not only helps to protect the organization in being able to identify system changes for security reasons but also helps support Change Management best practices. 

The following types of controls are established by the NIST:

  • AU-2 – According to this control, each organization determines the types of events that must be audited in addition to the events that are required by federal law, executive orders, directives, policies, regulations and standards. Auditing the changes to application settings as part of the Change Management process is a prime example.
  • AU-3 – This control identifies the content of the audit records that will be generated.  This may include what the event (or change) is and when it occurred, as well as the source, outcome and identities associated with the event.  In the case of Change Management, this may include process identifiers to more easily identify where in the process the change occurred, event descriptions listing what the change is and success or failure indicators.
  • AU-12 –The “Audit Generation” control includes being able to provide audit records and determine which records are to be audited. Having an easily accessible log of changes reduces recovery time in the event of a system or database rollback.  If specific records are identified, then the rollback can be targeted and the impact of the rollback is reduced.

The most efficient method to ensure that these controls are in place is to automate the process itself.  The solution below is one proposed method; however, organizations must understand their individual systems and constraints. 

Microsoft SQL Server has a built in function, COLUMNS_UPDATED (), which – when used in an update trigger- returns a value that will reveal every column that was updated.  The determination of whether or not the value for a specific column actually changed, if desired, has to be done in a separate step. 

The return value is expressed as a bitmask. Since the bitmask was created using a specific ordering of the table’s columns, it is imperative to have the same ordering of the columns during the comparison to ensure accuracy.  The methodology I have decided to use in my solution is an adaptation of a technique I found for dealing with potentially large bit masks - since there is a table in the application database for a development project I am a part of that has 230 columns! 

This methodology involves the use of a “number table,” which, in our case, contains numbers that represent the ordinal position of any column and for that column what the byte, bit value, bit position, byte value, and mask would be. All of this is accomplished by using simple arithmetic. I initially was going to use a table pre-populated with the correct values for each position of a column but decided to instead use a recursive common table expression to generate the calculated values.

This number table is used in a table-valued function, dbo.fnGetColumnInfo, which returns a table containing the column information combined with the number table.  This function accepts a table id (object ID in SQL Server) and queries the system tables that contain the column meta-data for all tables; in SQL Server this is the [sys].[columns] table. The column name and ordinal position are joined with the number table so the output includes the column name and the column’s ordinal position within the collection of columns, the byte, bit value, and bit position.

This next function, dbo.fnUpdatedColumnInfo, is also a table-valued function and is responsible for combining the data from the dbo.fnGetColumnInfo function with the information returned by the built-in COLUMNS_UPDATED() function. This will all be invoked from within an update trigger for the table that we’re auditing. The value returned by the COLUMNS_UPDATED() function is used to evaluate an expression involving a bitwise operator in order to test each of the columns.

I wrapped the last function discussed with a scalar function, dbo.GetColumnsUpdated, which is responsible for filtering the data from the dbo.fnUpdatedColumnInfo table-valued function so that only the columns involved in the update are included.  The refined list of column information is then used to produce a comma delimited list of the column names. Having the list of columns in the form of a delimited string makes it possible to easily pass this information to another function, as an argument. An alternative to using a delimited list would be to use a table-valued parameter, which requires creating a new type. Another alternative is to use an XML document to hold the refined list of columns.

With the comma delimited list of columns that were included in the update, the only thing left was to obtain and present the values for these columns before and after the update was performed in a compact but still helpful format. This was perhaps the most challenging part due to the constraints of SQL server and performance considerations.

There are tables available from within an update trigger, which expose the values being removed and the values being inserted. These are referred to as special tables (some refer to them as "magic tables") and are named [inserted] and [deleted] (https://msdn.microsoft.com/en-us/library/ms191300(v=sql.110).aspx ). All of the values before and after the update are available through these tables, but I really just want the values before and after the update for the columns in my delimited list – the columns involved in the update.

At first glance, this might appear to be a good scenario for using dynamic SQL. The problem with this approach is the context in which the dynamic query is executed. The [inserted] and [deleted] special tables aren’t accessible using dynamic SQL….and storing the contents of these tables in a way that would be accessible to dynamic SQL would mean using global temporary table. This approach is undesirable due to the performance concerns, which will be addressed in the next iteration of Automate Table Editing.

In Part 1 of Automating Table Auditing, the National Institute of Standards and Technology’s security audit policies AU-2, AU-3 and AU-12 were defined and identified for benefiting organizational security and supporting Change Management best practices. To ensure these controls are in place, a method was proposed for automating the process itself using dynamic SQL, but was deemed undesirable due to performance concerns. This is addressed below in the proposed true solution.

My solution to the problem of using [inserted] and [deleted] was to pivot off of the SQLXML extensions to SQL Server and use the FOR XML clause to represent the data as XML. I combined the data in the [inserted] and [deleted] special tables and then extracted the relevant attributes based on the delimited list of columns. It wasn’t as straight forward as I wanted it to be with regards to the XML support in SQL server, but I was able to write a function to safely extract the attribute values and was able to format the results in a compact but helpful format as shown below in Figure 1.

 

Figure 1: Example of possible output from solution implemented as insert/update trigger

<audit>

  <record PersonID="00000000-0000-0000-0000-000000000401" Timestamp="2017-03-08T04:13:44.093">

    <Original>

      <Column name="FirstName" value="TestName1" />

      <Column name="Phone" value="604-555-1234" />

      <Column name="POC" value="Test Contact" />

      <Column name="Active" value="0" />

      <Column name="Address1" value="1010 Binary Lane" />

    </Original>

    <Updated>

      <Column name="FirstName" value="TestName1.1" />

      <Column name="Phone" value="604-555-1234" />

      <Column name="POC" value="Test Contact" />

      <Column name="Active" value="0" />

      <Column name="Address1" value="4321 Memory Lane" />

    </Updated>

  </record>

  <record PersonID="E8FAF666-C6DF-4F11-A0E6-113185FC7E15" Timestamp="2017-03-08T04:13:44.093">

    <Original>

      <Column name="FirstName" value="TestName2" />

      <Column name="Phone" value="604-555-1234" />

      <Column name="POC" value="Test Contact" />

      <Column name="Active" value="0" />

      <Column name="Address1" value="" />

    </Original>

    <Updated>

      <Column name="FirstName" value="TestName2.2" />

      <Column name="Phone" value="604-555-1234" />

      <Column name="POC" value="Test Contact" />

      <Column name="Active" value="0" />

      <Column name="Address1" value="1234 Memory Lane" />

    </Updated>

  </record>

</audit>

 

Figure 2: Example of table which requires auditing changes made to records at the column level

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[TestTable] (

        [PersonID] [UNIQUEIDENTIFIER] NOT NULL,

        [FirstName] [VARCHAR](100) NOT NULL,

        [LastName] [VARCHAR](100) NOT NULL,

        [Address] [VARCHAR](50) NOT NULL,

        [City] [VARCHAR](15) NOT NULL,

        [State] [VARCHAR](3) NOT NULL,

        [Zip] [VARCHAR](10) NOT NULL,

        [Phone] [VARCHAR](20) NOT NULL,

        [PrimaryContact] [VARCHAR](50) NULL,

        [SecondaryContact] [VARCHAR](50) NULL, 

 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

(

        [PersonID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SampleDatabase]

) ON [SampleDatabase]

 

GO

 

SET ANSI_PADDING OFF

GO

 

Figure 3: Example of implementation of solution within an insert/update trigger

 

CREATE TRIGGER [dbo].[TestTable_Audit_Update_Trigger] ON [dbo].[TestTable] FOR UPDATE

AS

BEGIN

        SET NOCOUNT ON

        DECLARE @table_id AS INT

        SELECT @table_id = parent_id FROM sys.triggers WHERE object_id = @@procid

 

        DECLARE @columns_updated AS VARBINARY(MAX);

        SET @columns_updated = COLUMNS_UPDATED();      

       

        DECLARE @UpdatedColumnList AS VARCHAR(MAX)

        SELECT @UpdatedColumnList = dbo.fnGetColumnsUpdated(@table_id, @columns_updated)               

       

        ;WITH [key]

        AS

        (

                SELECT PersonID

                FROM inserted

                UNION

                SELECT PersonID

                FROM deleted

        )

        SELECT

                [record].PersonID

                ,GETDATE() As [Timestamp]

                ,(

                        SELECT dbo.fnGetAttributeValues(@UpdatedColumnList,

                                        (SELECT *

                                        FROM deleted

                                        WHERE deleted.PersonID = [record].PersonID

                                        FOR XML AUTO, TYPE))

                ) AS [Original]

                ,(

                        SELECT dbo.fnGetAttributeValues(@UpdatedColumnList,

                                        (SELECT *

                                        FROM inserted

                                        WHERE inserted.PersonID = [record].PersonID

                                        FOR XML AUTO, TYPE))

                ) AS [Updated]

        FROM [key] AS [record] FOR XML AUTO, TYPE, ROOT('audit')

               

END

GO

 

Solution Source Code

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ====================================================

-- Author: William Hendrix

-- Create date: 05/05/2016

-- Description: Returns a table which will have as

--              many rows as there are columns in the

--              table as specified by the @table_id

--              argument.  The @table_id is the

--              object ID for the table for which

--              the column info is required.

--              The columns included in the result

--              will have the column name, its ordinal

--              position, the byte, bit value, and

--              bit position.

-- ====================================================

CREATE FUNCTION [dbo].[fnColumnInfo] (

        @table_id INT

) RETURNS

@Numbers TABLE

(      

        ColumnName VARCHAR(255),

        Number INTEGER,

        Byte INTEGER,

        BitValue INTEGER,

        BitPosition INTEGER

)

AS

BEGIN

        ;WITH Numbers

        AS

        (

                SELECT (a.[number] * 256 + b.[number]) AS [Number],

                        CASE (a.[number] * 256 + b.[number]) % 8

                                WHEN 0 THEN ((a.[number] * 256 + b.[number]) - 1) / 8

                                ELSE (a.[number] * 256 + b.[number]) / 8

                                END + 1 AS [Byte]                      

                        ,POWER(2, CASE (a.[number] * 256 + b.[number]) % 8

                                WHEN 0 THEN 8

                                ELSE (a.[number] * 256 + b.[number]) % 8

                                END-1) AS [BitValue]

                        ,(((a.[number] * 256 + b.[number]) -1)%8)+1 AS [BitPosition]

                FROM

                        (

                                SELECT [number]

                                FROM [master]..[spt_values]

                                WHERE

                                        [type] = 'P'

                                        AND [number] <= 255

                        ) a (Number),

                        (

                                SELECT [number]

                                FROM [master]..[spt_values]

                                WHERE

                                        [type] = 'P'

                                        AND [number] <= 255

                        ) b (Number)

                WHERE (a.[number] * 256 + b.[number])

                        BETWEEN 1 AND (SELECT COUNT(*)

                                                  FROM [sys].[columns] WHERE [object_id] = @table_id)

        )

        INSERT INTO @Numbers

        SELECT c.[name], n.[Number], n.[Byte], n.[BitValue], n.[BitPosition]

        FROM [Numbers] n

        INNER JOIN [sys].[columns] c ON c.[column_id] = n.[number]

        WHERE c.[object_id] = @table_id;

 

        RETURN;

END

GO

 

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

-- ===============================================

-- Author: William Hendrix

-- Create date: 01/24/2017

-- Description: Returns meta data about which

--              columns were involved in an

--              update statement - given the

--              result from the COLUMNS_UPDATED()

--              built-in system function.

-- ===============================================

CREATE FUNCTION [dbo].[fnUpdatedColumnInfo]

(

        @TableId INT,

        @ColumnsUpdated VARBINARY(MAX)

)

RETURNS

@UpdatedColumnInfo TABLE

(

        TableId INT,

        ColumnName VARCHAR(255),

        Number INTEGER,

        Byte INTEGER,

        BitValue INTEGER,

        BitPosition INTEGER,

        ByteValue BINARY(1),

        Mask BIGINT,

        ColumnUpdated BIT

)

AS

BEGIN

        ;WITH ColumnInfo

        AS

        (

                SELECT

                     [ColumnName]

                    ,[Number]

                    ,[Byte]

                    ,[BitValue]

                    ,[BitPosition]             

                    ,CONVERT(BINARY(1),SUBSTRING(@ColumnsUpdated, Byte, 1)) AS [ByteValue]

                    ,POWER(2, [BitPosition] - 1) AS [Mask]                     

                FROM [dbo].[fnColumnInfo](@TableId)            

        )

        ,UpdatedInfo

        AS

        (

                SELECT

                  @TableId AS [TableId]

                 ,ColumnName

                 ,Number

                 ,Byte

                 ,BitValue

                 ,BitPosition

                 ,ByteValue

                 ,Mask

                 ,CASE WHEN ByteValue&Mask > 0 THEN 1 ELSE 0 END AS [ColumnUpdated]

                FROM ColumnInfo

        )

        INSERT INTO @UpdatedColumnInfo (TableId, ColumnName, Number, Byte,

                                        BitValue, BitPosition, ByteValue, Mask, ColumnUpdated)

        SELECT TableId, ColumnName, Number, Byte, BitValue, BitPosition, ByteValue, Mask, ColumnUpdated

        FROM UpdatedInfo

        RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===============================================

-- Author: William Hendrix

-- Create date: 01/25/2017

-- Description: Returns a string containing a

--              comma delimited list of the

--              fields that were updated

--              when run inside of a trigger

--              using the provided table id

--              (object id) and the provided

--              varbinary result of calling the

--              built-in system function

--              COLUMNS_UPDATED()

-- ===============================================

CREATE FUNCTION [dbo].[fnGetColumnsUpdated]

(

        @TableID INT,

        @ColumnsUpdated VARBINARY(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN

        -- Declare the return variable here

        DECLARE @Result AS VARCHAR(MAX)

        -- Add the T-SQL statements to compute the return value here

        SELECT @Result = 

                REPLACE(

                 (

                        SELECT QUOTENAME(ColumnName,'[') AS [data()]

                        FROM dbo.fnUpdatedColumnInfo(@TableID, @ColumnsUpdated)

                        WHERE ColumnUpdated = 1

                        FOR XML PATH('')

                ), '] [','],[')

        -- Return the result of the function

        RETURN @Result

END

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =================================================

-- Author: William Hendrix

-- Create date: 01/24/2017

-- Description: Extracts the values for a given

--              list of attributes provided as a

--              string of delimited attribute names

--              from a given XML element

--             

-- =================================================

CREATE FUNCTION [dbo].[fnGetAttributeValues]

(      

         @AttributeNames AS VARCHAR(MAX)

        ,@XmlElement AS XML

)

RETURNS XML

AS

BEGIN

        -- Declare the return variable here

        DECLARE @ResultVar AS XML

        DECLARE @columns AS TABLE([name] VARCHAR(300))

        INSERT INTO @columns ([Name])

        SELECT PARSENAME([Item],1)

        FROM dbo.fnDelimitedStringTable(@AttributeNames, ',', 1)

        ;WITH

        AttributeName

        AS (

                SELECT [name], ('% ' + [name] + '=%') AS [pattern],

                        CAST(@XmlElement AS VARCHAR(MAX)) AS [xmlrow]

                FROM @columns AS [Column]

        ),

        AttributeStart

        AS (

                SELECT [name], [xmlrow], [pattern],

                      PATINDEX([pattern], [xmlrow]) + DATALENGTH([name]) + 3 AS [start]

            FROM AttributeName

        ),

 

        AttributeValue

        AS (

                SELECT [name]

                        , [xmlrow]

                        , [pattern]

                        , [start]

                        , SUBSTRING([xmlrow], [start], CHARINDEX(CHAR(34)+' ',

                           [xmlrow], [start] + 1) - [start]) AS [value]

                FROM AttributeStart

        )

        -- Add the T-SQL statements to compute the return value here   

        SELECT @ResultVar = (SELECT [name], [value] FROM AttributeValue AS [Record] FOR XML AUTO, TYPE)

        -- Return the result of the function

        RETURN @ResultVar

END

GO

 

 

-- ====================================================

-- Author: William Hendrix

-- Create date: 05/05/2016

-- Description: Returns a table with columns to

--              identify the position and value for

--              each item populated from a custom

--              delimited list of values provided

--              through the @OriginalString parameter.

--              Custom delimiter is specified through

--              @Delimiter parameter.  If the

--              @RequireUnique parameter is specfied

--              and has a value of TRUE, then the

--              returned table will contain unique

--              set of items (any duplicate entries in

--              the delimited list will be ignored).

-- ====================================================

CREATE FUNCTION [dbo].[fnDelimitedStringTable] (

        @OriginalString VARCHAR(MAX),

        @Delimiter CHAR(1),

        @RequireUnique BIT = 0

) RETURNS

@Values TABLE

(      

        [Position] INT IDENTITY(1,1),

        [Item] VARCHAR(MAX) NOT NULL

)

AS

BEGIN  

        DECLARE @DelimitedValues        VARCHAR(MAX);

        DECLARE @ExtractedValue         VARCHAR(MAX);  

        DECLARE @Value                          VARCHAR(MAX);

        DECLARE @DelimiterPosition      INT;

 

        SET @DelimitedValues = @OriginalString;

        SET @DelimiterPosition = CHARINDEX(@Delimiter, @DelimitedValues);

 

    WHILE(@DelimiterPosition > 0)

    BEGIN              

        -- Obtain the value to extract

        SET @ExtractedValue = LEFT(@DelimitedValues, @DelimiterPosition);

       

        -- Update the @DelimitedValues accordingly

        SET @DelimitedValues = RIGHT(@DelimitedValues,

                                       DATALENGTH(@DelimitedValues)

                                     - DATALENGTH(@ExtractedValue));

       

        -- Prepare extracted value for insert

        SET @Value = LTRIM(RTRIM(REPLACE(@ExtractedValue, @Delimiter,'')));

                -- Insert trimmed extracted value with delimiter removed into output table

        IF(@RequireUnique = 0 OR (@RequireUnique = 1 AND

                   NOT(EXISTS(SELECT [Item] FROM @Values WHERE [Item] = @Value))))

                INSERT INTO @Values ([Item]) VALUES (@Value);

                SET @DelimiterPosition = CHARINDEX(@Delimiter, @DelimitedValues);

    END        

 

        -- Insert any remaining string as either the first (and only item) or the last item of many

        IF(DATALENGTH(@DelimitedValues) > 0)

        BEGIN

                SET @Value = LTRIM(RTRIM(REPLACE(@DelimitedValues, @Delimiter,'')));

                IF(@RequireUnique = 0 OR (@RequireUnique = 1 AND

                    NOT(EXISTS(SELECT [Item] FROM @Values WHERE [Item] = @Value))))

                        INSERT INTO @Values ([Item]) VALUES (@Value);

        END

 

        RETURN;

END

GO

Sources:

https://www.va.gov/vapubs/viewPublication.asp?Pub_ID=793 (VA 6500 Handbook)

http://nvlpubs.nist.gov/nistpubs/SpecialPublications/NIST.SP.800-53r4.pdf (NIST SP800-53 Revision 4)

Add new comment

This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

code
auditing
Automation