[MXS-3172] Database grants with escape characters do not work (strip_db_esc) Created: 2020-09-10  Updated: 2020-10-26  Resolved: 2020-10-26

Status: Closed
Project: MariaDB MaxScale
Component/s: mariadbclient
Affects Version/s: 2.5.3
Fix Version/s: 2.5.6

Type: Bug Priority: Major
Reporter: Anthony Assignee: Esa Korhonen
Resolution: Fixed Votes: 1
Labels: None
Environment:

Percona XtraDB cluster 5.7.29

Sprint: MXS-SPRINT-117

 Description   

Dear all,

Since the migration to Maxscale 2.4 -> 2.5 (worked well in 2.4), we cannot connect to any database via mysql command:
mysql -u test -ptest -P 3306 -h XXXX: works
mysql -u test -ptest -P 3406 -h XXXX: doesn't works: ERROR 1044 (42000): Access denied for user 'test '@'XXXX' to database 'test'

SELECT permissions has been added to tables_priv & columns_priv also. Not added for roles_mapping because this table doesn't exist with Percona.

Maxscale logs (with info):

2020-09-10 16:53:39   info   : (1) Found matching user entry 'test'@'%' for client 'test'@'XXXX' but user does not have access to database 'test'.
 
2020-09-10 16:53:39   info   : Read 28 user@host entries from 'XXXX' for service 'XX-Service'. The data was identical to existing user data.
 
2020-09-10 16:53:39   warning: (1) [mariadbclient] Authentication failed for user 'test'@[XXXX] to service 'XX-Service'. Originating listener: 'XX-Listener'.
 
MariaDB error: 'Access denied for user 'test'@'XXXX' to database 'test''.
 
2020-09-10 16:53:39   info   : Stopped XX-Service client session [1]

Maxscale conf:

[maxscale]
 
threads=auto
 
writeq_high_water=64Mi
 
writeq_low_water=32Mi
 
 
 
[XXXX1]
 
type=server
 
protocol=MariaDBBackend
 
address=XXXX1
 
port=3306
 
priority=2
 
 
 
[XXXX2]
 
type=server
 
protocol=MariaDBBackend
 
address=XXXX2
 
port=3306
 
priority=1
 
 
 
[MariaDB-Monitor]
 
type=monitor
 
module=galeramon
 
servers=XXXX1,XXXX2
 
user=XX
 
password=XX
 
monitor_interval=2000
 
available_when_donor=true
 
use_priority=true
 
 
 
[dt-acc-db-Service]
 
type=service
 
router=readwritesplit
 
servers=XXXX1,XXXX2
 
user=XX
 
password=XX
 
connection_keepalive=10
 
 
 
[XX-Listener]
 
type=listener
 
service=XX-Service
 
protocol=MariaDBClient
 
address=0.0.0.0
 
port=3406

Best regards,

 Comments   
Comment by markus makela [ 2020-09-11 ]

Can you show the grants for that user? Looks like a missing grant on the test database.

Comment by Anthony [ 2020-09-11 ]

Dear @markus,

You can find here the grants:
Maxscale service:

| GRANT SHOW DATABASES ON *.* TO 'mxs'@'%'            |
 
| GRANT SELECT ON `mysql`.`tables_priv` TO 'mxs'@'%'  |
 
| GRANT SELECT ON `mysql`.`proxies_priv` TO 'mxs'@'%' |
 
| GRANT SELECT ON `mysql`.`user` TO 'mxs'@'%'         |
 
| GRANT SELECT ON `mysql`.`columns_priv` TO 'mxs'@'%' |
 
| GRANT SELECT ON `mysql`.`db` TO 'mxs'@'%'           |

Test user:

| GRANT USAGE ON *.* TO 'test'@'%'                                                |
 
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'%' |

Please note that the DB name has been sanitized in this ticket. (the original name contain XX_XX (with underscore) instead test name)

Comment by markus makela [ 2020-09-11 ]

Can you create a reproducible test case that we can try?

Comment by Anthony [ 2020-09-11 ]

Here an example (password included):

MySQL cli on Percona directly:

$ echo 'show databases' | mysql -h XXXXX -u aaaaweb -pTT75z9zZG6qb aaaaaaaa_aaaaaaa_aaaaaaa -P 3306 | grep aa
 
Warning: Using a password on the command line interface can be insecure.
 
aaaaaaaa_aaaaaaa_aaaaaaa

MySQL cli on Maxscale:

echo 'show databases' | mysql -h XXXXX -u aaaaweb -pTT75z9zZG6qb aaaaaaaa_aaaaaaa_aaaaaaa -P 3406 | grep aa
 
Warning: Using a password on the command line interface can be insecure.
 
ERROR 1044 (42000): Access denied for user 'aaaaweb'@'XXXXX' to database 'aaaaaaaa_aaaaaaa_aaaaaaa'

MySQL grants:

| Grants for aaaaweb@%                                                    |
 
+-------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'aaaaweb'@'%'                                     |
 
| GRANT ALL PRIVILEGES ON `aaaaaaaa\_aaaaaaa\_aaaaaaa`.* TO 'aaaaweb'@'%' |

Best regards,

Comment by markus makela [ 2020-09-11 ]

Thanks for the test case, we'll see if we can reproduce it.

Comment by markus makela [ 2020-10-12 ]

I tested this with the upcoming 2.5.5 release of MaxScale with MariaDB 10.5.5 and it worked as expected when both MaxScale and MariaDB were running locally. Have you been able to test whether you see this problem with MariaDB? Does the problem only occur when the database has underscores in the name?

In addition, if you can execute the authentication queries listed here for MaxScale 2.5 we can see what the actual data for the user is. It might be that Percona formats the user data in some other format which causes problems with the authentication in MaxScale.

Comment by Cuchac [ 2020-10-12 ]

Hello,

I can reproduce it in 2.5.4.

It is caused by escaped wildcard character '_' in `mysql.db` table in `db` column. I guess `strip_db_esc=true` was developped just for this case but probably stopped working in 2.5.
I believe wildcard characters should be escaped to avoid matching different databases.

To reproduce, just create a grant for user to database 

sample\_database

and it does not work. After removing backslash, it starts to work. But the backslash is required there.

Generated at Thu Feb 08 04:19:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.