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'; DELETE FROM secrettable; That could be a dangerous situation for you if you leave those two permissions on the 'hidden' table. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Charron <[EMAIL PROTECTED]> wrote on 08/30/2004 03:55:00 PM: > > GRANT usage on samp.* to 'permtest'@'localhost' identified by 'password'; > > GRANT insert, update, delete on samp.secrettable to > > 'permtest'@'localhost'; > > GRANT select, insert, update, delete on samp.Account to > > 'permtest'@'localhost'; > > > > This worked much better for me, though it's not a perfect solution (see below) > > > This worked for me on 4.1.1a-alpha-nt-log. By explicitly denying access at > > the database level, the only tables I could see in my test database were > > the ones I specifically granted myself right to (even though there were > > others in the database). > > In my case I was able to see all the tables but could not select anydata from > the restricted table (mysql-server 4.0.18-5 from debian testing) > > > > > 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: > > > > ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' > > for column 'col1' in table 'secrettable' > > > > Yes, this is the problem I ran into when trying to update any records in that > table. So it appears to be insert only. > > > Let us know if this works or what errors you get. > > > > Thanks for all your help. This may be the best solution available. Would it > be possible to get it posted to a faq or mentioned in the docs? I imagine > it's a situation that may come up frequently for users. > > Also, if you have any ideas on how one might perform updates on specific > entries in that table without select privileges I'd be glad to hear them. > (though it sounds like it may be impossible) > > Thanks again, > > Todd
