Re: Separate Sessions?? (View data <-> Query tool)

""Willy-Bas Loos"" <willybas(at)gmail(dot)com> a következoket írta üzenetében
news:1dd6057e0811280030q6df9a8ebqf6a71ac28b16ea3c(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
how about this:

create role firm1 nologin;
create role john password 'secret' login;
grant firm1 to john;
create role firm2 nologin;
create role amy password 'secret' login;
grant firm2 to amy;

create table table1 (id serial primary key,firm integer, val integer);
insert into table1 (firm, val) values (1, 101);
insert into table1 (firm, val) values (1, 102);
insert into table1 (firm, val) values (1, 103);
insert into table1 (firm, val) values (1, 104);
insert into table1 (firm, val) values (1, 105);
insert into table1 (firm, val) values (2, 206);
insert into table1 (firm, val) values (2, 207);
insert into table1 (firm, val) values (2, 208);
insert into table1 (firm, val) values (2, 209);
insert into table1 (firm, val) values (2, 210);

revoke all on table1 from john;
revoke all on table1 from amy;
revoke all on table1 from firm1;
revoke all on table1 from firm2;

create view view_firm1 as select * from table1 where firm =1;
create view view_firm2 as select * from table1 where firm =2;
grant select, update on view_firm1 to firm1;
grant select, update on view_firm2 to firm2;

create or replace rule _update as on update
to view_firm1 do instead
update table1 set val = NEW.val where id=old.id;

create or replace rule _update as on update
to view_firm2 do instead
update table1 set val = NEW.val where id=old.id;
--------------------------

Willy,
THX! This sounds quite promissing. If I understand well you say that I could
isolate data by using separate group roles for each firms and defining
updatable views to work on. The most strange thing for me is revoking all
rights from table1 but I guess this is the heart of it... If I revoke all
rights on table1 from the roles how will the user be able to modify the data
of table1 trough the views? I read somewhere that when a user wants to
select rows from a view the user needs to have the select permission for the
undelaying table as well, and thats the case with updating or inserting too.
I just would like to know - but the fact is that it works!

Editing table1 in View data tool is also working! Great. This appetizing
news makes me ask :) that if it is possible to edit a view in the View data
tool? (I defined both _update and _insert rules) It is not that important
because I can edit the table directly but why not if it's possible.

Thank you very much for opening my eyes. To tell the truth I was afraid a
bit of using updatable views because of the above misunderstanding.

--
Best Regards,
Csaba Együd
IN-FO Studio