| 1 |
# Setup |
| 2 |
CREATE USER sys_user, non_sys_user; |
| 3 |
GRANT CONNECTION_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO non_sys_user; |
| 4 |
GRANT SYSTEM_USER, CREATE, SELECT, INSERT, EXECUTE,SET_USER_ID, |
| 5 |
SYSTEM_VARIABLES_ADMIN ON *.* TO sys_user; |
| 6 |
#------------------------------------------------------------------------ |
| 7 |
# 1. User without SYSTEM_USER privilege cannot the kill query running |
| 8 |
# in the power session |
| 9 |
#------------------------------------------------------------------------ |
| 10 |
SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue'; |
| 11 |
PREPARE stmt FROM 'SELECT count(*) FROM t1,t2,t3,t4,t5 WHERE a1=a2 AND a2=a3 AND a3=a4 AND a4=a5 '; |
| 12 |
EXECUTE stmt; |
| 13 |
# Regular session should not be able to to kill the query running in |
| 14 |
# power session. |
| 15 |
SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; |
| 16 |
KILL QUERY <CONNECTION_ID>; |
| 17 |
ERROR HY000: You are not owner of thread <CONNECTION_ID> |
| 18 |
SET DEBUG_SYNC= 'now SIGNAL continue'; |
| 19 |
count(*) |
| 20 |
7 |
| 21 |
SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue'; |
| 22 |
EXECUTE stmt; |
| 23 |
# The running query cannot be killed by non_sys_user user |
| 24 |
# even after revoking the SYSTEM_USER privilege from sys_user |
| 25 |
REVOKE SYSTEM_USER ON *.* FROM sys_user; |
| 26 |
SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; |
| 27 |
KILL QUERY <CONNECTION_ID>; |
| 28 |
ERROR HY000: You are not owner of thread <CONNECTION_ID> |
| 29 |
SET DEBUG_SYNC= 'now SIGNAL continue'; |
| 30 |
count(*) |
| 31 |
7 |
| 32 |
SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue'; |
| 33 |
EXECUTE stmt; |
| 34 |
# Another power_session should be able to kill the query. |
| 35 |
SET DEBUG_SYNC= 'now WAIT_FOR in_sync'; |
| 36 |
KILL QUERY <CONNECTION_ID>; |
| 37 |
SET DEBUG_SYNC= 'now SIGNAL continue'; |
| 38 |
# Verify that query was killed |
| 39 |
ERROR 70100: Query execution was interrupted |
| 40 |
SET DEBUG_SYNC = 'RESET'; |
| 41 |
# Test Cleanup |
| 42 |
DROP USER sys_user, non_sys_user; |