show grants syntax contains inconsistencies with MySQL

[morgo]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

There are inconsistencies with MySQL SHOW GRANTS. Some of them make TiDB user administration more susceptible to typos etc.

2. What did you expect to see?

In MySQL checking grants for a non-existing user (not dependent on SQL mode):

mysql [localhost] {msandbox} ((none)) > SHOW GRANTS for 'nonexistent';
ERROR 1141 (42000): There is no such grant defined for user 'nonexistent' on host '%'

In TiDB:

mysql> SHOW GRANTS FOR 'nonexistent';
Empty set (0.00 sec)

In MySQL there is a shortcut to show your current_user's permissions:

mysql [localhost] {msandbox} ((none)) > SHOW GRANTS;
+-------------------------------------------------------+
| Grants for msandbox@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

In TiDB (Fixed in #7954 ):

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
Empty set (0.00 sec)

In MySQL a non-privileged user has the "usage" privilege assigned:

mysql [localhost] {msandbox} ((none)) > CREATE USER nonprivileged;
Query OK, 0 rows affected (0.00 sec)

...

mysql [localhost] {nonprivileged} ((none)) > SHOW GRANTS;
+-------------------------------------------+
| Grants for nonprivileged@%                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'nonprivileged'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

In TiDB (notwithstanding that the SHOW GRANTS shortcut mentioned above), the user has an empty set of permissions (Fixed in #7955 ):

mysql> CREATE USER nonprivileged;
Query OK, 1 row affected (0.01 sec)
..
mysql> SHOW GRANTS FOR 'nonprivileged'@'%';
Empty set (0.00 sec)
..
mysql> GRANT ALL ON *.* TO 'nonprivileged';
Query OK, 0 rows affected (0.01 sec)
..
mysql> SHOW GRANTS FOR 'nonprivileged'@'%';
+----------------------------------------------------+
| Grants for nonprivileged@%                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'nonprivileged'@'%' |
+----------------------------------------------------+
1 row in set (0.00 sec)

3. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

I am running a feature branch close to master:

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: rc4-2058-g51680c45b
Git Commit Hash: 51680c45b3cb5dd81fddade39e68072071178ed1
Git Branch: load_data_ignore
UTC Build Time: 2018-09-04 03:24:13
GoVersion: go version go1.10.1 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

[crazycs520]

Thanks for your feedback.