#
# WL#14303: More granular privilege control of the FLUSH operations
#
# Check who has the FLUSH_xx privileges.
# Success criteria: Should be root and 4 rows
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE LIKE 'FLUSH_%' ORDER BY 1,2,3;
GRANTEE	PRIVILEGE_TYPE	IS_GRANTABLE
'root'@'localhost'	FLUSH_OPTIMIZER_COSTS	YES
'root'@'localhost'	FLUSH_STATUS	YES
'root'@'localhost'	FLUSH_TABLES	YES
'root'@'localhost'	FLUSH_USER_RESOURCES	YES
# FR1: FLUSH HOSTS is deprecated
# Success criteria: deprecation warning
FLUSH HOSTS;
Warnings:
Warning	1287	'FLUSH HOSTS' is deprecated and will be removed in a future release. Please use TRUNCATE TABLE performance_schema.host_cache instead
# Success criteria: no warning
TRUNCATE TABLE performance_schema.host_cache;
# FR2: FLUSH_OPTIMIZER_COSTS should allow FLUSH OPTIMIZER_COSTS
CREATE USER wl14303@localhost;
GRANT FLUSH_OPTIMIZER_COSTS ON *.* TO  wl14303@localhost;
connect FLUSH_OPTIMIZER_COSTS_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH OPTIMIZER_COSTS;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_OPTIMIZER_COSTS_con;
DROP USER wl14303@localhost;
# FR3: FLUSH_STATUS should allow FLUSH STATUS
CREATE USER wl14303@localhost;
GRANT FLUSH_STATUS ON *.* TO  wl14303@localhost;
connect FLUSH_STATUS_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH STATUS;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_STATUS_con;
DROP USER wl14303@localhost;
# FR4: FLUSH_USER_RESOURCES should allow FLUSH USER_RESOURCES
CREATE USER wl14303@localhost;
GRANT FLUSH_USER_RESOURCES ON *.* TO  wl14303@localhost;
connect FLUSH_USER_RESOURCES_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH USER_RESOURCES;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_USER_RESOURCES_con;
DROP USER wl14303@localhost;
# FR5: FLUSH_TABLES should allow FLUSH TABLES
CREATE USER wl14303@localhost;
GRANT FLUSH_TABLES ON *.* TO wl14303@localhost;
CREATE TABLE t1(a int);
GRANT ALL PRIVILEGES ON t1 TO wl14303@localhost;
connect FLUSH_TABLES_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH TABLES;
# Success criteria: should work
FLUSH TABLES t1;
# Success criteria: should work
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
# Success criteria: should work
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
# Success criteria: should work
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
# Success criteria: should fail
FLUSH HOSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
connection default;
disconnect FLUSH_TABLES_con;
REVOKE ALL PRIVILEGES ON t1 FROM wl14303@localhost;
DROP USER wl14303@localhost;
DROP TABLE t1;
# FR6: RELOAD privilege works
CREATE USER wl14303@localhost;
GRANT RELOAD ON *.* TO wl14303@localhost;
connect RELOAD_con,localhost,wl14303,,;
# Success criteria: should work
FLUSH HOSTS;
Warnings:
Warning	1287	'FLUSH HOSTS' is deprecated and will be removed in a future release. Please use TRUNCATE TABLE performance_schema.host_cache instead
FLUSH OPTIMIZER_COSTS;
FLUSH STATUS;
FLUSH USER_RESOURCES;
FLUSH TABLES;
connection default;
disconnect RELOAD_con;
DROP USER wl14303@localhost;
# test combo of FLUSH options
CREATE USER wl14303@localhost;
GRANT FLUSH_STATUS,FLUSH_USER_RESOURCES ON *.* TO  wl14303@localhost;
connect COMBO_con,localhost,wl14303,,;
# two new ones (allowed, forbidden): should fail
FLUSH STATUS,OPTIMIZER_COSTS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD or FLUSH_OPTIMIZER_COSTS privilege(s) for this operation
# two new ones (forbidden, allowed): should fail
FLUSH OPTIMIZER_COSTS,STATUS;
ERROR 42000: Access denied; you need (at least one of) the RELOAD or FLUSH_OPTIMIZER_COSTS privilege(s) for this operation
# old and a new one: should fail
FLUSH STATUS,PRIVILEGES;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
# two new ones: should work
FLUSH STATUS,USER_RESOURCES;
connection default;
disconnect COMBO_con;
DROP USER wl14303@localhost;
# FR8: test mysqladmin
SET @saved_log_output = @@global.log_output;
SET @saved_general_log = @@global.general_log;
SET global log_output='table';
SET global general_log=on;
SET global general_log=@saved_general_log;
SET global log_output=@saved_log_output;
# Success criteria: should contain just 1 TRUNCATE TABLE rows
SELECT command_type, argument FROM mysql.general_log WHERE command_type='Query';
command_type	Query
argument	SET global general_log=on
command_type	Query
argument	TRUNCATE TABLE performance_schema.host_cache
command_type	Query
argument	SET global general_log=@saved_general_log
command_type	Query
argument	SET global log_output=@saved_log_output
TRUNCATE TABLE mysql.general_log;
# End of 8.0 tests