Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27898

CREATE VIEW AS SELECT involving JSON column requires root privileges

    XMLWordPrintable

Details

    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

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              ilgrosso Francesco Chicchiriccò
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.