Re: Access Control

You must have select privilege if the search condition in the update/delete statement contains a reference to the table you are updating/deleting.

DELETE FROM T WHERE C = 1;
requires select privilege since you refer to T.C

but
DELETE FROM T;
does not require select privilege

UPDATE T SET C = 1 WHERE C = 0;
requires select privilege

UPDATE T SET C = 1;
does not require select privilege

--
Daniel Gustafsson
Mimer SQL Development
Validate SQL statements for SQL-92 and SQL-99 compliance at 
http://developer.mimer.se/validator

kyfung_at_hotmail.com (K.Y. Fung) wrote in message news:<c22bb40d.0211020640.394223de_at_posting.google.com>...


> 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