How to set access permissions to protect a database file?

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

How to set access permissions to protect a database file?

Markos
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.tcl 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 (to read or write). 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?

Thanks,

Markos

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Luuk

On 26-5-2019 01:49, Markos 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.tcl 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 (to read or write). 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?
>
> Thanks,
>
> Markos
>
If you have read, and used, this:
https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt than you
should know the answer ;)

Otherwise set access permissions on the database  (use: 'man chmod'
and/or 'man chown', to find out how to do that under Debian 9)

If a user has no right to modify the db, and tries to do an update, an
error is returned from sqlite:

sqlite> insert into User values(42);
Error: attempt to write a readonly database



> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Adrian Ho
In reply to this post by Markos
On 26/5/19 7:49 AM, Markos wrote:

> 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.tcl 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 (to read or write). But
> that no user could delete or write to the file books.db (only the
> program reading_room.tcl)

The standard Unix permissions/ACLs architecture doesn't support this use
case directly. A relatively simple and bulletproof way to achieve what
you want is to use sudo to get everyone running reading_room.tcl as a
separate (non-login) user.

As root, run "useradd reading_room", then "visudo" to add the following
line to /etc/sudoers:

ALL    ALL = (reading_room) /path/to/reading_room.tcl

Then, "chown reading_room /path/to/books.db" and "chmod 600
/path/to/books.db" to ensure that only user "reading_room" can access
the DB.

Finally, create a "reading_room" script that your users will run:

#!/usr/bin/env bash

sudo -u reading_room /path/to/reading_room.tcl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Adrian Ho
In reply to this post by Luuk

On 26/5/19 5:26 PM, Luuk wrote:

>
> On 26-5-2019 01:49, Markos wrote:
>> 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 (to read or write). But
>> that no user could delete or write to the file books.db (only the
>> program reading_room.tcl)
> If you have read, and used, this:
> https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt than
> you should know the answer ;)

WARNING: The documentation seems to suggest that an SQLite library/shell
compiled without SQLITE_USER_AUTHENTICATION still has full access to the
DB. A quick build and test seems to confirm this:

$ ./sqlite3_with_user_auth ~/tmp/test_userauth.db3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .user help
Usage: .user login|add|edit|delete ...
sqlite> .user add aho testing yes
sqlite> create table test(x int);
sqlite> insert into test values (1);
sqlite> select * from test;
1
sqlite>

Now let's see if an SQLite shell that doesn't do user auth can muck with
this DB:

$ sqlite3 ~/tmp/test_userauth.db3

sqlite> .user help
Error: unknown command or invalid arguments:  "user". Enter ".help" for help
sqlite> .schema
CREATE TABLE sqlite_user(
  uname TEXT PRIMARY KEY,
  isAdmin BOOLEAN,
  pw BLOB
) WITHOUT ROWID;
CREATE TABLE test(x int);
sqlite> select * from sqlite_user;
aho|1|$▒i����P}▒�m��
sqlite> select * from test;
1
sqlite> insert into test values (2);
sqlite> select * from test;
1
2

Uh oh...

> Otherwise set access permissions on the database  (use: 'man chmod'
> and/or 'man chown', to find out how to do that under Debian 9)

To give *any* user access to the DB *only* via reading_room.tcl, as the
OP requested, access permissions aren't sufficient by themselves. You'd
also need to force all users to run reading_room.tcl as the DB's owner,
i.e. something like "sudo".

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Graham Holden
In reply to this post by Adrian Ho
You should probably also make sure that users cannot alter the tcl
file through which they access the database file; probably something
like:

chown reading_room /path/to/reading_room.tcl
chmod 644 /path/to/reading_room.tcl

(It's possible that you will also need execute permission on the file,
in which case change "644" to "755").

Graham

Sunday, May 26, 2019, 12:52:29 PM, Adrian Ho <[hidden email]> wrote:

> On 26/5/19 7:49 AM, Markos wrote:
>> 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.tcl 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 (to read or write). But
>> that no user could delete or write to the file books.db (only the
>> program reading_room.tcl)

> The standard Unix permissions/ACLs architecture doesn't support this use
> case directly. A relatively simple and bulletproof way to achieve what
> you want is to use sudo to get everyone running reading_room.tcl as a
> separate (non-login) user.

> As root, run "useradd reading_room", then "visudo" to add the following
> line to /etc/sudoers:

> ALL    ALL = (reading_room) /path/to/reading_room.tcl

> Then, "chown reading_room /path/to/books.db" and "chmod 600
> /path/to/books.db" to ensure that only user "reading_room" can access
> the DB.

> Finally, create a "reading_room" script that your users will run:

> #!/usr/bin/env bash

> sudo -u reading_room /path/to/reading_room.tcl



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Luuk
In reply to this post by Adrian Ho

On 26-5-2019 13:52, Adrian Ho wrote:

> On 26/5/19 7:49 AM, Markos wrote:
>> 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.tcl 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 (to read or write). But
>> that no user could delete or write to the file books.db (only the
>> program reading_room.tcl)
> The standard Unix permissions/ACLs architecture doesn't support this use
> case directly.

Can you give some more information on this, because it seems to work as
i excpect it to:

Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
users are in the group 'users':

luuk2@opensuse1:/home/luuk/temp> whoami
luuk2
luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
-r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> select * from test;
1
2
sqlite> insert into test values(3);
Error: attempt to write a readonly database
sqlite> .q
luuk2@opensuse1:/home/luuk/temp>




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

James K. Lowden
In reply to this post by Adrian Ho
On Sun, 26 May 2019 19:52:29 +0800
Adrian Ho <[hidden email]> wrote:

> Finally, create a "reading_room" script that your users will run:
>
> #!/usr/bin/env bash
>
> sudo -u reading_room /path/to/reading_room.tcl

This script is more efficient and portable:

#! /bin/sh
sudo -u reading_room /path/to/reading_room.tcl

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Adrian Ho
In reply to this post by Luuk
On 27/5/19 12:43 AM, Luuk wrote:

>
> On 26-5-2019 13:52, Adrian Ho wrote:
>> On 26/5/19 7:49 AM, Markos wrote:
>>> 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.tcl 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 (to read or write). But
>>> that no user could delete or write to the file books.db (only the
>>> program reading_room.tcl)
>> The standard Unix permissions/ACLs architecture doesn't support this use
>> case directly.
>
> Can you give some more information on this, because it seems to work
> as i excpect it to:
>
> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> users are in the group 'users':
>
> luuk2@opensuse1:/home/luuk/temp> whoami
> luuk2
> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from test;
> 1
> 2
> sqlite> insert into test values(3);
> Error: attempt to write a readonly database
> sqlite> .q
> luuk2@opensuse1:/home/luuk/temp>
>
The OP wants *all users* to be able to update (write) the DB via the Tcl
script reading_room.tcl, but *not* by (say) running the SQLite shell or
something else. In your setup, as long as a specific user has write
permissions, *every program* the user runs can write to the DB.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Adrian Ho
In reply to this post by Graham Holden
On 26/5/19 9:10 PM, Graham Holden wrote:
> You should probably also make sure that users cannot alter the tcl
> file through which they access the database file; probably something
> like:
>
> chown reading_room /path/to/reading_room.tcl
> chmod 644 /path/to/reading_room.tcl

Good point. In fact, since most Linux distros (including Debian, I
think) create a dedicated eponymous group by default for each new user,
this can be tightened further:

chown reading_room /path/to/reading_room.tcl
chgrp aho /path/to/reading_room.tcl
chmod 560 /path/to/reading_room.tcl

That leaves me able to edit the script without fancy sudo footwork,
while ensuring that everyone (including me) needs to sudo as user
"reading_room" to actually run it.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Adrian Ho
In reply to this post by James K. Lowden
On 27/5/19 4:51 AM, James K. Lowden wrote:

> On Sun, 26 May 2019 19:52:29 +0800
> Adrian Ho <[hidden email]> wrote:
>
>> Finally, create a "reading_room" script that your users will run:
>>
>> #!/usr/bin/env bash
>>
>> sudo -u reading_room /path/to/reading_room.tcl
> This script is more efficient and portable:
>
> #! /bin/sh
> sudo -u reading_room /path/to/reading_room.tcl
True, though the space after your shebang reminded me of this:
https://www.in-ulm.de/~mascheck/various/shebang/#blankrequired
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Adrian Ho
On 27 May 2019, at 3:33am, Adrian Ho <[hidden email]> wrote:

> The OP wants *all users* to be able to update (write) the DB via the Tcl script reading_room.tcl, but *not* by (say) running the SQLite shell or something else. In your setup, as long as a specific user has write permissions, *every program* the user runs can write to the DB.

Some work in this thread has been because the file is a SQLite database.  But it's not really a SQLite question.  It's more about the access/permissions model of Debian 9.  What OP wants can be reduced to a simpler situation:

"I have a text file.  It's on a computer running Debian 9.  I want to make sure that this text file can be read/written by multiple people, but that it can be read/written only using this program I wrote."

I've never used Debian so I can't solve the problem.  But from what little I remember of Linux, one solution is to create a special account for that one operation, and set up that account in a non-standard way.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Damon
On 5/27/19 9:16 AM, Simon Slavin wrote:
> On 27 May 2019, at 3:33am, Adrian Ho <[hidden email]> wrote:
>
>> The OP wants *all users* to be able to update (write) the DB via the Tcl script reading_room.tcl, but *not* by (say) running the SQLite shell or something else. In your setup, as long as a specific user has write permissions, *every program* the user runs can write to the DB.
> Some work in this thread has been because the file is a SQLite database.  But it's not really a SQLite question.  It's more about the access/permissions model of Debian 9.  What OP wants can be reduced to a simpler situation:
>
> "I have a text file.  It's on a computer running Debian 9.  I want to make sure that this text file can be read/written by multiple people, but that it can be read/written only using this program I wrote."
>
> I've never used Debian so I can't solve the problem.  But from what little I remember of Linux, one solution is to create a special account for that one operation, and set up that account in a non-standard way.

Actually, It can be an SQLite question, as another way to solve the base
problem is to do something to the database so that only 'authorized'
applications can access/modify it. One way to do that is to use the SEE
extension and encrypt the database. If you do that then you no longer
have the equivalent of a 'text file', so unauthorized applications can't
access the file.

It isn't perfect protection, because someone still will have the ability
to delete/overwrite the file, to protect from that seems to need the
protection method you describe, but if you are only trying to protect
against Murphy, and not Machiavelli (as the saying goes) it may be a
viable, and portable, solution.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Peter da Silva-2
This is what the UNIX group ID and the set-group-id capability is for.

You make the files readable (or read and write) by a group (mode 640 or
660).

You make the program that controls access to the files set-group-id to that
group.

You may need a small "C" wrapper program to run the script set-group-id
depending on the version of UNIX and security settings. Allowing scripts to
run set-group-id has at times been violently controversial, and I have not
bothered to track teh state of teh art.

On Mon, May 27, 2019 at 8:37 AM Richard Damon <[hidden email]>
wrote:

> On 5/27/19 9:16 AM, Simon Slavin wrote:
> > On 27 May 2019, at 3:33am, Adrian Ho <[hidden email]> wrote:
> >
> >> The OP wants *all users* to be able to update (write) the DB via the
> Tcl script reading_room.tcl, but *not* by (say) running the SQLite shell or
> something else. In your setup, as long as a specific user has write
> permissions, *every program* the user runs can write to the DB.
> > Some work in this thread has been because the file is a SQLite
> database.  But it's not really a SQLite question.  It's more about the
> access/permissions model of Debian 9.  What OP wants can be reduced to a
> simpler situation:
> >
> > "I have a text file.  It's on a computer running Debian 9.  I want to
> make sure that this text file can be read/written by multiple people, but
> that it can be read/written only using this program I wrote."
> >
> > I've never used Debian so I can't solve the problem.  But from what
> little I remember of Linux, one solution is to create a special account for
> that one operation, and set up that account in a non-standard way.
>
> Actually, It can be an SQLite question, as another way to solve the base
> problem is to do something to the database so that only 'authorized'
> applications can access/modify it. One way to do that is to use the SEE
> extension and encrypt the database. If you do that then you no longer
> have the equivalent of a 'text file', so unauthorized applications can't
> access the file.
>
> It isn't perfect protection, because someone still will have the ability
> to delete/overwrite the file, to protect from that seems to need the
> protection method you describe, but if you are only trying to protect
> against Murphy, and not Machiavelli (as the saying goes) it may be a
> viable, and portable, solution.
>
> --
> Richard Damon
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Markos
In reply to this post by Adrian Ho
Many thanks to Luuk, Adrian, Graham, James, Simon, Richard and Peter,

To guarantee "some" protection to the files containing the database I
decided to use the following strategy:

I created, as root, the directory /home/reading_room

And activated the "sticky bit" of the reading_room directory with the
command:

chmod +t /home/reading_room/

And transferred, the files to the new directory with the following
access permissions:  

reading_room.tcl  rwxr--r-x  (owner markos)

reading_room.db rw-r--rw- (owner markos)
 

This way other users can run the reading_room.tcl program but can't  but
not edit it.

And can't delete the files (.tcl or .db)

Trying to protect against Murphy, but not Machiavelli. (As Richard said.)

Thank you,
Markos

Em 26-05-2019 23:33, Adrian Ho escreveu:

> On 27/5/19 12:43 AM, Luuk wrote:
>> On 26-5-2019 13:52, Adrian Ho wrote:
>>> On 26/5/19 7:49 AM, Markos wrote:
>>>> 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.tcl 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 (to read or write). But
>>>> that no user could delete or write to the file books.db (only the
>>>> program reading_room.tcl)
>>> The standard Unix permissions/ACLs architecture doesn't support this use
>>> case directly.
>> Can you give some more information on this, because it seems to work
>> as i excpect it to:
>>
>> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
>> users are in the group 'users':
>>
>> luuk2@opensuse1:/home/luuk/temp> whoami
>> luuk2
>> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
>> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
>> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
>> SQLite version 3.28.0 2019-04-16 19:49:53
>> Enter ".help" for usage hints.
>> sqlite> select * from test;
>> 1
>> 2
>> sqlite> insert into test values(3);
>> Error: attempt to write a readonly database
>> sqlite> .q
>> luuk2@opensuse1:/home/luuk/temp>
>>
> The OP wants *all users* to be able to update (write) the DB via the Tcl
> script reading_room.tcl, but *not* by (say) running the SQLite shell or
> something else. In your setup, as long as a specific user has write
> permissions, *every program* the user runs can write to the DB.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Peter da Silva-2
I don't believe you are providing any protection against editing the file,
just deleting.

On Sun, Jun 9, 2019, 4:38 PM Markos <[hidden email]> wrote:

> Many thanks to Luuk, Adrian, Graham, James, Simon, Richard and Peter,
>
> To guarantee "some" protection to the files containing the database I
> decided to use the following strategy:
>
> I created, as root, the directory /home/reading_room
>
> And activated the "sticky bit" of the reading_room directory with the
> command:
>
> chmod +t /home/reading_room/
>
> And transferred, the files to the new directory with the following
> access permissions:
>
> reading_room.tcl  rwxr--r-x  (owner markos)
>
> reading_room.db rw-r--rw- (owner markos)
>
>
> This way other users can run the reading_room.tcl program but can't  but
> not edit it.
>
> And can't delete the files (.tcl or .db)
>
> Trying to protect against Murphy, but not Machiavelli. (As Richard said.)
>
> Thank you,
> Markos
>
> Em 26-05-2019 23:33, Adrian Ho escreveu:
> > On 27/5/19 12:43 AM, Luuk wrote:
> >> On 26-5-2019 13:52, Adrian Ho wrote:
> >>> On 26/5/19 7:49 AM, Markos wrote:
> >>>> 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.tcl 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 (to read or write). But
> >>>> that no user could delete or write to the file books.db (only the
> >>>> program reading_room.tcl)
> >>> The standard Unix permissions/ACLs architecture doesn't support this
> use
> >>> case directly.
> >> Can you give some more information on this, because it seems to work
> >> as i excpect it to:
> >>
> >> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> >> users are in the group 'users':
> >>
> >> luuk2@opensuse1:/home/luuk/temp> whoami
> >> luuk2
> >> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> >> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> >> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> >> SQLite version 3.28.0 2019-04-16 19:49:53
> >> Enter ".help" for usage hints.
> >> sqlite> select * from test;
> >> 1
> >> 2
> >> sqlite> insert into test values(3);
> >> Error: attempt to write a readonly database
> >> sqlite> .q
> >> luuk2@opensuse1:/home/luuk/temp>
> >>
> > The OP wants *all users* to be able to update (write) the DB via the Tcl
> > script reading_room.tcl, but *not* by (say) running the SQLite shell or
> > something else. In your setup, as long as a specific user has write
> > permissions, *every program* the user runs can write to the DB.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

wmertens
In reply to this post by Markos
You are preventing Group users from eXecuting your script by removing the
x. I think you wanted chmod 755 reading_room.tcl

Same for the db file where users of the same Group are not allowed to
Write.

Finally, I think locking may be a problem: users will run the script, which
will create journal files as them, and then others won't be able to write
to the db until the journal files are removed?

Wout.

On Sun., Jun. 9, 2019, 11:38 p.m. Markos <[hidden email]> wrote:

> Many thanks to Luuk, Adrian, Graham, James, Simon, Richard and Peter,
>
> To guarantee "some" protection to the files containing the database I
> decided to use the following strategy:
>
> I created, as root, the directory /home/reading_room
>
> And activated the "sticky bit" of the reading_room directory with the
> command:
>
> chmod +t /home/reading_room/
>
> And transferred, the files to the new directory with the following
> access permissions:
>
> reading_room.tcl  rwxr--r-x  (owner markos)
>
> reading_room.db rw-r--rw- (owner markos)
>
>
> This way other users can run the reading_room.tcl program but can't  but
> not edit it.
>
> And can't delete the files (.tcl or .db)
>
> Trying to protect against Murphy, but not Machiavelli. (As Richard said.)
>
> Thank you,
> Markos
>
> Em 26-05-2019 23:33, Adrian Ho escreveu:
> > On 27/5/19 12:43 AM, Luuk wrote:
> >> On 26-5-2019 13:52, Adrian Ho wrote:
> >>> On 26/5/19 7:49 AM, Markos wrote:
> >>>> 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.tcl 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 (to read or write). But
> >>>> that no user could delete or write to the file books.db (only the
> >>>> program reading_room.tcl)
> >>> The standard Unix permissions/ACLs architecture doesn't support this
> use
> >>> case directly.
> >> Can you give some more information on this, because it seems to work
> >> as i excpect it to:
> >>
> >> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> >> users are in the group 'users':
> >>
> >> luuk2@opensuse1:/home/luuk/temp> whoami
> >> luuk2
> >> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> >> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> >> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> >> SQLite version 3.28.0 2019-04-16 19:49:53
> >> Enter ".help" for usage hints.
> >> sqlite> select * from test;
> >> 1
> >> 2
> >> sqlite> insert into test values(3);
> >> Error: attempt to write a readonly database
> >> sqlite> .q
> >> luuk2@opensuse1:/home/luuk/temp>
> >>
> > The OP wants *all users* to be able to update (write) the DB via the Tcl
> > script reading_room.tcl, but *not* by (say) running the SQLite shell or
> > something else. In your setup, as long as a specific user has write
> > permissions, *every program* the user runs can write to the DB.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users