Re: [sqlite] Proof that a line has been modified

2017-09-11 Thread Paxdo
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 8 septembre 2017 à 11:41 On Fri, Sep 8, 2017 at 12:29 AM, Nico Williams See also

Re: [sqlite] Proof that a line has been modified

2017-09-10 Thread Paxdo
Thank you Clemens! Clemens Ladisch 7 septembre 2017 à 10:34 Something like . See git or fossil, where the current state of the entire repository is identified by a hash over all data and all previous changes; once

Re: [sqlite] Proof that a line has been modified

2017-09-08 Thread Dominique Devienne
On Fri, Sep 8, 2017 at 12:29 AM, Nico Williams wrote: > > 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) > See also

Re: [sqlite] Proof that a line has been modified

2017-09-08 Thread Eric Grange
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,

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Michael Stephenson
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

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 2:47 PM, Keith Medcalf 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

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Nico Williams
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

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Keith Medcalf
ON Thursday, 7 September, 2017 10:32, Jens Alfke wrote: >> On Sep 7, 2017, at 1:38 AM, Paxdo wrote: >> But of course, someone who knows this checksum and its salt could >make changes on the table and recalculate all checksums. :-( > >Use digital signatures. I

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 11:55 AM, Igor Tandetnik wrote: > > It might be difficult to keep the private key secret. A technician that has > direct access to SQLite database file probably also has access to the binary > used to manipulate it; and that binary would need the

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Graham Holden
Original message From: Jens Alfke <j...@mooseyard.com> Date: 07/09/2017 19:32 (GMT+00:00) To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Proof that a line has been modified > On Sep 7, 2017, at 10:24 AM, Ig

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Igor Tandetnik
On 9/7/2017 2:32 PM, Jens Alfke wrote: On Sep 7, 2017, at 10:24 AM, Igor Tandetnik wrote: "Device will refuse to install" is precisely an instance of "security built in at the OS level". Yes, but that's beside the point; it wasn't the relevant part of the example. Any

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 10:24 AM, Igor Tandetnik wrote: > > "Device will refuse to install" is precisely an instance of "security built > in at the OS level". Yes, but that's beside the point; it wasn't the relevant part of the example. Any software, privileged or not, can

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Igor Tandetnik
On 9/7/2017 1:16 PM, Jens Alfke wrote: On Sep 7, 2017, at 10:06 AM, Simon Slavin wrote: In that case any solution implemented entirely within SQLite is insecure because the admins can simply replace the entire file. Or use a hex editor to replace the checksum values.

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 10:06 AM, Simon Slavin wrote: > > In that case any solution implemented entirely within SQLite is insecure > because the admins can simply replace the entire file. Or use a hex editor > to replace the checksum values. In cases like this the

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Simon Slavin
On 7 Sep 2017, at 5:34pm, Jens Alfke wrote: > On Sep 7, 2017, at 9:31 AM, Simon Slavin wrote: > >> Either way, you should be able to do something like this with UPDATE and >> DELETE TRIGGERs which causes the new command to fail. They could do this

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 9:31 AM, Simon Slavin wrote: > > Either way, you should be able to do something like this with UPDATE and > DELETE TRIGGERs which causes the new command to fail. They could do this by > violating a constraint, or by division by zero, or referring

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Jens Alfke
> On Sep 7, 2017, at 1:38 AM, Paxdo wrote: > > But of course, someone who knows this checksum and its salt could make > changes on the table and recalculate all checksums. :-( Use digital signatures. I can think of two approaches: (a) Sign each row. The program doing the

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Simon Slavin
On 7 Sep 2017, at 9:16am, 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

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Clemens Ladisch
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).

Re: [sqlite] Proof that a line has been modified

2017-09-07 Thread Paxdo
I will be able to make a checksum calculated on all the columns of the row AND on the checksum of the previous row. So if you go back up in the table and recalculate all checksums, you could check that a line has been modified or deleted. But of course, someone who knows this checksum and

[sqlite] Proof that a line has been modified

2017-09-07 Thread Paxdo
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