[MDEV-27898] CREATE VIEW AS SELECT involving JSON column requires root privileges Created: 2022-01-30  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.8.2, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Francesco Chicchiriccò Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Docker official image https://hub.docker.com/_/mariadb

Attachments: Text File show_variables.txt    
Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed

 Description   

The following statement returns error when run by an user created with GRANT ALL PRIVILEGES - it works anyway when run as root:

    CREATE VIEW user_search AS
 
 
 
    SELECT u.id as any_id, u.*, attrs.*
 
    FROM SyncopeUser u, JSON_TABLE(COALESCE(plainAttrs, '[{}]'), '$[*]' COLUMNS (
 
    plainSchema VARCHAR(255) PATH '$.schema',
 
    NESTED PATH '$.values[*]' COLUMNS (
 
    binaryValue LONGBLOB PATH '$.binaryValue',
 
    booleanValue INT PATH '$.booleanValue',
 
    dateValue BIGINT(20) PATH '$.dateValue',
 
    doubleValue DOUBLE PATH '$.doubleValue',
 
    longValue BIGINT(20) PATH '$.longValue',
 
    stringValue VARCHAR(255) PATH '$.stringValue'),
 
    attrUniqueValue JSON PATH '$.uniqueValue')
 
    ) AS attrs

The reported error message is

ANY command denied to user 'syncope'@'172.17.0.1' for table 'attrs'

The syncope user is able anyway to create views not involving JSON columns.

 Comments   
Comment by Roman [ 2022-02-15 ]

Thanks for the report ilgrosso,
I wonder if this issue is Columnstore-specific or it is actual for any engine, e.g. InnoDB? Could you share SHOW CREATE TABLE SyncopeUser ?

Comment by Roman [ 2022-02-15 ]

JFYI Columnstore doesn't support JSON as of now so I am moving this issue into the MDEV queue.

Comment by Francesco Chicchiriccò [ 2022-02-15 ]

@drrtuy FYI the engine was not set to InnoDB, the CREATE TABLE was set without specifying any engine, and the conf is the default from Docker image for Maria DB 10.7.1.

Comment by Roman [ 2022-02-15 ]

ilgrosso It should be InnoDB then. I asked the team to move your request into MDEV where InnoDB developers are. I will tell him about the issue also.

Comment by Elena Stepanova [ 2022-04-08 ]

I can't reproduce this.

Could you please paste or attach an unabridged output of the following from the client:

SHOW GRANTS;
 
    CREATE VIEW user_search AS
 
 
 
    SELECT u.id as any_id, u.*, attrs.*
 
    FROM SyncopeUser u, JSON_TABLE(COALESCE(plainAttrs, '[{}]'), '$[*]' COLUMNS (
 
    plainSchema VARCHAR(255) PATH '$.schema',
 
    NESTED PATH '$.values[*]' COLUMNS (
 
    binaryValue LONGBLOB PATH '$.binaryValue',
 
    booleanValue INT PATH '$.booleanValue',
 
    dateValue BIGINT(20) PATH '$.dateValue',
 
    doubleValue DOUBLE PATH '$.doubleValue',
 
    longValue BIGINT(20) PATH '$.longValue',
 
    stringValue VARCHAR(255) PATH '$.stringValue'),
 
    attrUniqueValue JSON PATH '$.uniqueValue')
 
    ) AS attrs;
 
SHOW CREATE TABLE SyncopeUser;
 
SHOW VARIABLES;

Comment by Francesco Chicchiriccò [ 2022-04-11 ]

@elenst here you go.

Connection output, from which server version can actually be spot:

mysql -u syncope -psyncope syncope
 
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 MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 42
 
Server version: 10.8.2-MariaDB-1:10.8.2+maria~focal mariadb.org binary distribution

Then, as requested:

MariaDB [syncope]> SHOW GRANTS;
 
+--------------------------------------------------------------------------------------------------------+
 
| Grants for syncope@%                                                                                   |
 
+--------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO `syncope`@`%` IDENTIFIED BY PASSWORD '*D776CEB35293A7E6EDD22D3D88B35B4DCC707EC3' |
 
| GRANT ALL PRIVILEGES ON `syncope`.* TO `syncope`@`%`                                                   |
 
+--------------------------------------------------------------------------------------------------------+


MariaDB [syncope]> CREATE VIEW user_search AS
 
    ->  
    ->     SELECT u.id as any_id, u.*, attrs.*
 
    ->     FROM SyncopeUser u, JSON_TABLE(COALESCE(plainAttrs, '[{}]'), '$[*]' COLUMNS (
 
    ->     plainSchema VARCHAR(255) PATH '$.schema',
 
    ->     NESTED PATH '$.values[*]' COLUMNS (
 
    ->     binaryValue LONGBLOB PATH '$.binaryValue',
 
    ->     booleanValue INT PATH '$.booleanValue',
 
    ->     dateValue BIGINT(20) PATH '$.dateValue',
 
    ->     doubleValue DOUBLE PATH '$.doubleValue',
 
    ->     longValue BIGINT(20) PATH '$.longValue',
 
    ->     stringValue VARCHAR(255) PATH '$.stringValue'),
 
    ->     attrUniqueValue JSON PATH '$.uniqueValue')
 
    ->     ) AS attrs;
 
ERROR 1142 (42000): ANY command denied to user 'syncope'@'localhost' for table 'attrs'


MariaDB [syncope]> SHOW CREATE TABLE SyncopeUser;
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| SyncopeUser | CREATE TABLE `SyncopeUser` (
 
  `id` varchar(36) NOT NULL,
 
  `creationContext` varchar(255) DEFAULT NULL,
 
  `creationDate` datetime(3) DEFAULT NULL,
 
  `creator` varchar(255) DEFAULT NULL,
 
  `lastChangeContext` varchar(255) DEFAULT NULL,
 
  `lastChangeDate` datetime(3) DEFAULT NULL,
 
  `lastModifier` varchar(255) DEFAULT NULL,
 
  `status` varchar(255) DEFAULT NULL,
 
  `changePwdDate` datetime(3) DEFAULT NULL,
 
  `cipherAlgorithm` varchar(20) DEFAULT NULL,
 
  `failedLogins` int(11) DEFAULT NULL,
 
  `lastLoginDate` datetime(3) DEFAULT NULL,
 
  `mustChangePassword` int(11) DEFAULT NULL,
 
  `password` varchar(255) DEFAULT NULL,
 
  `securityAnswer` varchar(255) DEFAULT NULL,
 
  `suspended` int(11) DEFAULT NULL,
 
  `token` text DEFAULT NULL,
 
  `tokenExpireTime` datetime(3) DEFAULT NULL,
 
  `username` varchar(255) DEFAULT NULL,
 
  `plainAttrs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`plainAttrs`)),
 
  `REALM_ID` varchar(36) DEFAULT NULL,
 
  `SECURITYQUESTION_ID` varchar(36) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `U_SYNCPSR_USERNAME` (`username`),
 
  KEY `REALM_ID` (`REALM_ID`),
 
  KEY `SECURITYQUESTION_ID` (`SECURITYQUESTION_ID`),
 
  CONSTRAINT `SyncopeUser_ibfk_1` FOREIGN KEY (`REALM_ID`) REFERENCES `Realm` (`id`),
 
  CONSTRAINT `SyncopeUser_ibfk_2` FOREIGN KEY (`SECURITYQUESTION_ID`) REFERENCES `SecurityQuestion` (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Finally, check show_variables.txt for SHOW VARIABLES output.

Comment by Alice Sherepa [ 2022-04-11 ]

Thank you! I repeated the problem on 10.6-10.8:

create view v1 as (select * from 
	json_table('[{"a":"1"}]', '$[*]' columns(a int path '$.a')  ) as jt);
 
 
 
create user u1@localhost;
 
grant ALL on test.* to u1@localhost;
 
 
 
--connect (con1,localhost,u1,,test)
 
 
 
create view v2 as (select * from 
	json_table('[{"a":"1"}]', '$[*]' columns(a int path '$.a')  ) as jt);


mysqltest: 
query 'create view v2 as (select * from 
json_table('[{"a":"1"}]', '$[*]' columns(a int path '$.a')  ) as jt)' failed: ER_TABLEACCESS_DENIED_ERROR (1142): ANY command denied to user 'u1'@'localhost' for table 'jt'

Comment by Francesco Chicchiriccò [ 2022-12-26 ]

This issue looks still present in 10.10.2: can you please confirm?

Generated at Thu Feb 08 09:56:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.