Description:
The permission error message is not clear，Please refer to [How to repeat]
How to repeat:
session1 (create new user):
mysql> create user read_only identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on mysql.* to read_only;
Query OK, 0 rows affected (0.01 sec)
session2（login database）:
root~# mysql -h127.0.0.1 -uread_only -p123456 -P3380
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.01 sec)
mysql> select * from abc.user;
ERROR 1142 (42000): SELECT command denied to user 'read_only'@'127.0.0.1' for table 'user'
mysql>
Suggested fix:
I have only authorized the query permission of the mysql database for the read_only user. If I use the read_only user to log in to the database and query a table in other databases, I get an error that I do not have permission to access this table.
I think this error message is incomplete,
The complete error message should be
ERROR 1142 (42000): SELECT command denied to user 'read_only'@'127.0.0.1' for table 'abc.user'
In most cases, the DBA will only receive the MySQL error message from the developer, and the developer will not feedback the executed SQL statement.
Therefore, the error information is incomplete, and it is very easy for us to fall into the misunderstanding of troubleshooting. I hope these details can be optimized.
