Recent

Author Topic: SQLite database on a shared folder  (Read 4980 times)

bpp

  • New Member
  • *
  • Posts: 16
SQLite database on a shared folder
« 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

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

Thanks


GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: SQLite database on a shared folder
« Reply #1 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.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: SQLite database on a shared folder
« Reply #2 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: SQLite database on a shared folder
« Reply #3 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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: SQLite database on a shared folder
« Reply #4 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MarkMLl

  • Hero Member
  • *****
  • Posts: 6647
Re: SQLite database on a shared folder
« Reply #5 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.
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

bpp

  • New Member
  • *
  • Posts: 16
Re: SQLite database on a shared folder
« Reply #6 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

MarkMLl

  • Hero Member
  • *****
  • Posts: 6647
Re: SQLite database on a shared folder
« Reply #7 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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

 

TinyPortal © 2005-2018