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

UPDATE through a view requires an UPDATE GRANT on columns used for JOIN

    XMLWordPrintable

Details

    Description

      When doing an UPDATE on a JOIN of views, mariadb requires that the user holds the UPDATE privilege on the columns referenced in the ON clause.

      Expected behavior: only SELECT should be needed on those columns. The = there are comparison operators, not assignments.

      Test setup:

      CREATE DATABASE test;
      use test;
       
      -- Create a couple tables and fill with data
      CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(255) );
      CREATE TABLE properties (item INT, value VARCHAR(255) );
       
      INSERT INTO items (id, name) VALUES (1, 'ball'), (2, 'pencil'), (3, 'bag');
      INSERT INTO properties (item, value) VALUES (1, 'red'), (2, 'black'), (3, 'blue');
       
      -- Create a user which can only update the value column, on the real table
      CREATE USER 'testuser1'@'%' IDENTIFIED BY 'demo';
      GRANT SELECT ON items TO 'testuser1'@'%';
      GRANT SELECT ON properties TO 'testuser1'@'%';
      GRANT UPDATE (value) ON properties TO 'testuser1'@'%';
       
      -- Create views mapping the tables
      CREATE VIEW itemview AS SELECT * FROM items;
      CREATE VIEW propertyview AS SELECT * FROM properties;
       
      -- Create a user which can only update the value column, using the views
      CREATE USER 'testuser2'@'%' IDENTIFIED BY 'demo';
      GRANT SELECT ON itemview TO 'testuser2'@'%';
      GRANT SELECT ON propertyview TO 'testuser2'@'%';
      GRANT UPDATE (value) ON propertyview TO 'testuser2'@'%';
      

      Test execution:

      # testuser1 can update the real table doing a JOIN
      mysql -u testuser1 -pdemo test -e "UPDATE items JOIN properties ON (id=item) SET value='yellow' WHERE name='ball';"
       
      # However, testuser2 needs UPDATE right on columns used for joining 
      mysql -u testuser2 -pdemo test -e "UPDATE itemview JOIN propertyview ON (id=item) SET value='blue' WHERE name='ball';"
      ERROR 1143 (42000) at line 1: UPDATE command denied to user 'testuser2'@'localhost' for column 'item' in table 'propertyview'
       
      # Although it works correctly if moving them to the WHERE clause
      mysql -u testuser2 -pdemo test -e "UPDATE itemview JOIN propertyview SET value='blue' WHERE name='ball' AND id=item;"
      

      Expected behavior:

      The second command should have succeeded, as in the real table ("You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified.").

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            Ángel Ángel
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.