[MDEV-27898] CREATE VIEW AS SELECT involving JSON column requires root privileges - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.8.2, 11.4.2, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.6, 11.4
Component/s: Server
Labels:
None
Environment:
Docker official image https://hub.docker.com/_/mariadb

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.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

show_variables.txt
2022-04-11 13:22
548 kB
Francesco Chicchiriccò

Issue Links

relates to

MDEV-17399 Add support for JSON_TABLE

Closed

links to

[SYNCOPE-1662] Leverage MariaDB JSON type

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Elena Stepanova added a comment - 2022-04-08 16:04

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;

Elena Stepanova added a comment - 2022-04-08 16:04 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;

Francesco Chicchiriccò added a comment - 2022-04-11 13:19 - edited

@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.

Francesco Chicchiriccò added a comment - 2022-04-11 13:19 - edited @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.

Alice Sherepa added a comment - 2022-04-11 17:40

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'

Alice Sherepa added a comment - 2022-04-11 17:40 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'

Francesco Chicchiriccò added a comment - 2022-12-26 15:30

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

Francesco Chicchiriccò added a comment - 2022-12-26 15:30 This issue looks still present in 10.10.2: can you please confirm?

Francesco Chicchiriccò added a comment - 2024-07-24 08:35

Almost two years later, no update.
I've just tested with 11.4.2 and this issue is still there.

Francesco Chicchiriccò added a comment - 2024-07-24 08:35 Almost two years later, no update. I've just tested with 11.4.2 and this issue is still there.

MariaDB Server

CREATE VIEW AS SELECT involving JSON column requires root privileges

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration