Re: Different views with same name for different users

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(at)ghum(dot)de>
To: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>