mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
ce7ab467db
DROP USER looks for sessions by the do-be-dropped user and if found: * fails with ER_CANNOT_USER in Oracle mode * continues with ER_ACTIVE_CONNECTIONS_FOR_USER_TO_DROP warning otherwise Every user being dropped is marked with flag that disallow establishing a new connections on behalf this user.
298 lines
13 KiB
Text
298 lines
13 KiB
Text
# The numbers represent test cases of the test plan.
|
|
|
|
--source include/have_plugin_auth.inc
|
|
--source include/not_embedded.inc
|
|
#enable view protocol after fix MDEV-29542
|
|
--source include/no_view_protocol.inc
|
|
--source include/count_sessions.inc
|
|
|
|
CREATE DATABASE test_user_db;
|
|
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
|
|
--echo ========== test 1.1.3.2 ====================================
|
|
|
|
# CREATE...WITH/CREATE...BY/GRANT
|
|
CREATE USER plug_user IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON `plug%dest` TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON `plug%dest` FROM plug_user;
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO 'plug_dest'@'%';
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON plug_dest FROM plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
--source include/wait_until_count_sessions.inc
|
|
DROP USER plug_user,plug_dest;
|
|
#
|
|
# GRANT...WITH
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--echo 1)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user';
|
|
--echo 2)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON plug_dest FROM plug_user;
|
|
--echo 3)
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
--source include/wait_until_count_sessions.inc
|
|
DROP USER plug_user,plug_dest;
|
|
#
|
|
# GRANT...WITH/CREATE...BY
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
--echo 1)
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO 'plug_dest'@'%';
|
|
--echo 2)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
--source include/wait_until_count_sessions.inc
|
|
DROP USER plug_user,plug_dest;
|
|
|
|
--echo ========== test 1.2 ========================================
|
|
|
|
# GRANT...WITH/CREATE...BY
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
RENAME USER plug_dest TO new_dest;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON new_dest TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--source include/wait_until_count_sessions.inc
|
|
DROP USER plug_user,new_dest;
|
|
|
|
# CREATE...WITH/CREATE...BY
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
RENAME USER plug_dest TO new_dest;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON new_dest TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--source include/wait_until_count_sessions.inc
|
|
DROP USER plug_user,new_dest;
|
|
# CREATE...WITH
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
connect(plug_user,localhost,plug_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
RENAME USER plug_user TO new_user;
|
|
connect(plug_user,localhost,new_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
disconnect plug_user;
|
|
UPDATE mysql.global_priv SET user='plug_user' WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
DROP USER plug_dest,plug_user;
|
|
--echo ========== test 1.3 ========================================
|
|
|
|
#
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
connect(plug_user,localhost,plug_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET user='new_user' WHERE user='plug_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.authentication_string', 'new_dest') WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'new_plugin_server') WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--disable_query_log
|
|
--error ER_PLUGIN_IS_NOT_LOADED
|
|
connect(plug_user,localhost,new_user,new_dest);
|
|
--enable_query_log
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'test_plugin_server') WHERE user='new_user';
|
|
UPDATE mysql.global_priv SET user='new_dest' WHERE user='plug_dest';
|
|
FLUSH PRIVILEGES;
|
|
GRANT PROXY ON new_dest TO new_user;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(plug_user,localhost,new_user,new_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
UPDATE mysql.global_priv SET user='plug_dest' WHERE user='new_dest';
|
|
FLUSH PRIVILEGES;
|
|
CREATE USER new_dest IDENTIFIED BY 'new_dest_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
GRANT ALL PRIVILEGES ON test.* TO new_user;
|
|
connect(plug_user,localhost,new_dest,new_dest_passwd,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
DROP USER new_user,new_dest,plug_dest;
|
|
|
|
--echo ========== test 2, 2.1, 2.2 ================================
|
|
|
|
CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
--echo ========== test 2.2.1 ======================================
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_query_log
|
|
--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
|
|
connect(proxy_con,localhost,proxy_user,proxied_user);
|
|
--enable_query_log
|
|
GRANT PROXY ON proxied_user TO ''@'%%';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect proxy_con;
|
|
connect(proxy_con,localhost,proxy_user,proxied_user,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
--echo ========== test 2.2.1 ======================================
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
DROP USER ''@'%%',proxied_user;
|
|
#
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'%%'
|
|
IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_query_log
|
|
--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
|
|
connect(proxy_con,localhost,proxy_user,proxied_user);
|
|
--enable_query_log
|
|
GRANT PROXY ON proxied_user TO ''@'%%';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect proxy_con;
|
|
connect(proxy_con,localhost,proxy_user,proxied_user,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
DROP USER ''@'%%',proxied_user;
|
|
#
|
|
CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user_1 IDENTIFIED BY 'proxied_user_1_pwd';
|
|
CREATE USER proxied_user_2 IDENTIFIED BY 'proxied_user_2_pwd';
|
|
CREATE USER proxied_user_3 IDENTIFIED BY 'proxied_user_3_pwd';
|
|
CREATE USER proxied_user_4 IDENTIFIED BY 'proxied_user_4_pwd';
|
|
CREATE USER proxied_user_5 IDENTIFIED BY 'proxied_user_5_pwd';
|
|
GRANT PROXY ON proxied_user_1 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_2 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_3 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_4 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_5 TO ''@'%%';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con_1,localhost,proxied_user_1,'proxied_user_1_pwd',"*NO-ONE*");
|
|
connect(proxy_con_2,localhost,proxied_user_2,proxied_user_2_pwd,"*NO-ONE*");
|
|
connect(proxy_con_3,localhost,proxied_user_3,proxied_user_3_pwd,"*NO-ONE*");
|
|
connect(proxy_con_4,localhost,proxied_user_4,proxied_user_4_pwd,"*NO-ONE*");
|
|
connect(proxy_con_5,localhost,proxied_user_5,proxied_user_5_pwd,"*NO-ONE*");
|
|
connection proxy_con_1;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_2;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_3;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_4;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_5;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con_1;
|
|
disconnect proxy_con_2;
|
|
disconnect proxy_con_3;
|
|
disconnect proxy_con_4;
|
|
disconnect proxy_con_5;
|
|
DROP USER ''@'%%',proxied_user_1,proxied_user_2,proxied_user_3,proxied_user_4,proxied_user_5;
|
|
|
|
--echo ========== test 3 ==========================================
|
|
|
|
GRANT ALL PRIVILEGES ON *.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
FLUSH PRIVILEGES;
|
|
|
|
# Not working with the patch.
|
|
|
|
#--replace_result $MYSQLADMIN MYSQLADMIN $MASTER_MYPORT MYPORT $MASTER_MYSOCK MYSOCK
|
|
#--exec $MYSQLADMIN -h localhost -u plug_user --password=plug_dest ping 2>&1
|
|
#--replace_result $MYSQL_CHECK MYSQL_CHECK $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_CHECK -h localhost -u plug_user --password=plug_dest test
|
|
#--replace_result $MYSQL_DUMP MYSQL_DUMP $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_DUMP -h localhost -u plug_user --password=plug_dest test
|
|
#--replace_result $MYSQL_SHOW MYSQL_SHOW $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_SHOW -h localhost --plugin_dir=../plugin/auth -u plug_user --password=plug_dest 2>&1
|
|
DROP USER plug_user, plug_dest;
|
|
DROP DATABASE test_user_db;
|
|
--exit
|