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.
createdatabase db;
createtable db.t (a int, b date, c date, f int, period for app(b,c), primarykey(a, app without overlaps));
createuser u@localhost;
grantselect (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
dropuser u@localhost;
dropdatabase 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 NULLNULL
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.
Attachments
Issue Links
is caused by
MDEV-22597Add views for periods in information_schema
Closed
relates to
MDEV-32500Information schema leaks table names and structure to unauthorized users
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.
Nikita Malyavin
added a comment - 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.
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.
Elena Stepanova
added a comment - 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.
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.
Sergei Golubchik
added a comment - 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.
Bug
Critical
MDEV-22597 Add views for periods in information_schema
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.