mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
22e41dae88
Restrict access to KEY_PERIOD_USAGE: show the constraint record iff any non-select privilege on any table column is granted. Also drop the unprivileged user in the end of test and add merge anchor.
118 lines
4.8 KiB
Text
118 lines
4.8 KiB
Text
select * from information_schema.periods;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'InnoDB'
|
|
Warning 1286 Unknown storage engine 'InnoDB'
|
|
Warning 1286 Unknown storage engine 'InnoDB'
|
|
create or replace table t1 (id int primary key, s timestamp(6), e timestamp(6),
|
|
period for mytime(s,e));
|
|
create or replace table t2 (id int primary key, s timestamp(6), e timestamp(6),
|
|
period for mytime(s,e),
|
|
vs timestamp(6) as row start,
|
|
ve timestamp(6) as row end,
|
|
period for system_time(vs, ve))
|
|
with system versioning;
|
|
show columns from t1;
|
|
Field Type Null Key Default Extra
|
|
id int(11) NO PRI NULL
|
|
s timestamp(6) NO NULL
|
|
e timestamp(6) NO NULL
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
def test t1 mytime s e
|
|
def test t2 SYSTEM_TIME vs ve
|
|
def test t2 mytime s e
|
|
create user periods_hidden@localhost;
|
|
grant create on test.nonexist to periods_hidden@localhost;
|
|
connect chopped,localhost,periods_hidden,,test;
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
connection default;
|
|
grant select(id) on test.t1 to periods_hidden@localhost;
|
|
connection chopped;
|
|
connection default;
|
|
revoke select(id) on test.t1 from periods_hidden@localhost;
|
|
connection chopped;
|
|
connection default;
|
|
grant update(id) on test.t1 to periods_hidden@localhost;
|
|
connection chopped;
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
def test t1 mytime NULL NULL
|
|
connection default;
|
|
grant select(s) on test.t1 to periods_hidden@localhost;
|
|
connection chopped;
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
def test t1 mytime s NULL
|
|
connection default;
|
|
grant select(e) on test.t2 to periods_hidden@localhost;
|
|
connection chopped;
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
def test t1 mytime s NULL
|
|
def test t2 SYSTEM_TIME NULL NULL
|
|
def test t2 mytime NULL e
|
|
connection default;
|
|
grant update on test.t2 to periods_hidden@localhost;
|
|
connection chopped;
|
|
select * from information_schema.periods where table_schema = 'test';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
|
|
def test t1 mytime s NULL
|
|
def test t2 SYSTEM_TIME vs ve
|
|
def test t2 mytime s e
|
|
connection default;
|
|
drop tables t1, t2;
|
|
# MDEV-32503 Queries from KEY_PERIOD_USAGE don't obey case-sensitivity
|
|
create table t (a int, b date, c date, period for app(b,c),
|
|
unique idx(a, app without overlaps));
|
|
set names latin1 collate latin1_general_cs;
|
|
select table_name from information_schema.periods where table_schema = 'TEST';
|
|
table_name
|
|
select table_name from information_schema.key_period_usage where table_schema = 'TEST';
|
|
table_name
|
|
set names latin1 collate latin1_general_ci;
|
|
select table_name from information_schema.periods where table_schema = 'TEST';
|
|
table_name
|
|
select table_name from information_schema.key_period_usage where table_schema = 'TEST';
|
|
table_name
|
|
# [DUPLICATE] MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME
|
|
# does not work
|
|
select constraint_name from information_schema.key_period_usage where table_name = 't';
|
|
constraint_name
|
|
idx
|
|
select constraint_name from information_schema.key_period_usage where constraint_name = 'idx';
|
|
constraint_name
|
|
idx
|
|
drop table t;
|
|
# MDEV-32501 KEY_PERIOD_USAGE reveals information to unprivileged user
|
|
create table t (a int, b date, c date, f int, period for app(b, c),
|
|
primary key(a, app without overlaps));
|
|
grant select (f) on t to periods_hidden@localhost;
|
|
connection chopped;
|
|
select period_name from information_schema.key_period_usage where table_name = 't';
|
|
period_name
|
|
connection default;
|
|
grant update (f) on t to periods_hidden@localhost;
|
|
connection chopped;
|
|
select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
|
|
can be seen constraint_name period_name
|
|
can be seen PRIMARY app
|
|
connection default;
|
|
revoke update (f) on t from periods_hidden@localhost;
|
|
connection chopped;
|
|
update t set f = 1;
|
|
ERROR 42000: UPDATE command denied to user 'periods_hidden'@'localhost' for table `test`.`t`
|
|
select period_name from information_schema.key_period_usage where table_name = 't';
|
|
period_name
|
|
connection default;
|
|
grant alter on t to periods_hidden@localhost;
|
|
connection chopped;
|
|
select 'can be seen', constraint_name, period_name from information_schema.key_period_usage where table_name = 't';
|
|
can be seen constraint_name period_name
|
|
can be seen PRIMARY app
|
|
connection default;
|
|
drop table t;
|
|
disconnect chopped;
|
|
connection default;
|
|
drop user periods_hidden@localhost;
|