Skip site navigation (1) Skip section navigation (2)

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

From: Csaba Együd <csegyud(at)gmail(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: Re: Separate Sessions?? (View data <-> Query tool)
Date: 2008-11-28 09:14:58
Message-ID: ggocqh$2tpk$1@news.hub.org (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-general
""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




In response to

Responses

pgadmin-support by date

Next:From: Willy-Bas LoosDate: 2008-11-28 11:04:37
Subject: Re: Separate Sessions?? (View data <-> Query tool)
Previous:From: Willy-Bas LoosDate: 2008-11-28 08:30:19
Subject: Re: Separate Sessions?? (View data <-> Query tool)

pgsql-general by date

Next:From: AndrusDate: 2008-11-28 09:15:42
Subject: Re: [GENERAL] Expected password response, got message type 88
Previous:From: Willy-Bas LoosDate: 2008-11-28 08:30:19
Subject: Re: Separate Sessions?? (View data <-> Query tool)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group