SHOW GRANTS incorrectly shows grants for user@ '%'

[Rick Pizzi]

If an user has grants both from everywhere ('user'@'%') and from a certain network ('user'@'10.10.10.%) those grants are incorrectly reported when issuing a show grants for the user related to the network.

Tested on latest 5.6 Oracle and the bug is not there, so this looks to be a Percona only bug.

How to repeat:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select on test.* to 'testuser'@'%' identified by 'test';
Query OK, 0 rows affected (0.02 sec)

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

mysql> show grants for 'testuser'@'10.10.10.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@10.10.10.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'10.10.10.%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'testuser'@'10.10.10.%' |
| GRANT SELECT ON `test`.* TO 'testuser'@'%' |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

[Nilnandan Joshi (nilnandan-joshi)]

Verified with PS 5.6.25. Also checked with MySQL Community 5.6.25 but its not reproducible there.

nilnandan@desktop:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

mysql> show grants for 'testuser'@'10.10.10.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@10.10.10.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'10.10.10.%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'testuser'@'10.10.10.%' |
| GRANT SELECT ON `test`.* TO 'testuser'@'%' |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

With community server,

nilnandan.joshi@bm-support01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox27002.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.25-log MySQL Community Server (GPL)

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select on test.* to 'testuser'@'%' identified by 'test';
Query OK, 0 rows affected (0.09 sec)

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

mysql> show grants for 'testuser'@'10.10.10.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@10.10.10.% |
+-------------------------------------------------------------------------------------------------------...

[Laurynas Biveinis (laurynas-biveinis)]

This could be a regression from bug https://bugs.launchpad.net/percona-server/+bug/1354988 / http://bugs.mysql.com/bug.php?id=53645 changes

[Rick Pizzi (pizzi)]

This bug is not present in 5.5.41 Percona and also not present in 5.6.25 Oracle.

[Nilnandan Joshi (nilnandan-joshi)]

Verified with 5.5.44

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_5_45$ mysql -uroot -p --socket=/tmp/mysql_sandbox25544.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.44-37.3-log Percona Server (GPL), Release 37.3, Revision 729fbe2

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select on test.* to 'testuser'@'%' identified by 'test';
Query OK, 0 rows affected (0.10 sec)

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

mysql> show grants for 'testuser'@'10.10.10.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@10.10.10.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'10.10.10.%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'testuser'@'10.10.10.%' |
| GRANT SELECT ON `test`.* TO 'testuser'@'%' |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql>

[Nilnandan Joshi (nilnandan-joshi)]

Invalid for 5.1.73

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_1_73$ mysql -uroot -p --socket=/tmp/mysql_sandbox5173.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73-14.12 Percona Server (GPL), Release 14.12, Revision 624

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> grant select on test.* to 'testuser'@'10.10.10.%' identified by 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'testuser'@'10.10.10.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@10.10.10.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'10.10.10.%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'testuser'@'10.10.10.%' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

[Laurynas Biveinis (laurynas-biveinis)]

Since both assigned grants, as in upstream, and effective grants, as in current Percona Server, are desirable to have, we should introduce new syntax for the latter, i.e. SHOW EFFECTIVE GRANTS, and revert SHOW GRANTS to the upstream behavior.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-281