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'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:
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
