I just got all the answers! I must have an email problem.
I'm going to look at all of this, thank you very much everyone!
> Dominique Devienne <mailto:[hidden email]>
> 8 septembre 2017 à 11:41
> On Fri, Sep 8, 2017 at 12:29 AM, Nico Williams <[hidden email]>
> See also https://sqlite.org/dbhash.html#overview --DD
> sqlite-users mailing list
> [hidden email]
> Nico Williams <mailto:[hidden email]>
> 8 septembre 2017 à 00:29
> 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
> data changed.
> 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".
> Paxdo <mailto:[hidden email]>
> 7 septembre 2017 à 10:16
> Hi all!
> 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?
> I thought of a hash calculated and recorded only when the line was
> inserted (calculated on all columns of the line, and stored in a
> column of that line).
> Or a trigger that would prevent any modification.
> But these solutions can be easily bypassed, right?
> Do you have any advice?
> Thank you!
> (Sorry for my bad english)
> sqlite-users mailing list
> [hidden email]
sqlite-users mailing list
|Free forum by Nabble||Edit this page|