Web lists-archives.com

Re: How to set access permissions to protect a database file?

On Sat, 25 May 2019 20:49:31 -0300
Markos <markos@xxxxxxxxxx> wrote:

> Hi,
> I made a program (reading_room.tcl), with Sqlite running on Debian 9,
> to control the books of a reading room.
> I implemented an authentication system for common users and 
> administrator users in the reading_room program.
> Now I want that any user logged in the Linux be able to run the
> program reading_room.tcl, which will access the database (books.db)
> But I want to protect the file books.db so that only the the program 
> reading_room.tcl can access the books.db file. But that no user could 
> delete or write to the file books.db (only the program
> reading_room.tcl)
> Please, how can I configure the system to do that?
> How to define the permissions?

I hate to say this, but this is why database management applications
have their own self-contained authentication and permissions systems.
Filesystem controls, even with granular ACL add-ons, are not generally
adequate for database work. DBMS authentication allows access control
down to individual fields and distinguishes between database
management actions and simple data access.

This may be the time to learn about MySQL/mariadb. No, it's not a
resource hog like SQL Server, it will run happily on a small computer.
I used to run it with fairly decent speed on an Atom-based netbook,
and I had about twenty databases on a 256MB RAM desktop that I used to
use as a home server.

The other point is that if your reading room expands to multiple
computers, sqlite isn't an ideal solution for a shared database. It has
the same drawback as MS Access files in that a network glitch can break
the file. Any SQL server is designed for network use and will handle
connection problems robustly.