create user foo;
create database some_db;
create table some_db.t1 (a int, secret int);
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
grant select on *.* to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT ON *.* TO `foo`@`%`
#############################
# Test global level denies. #
#############################
#
# Test masking global level denies.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
disconnect con1;
connection default;
#
# Mask all rigths.
#
deny select on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
#
# Not all rights masked.
#
grant insert on *.* to foo;
#
# some_db should now show up in the list because insert is not masked.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
disconnect con1;
connection default;
deny insert on *.* to foo;
#
# some_db should not be present now.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
grant show databases on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
disconnect con1;
connection default;
deny show databases on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
#
# Test masking database level grants with global denies.
#
grant select on some_db.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
grant update on some_db.* to foo;
connect  con1,localhost,foo,,;
#
# Update not masked via global deny, some_db should show up.
#
show databases;
Database
information_schema
some_db
test
disconnect con1;
connection default;
deny update on *.* to foo;
connect  con1,localhost,foo,,;
#
# Now it should show up.
#
show databases;
Database
information_schema
test
disconnect con1;
connection default;
#
# Test masking table level grants with global denies.
#
connection default;
grant insert on some_db.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, SHOW DATABASES ON *.* TO `foo`@`%`
GRANT SELECT, UPDATE ON `some_db`.* TO `foo`@`%`
GRANT INSERT ON `some_db`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
grant delete on some_db.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, SHOW DATABASES ON *.* TO `foo`@`%`
GRANT SELECT, UPDATE ON `some_db`.* TO `foo`@`%`
GRANT INSERT, DELETE ON `some_db`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
#
# some_db should show up because we have delete rights on t1.
#
show databases;
Database
information_schema
some_db
test
disconnect con1;
connection default;
deny delete on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
#
# Test masking column level grants with global denies.
#
grant references (a) on some_db.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, DELETE, SHOW DATABASES ON *.* TO `foo`@`%`
GRANT SELECT, UPDATE ON `some_db`.* TO `foo`@`%`
GRANT INSERT, DELETE, REFERENCES (a) ON `some_db`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
some_db
test
disconnect con1;
connection default;
deny references on *.* to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, DELETE, REFERENCES, SHOW DATABASES ON *.* TO `foo`@`%`
GRANT SELECT, UPDATE ON `some_db`.* TO `foo`@`%`
GRANT INSERT, DELETE, REFERENCES (a) ON `some_db`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
#
# Test masking procedure / function / package level grants with global
# denies.
#
create procedure some_db.proc_1()
begin
select 1;
end|
create function some_db.func_1() returns int
begin
return 3;
end|
set @old_sql_mode=@@sql_mode|
set sql_mode=ORACLE|
create package some_db.util_functions as
function f1(id int) return int;
end|
create package body some_db.util_functions as
function f1(id int) return int as result int;
begin
return 10;
end;
end|
grant execute on procedure some_db.proc_1 to foo;
grant execute on function some_db.func_1 to foo;
grant execute on package some_db.util_functions to foo;
set sql_mode=@old_sql_mode;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
some_db
test
disconnect con1;
connection default;
deny execute on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
disconnect con1;
connection default;
drop user foo;
drop database some_db;
###############################
# Test database level denies. #
###############################
create user foo;
grant select on *.* to foo;
#
# Test masking database level denies.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
#
# Mask all rights.
#
deny select on mysql.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
#
# Not all rights masked.
#
grant insert on *.* to foo;
grant insert on mysql.* to foo;
#
# mysql db should now show up in the list because insert is not masked.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny insert on mysql.* to foo;
#
# mysql db should not be present now.
#
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
grant show databases on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny show databases on *.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT ON `mysql`.* TO `foo`@`%`
grant update on mysql.* to foo;
connect  con1,localhost,foo,,;
#
# Update not masked via database deny, mysql should show up.
#
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny update on mysql.* to foo;
connect  con1,localhost,foo,,;
#
# Now it should not show up.
#
show grants;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
#
# Test masking table level grants with global denies.
#
create table mysql.t1 (a int);
connection default;
grant insert on mysql.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
GRANT INSERT ON `mysql`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
grant delete on mysql.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
GRANT INSERT, DELETE ON `mysql`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
#
# mysql should show up because we have delete rights on t1.
#
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny delete on mysql.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
#
# Test masking column level grants with global denies.
#
grant references (a) on mysql.t1 to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO `foo`@`%`
GRANT INSERT, DELETE, REFERENCES (a) ON `mysql`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny references on mysql.* to foo;
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, DELETE, REFERENCES ON `mysql`.* TO `foo`@`%`
GRANT INSERT, DELETE, REFERENCES (a) ON `mysql`.`t1` TO `foo`@`%`
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
#
# Test masking procedure / function / package level grants with global
# denies.
#
create procedure mysql.proc_1()
begin
select 1;
end|
create function mysql.func_1() returns int
begin
return 3;
end|
set @old_sql_mode=@@sql_mode|
set sql_mode=ORACLE|
create package mysql.util_functions as
function f1(id int) return int;
end|
create package body mysql.util_functions as
function f1(id int) return int as result int;
begin
return 10;
end;
end|
grant execute on procedure mysql.proc_1 to foo;
grant execute on function mysql.func_1 to foo;
grant execute on package mysql.util_functions to foo;
set sql_mode=@old_sql_mode;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
disconnect con1;
connection default;
deny execute on mysql.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv
where user = 'foo';
user	host	JSON_EXTRACT(priv, '$.deny')
foo	%	{"global": 16384, "db": [{"name": "`mysql`", "access": 264207}], "version_id": VERSION_ID}
show grants for foo;
Grants for foo@%
GRANT SELECT, INSERT, SHOW DATABASES ON *.* TO `foo`@`%`
DENY SHOW DATABASES ON *.* TO `foo`@`%`
GRANT INSERT, UPDATE ON `mysql`.* TO `foo`@`%`
DENY SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE ON `mysql`.* TO `foo`@`%`
GRANT INSERT, DELETE, REFERENCES (a) ON `mysql`.`t1` TO `foo`@`%`
GRANT EXECUTE ON PROCEDURE `mysql`.`proc_1` TO `foo`@`%`
GRANT EXECUTE ON FUNCTION `mysql`.`func_1` TO `foo`@`%`
GRANT EXECUTE ON PACKAGE `mysql`.`util_functions` TO `foo`@`%`
create database some_db;
grant all on some_db.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
some_db
sys
test
disconnect con1;
connection default;
deny all on some_db.* to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
performance_schema
sys
test
disconnect con1;
connection default;
#
# Removing a deny for show databases globaly should make some_db show up.
#
revoke deny all on *.* from foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
disconnect con1;
connection default;
drop database some_db;
drop table mysql.t1;
drop procedure mysql.proc_1;
drop function mysql.func_1;
set @old_sql_mode=@@sql_mode;
set sql_mode=ORACLE;
drop package body mysql.util_functions;
drop package mysql.util_functions;
set sql_mode=@old_sql_mode;
drop user foo;
#
# Test table level denies interacting with show databases
#
# If a user gets access to a database via a higher level grant,
# but has all the resources in that database denied individually,
# the user should still be able to see the database, empty.
#
create database some_db;
create user foo;
create table some_db.t1 (a int);
grant select on *.* to foo;
deny select on some_db.t1 to foo;
connect  con1,localhost,foo,,;
#
# some_db should still be visible, but it should show up as empty.
#
show databases;
Database
information_schema
mtr
mysql
performance_schema
some_db
sys
test
show tables from some_db;
Tables_in_some_db
disconnect con1;
connection default;
drop user foo;
drop database some_db;
#
# Test table level denies masking table and column level grants.
#
connection default;
create user foo;
create database some_db;
create table some_db.t1 (a int, b int);
grant select on some_db.t1 to foo;
grant insert(a) on some_db.t1 to foo;
connect  con1,localhost,foo,,;
use some_db;
show tables;
Tables_in_some_db
t1
show full columns from some_db.t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES		NULL		select,insert	
b	int(11)	NULL	YES		NULL		select	
disconnect con1;
connection default;
deny select on some_db.t1 to foo;
connect  con1,localhost,foo,,;
use some_db;
show tables from some_db;
Tables_in_some_db
t1
show full columns from some_db.t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES		NULL		insert	
disconnect con1;
connection default;
deny insert on some_db.t1 to foo;
#
# Now all rights for t1 are denied.
#
connect  con1,localhost,foo,,;
use some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
show tables from some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
disconnect con1;
connection default;
drop user foo;
drop database some_db;
#
# Test column level denies masking column level grants.
#
connection default;
create user foo;
create database some_db;
create table some_db.t1 (a int, b int);
grant insert(a) on some_db.t1 to foo;
deny select(a) on some_db.t1 to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
some_db
test
use some_db;
show tables from some_db;
Tables_in_some_db
t1
show full columns from some_db.t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES		NULL		insert	
disconnect con1;
connection default;
deny insert(a) on some_db.t1 to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
use some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
show tables from some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
show full columns from some_db.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
disconnect con1;
connection default;
grant select(b) on some_db.t1 to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
some_db
test
use some_db;
show tables from some_db;
Tables_in_some_db
t1
show full columns from some_db.t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
b	int(11)	NULL	YES		NULL		select	
disconnect con1;
connection default;
connection default;
deny select(b) on some_db.t1 to foo;
connect  con1,localhost,foo,,;
show databases;
Database
information_schema
test
use some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
show tables from some_db;
ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
show full columns from some_db.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
disconnect con1;
connection default;
drop user foo;
drop database some_db;