[MXS-716] Access Denied: User without global privileges on a schema but with grants only on some tables can't connect if the default schema is specified in the connection string Created: 2016-05-10  Updated: 2020-08-25  Resolved: 2016-08-10

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: 1.4.3
Fix Version/s: 2.0.0

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: None


 Description   

If a user has only privileges on some objects/tables of a schema, he can't connect by specifying such schema as default schema for the connection:
If the schema is not specified, it can connect regularly.

MySQL [(none)]> show grants for 'pippo'@'%';
------------------------------------------------------------------------------------------------------

Grants for pippo@%

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

GRANT ALL PRIVILEGES ON `db3`.`a` TO 'pippo'@'%'

Schema specified:

$ mysql -upippo -p123 -h127.0.0.1 -P4006 db3
ERROR 1045 (28000): Access denied for user 'pippo'@'127.0.0.1' (using password: YES) to database 'db3'

No schema specified:

$ mysql -upippo -p123 -h127.0.0.1 -P4006
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2485
Server version: 10.0.0 1.4.3-maxscale MariaDB Server, wsrep_25.13.raf7f02e

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>



 Comments   
Comment by Timofey Turenko [ 2016-05-27 ]

I can't reproduce it.

I tried to create user with privilege only for one table:

GRANT SELECT ON d1.t1 TO 'table_privilege'@'%' IDENTIFIED BY 'pass'

and then connect:
mysql -utable_privilege -ppass -h $maxscale_IP -P 4006 d1
and
mysql -utable_privilege -ppass -h $maxscale_IP -P 4006

both work.

But if I check privileges:

MariaDB [d1]> show grants for table_privilege;
----------------------------------------------------------------------------------------------------------------

Grants for table_privilege@%

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

GRANT USAGE ON . TO 'table_privilege'@'%' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7'
GRANT SELECT ON `d1`.`t1` TO 'table_privilege'@'%'

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

I can not connect to Maxscale only if I trying with another database:
mysql -utable_privilege -ppass -h $maxscale_IP -P 4006 test
ERROR 1045 (28000): Access denied for user 'table_privilege'@'192.168.121.1' (using password: YES) to database 'test'

Comment by markus makela [ 2016-08-10 ]

The user data gathering query was modified to properly include all combinations of user, host and database.

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