[MDEV-32501] INFORMATION_SCHEMA.KEY_PERIOD_USAGE reveals information to unprivileged user Created: 2023-10-17  Updated: 2024-01-15

Status: In Testing
Project: MariaDB Server
Component/s: Authentication and Privilege System, Information Schema
Affects Version/s: N/A
Fix Version/s: 11.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-22597 Add views for periods in information_... In Testing
Relates
relates to MDEV-32500 Information schema leaks table names ... Closed

 Description   

The general problem isn't new, see MDEV-32500 for some other examples (if it's indeed a problem at all, it remains to be confirmed or not).
If it is a problem, it appears that it affects only some I_S views, so possibly it has to be fixed on case-by-case basis; then it should definitely be fixed for new views before they are released. If, on the other hand, the fix is generic for all cases, then this issue can be closed as a duplicate of MDEV-32500.

create database db;
 
create table db.t (a int, b date, c date, f int, period for app(b,c), primary key(a, app without overlaps));
 
 
 
create user u@localhost;
 
grant select (f) on db.t to u@localhost;
 
 
 
--connect (con1,localhost,u,,db)
 
select period, start_column_name, end_column_name from information_schema.periods where table_name = 't';
 
select constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
drop user u@localhost;
 
drop database db;

Actual result, bb-11.3-periods-schema 24018c74fae6e5a93b93c2efefd60bde4ad1488e

 
select period, start_column_name, end_column_name from information_schema.periods where table_name = 't';
 
period	start_column_name	end_column_name
 
app	NULL	NULL
 
select constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
 
constraint_name	period_name
 
PRIMARY	app

Since the user isn't allowed to see the table structure or any columns involved into the period, I would expect both queries to return empty set.

 Comments   
Comment by Nikita Malyavin [ 2024-01-01 ]

The rule I was following in the implementations is "we show the record if the user has privileges on any table column" so the behavior seems correct to me. The case for PERIODS is covered by current tests. I guess it's also ok to see the constraint name if we can see at least one column or period of the constraint.

Comment by Elena Stepanova [ 2024-01-01 ]

This needs to be confirmed by serg, as the reasoning "the implementation follows certain logic, hence the logic is correct" isn't in itself sufficient.
It may be or may be not (correct); in MDEV-32500 Sergei categorized a few cases of a similar nature, some of which as a problem and some not, so it depends on where he places this one.

Comment by Sergei Golubchik [ 2024-01-01 ]

period is ok. It should show the period name if the user has any privilege on any of the table columns. According to the standard it should only show start/end columns to the schema owner, but as we don't have that, let's say any privilege on these columns is enough.

key_period_usage is not ok. It should only show something when a user has any non-SELECT privilege on any of the table columns.

Comment by Nikita Malyavin [ 2024-01-04 ]

please review commit f39fce87a4d link

Comment by Nikita Malyavin [ 2024-01-05 ]

some tests are failing yet

Comment by Nikita Malyavin [ 2024-01-08 ]

please review commit 3459e69

Comment by Sergei Golubchik [ 2024-01-09 ]

3459e69 is ok to push

Generated at Thu Feb 08 10:31:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.