Lazarus

Programming => Databases => Topic started by: bpp on July 02, 2020, 08:35:51 pm

Title: SQLite database on a shared folder
Post by: bpp on July 02, 2020, 08:35:51 pm
Hi

I have a CRUD project that use an SQLite3 database.

In a first approach, I had the database on my computer's local disk, because it was only for single-user access.

Now, I need to share the database with acouple users. I put the database file in a shared folder. Only one user can write, and the other users connect the db in a read-only mode.

For now, I had no problems with that, and the software runs fine. Is it safe use the database in a shared folder? and be used by multiple users? (only one can write, and the others can only read)

https://www.sqlite.org/faq.html#q5 (https://www.sqlite.org/faq.html#q5)

I just want to use a client-server engine, if the database shared in the folder is not secure enough.

Thanks

Title: Re: SQLite database on a shared folder
Post by: GAN on July 02, 2020, 11:18:30 pm
Hi,
I have a SQlite database in a shared folder (on a server) with 5 Read/Write users. No problem.
As for security, you have the limitations of SQlite.
Title: Re: SQLite database on a shared folder
Post by: Zvoni on July 03, 2020, 10:43:49 am
SQLite is "unsecure" out-of-the-box (as in: No User/PW-Authentication and/or encryption).
So if you share the db-file on a shared network-folder, anyone who has a client for SQLite (e.g. DB Browser for SQLite), is able to open it "directly" (and manipulate it), given that that user has access to that folder.

IIRC, SQLite has a authentication-system in place, but you would have to compile it yourself to include that option.
No idea about encryption. Never used it.

If a Database transitions from "single-user" to "multi-user", i'd really think about using a "real" db-server (e.g. MySQL, PG, FB), especially if you need parallel read/write-access
Title: Re: SQLite database on a shared folder
Post by: mangakissa on July 03, 2020, 01:31:36 pm
Quote from: zvoni
anyone who has a client for SQLite (e.g. DB Browser for SQLite), is able to open it "directly" (and manipulate it)....
....If a Database transitions from "single-user" to "multi-user", i'd really think about using a "real" db-server (e.g. MySQL, PG, FB), especially if you need parallel read/write-access
The database are also to manipulate. The only thing different is user access. SQLite hasn't.
Title: Re: SQLite database on a shared folder
Post by: Zvoni on July 03, 2020, 02:55:57 pm
The database are also to manipulate. The only thing different is user access. SQLite hasn't.
But it does, just not out of the box.
You have to compile it yourself.
https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt
Title: Re: SQLite database on a shared folder
Post by: MarkMLl on July 03, 2020, 04:28:36 pm
If a Database transitions from "single-user" to "multi-user", i'd really think about using a "real" db-server (e.g. MySQL, PG, FB), especially if you need parallel read/write-access

I agree. A database using shared files ** is a disaster waiting to happen, and is particularly inexcusable now that there are free servers with proven reliability (PostgreSQL etc.).

MarkMLl

** Specifically, multiple database "engines" or libraries sharing files, possibly over a network, rather than a single server designed from scratch to support multiple simultaneous clients.
Title: Re: SQLite database on a shared folder
Post by: bpp on July 03, 2020, 08:25:13 pm
A client-server database management system will be the best choice, no doubt about it...

but looking at the software requirements, is it your opinion the same?
- no authentication needed
- only one user will be able to write
- two/three users at same time, but read-only (some select sql querys, and export them to a pdf using lazreport)
- shared network folder/db

The software is already developed and working,
will it be susceptible to data integrity problems?  or is it safe in that requirement?

thanks for your answers
Title: Re: SQLite database on a shared folder
Post by: MarkMLl on July 03, 2020, 09:53:09 pm
I use PostgreSQL. When I had to make a decision as to which server to use (a bit more than 20 years ago) there were very few choices unless you were prepared to slap a lot of money on the table and have IBM (or whoever) dictate to you what sort of hardware you had to be running.

Before that I had been involved in the sales/support of various database systems implemented as libraries (and sometimes, in the case of DOS, as TSRs) but all of them- without exception- were very wary of multiuser operation.

Between those points there were things like Borland Paradox, which tried to implement its own locking but would fairly regularly have problems. And- obviously- MS Access which is regularly maligned for its flakiness particularly when multiple users are involved.

I remember somebody saying that SQLite did have locking capabilities, and if that's the case then it /might/ be OK. However whatever anybody says in that sort of situation you're "trying to prove a negative": running it in a situation for which it's not been designed, and while most of the time it might be OK you have absolutely no way of knowing whether you're about to hit something which will make it fall over.

Single-write-multiple-read /should/ be OK, but a "real" server has lots of provision for transaction isolation which I bet something like SQLite doesn't bother with: can you be absolutely certain that a part-updated table will never be read?

As an absolute minimal precaution I think I'd try to implement some sort of application level token, so that one designated system can be started with write access and none of the others can claim that privilege. You /should/ get away with it... but even if you're talking for something for home use you'd better document that that protection must never under any circumstances be relaxed: I'm sure that everybody here has seen a cash-strapped business looking for ways to bodge things and having somebody comment out the single-write restriction is definitely not something you want to happen.

MarkMLl
TinyPortal © 2005-2018