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

CREATE VIEW AS SELECT involving JSON column requires root privileges

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

          relates to

          Task - A task that needs to be done. MDEV-17399 Add support for JSON_TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          links to

          Web Link [SYNCOPE-1662] Leverage MariaDB JSON type

          Activity

            Transition Time In Source Status Execution Times
            Elena Stepanova made transition -
            Open Needs Feedback
            		68d 8h 26m 1
            Elena Stepanova made transition -
            Needs Feedback Open
            		2d 21h 43m 1
            Alice Sherepa made transition -
            Open Confirmed
            		3h 55m 1

            People

              rucha174 Rucha Deodhar
              ilgrosso Francesco Chicchiriccò
              Votes:
              2 Vote for this issue
              Watchers:
              6 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.