Re: row based security ... was Different views with same name for

Harald wants to be able to show different users different subsets of
data, and also give them different update permissions.

This is also what I want to do with a PostrgeSQL implementation of
Virtual Private Databases. Harald, you might want to check out some of
Oracle's documentation on this to help soldify your own ideas. Google
should be able to help. If not contact me and I'll see what I can find
for you.

Here is a quick view of what I want to achieve:

Consider a table "t_x" to which we wish to control access according to
the value of its "name" field, and the caller's access rights.

We create a view "x" on this table as follows:

create view x as
select *
from t_x
where i_can_see(name);

The access control is now placed firmly in the hands of the i_can_see()
function which we can make as simple or complex as we like. And we can
play similar tricks with update, insert and delete rules.

The problem we face is that the i_can_see function probably needs
greater levels of access than we want to grant to our everyday user.
For this, we need to be able to have functions which run with the
permissions of the rule owner rather than the caller (please see my
response, in plsql-general, to depesz(at)depesz(dot)pl, Re: IDEA: "suid"
function).

There will necessarily be a performance penalty to pay for this but with
good design I believe that this can be minimsed.

Any thoughts anyone?

> Date: Sun, 20 Jan 2002 22:26:34 +0100
> From: "Harald Massa" <HaraldMassa(at)ghum(dot)de>
> To: "Postgres Mailing List" <pgsql-general(at)postgresql(dot)org>
> Subject: row based security ... was Different views with same name for
different users
> Message-ID: <015a01c1a1f9$2dc1aa80$0100a8c0(at)tog2>
>
> Hello,
>
> I have other words for my question, maybe they are more helpfull.
>
> What I'm looking for is ROW BASED SECURITY.
>
> That means:
>
> User A is allowed to look and update some rows
> User B is allowed to look and update some other rows
>
> Example: User A is only allowed to edit customers from the USA, User B
is
> allowed to edit customers from Europa.
>
> My solution is:
> one field (beraterid) in the table, and a view like
>
> 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
>
>
> some pain is connected. BUT I strongly beliefe, there must be a
simpler,
> more elegant solution.
>
> What is it?
>
> Thanl you very much in advance
>
> HArald

--
Marc marc(at)bloodnok(dot)com