Description:
In MySQL 5.7, it was not necessary to have the LOCK TABLES grant if you executed SELECT ... LOCK IN SHARE MODE. Now, in 8.0, this extra grant is required. This grant is not required for UPDATE statements, which also lock rows during transactions.
How to repeat:
* Install MySQL 8.0
* Create user with SELECT privs
* Attempt to SELECT .. LOCK IN SHARE MODE using above user.
* Observe failure.
--- 5.7
bash-4.2$ mysql -ulocktest -plocktest1 world
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.27-30 Percona Server (GPL), Release 30, Revision 8916819
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW GRANTS;
+-----------------------------------------------------+
| Grants for locktest@localhost |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'locktest'@'localhost' |
| GRANT SELECT ON `world`.* TO 'locktest'@'localhost' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM city WHERE name = 'Houston';
+------+---------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------+------------+
| 3796 | Houston | USA | Texas | 1953631 |
+------+---------+-------------+----------+------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM city WHERE name = 'Houston' LOCK IN SHARE MODE;
+------+---------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------+------------+
| 3796 | Houston | USA | Texas | 1953631 |
+------+---------+-------------+----------+------------+
1 row in set (0.00 sec)
--- 8.0
root@502563941daf:/# mysql -ulocktest -plocktest1234 world
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 312
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants for 'locktest'@'localhost';
+-----------------------------------------------------+
| Grants for locktest@localhost |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `locktest`@`localhost` |
| GRANT SELECT ON `world`.* TO `locktest`@`localhost` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM city WHERE name = 'Houston';
+------+---------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------+------------+
| 3796 | Houston | USA | Texas | 1953631 |
+------+---------+-------------+----------+------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM city WHERE name = 'Houston' LOCK IN SHARE MODE;
ERROR 1142 (42000): SELECT with locking clause command denied to user 'locktest'@'localhost' for table 'city'
(as root user, add necessary grant)
mysql> GRANT LOCK TABLES ON world.* TO 'locktest'@'localhost'; Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql> show grants for locktest@localhost;
+------------------------------------------------------------------+
| Grants for locktest@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `locktest`@`localhost` |
| GRANT SELECT, LOCK TABLES ON `world`.* TO `locktest`@`localhost` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
(as app user)
mysql> SELECT * FROM city WHERE name = 'Houston' LOCK IN SHARE MODE;
+------+---------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------+------------+
| 3796 | Houston | USA | Texas | 1953631 |
+------+---------+-------------+----------+------------+
1 row in set (0.00 sec)
Suggested fix:
To maintain compatibility with 5.7, I suggest the LOCK IN SHARE MODE behavior be reverted to comply with 5.7 behavior. Or the documentation needs to be updated to reflect this new behavior.
