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:
None 
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
Description:
When i try one of the examples from https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html i got the error:

ERROR 1142 (42000): SELECT command denied to user 'test'@'127.0.0.1' for table 'json_table'

SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.11                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+

ref: https://stackoverflow.com/questions/50001054/mysql-8-json-table-not-working

How to repeat:
docker run --name jsontabletest -e MYSQL_ROOT_PASSWORD=root -e MYSQL_USER=test -e MYSQL_PASSWORD=test -e MYSQL_DATABASE=test -d mysql:8

docker exec -it jsontabletest bash

mysql -h 127.0.0.1 -u test -ptest 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;
[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.