Re: [sqlite] Triggers to enforce table permissions

[Joshua Grauman]

Nevermind, I found the recursive_triggers PRAGMA...

Thanks!

Josh


Hello all,

I'm trying to create simple permissions for a table to prevent unwanted 
modification of certain table rows. I'm brand new to Triggers, but I almost 
have what I want working. Imagine I have a table t1 with some data, and a 
permission integer. I have two triggers that prevent modification of existing 
rows that have a certain permission level. So far, so good. I want to allow 
INSERTs of new data, but prevent modification of existing data. This works 
great except for one problem. You can get around the triggers by doing a 
INSERT OR REPLACE and neither Trigger gets called.


So, if that table and triggers look like:

CREATE TABLE t1 ('name' varchar(40), 'perm' integer, PRIMARY KEY('name'));

CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN (OLD.perm > 3)
BEGIN
 SELECT RAISE(ABORT, 'Permission Error');
END;

CREATE TRIGGER trig2 BEFORE UPDATE ON t1 WHEN (OLD.perm > 3)
BEGIN
 SELECT RAISE(ABORT, 'Permission Error');
END;

I create data like:
INSERT INTO t1 VALUES ('Normal data', 1);
INSERT INTO t1 VALUES ('Protected data', 5);

Then:
UPDATE t1 SET name='asdf' WHERE name='Protected data';
DELETE FROM t1 WHERE name='Protected data';
Will both fail as I want.

But I can do:
INSERT OR REPLACE INTO t1 VALUES('Protected data', 1);
DELETE FROM t1 WHERE name='Protected data';
And this will delete the row or allow modification. (Bad)

So... Is there any (easy) way to write an INSERT Trigger than just gets 
called in the REPLACE case? Or maybe just ON CONFLICT? (I'm trying to avoid 
writing an INSERT TRIGGER that does a SELECT of the entire table looking for 
key conflicts AND I'm also trying to make the permission system as 'air 
tight' as possible to prevent ways around it [It's not for a bank or anything 
super crucial, but I would like it to be reasonably protected by the 
permissions])


Thanks!

Josh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Triggers to enforce table permissions

[Joshua Grauman]

Hello all,

I'm trying to create simple permissions for a table to prevent unwanted 
modification of certain table rows. I'm brand new to Triggers, but I 
almost have what I want working. Imagine I have a table t1 with some data, 
and a permission integer. I have two triggers that prevent modification of 
existing rows that have a certain permission level. So far, so good. I 
want to allow INSERTs of new data, but prevent modification of existing 
data. This works great except for one problem. You can get around the 
triggers by doing a INSERT OR REPLACE and neither Trigger gets called.


So, if that table and triggers look like:

CREATE TABLE t1 ('name' varchar(40), 'perm' integer, PRIMARY KEY('name'));

CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN (OLD.perm > 3)
BEGIN
  SELECT RAISE(ABORT, 'Permission Error');
END;

CREATE TRIGGER trig2 BEFORE UPDATE ON t1 WHEN (OLD.perm > 3)
BEGIN
  SELECT RAISE(ABORT, 'Permission Error');
END;

I create data like:
INSERT INTO t1 VALUES ('Normal data', 1);
INSERT INTO t1 VALUES ('Protected data', 5);

Then:
UPDATE t1 SET name='asdf' WHERE name='Protected data';
DELETE FROM t1 WHERE name='Protected data';
Will both fail as I want.

But I can do:
INSERT OR REPLACE INTO t1 VALUES('Protected data', 1);
DELETE FROM t1 WHERE name='Protected data';
And this will delete the row or allow modification. (Bad)

So... Is there any (easy) way to write an INSERT Trigger than just gets 
called in the REPLACE case? Or maybe just ON CONFLICT? (I'm trying to 
avoid writing an INSERT TRIGGER that does a SELECT of the entire table 
looking for key conflicts AND I'm also trying to make the permission 
system as 'air tight' as possible to prevent ways around it [It's not for 
a bank or anything super crucial, but I would like it to be reasonably 
protected by the permissions])


Thanks!

Josh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Yuriy Kaminskiy]

Richard Hipp wrote:
> Please try the changes in the branch at
> http://www.sqlite.org/src/info/8759a8e4d8 and let me know if they
> adequately cover your concerns.

Let's suppose user just did
  cp -b somewhere/else/db opened.db
There *are* still file named opened.db, but it points to *different* file.
Sure, you can also compare stat() and fstat() to check if this is still same
file, ... but then you'll be asked for protection against

  cp opened.db bar
  while true; do
mv opened.db foo
mv bar opened.db
mv opened.db bar
mv foo opened.db
  done

... and there are none. SQLite is responsible for protecting database against
corruption in case of concurrent modification by other SQLite instances. It
cannot protect against concurrent modification by other processes that does not
use SQLite locking protocol. And, IMO, it should not pretend it can.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Richard Hipp]

Please try the changes in the branch at
http://www.sqlite.org/src/info/8759a8e4d8 and let me know if they
adequately cover your concerns.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[L. Wood]

> Simon Slavin wrote:
> If you can't trust your users not to
> move data files out of place

I was not talking about data files but regular documents (package folders). 
Moving regular documents has nothing to do with trust. It's a fact of everyday 
reality.


> Stephen Chrzanowski wrote:
> Even with MySQL, if you start messing around with directory
> structures, file permissions, or whatever, you're GOING to
> kill the software.

Moving regular documents is not "messing around" with anything.


> William Garrison wrote:
> It's your job to code for this possibility. Use the
> rest of the system to help you do that. 

> Simon Slavin wrote:
> My conclusion is that those programmers who want this
> can do it inside their app, outside the code which handles
> the database itself.

> Stephen Chrzanowski wrote:
> File handling is NOT SQLites responsibility, but your
> applications responsibility in knowing how the OS
> handles itself.

I can think of two ways to "achieve" this (neither actually works):

* Before every single SQLite C function call that accesses the database, check 
whether the document has moved. This is horrible for two reasons: (1) It will 
slow down the database operations. (2) The document could still be moved 
*between* our "move check" and the database operation.

* Using fsevents etc. to monitor the document move, as someone suggested. This 
will not work because lengthy database operations occur on a separate thread, 
so the database can get corrupted *before* we actually handle the document 
move. And even if fsevents and database operations are handled in the same 
thread, there is no guarantee that the system sends my program the event before 
the database gets corrupted.

Summary: AFAIK, there is nothing my program can do to handle this.


So IMO, this is a problem that SQLite should cope with. At most, it should give 
us errors to deal with - not corrupt the database.

If you disagree, why?

If you agree:

Let's go back to D. Richard Hipp's original (1)-(5) steps of corruption.

Could there be a way for a future version of SQLite to avoid that corruption? I 
asked whether opening the journal file only once was a potential solution to 
this. I'm not sure it is. If not, why not, and do you have any other ideas? 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Tim Streater]

On 06 Dec 2013 at 03:31, Stephen Chrzanowski  wrote: 

> On a personal note, I still cannot fathom why anyone would WANT to do file
> management while working on an active document.  Moving a document while
> its being worked on contradicts everything I understand.


I find being *unable* to do that under Windows one of its most primitive 
aspects. I receive a document, inspect it (by opening it), decide there and 
then where is needs to go in the file system, and move it. The application is 
quite happy with this arrangement, and updates any location info it presents to 
the user. I then get on editing the file.

It's called flexibility. I, the user, get to decide in what order I do things, 
rather than being told by the OS what I can and can't do.


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Functions affecting table structure ?

[Raheel Gupta]

Hi,

I am trying to add some additional data with the table structure stored in
SQLite. Everything seems to be working fine but when I do an alter query it
seems to drop the table.

I have made changes in the following functions to handle the addition of
the data:
sqlite3AlterFinishAddColumn() ,
sqlite3EndTable(),

I have made changes in the following functions to remove the changes during
runtime only (sqlite feels its a normal table)
sqlite3InitCallback(),
execExecSql()

I am trying to understand the ALTER queries and which function handles
them. Any hint would be immensely appreciated. I have wasted a day or two
trying to figure out why is the database dropping the structure when I add
a column to an existing table (modified by my code)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Yuriy Kaminskiy]

Warren Young wrote:
> On 12/5/2013 20:31, Stephen Chrzanowski wrote:
[...]
>> File handling is NOT SQLites responsibility
> 
> I'm not sure about that.  SQLite, at least at one time, was billed as a
> competitor for fopen() rather than for Oracle.

But fopen(3) have no locking *at all*. And lower-level locking
(flock/fcntl(F_*LCK)) is only advisory - if "rogue application" decide to ignore
it, locks won't stop it from changing file.

> Maybe all that's needed is a mode where SQLite never creates any
> external files.  Disable the WAL feature, don't create a lock file, etc.

And then there will be question: what if user will open database file in text
editor and corrupt it by saving?
There are no way you can handle such errors.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users