2

I'm running MySQL 5.1.73-0ubuntu0.10.04.1-log and I'm trying to make it so a user can LOCK TABLES. I GRANTed LOCK TABLEs with the following:

  • GRANT SELECT, LOCK TABLES ON *.* TO 'dbuser';

That responds with Query OK, 0 rows affected (0.00 sec).

After that, when I login as dbuser, and try to do LOCK TABLES table_name WRITE; I get the following error:

ERROR 1044 (42000): Access denied for user 'dbuser'@'localhost' to database 'dbname'

I do SHOW GRANTS as dbuser and do not see any GRANT for LOCK TABLES.

Any ideas?

neubert
  • 15,947
  • 24
  • 120
  • 212

2 Answers2

0

It´s been long time since you post this question. Anyway in my opinion you should be more specific on what database to lock. Perhaps is not quite good to lock all of them (.) because mysql itself uses a database for system puporses so once you adquire a lock to a table you have only access to that table as documentation says. Perhaps the information about locking is stored on that system database named mysql. So you're locking the one wich store the lock itself.

leonardo
  • 1
  • I wasn't trying to lock an entire database - just a table. Is it even possible to lock an entire database? – neubert Nov 16 '17 at 17:47
  • To help make leonardo's answer helpful to people who land here, a possible frustration with grant lock tables is that all of the tables in a database .* have to be specified because a table lock will implicitly lock other tables involved in a foreign key constraint, for example. One cannot grant lock tables on a single table, so the grant applies to an entire database although one would never likely lock all tables in a database. I see no lock database concept in MySQL. – Rich Andrews Nov 03 '20 at 16:26
0

It's been a while since this question was asked, but for anyone having the same issue, try running FLUSH PRIVILEGES; in MySQL.

We Are All Monica
  • 13,000
  • 8
  • 46
  • 72