List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 30 2004 8:08pm
Subject:Re: Revoking select on a single table
View as plain text  
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

Thread
Revoking select on a single tableTodd Charron30 Aug
  • Re: Revoking select on a single tableTodd Charron30 Aug
  • Re: Revoking select on a single tablePaul DuBois30 Aug
    • Re: Revoking select on a single tableTodd Charron30 Aug
      • Re: Revoking select on a single tableSGreen30 Aug
        • Re: Revoking select on a single tableTodd Charron30 Aug
          • Re: Revoking select on a single tableSGreen30 Aug
            • Re: Revoking select on a single tableTodd Charron30 Aug
            • Re: Revoking select on a single tableTodd Charron30 Aug
              • Re: Revoking select on a single tableSGreen30 Aug
            • Re: Revoking select on a single tableTodd Charron30 Aug
            • Re: Revoking select on a single tableMichael Stassen31 Aug