Hi Sergei!
On Tue, Sep 22, 2020 at 8:31 PM Sergei Golubchik
Hi, Aleksey!
Wow, this comes so late, sorry! :(
But it's fixVersion=10.6 and on the top my list, so here you are.
On May 07, Aleksey Midenkov wrote:
The conclusion, I believe, is that system_versioning_modify_history should allow INSERTs when secure_timestamp=NO, and it should allow UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER.
I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER?
Sure, I meant back then that INSERT/UPDATE/DELETE would be allowed when secure_timestamp=SUPER.
But now I think we shouldn't really allow UPDATE/DELETE at all.
I wrote earlier that "Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can use the BINLOG command and truly edit the history arbitrarily, by faking row events."
Which is true, but we want to change it. In https://jira.mariadb.org/browse/MDEV-18432.
So, it's looks reasonable to allow only INSERTs - they can fake new history, which is needed for mysqldump, but at least there will be no command to selectively delete or modify history.
The second thing I don't like at all, is when a table is created like
CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING
with row_start/row_end implicit. You don't have it in the test, but anyway one should be able to load history into such a table, while the table does not have row_start and row_end columns. From the user point of view these columns don't exist, they're pseudo-columns, like ROWID. They just cannot be insertable-into, conceptually. But a user will want to restore the history, right? I don't have a solution for this yet :( Any ideas?
We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that?
Yes, you're right.
Then for the purpose of mysqldump they should be insertable-into. And mysqldump should only care to list them explicitly in the column list, like with any other insivible columns. This looks easy.
And mysqldump should take care of setting system_versioning_insert_history sysvar. Right? Is everything else in the task description OK? 1. Add server variable system_versioning_insert_history which will allow INSERT history rows. 2. If secure_timestamp is YES or REPLICATION, system_versioning_insert_history does not have effect. If secure_timestamp is SUPER, system_versioning_insert_history requires special privilege (same as for setting current timestamp).
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
-- All the best, Aleksey Midenkov @midenok