Bug #90610 ERROR 1142 (42000) when using JSON_TABLE
Submitted: 24 Apr 15:07 Modified: 17 Aug 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 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 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 21:56] Miguel Solorzano
Please provide the dump file to create and populate the table. Thanks.
[25 Apr 7:06] Umesh Shastry
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 7:40] Gregory Graf
Thank you!
Maybe it could be helpful to add a note into the offial mysql docs.
[25 Apr 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 9:07] Umesh Shastry
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 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 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 12:10] Sinisa Milivojevic
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 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 14:13] Sinisa Milivojevic
Hi,

This is now a verified bug in the  MySQL server's privilege system.
[17 Aug 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.