Re: MySQL Privileges - table privileges question
Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. That is correct. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
Is this a typical situation? If those 1 or 2 tables have higher security requirements than the rest, so some users should have access to all the tables except them, another option would be to move them to a separate db. Then you could grant the average user access to the db with the rest of the tables, but only privileged users get access to both dbs. This would be easier to maintain, at the cost of slightly complicating queries that use tables in both dbs. That is, you'd have to qualify those 1 or 2 tables with db names every time you want to join them to another table. On the other hand, avoiding table-level privileges can have performance benefits http://dev.mysql.com/doc/mysql/en/Query_Speed.html. Michael [EMAIL PROTECTED] wrote: I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
