Bug #90610 | ERROR 1142 (42000) when using JSON_TABLE | ||
---|---|---|---|
Submitted: | 24 Apr 2018 15:07 | Modified: | 17 Aug 2018 14:42 |
Reporter: | Gregory Graf | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
Version: | 8.0.11 | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | json_table |
[24 Apr 2018 15:07]
Gregory Graf
[24 Apr 2018 15:19]
Gregory Graf
JSON_TABLE is working with root user: mysql -h 127.0.0.1 -u root -proot test SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; +------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+ 3 rows in set (0.00 sec)
[24 Apr 2018 21:56]
MySQL Verification Team
Please provide the dump file to create and populate the table. Thanks.
[25 Apr 2018 7:06]
MySQL Verification Team
Imho user 'test'@'127.0.0.1' needs at least "SELECT" privileges. Please grant SELECT privileges to 'test'@'127.0.0.1' and it should work: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; +------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+ 3 rows in set (0.00 sec) mysql> create user 'test'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> grant usage on *.* to 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -utest -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO `test`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'json_table' mysql> mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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> grant usage,select on *.* to 'test'@'localhost'; Query OK, 0 rows affected (0.06 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -utest -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test@localhost | +-------------------------------------------+ | GRANT SELECT ON *.* TO `test`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; +------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+ 3 rows in set (0.00 sec)
[25 Apr 2018 7:40]
Gregory Graf
Thank you! Maybe it could be helpful to add a note into the offial mysql docs.
[25 Apr 2018 8:35]
Gregory Graf
when i grant usage and select privileges on *.*, the user got (read) access to other databases and tables. is there a way to use JSON_TABLE w/o grant select privileges on all databases? root@d3c0f55a759d:/# mysql -u test -ptest test mysql> show grants; +------------------------------------------------+ | Grants for test@% | +------------------------------------------------+ | GRANT USAGE ON *.* TO `test`@`%` | | GRANT ALL PRIVILEGES ON `test`.* TO `test`@`%` | +------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.01 sec) mysql> ^DBye root@d3c0f55a759d:/# mysql -u root -proot mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> CREATE DATABASE newdb; Query OK, 1 row affected (0.09 sec) mysql> USE newdb; Database changed mysql> CREATE TABLE newtable (id INT, data JSON); Query OK, 0 rows affected (0.15 sec) mysql> INSERT INTO newtable VALUES (1, "[1,2,3,4,5]"); Query OK, 1 rows affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> ^DBye root@d3c0f55a759d:/# mysql -u test -ptest mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) mysql> ^DBye root@d3c0f55a759d:/# mysql -u root -proot mysql> grant usage, select on *.* to 'test'@'%'; mysql> ^DBye root@d3c0f55a759d:/# mysql -u test -ptest mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | newdb | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.01 sec) mysql> use newdb; Database changed mysql> show tables; +-----------------+ | Tables_in_newdb | +-----------------+ | newtable | +-----------------+ 1 row in set (0.00 sec) mysql> select * from newtable; +------+-----------------+ | id | data | +------+-----------------+ | 1 | [1, 2, 3, 4, 5] | +------+-----------------+ 1 rows in set (0.00 sec)
[25 Apr 2018 9:07]
MySQL Verification Team
Please note that I used "select on *.*" grants for testing purpose on my box and should not be done unless it is required in your case. My intention was to show you that due to lack of "SELECT" privileges you are getting access denied error. Thanks, Umesh
[26 Apr 2018 12:52]
Jon Stephens
This is a software bug. Per https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_select "The SELECT privilege enables you to select rows from tables in a database. SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database."
[3 May 2018 20:16]
William Chiquito
$ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 66 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test@localhost | +-------------------------------------------+ | GRANT SELECT ON *.* TO `test`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; +------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+ 3 rows in set (0.00 sec) -- NEW GRANTS mysql> SHOW GRANTS; +------------------------------------------------+ | Grants for test@localhost | +------------------------------------------------+ | GRANT USAGE ON *.* TO `test`@`localhost` | | GRANT SELECT ON `test`.* TO `test`@`localhost` | +------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'json_table'
[4 May 2018 12:10]
MySQL Verification Team
Hi, What is not clear from your example is what was your current database when you tested this. Try setting database `test` , if it exists, and running the same command again.
[4 May 2018 14:03]
William Chiquito
Hello, I add an example with more details: $ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test@localhost | +-------------------------------------------+ | GRANT SELECT ON *.* TO `test`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> USE `test`; Database changed mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; +------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+ 3 rows in set (0.00 sec) -- NEW GRANTS $ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 test@localhost | +------------------------------------------------+ | GRANT USAGE ON *.* TO `test`@`localhost` | | GRANT SELECT ON `test`.* TO `test`@`localhost` | +------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) mysql> USE `test`; Database changed mysql> SELECT * -> FROM -> JSON_TABLE( -> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', -> "$[*]" COLUMNS( -> xval VARCHAR(100) PATH "$.x", -> yval VARCHAR(100) PATH "$.y" -> ) -> ) AS jt1; ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'json_table'
[4 May 2018 14:13]
MySQL Verification Team
Hi, This is now a verified bug in the MySQL server's privilege system.
[17 Aug 2018 14:42]
Jon Stephens
Documented fix as follows in the MySQL 8.0.13 changelog: SELECT ... FROM JSON_TABLE() sometimes failed with a permissions error for a user other than MySQL root. Closed.