Hello, Sergei! On Fri, May 3, 2019 at 8:43 PM Sergei Golubchik <serg@mariadb.com> wrote:
Hi, Aleksey!
On May 03, Aleksey Midenkov wrote:
revision-id: ef2519fee4e (versioning-1.0.5-17-gef2519fee4e) parent(s): 56145be2951 author: Aleksey Midenkov <midenok@gmail.com> committer: Aleksey Midenkov <midenok@gmail.com> timestamp: 2018-06-28 13:42:09 +0300 message:
MDEV-16546 System versioning setting to allow history modification
1. Add server variable system_versioning_modify_history which will allow to set values for row_start, row_end in DML operations.
2. If secure_timestamp is YES or REPLICATION, system_versioning_modify_history does not have effect. If secure_timestamp is SUPER, system_versioning_modify_history requires special privilege (same as for setting current timestamp).
I thought more about this idea. We don't really want to have the history editable, do we?
Well, I'm thinking about rollback table data to specific point in time. That could be a useful feature.
But it's needed for replication, to keep the master and slave identical. That's what secure_timestamp is for.
The idea was that this new variable, system_versioning_modify_history, will be just a convenience feature, it will not allow history editing any more than one can do without it.
But now I suspect that even with secure_timestamp=NO one cannot truly edit history. One can only insert new rows with arbitrary timestamps. For example, to insert a row with row_start=1000 and row_end=2000, one needs to do (if secure_timestamp=NO):
set timestamp=1000; insert row; set timestamp=2000; delete row;
But I don't see how one can update or delete a history row with secure_timestamp=NO.
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.
I don't really get it why this is so important: since there is some limitation by configuration and privilege, we are just fine. Everything can be changed at filesystem level after all.
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?
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?
See below a couple of minor comments about the patch itself.
...
These are going to be fixed.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
-- All the best, Aleksey Midenkov @midenok