Linux

This is comment history from bugzilla:

Comment 1 Massimiliano 2015-02-04 09:43:19 UTC
MaxScale GA needs database names and db grants in order to authenticate db name specified at connect time.

SHOW DATABASES and mysql.db additional grants are required, as described in the pdf docs.

If these grants are not available the authentication with db name fails.

Please give us an example of the user that doesn't work, including privilegs for table level: we can do additional checks.

Comment 2 lisu87 2015-02-04 14:08:05 UTC
Username: test-maxscale
Hosts: 172.16.100.24 (backend mysql server), 172.16.77.14 (maxscale)

Privileges:

GRANT SHOW DATABASES ON . TO 'test-maxscale'@'172.16.100.24' IDENTIFIED BY PASSWORD 'passhash'
GRANT SELECT ON `mysql`.`user` TO 'test-maxscale'@'172.16.100.24'
GRANT SELECT ON `mysql`.`db` TO 'test-maxscale'@'172.16.100.24'
GRANT SELECT ON `CS`.`Events` TO 'test-maxscale'@'172.16.100.24'
GRANT SHOW DATABASES ON . TO 'test-maxscale'@'172.16.77.14' IDENTIFIED BY PASSWORD 'passhash'
GRANT SELECT ON `mysql`.`user` TO 'test-maxscale'@'172.16.77.14'
GRANT SELECT ON `mysql`.`db` TO 'test-maxscale'@'172.16.77.14'
GRANT SELECT ON `CS`.`Events` TO 'test-maxscale'@'172.16.77.14'

Now I'm trying to access CS.Events with default db specified:

$ mysql -h 172.16.77.14 -P 4007 -u test-maxscale -p CS
Enter password:
ERROR 1045 (28000): Access denied for user 'test-maxscale'@'172.16.100.24' (using password: YES) to database 'CS'

Without default db specified I'm able to connect and use CS db:

$ mysql -h 172.16.77.14 -P 4007 -u test-maxscale -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1089
Server version: 5.5.41-MariaDB (Ubuntu)

Copyright (c) 2000, 2011, 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> use CS;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
--------------

--------------

--------------
1 row in set (0.00 sec)

But if grant test-maxscale@172.16.100.24 and test-maxscale@172.16.77.14 with SELECT privilege on db level everything works just fine, as shown below.

Privileges:

GRANT SHOW DATABASES ON . TO 'test-maxscale'@'172.16.100.24' IDENTIFIED BY PASSWORD 'passhash'
GRANT SELECT ON `CS`.* TO 'test-maxscale'@'172.16.100.24'
GRANT SELECT ON `mysql`.`db` TO 'test-maxscale'@'172.16.100.24'
GRANT SELECT ON `mysql`.`user` TO 'test-maxscale'@'172.16.100.24'
GRANT SHOW DATABASES ON . TO 'test-maxscale'@'172.16.77.14' IDENTIFIED BY PASSWORD 'passhash'
GRANT SELECT ON `CS`.* TO 'test-maxscale'@'172.16.77.14'
GRANT SELECT ON `mysql`.`user` TO 'test-maxscale'@'172.16.77.14'
GRANT SELECT ON `mysql`.`db` TO 'test-maxscale'@'172.16.77.14'

$ mysql -h 172.16.77.14 -P 4007 -u test-maxscale -p CS
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1089
Server version: 5.5.41-MariaDB (Ubuntu)

Copyright (c) 2000, 2011, 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.

Am I missing something?

Comment 3 Massimiliano 2015-02-04 16:37:52 UTC
The user configured in service sections must have the privileges to SHOW DATABASES, mysql.user, mysql.db

Would you mind checking this and share us the maxscale configuration file as well?
Comment 4 lisu87 2015-02-04 17:07:33 UTC
Created attachment 182 [details]
current configuration of maxscale

Comment 5 lisu87 2015-02-04 17:08:38 UTC
The user configured in services are 'maxscale' and it already has SHOW DATABASES and other required privileges:

GRANT SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'maxscale'@'172.16.77.14' IDENTIFIED BY PASSWORD 'passhash'
GRANT SELECT ON `mysql`.* TO 'maxscale'@'172.16.77.14'

Configuration file attached here: http://bugs.mariadb.com/attachment.cgi?id=182

Comment 6 Massimiliano 2015-02-05 12:00:22 UTC
Current MaxScale GA authentication with DB name at connect time requires such a privilege:

GRANT SELECT ON `CS`.* TO 'test-maxscale'@'172.16.77.14', the SELECT privilege on db level

It's not enough for db name authentication the table level grant:

GRANT SELECT ON `CS`.`Events` TO 'test-maxscale'@'172.16.77.14'

Comment 7 lisu87 2015-02-05 12:52:48 UTC
I'm glad that you confirmed it.

Is it something you are going to change or it will stay as it is now?

Comment 8 Massimiliano 2015-02-05 16:37:08 UTC
We may enhance the database name authentication reading the content of mysql.tables_priv as well.

The aim is to cover such a common case:

GRANT SELECT ON `CS`.`Events` TO 'test-maxscale'@'172.16.77.14'

where one or more table of db are involved

Changing this to an improvement since it is known and intended behavior.

User grants are now detected at table level.

Fixed in commit c4bcc4ce88d97b7dc3c668beea83b443381b8594