Paul DuBois wrote:
At 12:59 -0500 11/2/05, Michael Stassen wrote:
BÁRTHÁZI András wrote:
<snip>
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'user'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxxx'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES,
INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `metazin`.*
TO 'user'@'localhost'
<snip>
[EMAIL PROTECTED] has global permission to lock tables in all databases.
That's enough to allow him/her to see all databases. Because you can
only lock a table from which you can SELECT, I expect [EMAIL PROTECTED]
should only have LOCK TABLES permission for metazin.*. So, I think
you need to
REVOKE LOCK TABLES ON *.* FROM [EMAIL PROTECTED];
GRANT LOCK TABLES ON metazin.* TO [EMAIL PROTECTED];
Neither FLUSH PRIVILEGES, FLUSH QUERY_CACHE, nor restarting mysqld are
necessary with GRANT AND REVOKE. See the manual for more
<http://dev.mysql.com/doc/refman/5.0/en/grant.html>.
The user has two global privileges: CREATE TEMPORARY TABLES and LOCK
TABLES.
Either is enough to allow all databases to be seen, because any global
privilege applies to any database.
Well, some privileges are strictly global, but should not affect SHOW DATABASES.
The FILE privilege is one example. In any case, you are right about "CREATE
TEMPORARY TABLES ON *.*" causing all dbs to show up. I should have suggested
REVOKE CREATE TEMPORARY TABLES, LOCK TABLES ON *.* FROM [EMAIL PROTECTED];
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON metazin.* TO [EMAIL PROTECTED];
Probably because temporary tables live in TMPDIR and do not show up in the
output of SHOW TABLES, I hadn't really thought of temp tables being part of a
db. Now I know better, though I still find it a little strange.
On the other hand, I would argue that LOCK TABLES shouldn't affect SHOW
DATABASES. LOCK TABLES ON *.* is only partially global, as you may only lock a
table for which you have the SELECT privilege. That is, the LOCK TABLES
privilege changes what I can do with tables I can already access, but it has no
effect on *which* tables I can access. Currently, however, LOCK TABLES ON *.*
does affect the output of SHOW DATABASES. I think that's slightly strange
behavior, but perhaps the moral is "Don't grant a privilege globally when
granting it locally would do."
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
