Re: Revoking select on a single table
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 any data 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
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
Re: Revoking select on a single table
On Monday 30 August 2004 4:08 pm, [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: The situation I have is like this. End User enters data in web page. Web page data is encrypted using mysql's aes_encrypt and stored in remote DB (not the same machine as the web server). If End User wants to update the data the new data is encrypted and overwrites the old (End User cannot see what old data was). If the db user account gets compromised via the web server (username and/or password gets disclosed/cracked/etc), the data (encrypted or not) cannot be retrieved via that account, only overwritten. UPDATE secrettable SET column = 'value'; DELETE FROM secrettable; That may be possible, but I can't really see anywhere where that would be useful. Thanks. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Thinking about this some more, it might be possible to achieve what my last email suggests by allowing select on the primary key column. Or would that set us back again? Thoughts? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Sorry to reply yet again, but I think I have the solution. After doing all we have said above I added grant select(ID_Num) on sampdb.secrettable to 'user'@'localhost' identified by 'password'; and of course updates and deletes are done via update secrettable set secretinfo=blah where ID_Num=2; So far as I've been testing this seems to work while preventing the user from ever selecting what's in the other secrettable columns. Thanks again! Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Yes, I think that _may_ solve your problem. Try granting only INSERT and UPDATE on the table then grant only SELECT permission on the ID column (I assume it's autoincrementing?) and the user_id column. If you don't make the user_id column visible, how will you ever discover the correct ID to update? (Unless there is only ever 1 record per user_id in which case you won't need an auto_incrementing ID column and you will already know which user_id to updatehmmmmaybe I should think just a bit more before I start typing... :-) ) You only need to expose what would be public knowledge anyway, right? I think your encrypted data will still be protected by the database so long as the invaders do not get ahold of a privileged account. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Charron [EMAIL PROTECTED] wrote on 08/30/2004 04:30:24 PM: Thinking about this some more, it might be possible to achieve what my last email suggests by allowing select on the primary key column. Or would that set us back again? Thoughts? Todd
Re: Revoking select on a single table
[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]
