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
