Re: Column info is available when a column is secured
mysql.db contains rows for the following grants:
GRANT ALL PRIVILEGES ON `test`.* TO ''@'%'
GRANT ALL PRIVILEGES ON `test\_%`.* TO ''@'%'
This is why everyone has access to the "test" database and all databases that start with "test_".
Executing:
SHOW GRANTS FOR ''@'%';
gives the following error:
ERROR 1141: There is no such grant defined for user '' on host '%'
If you try to revoke the grant on the "test" database with:
REVOKE ALL ON `test`.* FROM ''@'%';
you get the error:
ERROR 1141: There is no such grant defined for user '' on host '%'
However, if you execute:
GRANT USAGE on `test`.* to ''@'%';
before:
SHOW GRANTS FOR ''@'%';
the following grants are returned:
+-------------------------------------------------------------------------------------------------------+
| Grants for @% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ''@'%' |
| GRANT ALL PRIVILEGES ON `test`.* TO ''@'%' |
| GRANT ALL PRIVILEGES ON `test\_%`.* TO ''@'%' |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Now the grants can be revoked with:
REVOKE ALL on `test`.* from ''@'%';
REVOKE ALL on `test\_%`.* from ''@'%';
The following error is displayed for the second revoke, but it still seems to execute properly.
ERROR:
Unknown command '\_'.
I'm assuming this is a bug.
I believe the reason for this strange behavior is that mysql.user is not initialized with a row for ''@'%'. My guess is that the SHOW GRANTS FOR user command joins mysql.user with mysql.db on the user and host. Because there is no row for ''@'%' in mysql.user, no results are returned. After executing "GRANT USAGE on `test`.* to ''@'%';", the row is added to mysql.user so SHOW GRANTS FOR user returns the grants correctly.
This join must not be done when a user accesses the test and test_% databases, since that seems to work properly.
It seems to me that it is a bug that mysql.user is not initialized with a row for ''@'%'. I'll file this as a bug if I don't hear otherwise in the next day or so.
Gail Badner
SourceLabs
Dependable Open Source Systems
Subject
Views
Written By
Posted
7072
April 25, 2005 06:35PM
2941
April 25, 2005 09:27PM
2803
April 26, 2005 01:23PM
2915
April 26, 2005 06:47PM
Re: Column info is available when a column is secured
5845
April 26, 2005 08:51PM
3173
April 26, 2005 09:41PM
2922
April 27, 2005 10:29AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.