Bug #97399 Bogus warning when using grant on localhost + skip-name-resolve
Submitted: 28 Oct 2019 14:27 Modified: 29 Oct 2019 4:56
Reporter: Jaime Crespo (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:8.0.16, 8.0.18, 5.7.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: grant, grants, privileges, skip-name-resolve, warning

[28 Oct 2019 14:27] Jaime Crespo
Description:
When running a server configured with skip-name-resolve, and granting permissions to a an account with hostname@localhost, the following warning is given:

GRANT SELECT ON heartbeat.heartbeat TO 'nagios'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@db1114[(none)]> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------
| Level   | Code | Message                                                                                              
+---------+------+------------------------------------------------------------------------------------------------------
| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work
+---------+------+------------------------------------------------------------------------------------------------------

This is actually untrue, the grant actually takes effect, as tested by running:

sudo -u nagios mysql -u nagios -e "select * FROM heartbeat.heartbeat"

(this user had been setup with the auth_socket plugin)

select user, host, plugin FROM mysql.user where user = 'nagios';
+--------+-----------+-------------+
| user   | host      | plugin      |
+--------+-----------+-------------+
| nagios | localhost | auth_socket |
+--------+-----------+-------------+
1 row in set (0.00 sec)

How to repeat:
* enable auth_socket
* create user @localhost IDENTIFIED WITH auth_socket
* Grant privileges to such account, for example:
 GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'prometheus'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@db1114[(none)]> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1285
Message: MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work
1 row in set (0.00 sec)

* Check that you indeed have privileges:
sudo -u prometheus mysql -u prometheus -e "SHOW PROCESSLIST"
{processlist ensues}

Suggested fix:
This is essentially the same bug as Bug #81441, which was accepted and resolved, but instead of at log time, at grant time.
[29 Oct 2019 4:56] Umesh Shastry
Hello Jaime,

Thank you for the report and feedback.
Verified as described with 8.0.18 build.

regards,
Umesh