Granting rights on specific tuples?

Hi!

I'm setting up a multi-user system where I want different users to have
access to different tuples in the same schema. So that, for instance, userA
and userB both have access to the schema "Cars (color, age)", but only userA
has access to "car1 red 12" and userB has access to "car2 blue 1".

Is there any way to set these rights in SQL?

- Matthijs

Matthijs Holter wrote:
> Hi!
>
> I'm setting up a multi-user system where I want different users to have
> access to different tuples in the same schema. So that, for instance, userA
> and userB both have access to the schema "Cars (color, age)", but only userA
> has access to "car1 red 12" and userB has access to "car2 blue 1".
>
> Is there any way to set these rights in SQL?
>
> - Matthijs
>
>

Yes. Using views.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Don't give access to the table, but create as many views as you need
to implement your profiles, and then give rights to these views
(they could be updatable also).

You can also enforce a constraint
http://www.livejournal.com/users/arjen_lentz/49881.html

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

Thanks, Giuseppe!

- Matthijs

"Giuseppe Maxia" <gmax_@_cpan_._org> skrev i melding
news:4369f57a$0$22301$4fafbaef@reader1.news.tin.it ...
> Matthijs Holter wrote:
>> Hi!
>>
>> I'm setting up a multi-user system where I want different users to have
>> access to different tuples in the same schema. So that, for instance,
>> userA and userB both have access to the schema "Cars (color, age)", but
>> only userA has access to "car1 red 12" and userB has access to "car2 blue
>> 1".
>>
>> Is there any way to set these rights in SQL?
>>
>> - Matthijs
>
> Yes. Using views.
> http://dev.mysql.com/doc/refman/5.0/en/create-view.html
>
> Don't give access to the table, but create as many views as you need
> to implement your profiles, and then give rights to these views
> (they could be updatable also).
>
> You can also enforce a constraint
> http://www.livejournal.com/users/arjen_lentz/49881.html
>
> ciao
> gmax
>
> --
> _ _ _ _
> (_|| | |(_|><
> _|
> http://gmax.oltrelinux.com

Matthijs Holter wrote:
> Hi!
>
> I'm setting up a multi-user system where I want different users to have
> access to different tuples in the same schema. So that, for instance, userA
> and userB both have access to the schema "Cars (color, age)", but only userA
> has access to "car1 red 12" and userB has access to "car2 blue 1".
>

How's that supposed to work for tuples that don't exist yet?

"Christian Kirsch" <ck@bru6.de> skrev i melding
news:436a5192$0$21941$9b4e6d93@newsread2.arcor-online.net...
> Matthijs Holter wrote:
>> Hi!
>>
>> I'm setting up a multi-user system where I want different users to have
>> access to different tuples in the same schema. So that, for instance,
>> userA
>> and userB both have access to the schema "Cars (color, age)", but only
>> userA
>> has access to "car1 red 12" and userB has access to "car2 blue 1".
>>
>
> How's that supposed to work for tuples that don't exist yet?

I was thinking that when a tuple is created, it is assigned to an owner. So
when userB is logged in and creates a new tuple, only he can access it.

- Matthijs

On Fri, 04 Nov 2005 16:41:23 GMT, "Matthijs Holter"
<fjernspam.matthijs1000@fjernspam.hotmail.com> wrote:

>"Christian Kirsch" <ck@bru6.de> skrev i melding
>news:436a5192$0$21941$9b4e6d93@newsread2.arcor-online.net...
>> Matthijs Holter wrote:
>>> Hi!
>>>
>>> I'm setting up a multi-user system where I want different users to have
>>> access to different tuples in the same schema. So that, for instance,
>>> userA
>>> and userB both have access to the schema "Cars (color, age)", but only
>>> userA
>>> has access to "car1 red 12" and userB has access to "car2 blue 1".
>>>
>>
>> How's that supposed to work for tuples that don't exist yet?
>
>I was thinking that when a tuple is created, it is assigned to an owner. So
>when userB is logged in and creates a new tuple, only he can access it.
>
>- Matthijs

You can do it that way by adding the userID to the table as long
as (user : tuple) = (1 : many), not (many : many).

You wouldn't need a per-user view in that case.

SQL itself only defines per column privileges, not per row.
--
( Kees
)
c[_] Is "tired old cliche" one? (#11)