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