Share via

Securing SQL Server Data with Checksum value

  • Article

Case Study

You are a DBA for CORP1 and you maintain the company 's very  important table -The Employees table. It is very important because one of the field that you have to manage in that table is the "Salary" field.

The salary field is editable to  several human resource  personel.  When there are approved 
changes by the Human Resource  in the salary field you are to sign-off that change as a DBA by adding
the cheksum value.  If there are alterations in the salary field and if its checksum does not match
that record should be rectified immedietely. This will be the first level of validation and security in the employees table.

To understand checksum theoritically, you can consult wikipedia on this  link. 
Books Online Documentation for Checksum function is found on this link

Presentation #1: Proposed 'SalaryRatePerhour' For approval

Consider the scenario below. I am to sign-off the salary   encoded by the HR for  the following employees
below.  These Salary will be  approved  by adding  a checksum value for  the salary and the row.

*Figure 1. Proposed Salary For approval
*

Presentation #2: Approved Salary

I'm going to update (sign-off) the "salarychecksum" for the the salary together with a checksumcode '1234'.
Im not going to store my checksum code in the database. it will be pass via stored procedure parameters.

*Figure 2. Salary signed-off with checksum value
*

Presentation #3: Tampered Data

Now somebody from the HR department went into the server and update the salary of his friend employee#1
without approval from 250 to 275 per hour.  

*Figure 3. Salary data has been tampered
*

Presentation 4: Data Sanity Check

Before I do any calculation I can  run a sanity check on my data first to make sure it is not tampered.
Take a look at the audit columns.

Presentation #5: Other use of checksum value

The other scenario where you can make use of  the cheksum function is that for example, in the  real world an
employee fields would consists of more than 30 fields, and your task of doing datawarehousing solution
and employees table is one of the table that will be involved in the warehouse design.
Now instead of comparing all the 30 fields to know which data has changed, you can compare on the 'rowchecksum'
value instead.