Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3, 10.4, 10.5
-
None
Description
I suppose all of this is by design, but none is currently documented. So, if everything looks as expected, please confirm and re-redirect to Documentation.
1. Standard SELECT privilege allows access to historical data in system-versioned tables. It's not as obvious as it seems, because deleting history requires a special privilege other than ordinary DELETE, so one could expect the same for viewing history. However, it is not so, and there seems to be no way around it – whoever has SELECT access to a column, can also see all earlier versions of the data.
create database db; |
create table db.t (a int) with system versioning; |
insert into db.t values (1); |
update db.t set a = 2; |
create user foo; |
grant select (a) on db.t to foo; |
|
--connect (con1,localhost,foo,,)
|
select a from db.t for system_time all; |
|
# Cleanup
|
--disconnect con1
|
--connection default
|
drop database db; |
drop user foo; |
10.5 6cee9b19 |
MariaDB [(none)]> select a from db.t for system_time all; |
+------+ |
| a |
|
+------+ |
| 2 |
|
| 1 |
|
+------+ |
2 rows in set (0.001 sec) |
2. Even with only column access, a user can also access the automatically generated period columns (row_start, row_end), which may look like a violation of the privilege system:
MariaDB [(none)]> select a, row_start, row_end from db.t for system_time all; |
+------+----------------------------+----------------------------+ |
| a | row_start | row_end |
|
+------+----------------------------+----------------------------+ |
| 2 | 2020-07-03 19:57:29.516885 | 2038-01-19 05:14:07.999999 |
|
| 1 | 2020-07-03 19:57:29.509583 | 2020-07-03 19:57:29.516885 |
|
+------+----------------------------+----------------------------+ |
2 rows in set (0.001 sec) |
|
MariaDB [(none)]> show grants;
|
+-------------------------------------------+ |
| Grants for foo@% | |
+-------------------------------------------+ |
| GRANT USAGE ON *.* TO `foo`@`%` | |
| GRANT SELECT (a) ON `db`.`t` TO `foo`@`%` | |
+-------------------------------------------+ |
2 rows in set (0.001 sec) |
A workaround for this is creating the columns explicitly rather than relying on the automatic ones:
create database db; |
create table db.t (a int, row_start timestamp(6) as row start invisible, row_end timestamp(6) as row end invisible, period for system_time(row_start,row_end)) with system versioning; |
insert into db.t values (1); |
update db.t set a = 2; |
create user foo; |
grant select (a) on db.t to foo; |
|
--connect (con1,localhost,foo,,)
|
select a, row_start, row_end from db.t for system_time all; |
|
# Cleanup
|
--disconnect con1
|
--connection default
|
drop database db; |
drop user foo; |
MariaDB [(none)]> select a, row_start, row_end from db.t for system_time all; |
ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'row_start' in table 't' |