diff options
Diffstat (limited to 'mysql-test/main/plugin_auth_qa_1.test')
| -rw-r--r-- | mysql-test/main/plugin_auth_qa_1.test | 294 |
1 files changed, 294 insertions, 0 deletions
diff --git a/mysql-test/main/plugin_auth_qa_1.test b/mysql-test/main/plugin_auth_qa_1.test new file mode 100644 index 00000000..100670dc --- /dev/null +++ b/mysql-test/main/plugin_auth_qa_1.test @@ -0,0 +1,294 @@ +# 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 + +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 +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 +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 +REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user'; +#REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_dest''; +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'; +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'; +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 |