Thread: Different views with same name for different users
Hello, i've got a table of around 10.000 records. Users A, B, C are allowed to see all the records user D only some user E only some others To take logic away from the application to the database, I would like to have a view as for user D: CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for viewable user D) for user E: CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for viewable user E) for users A, B, C CREATE VIEW PERS as SELECT * FROM TOTALDATABASE so in my application I can do alll the SELECTS on PERS ... which looks different for every user. Which is the most elegant way to do this? (is there any way at all????) Tnx Harald
On Wed, Jan 16, 2002 at 02:21:39PM +0100, Harald Massa wrote: > i've got a table of around 10.000 records. > > Users A, B, C are allowed to see all the records > > user D only some > user E only some others > > Which is the most elegant way to do this? > (is there any way at all????) You cannot create different views with the same name in one PostgreSQL database. Hence the first answer is: there is no way. Nevertheless that's not the right answer. You can create only one view: CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria) Within criteria you can check the user (`current_user') and return `true' or `false' depending on the record at hand and the current user. -- Holger Krug hkrug@rationalizer.com
Correct me if i am wrong which is normally the case :-)) But you could create the views as you have said then grant user access permissions on them. I.e. get the priveldged user to create all the views and then use the GRANT command to allow users to access them. I.e. GRANT ALL ON PERS TO user D; Hope this helps Darren Darren Ferguson On Wed, 16 Jan 2002, Harald Massa wrote: > Hello, > > i've got a table of around 10.000 records. > > Users A, B, C are allowed to see all the records > > user D only some > user E only some others > > To take logic away from the application to the database, > I would like to have a view as > > for user D: > CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for viewable > user D) > > for user E: > CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for viewable > user E) > > for users A, B, C > CREATE VIEW PERS as SELECT * FROM TOTALDATABASE > > so in my application I can do alll the SELECTS on PERS ... which looks > different for every user. > > Which is the most elegant way to do this? > (is there any way at all????) > > Tnx > > Harald > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Wed, Jan 16, 2002 at 09:59:42AM -0500, Darren Ferguson wrote: > But you could create the views as you have said then grant user access > permissions on them. I.e. get the priveldged user to create all the views > and then use the GRANT command to allow users to access them. I.e. You forgot: He aimed to create all the different views with one and the same name. This is impossible in PostgreSQL. -- Holger Krug hkrug@rationalizer.com
Harald,
Possible method to implement security that I think matches your requirements
follows, please feel free to comment:-
(
Notes:
------
I have only implemented the 'select' view the model could be easily
expanded to cope with different security permissions.
I have added the notion of user groups for convenience.
You could probably do something similar hooking into the pg_tables,
there are would be pros and cons if you did that.
You would probably want to modify the keys / foreign keys before using
the model (i.e. I've set them all as varchar(50).
)
hih
steve boyle
-- create tables
create table users (
user_id varchar(50),
primary key (user_id)
);
create table items (
item_id varchar(50),
primary key (item_id)
);
create table groups (
group_id varchar(50),
primary key (group_id)
);
create table user_groups (
fk_user_id varchar(50),
fk_group_id varchar(50),
primary key (fk_user_id,fk_group_id)
);
create table item_group_permissions (
fk_group_id varchar(50),
fk_item_id varchar(50),
canselect bool default true,
canupdate bool default false,
candelete bool default false,
primary key (fk_group_id,fk_item_id)
);
-- add foreign key constraints
alter table user_groups add constraint fk_groups_user_groups_group_id
foreign key (fk_group_id)
references groups(group_id)
on update cascade
not deferrable
initially immediate;
alter table user_groups add constraint fk_users_user_groups_user_id
foreign key (fk_user_id)
references users(user_id)
on update cascade
not deferrable
initially immediate;
alter table item_group_permissions add constraint
fk_groups_item_group_permissions_group_id
foreign key (fk_group_id)
references groups(group_id)
on update cascade
not deferrable
initially immediate;
alter table item_group_permissions add constraint
fk_items_item_group_permissions_item_id
foreign key (fk_item_id)
references items(item_id)
on update cascade
not deferrable
initially immediate;
-- insert demo data
insert into items (item_id) values ('item 1');
insert into items (item_id) values ('item 2');
insert into items (item_id) values ('item 3');
insert into users (user_id) values ('user 1');
insert into users (user_id) values ('user 2');
insert into users (user_id) values ('user 3');
insert into users (user_id) values ('user 4');
insert into groups (group_id) values ('group 1');
insert into groups (group_id) values ('group 2');
insert into groups (group_id) values ('group 3');
insert into user_groups(fk_user_id, fk_group_id) values ('user 1', 'group
1');
insert into user_groups(fk_user_id, fk_group_id) values ('user 2', 'group
2');
insert into user_groups(fk_user_id, fk_group_id) values ('user 3', 'group
3');
insert into user_groups(fk_user_id, fk_group_id) values ('user 4', 'group
1');
insert into user_groups(fk_user_id, fk_group_id) values ('user 4', 'group
2');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 1', 'item 1', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 2', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 3', 'T', 'T', 'T');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 1', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 1', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 2', 'item 3', 'T', 'F', 'F');
insert into item_group_permissions
(fk_group_id, fk_item_id, canselect, canupdate, candelete) values
('group 3', 'item 2', 'T', 'F', 'F');
-- create permission api views
create view usr_select_items as
select distinct items.item_id, item_group_permissions.canselect,
users.user_id
from users inner join
items
inner join
groups
inner join item_group_permissions on
groups.group_id = item_group_permissions.fk_group_id
inner join user_groups
on groups.group_id = user_groups.fk_group_id
on items.item_id = item_group_permissions.fk_item_id
on users.user_id = user_groups.fk_user_id
where
item_group_permissions.canselect='T' and
users.user_id::text=getpgusername()::text
order by items.item_id;
-- Other api permission views could follow
----- Original Message -----
From: "Harald Massa" <HaraldMassa@ghum.de>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 16, 2002 1:21 PM
Subject: [GENERAL] Different views with same name for different users
> Hello,
>
> i've got a table of around 10.000 records.
>
> Users A, B, C are allowed to see all the records
>
> user D only some
> user E only some others
>
> To take logic away from the application to the database,
> I would like to have a view as
>
> for user D:
> CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for
viewable
> user D)
>
> for user E:
> CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria for
viewable
> user E)
>
> for users A, B, C
> CREATE VIEW PERS as SELECT * FROM TOTALDATABASE
>
> so in my application I can do alll the SELECTS on PERS ... which looks
> different for every user.
>
> Which is the most elegant way to do this?
> (is there any way at all????)
>
> Tnx
>
> Harald
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Holger Krug wrote:
> On Wed, Jan 16, 2002 at 02:21:39PM +0100, Harald Massa wrote:
>
>>i've got a table of around 10.000 records.
>>
>>Users A, B, C are allowed to see all the records
>>
>>user D only some
>>user E only some others
>>
>>Which is the most elegant way to do this?
>>(is there any way at all????)
>>
>
> You cannot create different views with the same name in one PostgreSQL
> database. Hence the first answer is: there is no way.
>
> Nevertheless that's not the right answer. You can create only one view:
>
> CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria)
>
> Within criteria you can check the user (`current_user') and return
> `true' or `false' depending on the record at hand and the current
> user.
>
>
how about
create view pers as
select ... from totaldb where userid='D' and (d criteria)
union
select ... from totaldb where userid in 'A, B, C' and (abc criteria)
union
select ... from totaldb where userid = 'E' and (e criteria);
You would have to match the columns, perhaps put "not available" where
appropriate. Of course there are spiffier ways to group permissions
instead of hardcoding ids.
elein
--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
spinning to infinity, hallelujah
--------------------------------------------------------
I found (thank you, Holger Krug) a way to do the access-problem create view pers as select * from totaldatabase where case currentuser="userA" then beraterid in (1256,2523,2521,623,124) else beraterid in (9123, 12312,12313) end (or more case statements.) But NOW there are 2 drawbacks 1.) INSERT and UPDATE became MUCH MORE complicated. I have defined rules (do instead insert ....), but now partial inserts (only SOME columns of the orignal table) do not work. 2.) there is some bad performance penality. If I do a explain select * from totaldatabase where case currentuser="userA" then beraterid in (1256,2523,2521,623,124) else beraterid in (9123, 12312,12313) end allways the sequential scan is used - also that only 2% of the rows are visible. The Planner seems not to realise, that currentuser="userA" DOES NOT change with each row. I think I'll ask my question in another way again.. Thank you all, HArald