Grant check for query analyzer

[Chris]

While working with the MySQL Enterprise Dashboard I found a small problem when trying to use the Query Analyzer. The test server I am running on had a user used by the dev team that had the following privileges:

GRANT USAGE ON *.* TO 'test_user'@'10.18.%' IDENTIFIED BY PASSWORD 'HASHNOTGIVEN';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, TRIGGER ON `canoe`.* TO 'test_user'@'10.18.%';
GRANT SELECT on `mysql`.`proc` TO 'test_user'@'10.18.%';

I the agent running on this server is mysqlmonitoragent-2.1.0.1093 and the proxy has been set to receive connections on the default port, 6446. When I try to connect I received the following error:

shell> mysql -utest_user -p --host=m120649.ningops.com --port=6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

I was able to use the test_user user to login on port 3306:

shell> mysql -utest_user -p --host=m120649.ningops.com --port=3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\\\g.
Your MySQL connection id is 2017
Server version: 5.5.1-m2-log MySQL Community Server (GPL)

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

mysql> \\\\q
Bye

Needless to say I was surprised to see localhost in the error message given that I was connecting through TCP/IP port 6446. After I placed the localhost grants on the master server

-- logged in through 3306
[e-mail]3@m1203c0:/local/home/eng#mysql -utest_user -p --host=m120649.ningops.com --port=3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\\\g.
Your MySQL connection id is 2017
Server version: 5.5.1-m2-log MySQL Community Server (GPL)

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

mysql> \\\\q
Bye

On the master server I had to add localhost privileges to the test_user user.

mysql> show grants for 'test_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD 'HASHNOTGIVEN' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, TRIGGER ON `canoe`.* TO 'test_user'@'localhost' |
| GRANT SELECT ON `mysql`.`proc` TO 'test_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

A couple things to remember when using the Query Analyzer:

1. Don't use it in production unless you have to
2. Make sure the user(s) have the same localhost privileges as they do for none localhost entries

[Chris]

You nailed it with the back end port! Anyway... I thought it was good to post given that I could not find this in the installation docs or FAQs. The Enterprise Dashboard is sweet in that you can turn the query analyzer on a off on three different levels.

Good luck with your installation.

[Yingkuan Liu]

So that's because the user connection going though agent proxy backend port, and agent running on localhost? Interesting. We were about to setup similar things, good to know.