MySQL Bugs: #9952: changing privileges with grant command takes effect only after FLUSH PRIVILEGES

Bug #9952	changing privileges with grant command takes effect only after FLUSH PRIVILEGES
Submitted:	16 Apr 2005 14:22	Modified:	16 Apr 2005 19:37
Reporter:	Gleb Paharenko	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
According to the http://dev.mysql.com/doc/mysql/en/privilege-changes.html
 *Changes to global privileges and passwords take effect the next time the client connects.

But in my case it takes effect only after FLUSH PRIVILEGES.

How to repeat:
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| localhost |      |
| localhost | root |
+-----------+------+
2 rows in set (0.00 sec)

mysql> grant all privileges on testdb.* to 'testuser'@'%.ensita.net';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -htoyou.ensita.net -utestuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for current_user;
+-----------------------------------------------------------------+
| Grants for testuser@%.ensita.net                                |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%.ensita.net'                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'testuser'@'%.ensita.net' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> Bye
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant select on testdb.* to 'testuser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -htoyou.ensita.net -utestuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for current_user;
+-----------------------------------------------------------------+
| Grants for testuser@%.ensita.net                                |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%.ensita.net'                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'testuser'@'%.ensita.net' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> Bye
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -htoyou.ensita.net -utestuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for current_user;
+----------------------------------------------+
| Grants for testuser@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'         |
| GRANT SELECT ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)

We able to see that privileges changed only after 'FLUSH PRIVILEGES'. I was using only
GRANT command.

Suggested fix:
Change this behavior, or add to the manual advice to use 'FLUSH PRIVILEGES' after
GRANT statement.

Another example:

mysql> delete from mysql.user where user='testuser';
Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>  grant all on testdb.* to testuser@"%.ensita.net";
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on testdb.* to testuser@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -htoyou.ensita.net -utestuser testdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for current_user();
+-----------------------------------------------------------------+
| Grants for testuser@%.ensita.net                                |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%.ensita.net'                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'testuser'@'%.ensita.net' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into testdb.testtable set a=7;
ERROR 1044 (42000): Access denied for user 'testuser'@'%.ensita.net' to database 'testdb'
mysql> Bye
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -uroot -e 'flush privileges;'
[gleb@toyou mysql-debug-4.1.11-pc-linux-gnu-i686]$ ./bin/mysql --defaults-file=my.cnf -htoyou.ensita.net -utestuser testdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for current_user();
+----------------------------------------------+
| Grants for testuser@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'         |
| GRANT SELECT ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)

See also thread: 
  http://lists.mysql.com/mysql/182637

Hi!

I was unable to reproduce this bug with a recent 4.1.11 bk pull.