Обсуждение: 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