Description:
A copy of this bug report can be found at
http://www.jlab.org/~marki/mysql_bug_2/order_in_grant.txt
Is order important when issuing grant commands?
Linux, Fedora Core 3
MySQL server version: 4.1.11-standard, installed via RPM.
perl DBI module installed from CPAN on top of this version.
I am trying to allow all privileges to a given user to a given
database from inside my local domain, but allow only select privileges
to the same user on the same database from outside that domain. If I
use the "bad" order (see below) of the grant commands, I get a
situation where the local user does not have insert privileges. If I
use the "good" order then privileges seem to be granted as expected.
This issue was discussed on the mysql list, the thread was
http://lists.mysql.com/mysql/182637
A bug report was submitted by Gleb Paharenko
http://bugs.mysql.com/bug.php?id=9952
but he emphasized a problem with flush privileges. That bug report is
in the "can't repeat" state. The problem I am having does not appear
to me to have anything to do with flush privileges. I am hoping
someone can have a fresh look at it and so am submitting a new bug report.
How to repeat:
What follows is a transcript of a session which illustrates the
problem. It is assumed that there is a table called testtable in the
database testdb. A mysqldump of the table is at
http://www.jlab.org/~marki/mysql_bug_2/testdb.sql
==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
testuser@%.jlab.org
|
+-----------------------------------------------------------------------------------------------------------------------+
| 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
testuser@%.jlab.org
|
+-----------------------------------------------------------------------------------------------------------------------+
| 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==
Suggested fix:
None known.
