1

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 ?

Arijit Pramanick
  • 11
  • 4

3 Answers3

0

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)]>
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
0

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?

Arijit Pramanick
  • 11
  • 4
0

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

for example:

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

ref: https://michaelrigart.be/mysqldump-1044-access-denied-using-lock-tables/

Khalid Bin Huda
  • 1,583
  • 17
  • 16