Gleb et al.,
I've tried something else. Fearing that the problem is due to some interaction with an already rather complicated set of privilege tables, I tried to reproduce the problem starting from a very simple mysql database. I was indeed able to reproduce the problem, but the order of "good" and "bad" grants is reversed! That is to say, in my original post, the good scenario was to grant the restricted select privilege to the entire world, and then grant all privileges to the local domain. In this new context, this order is the bad one. See the transcript below.
One difference is that the privileges listed when "all" is granted are enumerated in the show grant report with the simple mysql database rather than being reported as "ALL PRIVILEGES". This could be because, in previous posts, the mysql database used on the 4.1.11-standard server that I have been using for these tests was dumped from a server running 4.0.13-standard. (Or not. Dunno.)
- Mark
Here is the transcript:
==begin quote==
claspc2:marki:1026> mysql -uroot mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 152 to server version: 4.1.11-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> revoke all on testdb.* from testuser@"%"; Query OK, 0 rows affected (0.00 sec)
mysql> revoke all on testdb.* from testuser@"%.jlab.org"; Query OK, 0 rows affected (0.00 sec)
mysql> grant select on testdb.* to testuser@"%"; Query OK, 0 rows affected (0.00 sec)
mysql> grant all on testdb.* to testuser@"%.jlab.org"; Query OK, 0 rows affected (0.00 sec)
mysql> quit Bye claspc2:marki:1027> mysql -hclaspc2.jlab.org -utestuser testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 153 to server version: 4.1.11-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants for current_user();
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for [EMAIL PROTECTED] |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%.jlab.org' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `testdb`.* TO 'testuser'@'%.jlab.org' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into testdb.testtable set a=7;
ERROR 1044 (42000): Access denied for user 'testuser'@'%.jlab.org' to database 'testdb'
claspc2:marki:1028> mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 154 to server version: 4.1.11-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> revoke all on testdb.* from testuser@"%"; Query OK, 0 rows affected (0.00 sec)
mysql> revoke all on testdb.* from testuser@"%.jlab.org"; Query OK, 0 rows affected (0.00 sec)
mysql> grant all on testdb.* to testuser@"%.jlab.org"; Query OK, 0 rows affected (0.00 sec)
mysql> grant select on testdb.* to testuser@"%"; Query OK, 0 rows affected (0.00 sec)
mysql> quit Bye claspc2:marki:1029> mysql -hclaspc2.jlab.org -utestuser testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 155 to server version: 4.1.11-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants for current_user();
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for [EMAIL PROTECTED] |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%.jlab.org' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `testdb`.* TO 'testuser'@'%.jlab.org' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into testdb.testtable set a=7; Query OK, 1 row affected (0.01 sec)
==end quote==
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
