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 <[EMAIL PROTECTED]> wrote: > ""Willy-Bas Loos"" <[EMAIL PROTECTED]> a következoket írta üzenetében > news:[EMAIL PROTECTED] > > 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 ([EMAIL PROTECTED]) > 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
