SQLite file security

[Satish]

Hi!



I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).



Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security

Plz provide me best solution to provide security to my database without any
Data loss.



Regards,

Satish.G

[Michael Knigge]

Post by Satish
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security

o encrypt data by yourself

o buy the properitary encryption add-on from hwaci.com

o code your appl with .NET and use the ADO-Provider (supports
encryption, see http://sqlite.phxsoftware.com/)


Bye,
Michael

[Roger Binns]

Post by Satish
Plz provide me best solution to provide security to my database without any
Data loss.

You would have to store the database on another machine over the network
that you control. The application would have to send you a request for
each record and you would need to check if the access is ok and return
the data. There is still nothing stopping something malicious on the
client machine then copying that returned data elsewhere.

What you are trying to do is the same as DRM. If an application has to
be able to actually use the data then it has to have it in clear text
form. No matter how many layers of obfuscation and encryption you use,
it will eventually have to be in a usable form. Another malicious
process will then also be able to access it.

Roger

[Marcus Grimm]

Hi,

beside the other replys:

If you design a new database application:

You may simply change the sqlite fileformat header and
recompile your sqlite library. Look into the code, it
is mentioned there how to do this.

This will avoid that other standard sqlite application will
be able to read your table without recompiling their sqlite
library.

The more secure way of course would be to purchase the
encrypted option from hwaci.

kind regards

Marcus

Post by Satish
Hi!
I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security
Plz provide me best solution to provide security to my database without any
Data loss.
Regards,
Satish.G
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[MikeW]

Post by Satish
Hi!
I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security
Plz provide me best solution to provide security to my database without any
Data loss.
Regards,
Satish.G

A possibility would be to store the file on disc in encrypted/scrambled form.
When your app starts, it loads the DB file into memory and descrambles it,
then uses SQLite on an in-memory DB.
When it closes after any DB changes it rescrambles it and writes back to disc.

Obviously anyone analysing your app would eventually be able to work out
the process ... the devil is in the detail !!

Cheers,
MikeW

[Christophe Leske]

Hi,

first i´d like to thank the people on this list that I have found to be
very helpful in the past. This list is truly great and friendly.

I got two questions today:

I have two tables, a standard one and an rtree table, which are both
linked together logically by the ID field of the rtree table.

Can I spare some bytes in my DB by defining the ID field of the standard
table as being a foreign key of the rtree table? In other words, when
defining a foreign key, is the coloumn referencing the ID field of the
foreign table and thus NOT replicating them (using a smaller memory
footprint in the file)?
Or does the table which has a foreign key still have its own ID coloumn?

And regarding Indices:
my standard table has two indices, one for the coloumn ID (since it is
being referenced from the rtree), plus another one on a coloumn which I
use for sorting the results coming from that table. Are indices also
used for sorting results, or do they do just apply for searching?

Thanks in advance,

Christophe Leske

www.multimedial.de - info-PMHQyWuJmk+KPxWhvY+***@public.gmane.org
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31

[Christophe Leske]

Hi,

some more questions...

I am using in-memory temporary tables with results sets created on the fly.

On each round, i would like to create a new table with those interims
results. Question is:

- what is quicker/better? Dropping the temporary table on every time and
recreate it from scratch? Or just deleting the entries?

problem is, the result set can have a different amount of results, thus
one time for instance it can have 200 entries, then the next time 300,
then just 50.

If I use "replace", i potentially have leftover results if I do not
clear the table before rebuilding it. Am I being clear?

[D. Richard Hipp]

Post by Christophe Leske
Hi,
some more questions...
I am using in-memory temporary tables with results sets created on the fly.
On each round, i would like to create a new table with those interims
- what is quicker/better? Dropping the temporary table on every time and
recreate it from scratch? Or just deleting the entries?

I don't know. Have you run an experiment to see for yourself?

D. Richard Hipp
drh-***@public.gmane.org

[Christophe Leske]

Post by D. Richard Hipp
I don't know. Have you run an experiment to see for yourself?

Yes, but my results are inconclusive.

Currently, i am doing this:

drop table idlookup;create temp table idlookup as select id from (select
statement for temporary result set)


Thus the statement is shorter than

create temp table if not exists idlookup (id integer);insert into
idlookup id=select id from (select statement for temporary result set)


My thought was that by using "replace" after the table has been created,
i could simply expand it (if there are more results than used to be in
the table).
This is all fine for that case, but if the new result set has LESS
results than the previous one, then I end up with a temporary table
holding the new result set and leftovers from the previous one...

[P Kishor]

Post by Christophe Leske

Post by D. Richard Hipp
I don't know. Have you run an experiment to see for yourself?

Yes, but my results are inconclusive.

would you like to share your results? If you timed two tasks,
comparing their timing would be inconclusive *only* if there was a
tie.

Post by Christophe Leske
drop table idlookup;create temp table idlookup as select id from (select
statement for temporary result set)
Thus the statement is shorter than
create temp table if not exists idlookup (id integer);insert into
idlookup id=select id from (select statement for temporary result set)

Length of statement has nothing to do with being quicker, or, for that
matter, being better.

Post by Christophe Leske
My thought was that by using "replace" after the table has been created,
i could simply expand it (if there are more results than used to be in
the table).
This is all fine for that case, but if the new result set has LESS
results than the previous one, then I end up with a temporary table
holding the new result set and leftovers from the previous one...

Why don't you just do a

DELETE FROM table;

and start inserting new results? (end result is the same as dropping
and then recreating the table, but you wouldn't know).

As I referenced in an earlier email, when in doubt, try. When try
fails, then ask, but don't worry about saving space and time when
those may not really be the constraining factors. Instead, worry about
getting your application right, that is, doing what you want it to do
without having unintended consequences such as rebooting your computer
or flushing your toilet. Once everything is working, then work slowly
and carefully to make things faster better cheaper.

Post by Christophe Leske
--
Christophe Leske
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Christophe Leske]

Post by P Kishor
Why don't you just do a
DELETE FROM table;
and start inserting new results? (end result is the same as dropping
and then recreating the table, but you wouldn't know).

I am worried about creeping memory useage, as this is in-memory.
The timing in the app is critical, not on my dev machine, but the final
application (a 3d globe done in Director using an external thread with
sqlite3.exe to query the db in memory).

It should run on lower machines.

Post by P Kishor
As I referenced in an earlier email, when in doubt, try. When try
fails, then ask, but don't worry about saving space and time when
those may not really be the constraining factors. Instead, worry about
getting your application right, that is, doing what you want it to do
without having unintended consequences such as rebooting your computer
or flushing your toilet. Once everything is working, then work slowly
and carefully to make things faster better cheaper.

It IS doing what I want. However, it does not run on lower-spec machines, which is why i am trying to optimize anything available.


Christophe Leske

www.multimedial.de - info-PMHQyWuJmk+KPxWhvY+***@public.gmane.org
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31

[P Kishor]

Post by Christophe Leske

Post by P Kishor
Why don't you just do a
DELETE FROM table;
and start inserting new results? (end result is the same as dropping
and then recreating the table, but you wouldn't know).

I am worried about creeping memory useage, as this is in-memory.
The timing in the app is critical, not on my dev machine, but the final
application (a 3d globe done in Director using an external thread with
sqlite3.exe to query the db in memory).
It should run on lower machines.

Post by P Kishor
As I referenced in an earlier email, when in doubt, try. When try
fails, then ask, but don't worry about saving space and time when
those may not really be the constraining factors. Instead, worry about
getting your application right, that is, doing what you want it to do
without having unintended consequences such as rebooting your computer
or flushing your toilet. Once everything is working, then work slowly
and carefully to make things faster better cheaper.

It IS doing what I want. However, it does not run on lower-spec machines,
which is why i am trying to optimize anything available.

ahhh... now we are getting somewhere.

Here is a suggestion... start a new thread, with a clear and relevant
subject line, describe your app succinctly, note that it works on such
and such machine, and that it fails to work on such and such machine,
describe exactly what "does not run" mean -- does it not start, does
it start and then hang, does it start and then quit, does it give an
error message, etc., describe the specs of the machine it does not
work on...

That would be very helpful to all.

Post by Christophe Leske
Christophe Leske
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31

[Christophe Leske]

Post by P Kishor
ahhh... now we are getting somewhere.
Here is a suggestion... start a new thread, with a clear and relevant
subject line, describe your app succinctly, note that it works on such
and such machine, and that it fails to work on such and such machine,
describe exactly what "does not run" mean -- does it not start, does
it start and then hang, does it start and then quit, does it give an
error message, etc., describe the specs of the machine it does not
work on...
That would be very helpful to all.

Hi,

I understand what you are saying, but again, there is no such thing
(yet). I am finishing off my application and would like to prepare it to
the best. I already got feedback that it is stalling sometimes when
running from DVD, yet the database is also fighting with another vital
part of the application, which is the tile loader (for the graphical
patches on the globe).

I can also give you my machine specs, yet my client is unable to produce
a lowest common denominator spec machine on which the app still must run.

Furthermore, I am not your average user - i use sqlite in a multimedia
authoring program called Adobe Director, and I got a code extension for
the app (a so-called Xtra), as well as an external (threaded) shell
running sqlite3.exe for repeated queries on a city database in order to
show the name of the cities currently visible. Other than that, i am on
Windows XP, yet the app is ought to run on Win2K, WinXP and Vista with a
1.6 GHz CPU, 1 GB of RAM and a 3d graphics card and DVD drive. My dev
machine is different of course (2Ghz, 2GB of RAM, Windows XP).

Does it run? Yes.
Does it run fast? For me here, yes, it does.
Does it run fast enough? Does an application ever run fast enough?

I am tweaking it to the max, also considering Dr Hipps compression
extension in order to squeeze out the maximum amount of speed available
in order to let the application run on a maximum of machines.

We are currently facing stalling of the app when loading tiles and
querying the database, especially at a mid-size altitude from earth,
where class>2 cities are being shown (class of the city = its
importance). It is a typical loading bottleneck between pulling graphics
in (threaded as well) and querying/loading database content. Since the
DVD can only load one thing at a time, the queries for the database get
apparently stalled in the thread until the DVD hits the database file
again and executes them.

I have experiemented extensively with caching, making the cache size
high, low and medium - to no avail, the sheer amount of data (the size
of the database) is to big. We have 90Mb of data. Situation got better
when we yanked class 6 "cities" (small towns) which made up 2/3 of the
database.

I am currently doing in-memory databases of the most important stuff in
order to speed up the queries and get it to work.

So all in all, i am already finetuning, yet to a target "system" which
is at my client´s site through educated guesses in the code.

[MikeW]

Christophe Leske <***@...> writes:
Any chance that you could hint at the subject matter of the questions
in the Subject line ? In this case it would appear to be Performance ...

Helps to get your questions answered !
;-)

Regards,
MikeW

[Christophe Leske]

Post by MikeW
Any chance that you could hint at the subject matter of the questions
in the Subject line ? In this case it would appear to be Performance ...
Helps to get your questions answered !
;-)

Yes, will do so. My apologies.


Christophe Leske

www.multimedial.de - info-PMHQyWuJmk+KPxWhvY+***@public.gmane.org
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31

[D. Richard Hipp]

Post by Christophe Leske
Hi,
first i´d like to thank the people on this list that I have found to be
very helpful in the past. This list is truly great and friendly.
I have two tables, a standard one and an rtree table, which are both
linked together logically by the ID field of the rtree table.
Can I spare some bytes in my DB by defining the ID field of the standard
table as being a foreign key of the rtree table? In other words, when
defining a foreign key, is the coloumn referencing the ID field of the
foreign table and thus NOT replicating them (using a smaller memory
footprint in the file)?
Or does the table which has a foreign key still have its own ID coloumn?

The ID column is replicated in each table.

Post by Christophe Leske
my standard table has two indices, one for the coloumn ID (since it is
being referenced from the rtree), plus another one on a coloumn which I
use for sorting the results coming from that table. Are indices also
used for sorting results, or do they do just apply for searching?

Indices are used for both sorting and searching or both at the same
time if applicable.


D. Richard Hipp
drh-***@public.gmane.org

[Christophe Leske]

Post by D. Richard Hipp

Post by Christophe Leske
Can I spare some bytes in my DB by defining the ID field of the standard
table as being a foreign key of the rtree table? In other words, when
defining a foreign key, is the coloumn referencing the ID field of the
foreign table and thus NOT replicating them (using a smaller memory
footprint in the file)?
Or does the table which has a foreign key still have its own ID coloumn?

The ID column is replicated in each table.

Is there a construct in sqlite which would allow a coulumn to be shared amongst tables in such a way that the data is there only once, thus creating a smaller file?

In the example provided, the iDs are exactly the same, yet they are there twice...



Christophe Leske

www.multimedial.de - info-PMHQyWuJmk+KPxWhvY+***@public.gmane.org
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31

[P Kishor]

Post by Christophe Leske

Post by D. Richard Hipp

Post by Christophe Leske
Can I spare some bytes in my DB by defining the ID field of the standard
table as being a foreign key of the rtree table? In other words, when
defining a foreign key, is the coloumn referencing the ID field of the
foreign table and thus NOT replicating them (using a smaller memory
footprint in the file)?
Or does the table which has a foreign key still have its own ID coloumn?

The ID column is replicated in each table.

Is there a construct in sqlite which would allow a coulumn to be shared amongst tables in such a way that the data is there only once, thus creating a smaller file?

No, there is no such construct in standard SQL. Every table is its own
self-standing entity.

Post by Christophe Leske
In the example provided, the iDs are exactly the same, yet they are there twice...

It precisely because the IDs are identical that allows you to join the
two tables using that column.

Unless you are working with incredibly space constrained conditions,
which, it seems you are not because you are using R-Trees, you should
not worry about saving space over one column (unless you are curious
for just, well, the sake of curiosity).




N

Post by Christophe Leske
Christophe Leske
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[John Stanton]

Post by Christophe Leske

Post by D. Richard Hipp

Post by Christophe Leske
Can I spare some bytes in my DB by defining the ID field of the standard
table as being a foreign key of the rtree table? In other words, when
defining a foreign key, is the coloumn referencing the ID field of the
foreign table and thus NOT replicating them (using a smaller memory
footprint in the file)?
Or does the table which has a foreign key still have its own ID coloumn?

The ID column is replicated in each table.

Is there a construct in sqlite which would allow a coulumn to be shared amongst tables in such a way that the data is there only once, thus creating a smaller file?
In the example provided, the iDs are exactly the same, yet they are there twice...

"Third Normal Form" data design.

Post by Christophe Leske
Christophe Leske
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kees Nuyt]

On Wed, 19 Nov 2008 12:31:22 +0530, "Satish"

Post by Satish
Hi!
I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).

The SQLite database is a normal file. The only thing that
can protect it is the security the Windows filesystem
offers. With NTFS, you have fine grained control over file
access.

Post by Satish
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security
Plz provide me best solution to provide security to my database without any
Data loss.

Use ACL. Run the application in an account that has access
to the file, Refuse access to all other accounts.

Post by Satish
Regards,
Satish.G

[S***@public.gmane.org]

I think the question by the original poster was about data security and
not file security. He probably does not mind if the file is copied, but
the data should be decipherable only by his application.

-Shibu

-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of Kees Nuyt
Sent: Thursday, November 20, 2008 2:17 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite file security

On Wed, 19 Nov 2008 12:31:22 +0530, "Satish"

Post by Satish
Hi!
I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).

The SQLite database is a normal file. The only thing that
can protect it is the security the Windows filesystem
offers. With NTFS, you have fine grained control over file
access.

Post by Satish
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security
Plz provide me best solution to provide security to my database without any
Data loss.

Use ACL. Run the application in an account that has access
to the file, Refuse access to all other accounts.

Post by Satish
Regards,
Satish.G

--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


DISCLAIMER:
This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version.

[Timothy A. Sawyer]

I agree with that. However if I were writing such an application I would
worry about folks copying the database and possibly rendering the database
useless.

There are a few things that I would recommend.

To keep others from reading the database you will have to write some sort of
encryption routine into your application that uses a symmetric key - I would
recommend something that uses AES since this is the adopted standard,
however there are plenty of reasonable encryption routines available in the
public domain. Either the user has to type a password to unlock the key and
decrypt the database or you will have to store the key in a hash file that
is read when the application is open. I recommend that you get a copy of
"Applied Cryptography" by Bruce Schneier for more information on this
subject.

This will not prevent anyone from opening the encrypted database, however,
so you have to take care that your NTFS file permissions grant access to
only those authorized to access the database. Otherwise you would subject
the database to denial of service attacks by means of editing the encrypted
database and possibly corrupting it.

Bear in mind that no security controls are foolproof. If someone really
wants access, they are going to get it. All you are trying to do is make it
costly for potential attackers to access your data to a point where they
consider that the cost outweighs the value.

Timothy A. Sawyer, CISSP
Managing Director
MBD Solutions
Phone: [phone]
Web: http://www.mybowlingdiary.com
Email: tsawyer-***@public.gmane.org

-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of
Shibu.Narayanan-JciXlfA5DopCkLs28/***@public.gmane.org
Sent: Thursday, November 20, 2008 2:17 AM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] SQLite file security

I think the question by the original poster was about data security and
not file security. He probably does not mind if the file is copied, but
the data should be decipherable only by his application.

-Shibu

-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of Kees Nuyt
Sent: Thursday, November 20, 2008 2:17 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite file security

On Wed, 19 Nov 2008 12:31:22 +0530, "Satish"

Post by Satish
Hi!
I am basically a windows application developer. I am developing an
application for desktop which uses a database. I choose SQLite as my
database and my issue is if any one finds my application is using SQLite
database they can corrupt my database or they can see the contents of my
database using a program(they can open my SQLite file).

The SQLite database is a normal file. The only thing that
can protect it is the security the Windows filesystem
offers. With NTFS, you have fine grained control over file
access.

Post by Satish
Now my question is how I can provide security to my database for
example no one can access my database except my application .how can I
provide security
Plz provide me best solution to provide security to my database without any
Data loss.

Use ACL. Run the application in an account that has access
to the file, Refuse access to all other accounts.

Post by Satish
Regards,
Satish.G

--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


DISCLAIMER:
This message contains privileged and confidential information and is
intended only for an individual named. If you are not the intended
recipient, you should not disseminate, distribute, store, print, copy or
deliver this message. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive late
or incomplete or contain viruses. The sender, therefore, does not accept
liability for any errors or omissions in the contents of this message which
arise as a result of e-mail transmission. If verification is required,
please request a hard-copy version.

[Kees Nuyt]

On Thu, 20 Nov 2008 12:47:26 +0530,

Post by S***@public.gmane.org
I think the question by the original poster was about data security and
not file security. He probably does not mind if the file is copied, but
the data should be decipherable only by his application.

Post by Satish
my issue is if any one finds my application is using
SQLite database they can [1] corrupt my database
or they can [2] see the contents of my database using
a program(they can open my SQLite file).

So, he has two issues. The second issue was discussed a lot
already.

My point was about [1]: anyone with write access to the
databasefile can corrupt it, encrypted or not.

Post by S***@public.gmane.org
-Shibu