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

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;

On Thu, Nov 27, 2008 at 3:06 PM, Csaba Együd <csegyud(at)gmail(dot)com> wrote:

> ""Willy-Bas Loos"" <willybas(at)gmail(dot)com> a következoket írta üzenetében
> news:1dd6057e0811270036s67a6b8baqda17273b0484e53d(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
>
> The problem is that my triggers refuse modifying a record if a given Temp
>> table is not created before, because one or more fields have their default
>> values from that temp table
>>
>
> So why don't you either use a normal table, or if you need simultaneous use
> of the trigger with different values, use an array instead of a table. Or
> you might create the temp table in the trigger function..
> If you create a normal table you can still delete it afterwards.
>
> hth
> WBL
> --
> "Patriotism is the conviction that your country is superior to all others
> because you were born in it." -- George Bernard Shaw
>
> Willy,
> you are right but... this is a bit difficult but I try to explain. I
> develop an office management software which uses Postgres as a RDB server.
> This software has to handle many firms separately but by design it has to
> use only one database (customer requirement) so I had to find out a solid
> way to separate the firms form each other. I find out that I qualify every
> firm-dependent record with a firmid.
>
> After logging in the client software will create a TEMP table in which it
> stores the selected firmid. This TEMP table will live durring the login
> session. After this point every views will filter to this firmid and won't
> give back other firms' data just this. I know that other client softwares
> will be able to select data from the tables. (Unfortunatelly I don't know a
> way to revoke select right from a table while a view can select from it...
> Do you know such thing?) Because of this I know that the clean way would be
> using separate databases for separate firms but this was not my decision. :(
> If you know a setting which can improve the security pleas let me know.
>
> Also I had to assure that the INSERT and UPDATE operations will only work
> for the selected firm and the user won't be able to potter with other firms'
> data even not accidentally. This is done in triggers and this causes my
> "View data tool" problem because viewing is possible but modifying is
> enabled only when a firmid is set in the TEMP table.
>
> BTW, I can use script templates with Query tool (like INSERT or UPDATE
> script) which are very handy.
>
> Thx,
>
> --
> Best Regards,
> Csaba Együd
> IN-FO Studio
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw