I decided to test this so here's an almost-complete example minus any cross-checks on the tables which you should ultimately do. This implements exclusion rules which is what you've been describing. You can make them inclusion rules if you just remove the "not" in the select statement below, but then you need to do more for the admin user to see all. That could actually be a separate select anyways.
sqlite> create table objects (obj int); sqlite> create table pids (pid int, rule int); sqlite> create table rules (rule int, obj int); sqlite> // Our arbitrary objects that we ultimatately want to access sqlite> insert into objects values(100); sqlite> insert into objects values(200); sqlite> insert into objects values(300); sqlite> insert into objects values(400); sqlite> // No the rules that apply to each pid. You can actually have multiple rules per pid if you need to. sqlite> insert into pids values (1,0); sqlite> insert into pids values (2,1); sqlite> insert into pids values (3,2); sqlite> // Now our rules -- zero means no exclusion rules -- e.g. admin privledges // One row for each object excluded by a rule sqlite> insert into rules values(0,0); sqlite> insert into rules values(1,100); sqlite> insert into rules values(1,200); sqlite> insert into rules values(2,300); sqlite> insert into rules values(2,400); sqlite> select pid,rule from pids where pid=1; 1|0 sqlite> select obj from rules where rule=1; 100 200 sqlite> select obj from rules where rule in (select rule from pids where pid=1); 0 sqlite> select obj from rules where rule in (select rule from pids where pid=2); 100 200 // pid 2 sees just the last two objects in our table sqlite> select * from objects where obj not in(select obj from rules where rule in (select rule from pids where pid=2)); 300 400 // Our admin user sees all sqlite> select * from objects where obj not in(select obj from rules where rule in (select rule from pids where pid=1)); 100 200 300 400 // User 3 sees the first two objects in our table sqlite> select * from objects where obj not in(select obj from rules where rule in (select rule from pids where pid=3)); 100 200 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
