Re: Access Control
From: Tibor Karaszi <tibor_not_pressed_ham_.karaszi_at_cornerstone.se>
Date: Sat, 02 Nov 2002 15:45:43 GMT
Message-ID: <HqSw9.2784$1r1.84120_at_newsc.telia.net>
Date: Sat, 02 Nov 2002 15:45:43 GMT
Message-ID: <HqSw9.2784$1r1.84120_at_newsc.telia.net>
I remember being a bit surprised when I found this in SQL Server.
I guess that internally the engine need to find the row(s) ("select") to be able to update or delete.
Another explanation can be security. You could do an update or delete based on a search criteria, and do that modification inside a transaction. After the modification, you find out how many rows were affected and then you roll back. I.e., you did an existence check based on a criteria without SELECT permissions...
I couldn't find anything in the ANSI standard about this, after a quick look.
-- Tibor Karaszi "K.Y. Fung" <kyfung_at_hotmail.com> wrote in message news:c22bb40d.0211020640.394223de_at_posting.google.com...Received on Sat Nov 02 2002 - 16:45:43 CET
> I am an Open University student in Hong Kong studying relational
> database.
>
> The course material told me that if user A created a table and want
> user B to be able to delete and update it, user A need to grant delete
> and update privileges. So I did the necessary work and try the real
> test.
>
> When I try to delete or update as user B, I get SQLSTATE = 42000 error
> telling me that user B "do not have permission to select from" that
> table. Only after user A grant select privileges to user B as well,
> then user B can delete and update the table created by user A.
>
> As usual, tutors and course coordinator do not anwer this question,
> neither did they answer most of the other questions posted by my
> classmates.
>
> We are using SQL Anywhere as our course software. Is this software
> non-standard that cause this problem or is my course material wrong
> and we need to grant select privileges also for delete and update?
>
> Regards,
> K.Y. Fung