For complete tamper-proofness, you need "something" external to the system, ie. not in the database, not in the code, and not on the server: - if you use signatures, then those signatures should be made by a secure signing service or device - if you use hashes (be it a merkle tree, a blockchain, or a DAG), then top-level hashes need to be stored (or signed) on another system (and verification be made against those)
Note that simple signing of the individual rows would not guard you against deletions, and if not performed by something external, the signature would provide no date/time guarantee (ie. even if the falsifier cannot access the private key, if the attacker can change the signing system time, the attacker could trick the system into creating "historical" records, thus greatly simplifying tampering). To guard against deletions you need hashes at a higher level than the row, a blockchain or DAG (like git) being the simplest solutions, ie. the hash of a row is the hmac of (data of that row + hash of previous row), storing/signing the last row hash externally then allows verifying all previous rows. Eric On Fri, Sep 8, 2017 at 1:56 AM, Michael Stephenson <domehead...@gmail.com> wrote: > In the past, I've used the pager to secure data. This involved encrypting > the data before writing the data to disk and decrypting when loading from > disk but also optionally hashing the page and storing the hash in extra > data reserved for each page. If anyone tampered with the data, the hash > would indicate this and an error could be thrown. > > Also encrypting the page data makes it more difficult to tamper with the > data. > > Products like sqlcipher do things like this (encryption, hashing), and > it's fairly easy to see how it's done by pulling the sqlite source (not the > amalgamation) and diffing it with the sqlcipher source. > > ~Mike > > > On Sep 7, 2017, at 6:34 PM, Jens Alfke <j...@mooseyard.com> wrote: > > > > > > > >> On Sep 7, 2017, at 2:47 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > >> > >> Again, this is a detection for changed data and does nothing to prevent > changes being made. > > > > The OP did not require that it be impossible to make changes (which is > clearly impossible without locking down write access to the file.) He > specifically said that detection of changed data was OK: > > > >>> 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). > > > > The procedures I described provide detection that a row has been > modified. The first one doesn't make it evident that a row has been > deleted, though the second one does. > > > > —Jens > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
