[#MDEV-23087] Standard SELECT privilege allows access to temporal data and automatic columns

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'

[MDEV-23087] Standard SELECT privilege allows access to temporal data and automatic columns Created: 2020-07-03 Updated: 2022-04-08 Resolved: 2022-04-08
Status:	Closed
Project:	MariaDB Server
Component/s:	Documentation, Versioned Tables
Affects Version/s:	10.3, 10.4, 10.5
Fix Version/s:	N/A

[MDEV-23087] Standard SELECT privilege allows access to temporal data and automatic columns Created: 2020-07-03 Updated: 2022-04-08 Resolved: 2022-04-08