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:
- 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:
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:
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.