Every Record Is Signed

Problem: Inputs to a system need to be verifiable after insertion so that tampering can be detected

Pattern: The client application signs each record with a private key before submitting it to the system

Example: Consider the following table schema:
  • orders
    • order_id: varchar(18)
    • billCustomer_id: varchar(8)
    • shipCustomer_id: varchar(8)
    • shippingCost: decimal
    • total: decimal
    • orderDate: datetime
    • version: tinyint
    • mac: binary(128)
    • certificateID: varchar(16)
A record inserted into such a table might look like this:

order_id 103-2423-53523
billCustomer_id 3253DXF
shipCustomer_id 3253DYG
shippingCost $4.50
total $127.33
orderDate 2009-12-1
version 1
mac 0x9EAE267D72EF9FB04436FA8CCBDA3616660FBCDA15CACB60CDD5979
0EEF3A2B746BE167C631B7D7A5540F8D80B543A60C2CE2F6167BBFD97A
E9BA84742CA2ACAC5B87AFA0FA1979E850D2174F61E22BF8DA9E67965C
73245DCCBB15723D87FA6BB4058A47137CCCEFDFF24755A849D16766512
F477AFD3F5C5EAFFBBBE400B6B
certificateID 48EEE01C000000012098


Discussion: If the record is an ordinary database table row, then the table schema is extended to include a certificate ID and a Message Authentication Code (MAC). When the record is inserted, the client first creates a "footprint" of the record--a comma-separated key=value string of the table name and all columns in the table except for "mac"--then signs it with the user's private key and stores the signature in the "mac" column. 

 The footprint for the above record would be something like this:

table=orders,billCustomer_id=3253DXF,certificateID=48EEE01C000000012098,order_id=103-2423-53523, etc...

 Where the values are first sorted by alphabetical order of column name. The table name, a version number for the table revision, and the certificate ID itself must also be included in the fingerprint to preserve the Horton Principle--validating the meaning of the message as well as its content.

 Records can then be validated at any time by re-creating the footprint based on the column values and using the user's public key to validate the MAC. 
Comments