Re: [sqlite] Make a database read-only?

[James K. Lowden]

On Tue, 14 Oct 2014 18:21:27 -0400
Ross Altman  wrote:

> Yeah, that's actually a really good point. Oh well, I guess I'll just
> have to hope that people decide to use the database responsibly...
> haha

You can advertise your database with the tagline, "Please compute
responsibly".  

The first rule of security is that there's no security without physical
security.  If you don't control the physical thing itself -- usually
hardware, but in this case a file -- then you don't control what
can be done with it.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Stephen Chrzanowski]

I've got three options, two of which require an internet connection, one
part time, the other full time.  The third option has the constraint on the
size of the data in question.

- Have your preference of a resultant hash check in a plain text file
sitting somewhere on your web server.  The application pokes the server at
each run to verify the hash against the physical hash of the DB.  If a
missmatch is found, force a download.  This will make sure your end users
have the most up to date data, as well as protect your primary criteria of
keeping the data 'read-only' at application run, however, an internet
connection would be required at least for the initial check.

- Have your application query a database on your server via web or socket
protocols instead of relying on the flat file.  This becomes bandwidth
heavy, and the end user machine requires an internet connection for the
life of your applications running lifespan on the client computer

- If on Windows (I'm not sure if other OS compilers have the capabilities)
Build the database into your software via a resource file.
-- Depending on the IDE, you can have the compiler automatically create a
resource file that'll be put directly into your compiled code, and it'll
build that resource file on a full build, or, on an if-needed basis.
-- Run an MD5 (Or whatever your pref is) against the physical file being
built into the EXE
-- At run time, if the database doesn't exist or if the MD5 check fails,
dump the resource file back out to the physical file.
- The downside of this is that you'll be retransmitting the application
each time, BUT, it'll guarantee that your data is consistent based on the
build of your application.
- Implementation of the checks and balances would be new code, but, only
need to be built for the applications startup.  Once the physical file is
placed down and is valid, your existing routines will work (Provided you're
closing the DB properly, writing out the database file, then re-opening the
database against the same variables)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Yeah, that's actually a really good point. Oh well, I guess I'll just have
to hope that people decide to use the database responsibly... haha

Best,
Ross

On Tue, Oct 14, 2014 at 2:57 PM, Jungle Boogie <jungleboog...@gmail.com>
wrote:

> Dear Ross,
> 
> From: Ross Altman <altman...@husky.neu.edu>
> Sent:  Tue, 14 Oct 2014 14:38:41 -0400
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Make a database read-only?
> >
>
>> Thanks everyone for the helpful answers. Here's some context:
>>
>> The database I have is for academic purposes. Research groups will need to
>> be able to download it in order to do large-scale scans using it as input,
>> so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
>> someone else to take it, add to it, and put it online somewhere else, so
>> that there are multiple versions floating around the web. I don't mind if
>> there are multiple COPIES, but I want to make sure that they're all the
>> same.
>>
>>
> To a degree this sounds like the Streisand effect:
> https://en.wikipedia.org/wiki/Streisand_effect
>
>
> Pasting the sha256 is your best bet, but anyone who's going to verify the
> sha256 can already dump the database as explained by Clemens Ladisch.
>
>  Thanks again,
>> Ross
>>
>>
>
> --
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> xmpp: jungle-boo...@jit.si
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Jungle Boogie]

Dear Ross,

From: Ross Altman <altman...@husky.neu.edu>
Sent:  Tue, 14 Oct 2014 14:38:41 -0400
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Make a database read-only?
>

Thanks everyone for the helpful answers. Here's some context:

The database I have is for academic purposes. Research groups will need to
be able to download it in order to do large-scale scans using it as input,
so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
someone else to take it, add to it, and put it online somewhere else, so
that there are multiple versions floating around the web. I don't mind if
there are multiple COPIES, but I want to make sure that they're all the
same.



To a degree this sounds like the Streisand effect:
https://en.wikipedia.org/wiki/Streisand_effect


Pasting the sha256 is your best bet, but anyone who's going to verify the 
sha256 can already dump the database as explained by Clemens Ladisch.



Thanks again,
Ross




--
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 2:38 PM, Ross Altman wrote:

I don't mind if there are multiple COPIES, but I want to make sure that they're 
all the
same.


Well, you can't, really. If nothing else, whoever has read access to the 
database can read all the data out, then create a new database of their 
own, insert all that data into it (altered to taste), then publish it on 
their site (with the same file name as yours).


The best you can do, from technical standpoint, is publish it on your 
site, complete with an MD5 hash or similar, and encourage the community 
to always get it from the "official" source (or at least, check the hash 
against the known-good one).


From non-technical standpoint, there might be options for legal 
enforcement against unauthorized copies - but I'm not a lawyer and would 
not speculate on this topic. Consult your lawyer if you are interested 
in going down this path.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Thanks everyone for the helpful answers. Here's some context:

The database I have is for academic purposes. Research groups will need to
be able to download it in order to do large-scale scans using it as input,
so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
someone else to take it, add to it, and put it online somewhere else, so
that there are multiple versions floating around the web. I don't mind if
there are multiple COPIES, but I want to make sure that they're all the
same.

Thanks again,
Ross

On Tue, Oct 14, 2014 at 12:16 PM, Igor Tandetnik  wrote:

> On 10/14/2014 10:12 AM, John Hascall wrote:
>
>> Some code you may find useful to enforce the readonly byte
>>
>
> Of course, anyone smart enough to change the byte from read-only to
> read-write before making changes, would also be smart enough to set it back
> afterwards.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 10:12 AM, John Hascall wrote:

Some code you may find useful to enforce the readonly byte


Of course, anyone smart enough to change the byte from read-only to 
read-write before making changes, would also be smart enough to set it 
back afterwards.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Andrea Peri]

Against legal. The best approach is to calculate the MD5 of the file
If the file chance, the MD5 change Aldo.
 Il 14/ott/2014 15:37 "RSmith"  ha scritto:

>
> On 2014/10/14 13:48, Ross Altman wrote:
>
>> Hi Martin,
>>
>> Thank you, I'll definitely look into that. It's unfortunate that there
>> isn't a simpler way to do this... oh well.
>>
>
> Let me bud in here since I encounter this question a lot in other matters.
> There typically are three reasons one would like to protect the data in a
> file from end-users' meddling:
>   - You need to protect idiot users against themselves,
>   - You need the data to remain clean and untarnished to make some other
> system depending on it function correctly, or
>   - The data itself is important for legal reasons or you have some kind
> of liability towards data accuracy.
>
> If it is the first case, then you are stuffed and Richard's byte-change is
> the closest to a solution you can come.
>
> If the second case, then make the other system check the file, add table
> with encrypted values that has meaning only to the other system, or even
> use file encryption for the entire database - this is common and can be had
> commercially from http://www.hwaci.com/sw/sqlite/see.html
>
> For the latter I suggest recording the file hash (sha512+) whenever you
> update it and store that in a data list marking release dates. That way if
> someone claims that they have data gotten from you that says x while you
> claim it says y...  then simply whip out the hash list and compare to their
> file, any changes will be evident immediately.
>
> You probably need to then also keep a register history of DBs that
> correspond to those hashes, else you cannot prove the data from that file
> to correspond to any specific hash. Also it is safer to upload such hashes
> to a blog or something that is not under your control, where any edits will
> be marked and timestamped, then it is impossible for yourself to meddle
> with the files after release and a public record exists of the file version
> hashes. Pretty solid in legal terms.
>
> Whichever way, good luck!
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 2:19 AM, Ross Altman wrote:

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?


Why do you care what a person does with a file on their hard drive in 
the privacy of their home? In any case, you can't really stop them from 
doing whatever they want with their own file, even if that file started 
life as a copy of yours.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[John Hascall]

Well some keystroke I hit apparently made the stupid browser send that
before I finished editing it, but you get the idea


Sigh,
John

On Tue, Oct 14, 2014 at 9:12 AM, John Hascall  wrote:

> Some code you may find useful to enforce the readonly byte (do this before
> your program opens the DB).
> John
>
> #include 
> #include 
> #define
>
> int setRObyte (
> const char * sqDBfn
> ) {
> int fd  = open(sqDBfn, O_WRONLY, 0);
> int rc  = -1;
>
> if (fd == -1) return -1;
> #ifdef  HAVE_PWRITE
> if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
> #else
> if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
> rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
> } else rc = -1;
> #endif
> (void)close(fd);
> return rc;
> }
>
> On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp  wrote:
>
>> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
>> wrote:
>>
>> > I need to host a fixed, unchanging database online, and I want to make
>> sure
>> > that anyone who downloads it cannot add to it. Is there any way to set
>> the
>> > permissions to be read-only within sqlite?
>> >
>>
>> Change the 18th byte of the file from 1 or 2 to 99.
>>
>> Anyone who downloads the file can always change that byte back to its
>> original value using a binary editor and then write the database.  But you
>> have at least then made the problem more difficult for them.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[John Hascall]

Some code you may find useful to enforce the readonly byte (do this before
your program opens the DB).
John

#include 
#include 
#define

int setRObyte (
const char * sqDBfn
) {
int fd  = open(sqDBfn, O_WRONLY, 0);
int rc  = -1;

if (fd == -1) return -1;
#ifdef  HAVE_PWRITE
if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
#else
if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
} else rc = -1;
#endif
(void)close(fd);
return rc;
}

On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp  wrote:

> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
> wrote:
>
> > I need to host a fixed, unchanging database online, and I want to make
> sure
> > that anyone who downloads it cannot add to it. Is there any way to set
> the
> > permissions to be read-only within sqlite?
> >
>
> Change the 18th byte of the file from 1 or 2 to 99.
>
> Anyone who downloads the file can always change that byte back to its
> original value using a binary editor and then write the database.  But you
> have at least then made the problem more difficult for them.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[RSmith]

On 2014/10/14 13:48, Ross Altman wrote:

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.


Let me bud in here since I encounter this question a lot in other matters. There typically are three reasons one would like to 
protect the data in a file from end-users' meddling:

  - You need to protect idiot users against themselves,
  - You need the data to remain clean and untarnished to make some other system 
depending on it function correctly, or
  - The data itself is important for legal reasons or you have some kind of 
liability towards data accuracy.

If it is the first case, then you are stuffed and Richard's byte-change is the 
closest to a solution you can come.

If the second case, then make the other system check the file, add table with encrypted values that has meaning only to the other 
system, or even use file encryption for the entire database - this is common and can be had commercially from 
http://www.hwaci.com/sw/sqlite/see.html


For the latter I suggest recording the file hash (sha512+) whenever you update it and store that in a data list marking release 
dates. That way if someone claims that they have data gotten from you that says x while you claim it says y...  then simply whip out 
the hash list and compare to their file, any changes will be evident immediately.


You probably need to then also keep a register history of DBs that correspond to those hashes, else you cannot prove the data from 
that file to correspond to any specific hash. Also it is safer to upload such hashes to a blog or something that is not under your 
control, where any edits will be marked and timestamped, then it is impossible for yourself to meddle with the files after release 
and a public record exists of the file version hashes. Pretty solid in legal terms.


Whichever way, good luck!
Ryan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Clemens Ladisch]

Ross Altman wrote:
> I need to host a fixed, unchanging database online,

An SQLite database is just a file.

> and I want to make sure that anyone who downloads it cannot add to it.

It's possible to run "sqlite3 thedata.db .dump > thedata.sql" and then
to remove any protection that is still present with a text editor.

What are you actually trying to protect against?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Richard Hipp]

On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
wrote:

> I need to host a fixed, unchanging database online, and I want to make sure
> that anyone who downloads it cannot add to it. Is there any way to set the
> permissions to be read-only within sqlite?
>

Change the 18th byte of the file from 1 or 2 to 99.

Anyone who downloads the file can always change that byte back to its
original value using a binary editor and then write the database.  But you
have at least then made the problem more difficult for them.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Martin Engelschalk]

Hi Ross,

i don't know if there is a simpler way. Perhaps someone on the list has 
a better idea. What I wanted to say is that you probanly cannot prevent 
your downloaders from removing anything you add to make the data read-only.


Martin.


Am 14.10.2014 13:48, schrieb Ross Altman:

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.

Best,
Ross

On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:


Hello Ross,

you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on
all operations (insert, update and delete), see
http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
However, it is difficult to see how to stop the downloaders from removing
these triggers or indeed any other mechanism to prevent changes to the
data. It is their file after download

HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:


I need to host a fixed, unchanging database online, and I want to make
sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

Thanks,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.

Best,
Ross

On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hello Ross,
>
> you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on
> all operations (insert, update and delete), see
> http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
> However, it is difficult to see how to stop the downloaders from removing
> these triggers or indeed any other mechanism to prevent changes to the
> data. It is their file after download
>
> HTH
> Martin
>
> Am 14.10.2014 08:19, schrieb Ross Altman:
>
>> I need to host a fixed, unchanging database online, and I want to make
>> sure
>> that anyone who downloads it cannot add to it. Is there any way to set the
>> permissions to be read-only within sqlite?
>>
>> Thanks,
>> Ross
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
>
> *Codeswift GmbH *
> Kräutlerweg 20a
> A-5020 Salzburg
> Tel: +49 (0) 8662 / 494330
> Mob: +49 (0) 171 / 4487687
> Fax: +49 (0) 3212 / 1001404
> engelsch...@codeswift.com
> www.codeswift.com / www.swiftcash.at
>
> Codeswift Professional IT Services GmbH
> Firmenbuch-Nr. FN 202820s
> UID-Nr. ATU 50576309
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Martin Engelschalk]

Hello Ross,

you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') 
on all operations (insert, update and delete), see 
http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
However, it is difficult to see how to stop the downloaders from 
removing these triggers or indeed any other mechanism to prevent changes 
to the data. It is their file after download


HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

Thanks,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users