[EMAIL PROTECTED] wrote:
I was blocked from running a SELECT query against secrettable. I could INSERT values but not DELETE them. I reason that this is because DELETE ... WHERE ... requires a SELECT to be run on the table to identify the rows to get rid of. The error I got when trying to delete was:
I don't know that I would have put it in those words, but yes, you have to have the SELECT priv to match rows with WHERE.
ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' for column 'col1' in table 'secrettable'
Let us know if this works or what errors you get.
Then, in another message, [EMAIL PROTECTED] wrote:
I think that INSERT-only would be as good as we could hope for as I have been having a very hard time trying to think of a valid business reason why a user would be allowed to either UPDATE or DELETE rows from a table where they weren't allowed to even see the data. However I can think of several business reasons for an INSERT-only table:
A "suggestion box"
Sales figures
TimeCard entries
Anywhere that people need to add data to a common table but not see what anyone else had added to that same table.
I didn't test the situation but thought of two cases you might want to test for (I have already dropped my test tables and users).... Try running a whole table UPDATE or whole table DELETE. I wondered if you run those statements without a WHERE clause, would the engine allow them even if the user doesn't have SELECT rights?
UPDATE secrettable SET column = 'value';
Yes, this works. UPDATE priv allows updates. No WHERE clause, no need for SELECT priv.
DELETE FROM secrettable;
This works as well, for the same reason.
That could be a dangerous situation for you if you leave those two permissions on the 'hidden' table.
Absolutely.
Yours, Shawn Green
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
