| 1 |
create user foo; |
| 2 |
create database some_db; |
| 3 |
create table some_db.t1 (a int, b int, secret int); |
| 4 |
create role r_select_column; |
| 5 |
create role r_active_column; |
| 6 |
grant r_select_column to r_active_column; |
| 7 |
grant r_active_column to foo; |
| 8 |
grant select(a) on some_db.t1 to r_select_column; |
| 9 |
select * from mysql.tables_priv order by user; |
| 10 |
Host Db User Table_name Grantor Timestamp Table_priv Column_priv |
| 11 |
localhost mysql mariadb.sys global_priv root@localhost 0000-00-00 00:00:00 Select,Delete |
| 12 |
some_db r_select_column t1 root@localhost 0000-00-00 00:00:00 Select |
| 13 |
grant insert(a) on some_db.t1 to r_active_column; |
| 14 |
select * from mysql.tables_priv order by user; |
| 15 |
Host Db User Table_name Grantor Timestamp Table_priv Column_priv |
| 16 |
localhost mysql mariadb.sys global_priv root@localhost 0000-00-00 00:00:00 Select,Delete |
| 17 |
some_db r_active_column t1 root@localhost 0000-00-00 00:00:00 Insert |
| 18 |
some_db r_select_column t1 root@localhost 0000-00-00 00:00:00 Select |
| 19 |
connect con1, localhost, foo,,; |
| 20 |
insert into some_db.t1(a) values (1); |
| 21 |
ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table `some_db`.`t1` |
| 22 |
set role r_active_column; |
| 23 |
insert into some_db.t1(a) values (1); |
| 24 |
disconnect con1; |
| 25 |
connection default; |
| 26 |
revoke insert(a) on some_db.t1 from r_active_column; |
| 27 |
connect con1, localhost, foo,,; |
| 28 |
insert into some_db.t1(a) values (1); |
| 29 |
ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table `some_db`.`t1` |
| 30 |
set role r_active_column; |
| 31 |
insert into some_db.t1(a) values (1); |
| 32 |
ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table `some_db`.`t1` |
| 33 |
disconnect con1; |
| 34 |
connection default; |
| 35 |
drop role r_select_column; |
| 36 |
drop role r_active_column; |
| 37 |
drop user foo; |
| 38 |
drop database some_db; |