Access denied to remote user when doing LOCK TABLES

I'm running mariadb-server v10.2.33-1 on two openwrt-qemu systems. I want a remote user to achieve LOCK TABLE on a particular table.

In the local mysql server, I have created a remote user and granted LOCK TABLES privilege using the following commands:

CREATE USER 'root'@'192.168.%' IDENTIFIED BY 'root'; GRANT SELECT ON `qkd`.`RawKeyStatus` TO 'root'@'192.168.%'; GRANT LOCK TABLES ON `qkd`.* TO 'root'@'192.168.%'; FLUSH PRIVILEGES; 

Upon running SHOW GRANTS in the remote user system, I can notice the LOCK TABLES privilege has been assigned to 'root'@'192.168.%'. The remote user also has SELECT privilege on the table qkd.RawKeyStatus.

However LOCK TABLE qkd.RawKeyStatus READ; command in the remote system fails with the error:

ERROR 1044 (42000): Access denied for user 'root'@'192.168.%' to database 'qkd' 

What am I missing ?

3 Answers

Run SHOW PROCESSLIST; nd see from which System the remote user is login and change the GRANT if it not 192.168,%

MariaDB [(none)]> show processlist; +------+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +------+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 3357 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +------+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.01 sec) MariaDB [(none)]> 

UPDATE: The issue was related to the wildcard 192.168.%. In order to give GRANT to all users of the subnet 192.168.A.B a correct wildcard would be 192.168.%.%

A more safer option is to use a subnet mask:

GRANT ... TO 'user'@'192.168.0.0/255.255.255.0' IDENTIFIED BY ... 

Now LOCK TABLE qkd.RawKeyStatus READ; command runs perfectly with the output Query OK, 0 rows affected (0.02 sec)

Reference: How to grant remote access to MySQL for a whole subnet?

A quick workaround is to pass the –-single-transaction option to mysqldump:

for example:

$ mysqldump --single-transaction -u user -p DBNAME > backup.sql 

ref: