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

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




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


        Issue Links



              greenman Ian Gilfillan
              elenst Elena Stepanova
              0 Vote for this issue
              2 Start watching this issue



                Git Integration

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