Re: [sqlite] How to set access permissions to protect a database file?
On 27/5/19 4:51 AM, James K. Lowden wrote: > On Sun, 26 May 2019 19:52:29 +0800 > Adrian Ho 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round function inconsistent
On Fri, 24 May 2019 13:10:49 + Jose Isaias Cabrera wrote: > >Consider these two queries: > > > > SELECT round(3.255,2); > > SELECT round(3.2548,2); > > > >Do you expect them to give different answers? > > 3.26 > 3.25 sqlite> SELECT cast(100 * (0.005 + 3.2548) as ...> integer)/100.0; 3.26 --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
On Sun, 26 May 2019 19:52:29 +0800 Adrian Ho 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SEE extension questions
On 5/26/19, Karl Sanders wrote: > Hi, > I'd like to ask a few questions about the SEE extension. > > - Are tables (virtual and real) used by the various extensions (in > particular by the FTS5 and R*Tree modules) encrypted? Yes > > - Are there any pragmas or compile-time options that don't work with > encryption? none come to my mind. But there are a huge number of pragmas and compile-time options, so perhaps I have missed one. > > - Is there any functionality that is available in a limited way, or > not at all, while using an encrypted database? No, not that I can think of. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SEE extension questions
On 26 May 2019, at 2:27pm, Karl Sanders wrote: > I'd like to ask a few questions about the SEE extension. > > - Are tables (virtual and real) used by the various extensions (in > particular by the FTS5 and R*Tree modules) encrypted? The entire database file is encrypted, block by block, including the header block, blocks describing the schema, and blocks containing freespace. If you don't understand how to decrypt it you can't tell how many tables it holds, what part of the file is tables, or what part is indexes. > - Are there any pragmas or compile-time options that don't work with > encryption? Any program which doesn't have the SEE extension compiled into it will not be able to open the database file. So, for instance, you won't be able to use the downloadable sqlite3 shell tool. The SEE source code includes source code for a special version of the shell tool which includes the SEE extension and adds extra features useful for managing encryption. There are similar concerns about utilities like the sqlite3_analyzer. > - Is there any functionality that is available in a limited way, or > not at all, while using an encrypted database? I don't think so, since if you can read any part of the database you can read all the database. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SEE extension questions
Hi, I'd like to ask a few questions about the SEE extension. - Are tables (virtual and real) used by the various extensions (in particular by the FTS5 and R*Tree modules) encrypted? - Are there any pragmas or compile-time options that don't work with encryption? - Is there any functionality that is available in a limited way, or not at all, while using an encrypted database? Regards, Karl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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|$▒iP}▒�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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set access permissions to protect a database file?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
