Thread: row based security ... was Different views with same name for different users

row based security ... was Different views with same name for different users

From
"Harald Massa"
Date:
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


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

From
Bruno Wolff III
Date:
On Sun, Jan 20, 2002 at 10:26:34PM +0100,
  Harald Massa <HaraldMassa@ghum.de> wrote:
> 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

The "look" part can be done with views. To do updates rules need to be used.

If the security model is that each row of the database can have a single
security type associated with it, but that users can have multiple types,
then I suggest using a table listing valid username security type pairs
and joining this table with the underlying table in the views on the
security type and checking the the username column matches the current
user.