1CREATE DATABASE test_user_db; 2========== test 1.1.3.2 ==================================== 3=== check contens of components of info ==================== 4CREATE USER qa_test_1_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_1_dest'; 5CREATE USER qa_test_1_dest IDENTIFIED BY 'dest_passwd'; 6GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_1_dest identified by 'dest_passwd'; 7GRANT PROXY ON qa_test_1_dest TO qa_test_1_user; 8SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 9User plugin authentication_string 10mariadb.sys mysql_native_password 11qa_test_1_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 12qa_test_1_user qa_auth_interface qa_test_1_dest 13SELECT @@proxy_user; 14@@proxy_user 15NULL 16SELECT @@external_user; 17@@external_user 18NULL 19exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_1_user --password=qa_test_1_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 20current_user() user() @@local.proxy_user @@local.external_user 21qa_test_1_user@% qa_test_1_user@localhost NULL NULL 22SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 23User plugin authentication_string 24mariadb.sys mysql_native_password 25qa_test_1_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 26qa_test_1_user qa_auth_interface qa_test_1_dest 27DROP USER qa_test_1_user; 28DROP USER qa_test_1_dest; 29=== Assign values to components of info ==================== 30CREATE USER qa_test_2_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_2_dest'; 31CREATE USER qa_test_2_dest IDENTIFIED BY 'dest_passwd'; 32CREATE USER authenticated_as IDENTIFIED BY 'dest_passwd'; 33GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_2_dest identified by 'dest_passwd'; 34GRANT PROXY ON qa_test_2_dest TO qa_test_2_user; 35GRANT PROXY ON authenticated_as TO qa_test_2_user; 36SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 37User plugin authentication_string 38authenticated_as mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 39mariadb.sys mysql_native_password 40qa_test_2_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 41qa_test_2_user qa_auth_interface qa_test_2_dest 42SELECT @@proxy_user; 43@@proxy_user 44NULL 45SELECT @@external_user; 46@@external_user 47NULL 48exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_2_user --password=qa_test_2_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 49current_user() user() @@local.proxy_user @@local.external_user 50authenticated_as@% user_name@localhost 'qa_test_2_user'@'%' externaluser 51SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 52User plugin authentication_string 53authenticated_as mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 54mariadb.sys mysql_native_password 55qa_test_2_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 56qa_test_2_user qa_auth_interface qa_test_2_dest 57DROP USER qa_test_2_user; 58DROP USER qa_test_2_dest; 59DROP USER authenticated_as; 60=== Assign too high values for *length, which should have no effect ==== 61CREATE USER qa_test_3_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_3_dest'; 62CREATE USER qa_test_3_dest IDENTIFIED BY 'dest_passwd'; 63GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_3_dest identified by 'dest_passwd'; 64GRANT PROXY ON qa_test_3_dest TO qa_test_3_user; 65exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_3_user --password=qa_test_3_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 66current_user() user() @@local.proxy_user @@local.external_user 67qa_test_3_dest@% qa_test_3_user@localhost 'qa_test_3_user'@'%' qa_test_3_dest 68DROP USER qa_test_3_user; 69DROP USER qa_test_3_dest; 70=== Assign too low values for *length, which should have no effect ==== 71CREATE USER qa_test_4_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_4_dest'; 72CREATE USER qa_test_4_dest IDENTIFIED BY 'dest_passwd'; 73GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_4_dest identified by 'dest_passwd'; 74GRANT PROXY ON qa_test_4_dest TO qa_test_4_user; 75exec MYSQL -h localhost -P MASTER_MYPORT -u qa_test_4_user --password=qa_test_4_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 76current_user() user() @@local.proxy_user @@local.external_user 77qa_test_4_dest@% qa_test_4_user@localhost 'qa_test_4_user'@'%' qa_test_4_dest 78DROP USER qa_test_4_user; 79DROP USER qa_test_4_dest; 80=== Assign empty string especially to authenticated_as (in plugin) ==== 81CREATE USER qa_test_5_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_5_dest'; 82CREATE USER qa_test_5_dest IDENTIFIED BY 'dest_passwd'; 83CREATE USER ''@'localhost' IDENTIFIED BY 'dest_passwd'; 84GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_5_dest identified by 'dest_passwd'; 85GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'localhost' identified by 'dest_passwd'; 86GRANT PROXY ON qa_test_5_dest TO qa_test_5_user; 87GRANT PROXY ON qa_test_5_dest TO ''@'localhost'; 88SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 89User plugin authentication_string Password 90 mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22 91mariadb.sys mysql_native_password 92qa_test_5_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22 93qa_test_5_user qa_auth_interface qa_test_5_dest 94exec MYSQL -h localhost -P MASTER_MYPORT --user=qa_test_5_user --password=qa_test_5_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 95ERROR 1045 (28000): Access denied for user 'qa_test_5_user'@'localhost' (using password: YES) 96DROP USER qa_test_5_user; 97DROP USER qa_test_5_dest; 98DROP USER ''@'localhost'; 99=== Assign 'root' especially to authenticated_as (in plugin) ==== 100CREATE USER qa_test_6_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest'; 101CREATE USER qa_test_6_dest IDENTIFIED BY 'dest_passwd'; 102GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_6_dest identified by 'dest_passwd'; 103GRANT PROXY ON qa_test_6_dest TO qa_test_6_user; 104SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 105User plugin authentication_string Password 106mariadb.sys mysql_native_password 107qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22 108qa_test_6_user qa_auth_interface qa_test_6_dest 109exec MYSQL -h localhost -P MASTER_MYPORT --user=qa_test_6_user --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 110ERROR 1045 (28000): Access denied for user 'qa_test_6_user'@'localhost' (using password: YES) 111GRANT PROXY ON qa_test_6_dest TO root IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest'; 112SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 113User plugin authentication_string Password 114mariadb.sys mysql_native_password 115qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22 116qa_test_6_user qa_auth_interface qa_test_6_dest 117exec MYSQL -h localhost -P MASTER_MYPORT --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 118ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 119REVOKE PROXY ON qa_test_6_dest FROM root; 120SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 121User plugin authentication_string 122mariadb.sys mysql_native_password 123qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 124qa_test_6_user qa_auth_interface qa_test_6_dest 125exec MYSQL -h localhost -P MASTER_MYPORT --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 126ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 127DROP USER qa_test_6_user; 128DROP USER qa_test_6_dest; 129DELETE FROM mysql.user WHERE user='root' AND plugin='qa_auth_interface'; 130SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 131User plugin authentication_string Password 132mariadb.sys mysql_native_password 133=== Test of the --default_auth option for clients ==== 134CREATE USER qa_test_11_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_11_dest'; 135CREATE USER qa_test_11_dest IDENTIFIED BY 'dest_passwd'; 136GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_11_dest identified by 'dest_passwd'; 137GRANT PROXY ON qa_test_11_dest TO qa_test_11_user; 138exec MYSQL --default_auth=qa_auth_client -h localhost -P MASTER_MYPORT -u qa_test_11_user --password=qa_test_11_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1 139ERROR 1045 (28000): Access denied for user 'qa_test_11_user'@'localhost' (using password: YES) 140DROP USER qa_test_11_user, qa_test_11_dest; 141DROP DATABASE test_user_db; 142