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 <vantage@stripped> 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
