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

Column privileges prevent from direct history insert but not from timestamp tampering

    XMLWordPrintable

    Details

      Description

      I don't see anything wrong with the behavior, so I guess it's just a documentation point; but I'm filing it referring to the general definition of the feature "whenever timestamp tampering works, insert should too".

      if (! `SELECT @@secure_timestamp = 'NO'`)
      {
        --die # We want unsecure timestamp for this exercise
      }
       
      create database db;
      create table db.t (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
       
      create user historian@localhost;
      grant select, delete on db.t to historian@localhost;
      grant insert (a) on db.t to historian@localhost;
       
      --connect(con1,localhost,historian,,db)
       
      set @@timestamp= unix_timestamp('2022-01-01');
      insert into t (a) values (1);
      set @@timestamp= unix_timestamp('2023-12-31');
      delete from t where a = 1;
      set @@timestamp= default;
      select * from t for system_time all;
       
      set system_versioning_insert_history= on;
       
      insert into t (a,s,e) values (2,'2022-01-01','2023-12-31');
       
      # Cleanup
      --disconnect con1
      --connection default
      drop database db;
      drop user historian@localhost;
      

      bb-10.11-MDEV-16546 87fca0525

      mysqltest: At line 24: query 'insert into t (a,s,e) values (2,'2022-01-01','2023-12-31')' failed: ER_COLUMNACCESS_DENIED_ERROR (1143): INSERT command denied to user 'historian'@'localhost' for column 's' in table 't'
      

      So, if the user only has INSERT privilege on the normal column a, inserting directly into versioning columns is prohibited (seems natural).
      But generating history via @@timestamp change still works – and again, technically there is nothing wrong there, the user has a permission to modify the timestamp and they only insert into the allowed column.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              greenman Ian Gilfillan
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.