At 12:59 -0500 11/2/05, Michael Stassen wrote:
BÁRTHÁZI András wrote:
Hi Michael,
Thanks for your help.
When I migrated from 3.23 to 4.0 version (if
I'm remember well), I think I missed to
upgrade something, so all my MySQL users are
able to see the list of the databases on my
server. Currently, the MySQL version is
5.0.15, i ran the mysql database upgrade
script, but it still is a problem. What
should I do, to hide the databases, and my
users just see the databases they have rights
for to select/etc. from? Or is it the normal
behaviour?
By "the mysql database upgrade script", do you
mean mysql_fix_privilege_tables? Did you
follow the upgrade instructions in
Yes, I wasn't clear enough.
the manual
<http://dev.mysql.com/doc/refman/5.0/en/upgrade.html>?
If you did, then the problem is probably with
user privileges.
I think I did everything listed there, except I
didn't dumped and imported all my tables. Maybe
missed something, but don't know what. I ran
the mysql_fix_privilege_tables when I was
running MySQL 5.0, but I don't think it's
important.
The default in 4.0 and higher is to only show
databases for which a user has privileges, but
this can be overridden for an individual user
with the SHOW DATABASES privilege
<http://dev.mysql.com/doc/refman/5.0/en/show-databases.html>.
If you believe [EMAIL PROTECTED] can see databases
he shouldn't, the first thing to do is to run
SHOW GRANTS FOR [EMAIL PROTECTED];
to see what privileges he has. Post the
output if you need help interpreting it.
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'
When I ran SHOW GRANTS first, the SHOW
DATABASES was there for this user. Then I
revoked it, plus said FLUSH PRIVILEGES, FLUSH
QUERY_CACHE, but SHOW DATABASES still shows all
the databases for me logged in with that user
with the mysql shell. I tried to check the
mysql.db and mysql.user tables manually, but
found nothing strange in them.
Now I restarted the MySQL server, but no changes.
Bye,
Andras
[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.
--
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]
