SHOW GRANTS incorrectly shows grants for user@ '%'

Bug #1486005 reported by Rick Pizzi on 2015-08-18
22
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Invalid
Undecided
Unassigned
5.5
Triaged
Medium
Unassigned
5.6
Triaged
Medium
Unassigned
5.7
Triaged
Medium
Unassigned

Bug Description

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>

Download full text (3.5 KiB)

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.% |
+-------------------------------------------------------------------------------------------------------...

Read more...

Changed in percona-server:
status: New → Confirmed
tags: added: regression
Rick Pizzi (pizzi) wrote :

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

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>

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>

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.

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.