On Thu, Sep 07, 2017 at 10:16:15AM +0200, Paxdo wrote:
> For security reasons, a customer wants to be sure that a database line
> cannot be modified after its initial insertion (or unmodified without
> being visible, with proof that the line has been modified). Including
> by technicians who can open the database (SQLITE of course).
> Is there a solution to that?
You have these choices:
- hash the whole SQLite3 file and record or sign such hash values for
approved DB files (this will let you detect all changes)
- something like what you described (hash every row of every table and
bind them all somehow, then save or sign this; this too will let you
detect all changes for all tables that you apply this to)
(this is DB-agnostic)
- switch to a DB that uses a Merkle hash tree (see below)
Generally speaking, the best approach for this sort of thing is to use
something called a Merkle Hash Tree, such that for each database/
filesystem/datastore you always have available a single, small (e.g.,
256 bits) cryptographic hash value for the entire thing.
In order to make such hash values usable for this purpose you'll need
the system to be "content-addressed" storage (CAS) if at all possible
(more on that in below).
A good example of a system that comes close to this is ZFS. ZFS is a
filesystem that actually is a Merkle Hash Tree on-disk, but it's not
content-addressed, which means that if the locations of data on-disk
changes, then the root hash also changes, even if none of the actual
What this actually means in practice is that any time you have a
"pointer" from a database/filesystem/datastore page to another, what
must actually be stored is not just the address of the target page, but
the hash of its contents. If you apply this rule rigorously, and if you
have a root page (filesystems generally do, and databases can too, at
least per-table, and often for the entire DB), you necessarily end up
with a root page whose cryptographic hash *is* the cryptographic hash of
the entire DB/FS.
To get CAS you also need to not include block/page addresses in the
cryptographic hash computations (but still must include the hashes of
pointed-to pages/blocks). Then you can say that the hash of a page's
content *is* its address (it's not really).
One reason that Merkle hash trees are best is that you don't have to
read every page of a DB/FS to verify the root hash. You need only hash
the root page and you're done -- if, anyways, any errors verifying other
page hashes can be handled at run-time. Another is that they let you
compute hashes for sub-trees. Another is that they're easy to build.
SQLite3 is NOT a Merkle hash tree, however. You *can* build a Merkle
hash tree with SQLite3 though. Fossil is a version control system that
does exactly that, but that's not a technique you're likely to apply to
your use case (I'm guessing).
Given a Merkley hash tree, you can digitally sign (or save in a remote,
secure system) root hash values of approved DB/FS states. This is
interesting, for example, for secure-boot/TPM applications.
Given that you can't easily use a Merkle hash tree with SQLite3 this
without building a DB on top of a DB (like Fossil basically does) or
switching to one that uses a Merkle hash tree (and exposes the root hash
value to you), you could hash every row, XOR the hash values (since
there's no defined order for the rows, or else you can hash the
concatenation of the hashes in some order you define), and sign that.
You could apply this for every table and XOR all the table hashes, or
just those tables that are of interest to you. You'll want to do this
for all interesting rows in sqlite_master as well.
Lastly, as others have pointed out, the best you can do with a DB hash
is cryptographically prove that the FS/DB has approved content, for some
value of "approved content".
You cannot prove that the DB/FS hasn't been reset to an earlier approved
state without adding a revocation system.
Nor can you prove that the DB/FS has no malicious content in it -- only
that an approved entity signed it as "approved".
sqlite-users mailing list