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]
