Обсуждение: Questions about privileges

Hello,

For an exercise at university, I have several SQL queries to find to
manage privileges on a database.
I didn't found all the queries.

So, this is my problem :

I have got one table named books with several columns.
* The first question is :

- All users can find titles, codes and prices of books (which are
columns of the table books)

For the moment, I did this :   grant select on table books to public;

But with this solution, all users can find information about all the
attributes of the table books and not only about titles, codes and
prices.
So, I would like if a solution existed for that (to allow all users to
access only at the columns titles, codes and prices).


* My second question is :

- Mister X can create an index on the table books

For the moment, I did this :   grant create on tablespace books to X;

Is that good ?


* My third question is :

- Mister X can update the structure of the table books

For that, I don't know how I can do. Someone would have an idea to do
that ?


*  My last question is :

- Mister X can update the column quantity of the table books where the
column codes is equal to 2 but He can't access at others datas of the
table books.

For the moment, I found how to limit the update at the column quantity
of the table books with the following query :

grant update(quantity) on books to X;

But, I don't know how to limit the update of the column quantity only
where column codes is equal to 2.
Someone would have an idea to do that ?

Thanks to help me.

Sylvain.

On Sat, Mar 04, 2006 at 05:08:27AM -0800, sylsau14@hotmail.com wrote:
> I have got one table named books with several columns.
> * The first question is :
>
> - All users can find titles, codes and prices of books (which are
> columns of the table books)
>
> So, I would like if a solution existed for that (to allow all users to
> access only at the columns titles, codes and prices).

Create a view and only grant access to that.

The next two questions I can't help with

> *  My last question is :
>
> - Mister X can update the column quantity of the table books where the
> column codes is equal to 2 but He can't access at others datas of the
> table books.
>
> But, I don't know how to limit the update of the column quantity only
> where column codes is equal to 2.
> Someone would have an idea to do that ?

Same thing. Create a view with what he can see and make it updatable
with some rules.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Thanks, it's a good idea.

Someone can help me with the two others question (the index and the
update of the structure of the table) ?