#
# WL11772 CREATE USER IDENTIFIED BY RANDOM PASSWORD
#

--source include/have_log_bin.inc

SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;

--replace_column 3 random
CREATE USER u1 IDENTIFIED BY RANDOM PASSWORD, u2 IDENTIFIED BY RANDOM PASSWORD, u3 IDENTIFIED BY RANDOM PASSWORD;
--replace_column 3 random
CREATE USER u4 IDENTIFIED BY RANDOM PASSWORD;

--let $sql= 'CREATE USER foo IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT'
--let $pass= query_get_value($sql,generated password,1)

# connect using the generated password.
--connect(con1,localhost,foo,$pass,test)
select current_user;
--echo ** Verify that REPLACE-syntax works
--disable_query_log
--replace_column 3 random
--eval SET PASSWORD TO RANDOM REPLACE '$pass';
--enable_query_log
--connection default
--disconnect con1

--echo ** Verify that RETAIN-syntax works
--disable_query_log
--replace_column 3 random
--eval SET PASSWORD FOR foo TO RANDOM RETAIN CURRENT PASSWORD;
--enable_query_log

--echo ** Verify that ALTER USER works as expected.
--let $sql= 'ALTER USER foo IDENTIFIED BY RANDOM PASSWORD'
--let $pass= query_get_value($sql,generated password,1)
--connect(con1,localhost,foo,$pass,test)
select current_user;
--echo ** Verify that REPLACE-syntax works
--disable_query_log
--replace_column 3 random
--eval ALTER USER foo IDENTIFIED BY RANDOM PASSWORD REPLACE '$pass';
--enable_query_log
--connection default
--disconnect con1
SET PASSWORD FOR u1 = 'foo';
--connect(con1,localhost,u1,foo,test)
--echo ** Verify that it works for current user.
--replace_column 3 random
SET PASSWORD TO RANDOM;
--replace_column 3 random
--let $sql ="SET PASSWORD FOR CURRENT_USER TO RANDOM";
--let $pass= query_get_value($sql,generated password,1)
--connection default
--disconnect con1
--connect(con1,localhost,u1,$pass,test)
SELECT CURRENT_USER();
--connection default
--disconnect con1

--echo ** Test password length
set session generated_random_password_length=6;
--let $sql= 'SET PASSWORD FOR foo TO RANDOM'
--let $pass= query_get_value($sql,generated password,1)
--disable_query_log
--eval SELECT LENGTH('$pass') AS "Password length";
--enable_query_log
set session generated_random_password_length=66;
--let $sql= 'SET PASSWORD FOR foo TO RANDOM'
--let $pass= query_get_value($sql,generated password,1)
--disable_query_log
--eval SELECT LENGTH('$pass') AS "Password length";
--enable_query_log

DROP USER u1,u2,u3,u4,foo;

--replace_column 3 random
CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
--let $qry="SET PASSWORD FOR usr TO RANDOM"
let $pswd = query_get_value ( $qry, "generated password", 1 );
connect(con1, localhost, usr, $pswd,test);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
--echo "Disconnect from usr ..."
connection default;
disconnect con1;
DROP USER usr;

--let $sql= "CREATE USER usr1 IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD"
let $pswd= query_get_value( $sql, "generated password", 1);
connect(con1, localhost, usr1, $pswd,test);
--echo Logged in successfully through usr1
SELECT CURRENT_USER();
--echo "Disconnect from usr1 ..."
connection default;
disconnect con1;
DROP USER usr1;

-- echo
-- echo #-- Testing Syntax of the new CREATE USER/ALTER USER/SET PASSWORD extension
-- echo
#
#-------------------------------------------------------------
# Tests should not throw any syntax errors
#-------------------------------------------------------------
--echo #--Valid combinations/ syntaxes of RANDOM PASSWORD

--replace_column 3 #####
CREATE USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for 'usr'@'localhost' TO RANDOM;

DROP USER 'usr'@'localhost';

--echo

--replace_column 3 #####
CREATE USER 'usr' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER 'usr' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for 'usr' TO RANDOM;

DROP USER 'usr';

--echo

--replace_column 3 #####
CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for 'usr'@'%' TO RANDOM;

DROP USER 'usr'@'%';

--echo

--replace_column 3 #####
CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for usr TO RANDOM;

DROP USER usr;

--echo

--replace_column 3 #####
CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for usr@localhost TO RANDOM;

DROP USER usr@localhost;

--echo

--replace_column 3 #####
CREATE USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for usr@'%' TO RANDOM;

DROP USER usr@'%';

--echo

--replace_column 3 #####
CREATE USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for 'usr'@localhost TO RANDOM;

DROP USER 'usr'@localhost;

--echo

--replace_column 3 #####
CREATE USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
SET PASSWORD for usr@'localhost' TO RANDOM;

DROP USER usr@'localhost';

--echo

--replace_column 3 #####
CREATE USER usr IDENTIFIED BY Random Password;
DROP USER usr;

--replace_column 3 #####
CREATE USER usr IDENTIFIED BY random password;
DROP USER usr;

--replace_column 3 #####
CREATE USER usr IDENTIFIED BY rANDOm PASSword;
DROP USER usr;

--echo

--replace_column 3 #####
CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD,
     usr2 IDENTIFIED BY RANDOM PASSWORD,
     usr3 IDENTIFIED BY RANDOM PASSWORD;

--replace_column 3 #####
ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD,
     usr2 IDENTIFIED BY RANDOM PASSWORD,
     usr3 IDENTIFIED BY RANDOM PASSWORD;

DROP USER usr1;
DROP USER usr2;
DROP USER usr3;


--echo

#-------------------------------------------------------------
# Invalid combinations/ syntaxes of RANDOM PASSWORD
#-------------------------------------------------------------
--echo #--Invalid combinations/ syntaxes of RANDOM PASSWORD

--error ER_PARSE_ERROR
CREATE USER usr IDENTIFIED BY RANDOM;

--echo
--error ER_PARSE_ERROR
CREATE USER usr IDENTIFIED BY RANDOM PASSWD;

--echo
--error ER_PARSE_ERROR
CREATE USER usr IDENTIFIED RANDOM PASSWORD;

--echo
--error ER_PARSE_ERROR
CREATE USER usr BY RANDOM PASSWORD;

--echo
--replace_column 3 ######
CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;

--echo
--error ER_PARSE_ERROR
ALTER USER usr IDENTIFIED BY RANDOM;

--echo
--error ER_PARSE_ERROR
ALTER USER usr IDENTIFIED BY RANDOM PASSWD;

--echo
--error ER_PARSE_ERROR
ALTER USER usr IDENTIFIED RANDOM PASSWORD;

--echo
--error ER_PARSE_ERROR
ALTER USER usr BY RANDOM PASSWORD;

--echo
--error ER_PARSE_ERROR
SET PASSWORD for usr RANDOM;

DROP USER usr;

--echo
--error ER_CANNOT_USER
ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;

--echo
--replace_column 3 ######
CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;

--echo
--error ER_CANNOT_USER
ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

DROP USER usr;

#------------


--echo
--echo #-------------------------------------------------------------
--echo # Privileges and Users
--echo #-------------------------------------------------------------
--echo

#-------------------------------------------------------------
#non privileged user
#-------------------------------------------------------------

--echo
--echo #--CREATE temp user with RANDOM PASSWORD
--replace_column 3 ######
CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
--echo #--CREATE user with RANDOM PASSWORD
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );

#-------------------------------------------------------------
# non privileged user can set a RANDOM PASSWORD for itself
#-------------------------------------------------------------
--echo
--echo #--Login to usr with RANDOM PASSWORD
connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
let $qry = SET PASSWORD TO RANDOM;
let $pswd = query_get_value ( "$qry", "generated password", 1 );
--echo #--Disconnect from usr ...
disconnect con1;


connect(con1, localhost, usr, $pswd,);
#-------------------------------------------------------------
# non privileged user cannot set a RANDOM PASSWORD for other user
#-------------------------------------------------------------
--error ER_DBACCESS_DENIED_ERROR
SET PASSWORD FOR usr_temp TO RANDOM;

#-------------------------------------------------------------
# non privileged user cannot alter a RANDOM PASSWORD for other users
#-------------------------------------------------------------
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;

#-------------------------------------------------------------
# non privileged user cannot create user with a RANDOM PASSWORD
#-------------------------------------------------------------
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;

--echo #--Disconnect from usr ...
disconnect con1;

connection default;
DROP USER usr;
DROP USER usr_temp;

#-------------------------------------------------------------
#privileged user
#-------------------------------------------------------------
--echo
--echo #--CREATE temp user with RANDOM PASSWORD
--replace_column 3 #####
CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
--echo
--echo #--CREATE user with RANDOM PASSWORD
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--GRANT ALL PRIVILEGES TO user
GRANT ALL ON *.* TO usr;
FLUSH PRIVILEGES;

--echo
--echo #--Login to usr with RANDOM PASSWORD
connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
#-------------------------------------------------------------
# privileged user can set a RANDOM PASSWORD for itself
#-------------------------------------------------------------
SELECT CURRENT_USER();
--echo #--SET PASSWORD for user with RANDOM PASSWORD
let $qry = SET PASSWORD TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo #--Disconnect from usr ...
disconnect con1;
--echo

--echo #--Login to usr with RANDOM PASSWORD
connect(con1, localhost, usr, $pswd,);
#-------------------------------------------------------------
# privileged user can set a RANDOM PASSWORD for other user
#-------------------------------------------------------------
--echo #--SET PASSWORD for user with RANDOM PASSWORD
--replace_column 3 #####
SET PASSWORD FOR usr_temp TO RANDOM;

#-------------------------------------------------------------
# privileged user can alter a RANDOM PASSWORD for other users
#-------------------------------------------------------------
--echo
--echo #--ALTER user with RANDOM PASSWORD
--replace_column 3 #####
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;

#-------------------------------------------------------------
# privileged user can create user with a RANDOM PASSWORD
#-------------------------------------------------------------
--echo
--echo #--CREATE tempuser with RANDOM PASSWORD
--replace_column 3 #####
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
--echo #--Disconnect from usr ...
disconnect con1;

--echo
connection default;
--echo #--ALTER user with RANDOM PASSWORD
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo

--echo #--Login to usr with RANDOM PASSWORD
connect(con1, localhost, usr, $pswd,);
#-------------------------------------------------------------
# privileged user can set the same RANDOM PASSWORD for other user
#-------------------------------------------------------------
--echo #--SET PASSWORD for user with RANDOM PASSWORD
--disable_query_log
SET PASSWORD for usr_temp = '$pswd';
--enable_query_log
--echo #--Disconnect from usr ...
disconnect con1;
--echo

--echo
--echo #--Login to usr with RANDOM PASSWORD
connect(con1, localhost, usr, $pswd,);
#-------------------------------------------------------------
# privileged user can set a RANDOM PASSWORD for the root user
#-------------------------------------------------------------

--echo
--echo #--SET PASSWORD for user with RANDOM PASSWORD
--error ER_PASSWORD_NO_MATCH
SET PASSWORD FOR root TO RANDOM;
--echo #--ALTER root with RANDOM PASSWORD
--error ER_CANNOT_USER
ALTER USER root IDENTIFIED BY RANDOM PASSWORD;

disconnect con1;
connection default;
DROP USER usr;
DROP USER usr_temp;
DROP USER usr_ntemp;
--echo
#-------------------------------------------------------------
# User with limited privileges
#-------------------------------------------------------------
connection default;
--replace_column 3 #####
CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
GRANT INSERT ON *.* TO usr_temp;
FLUSH PRIVILEGES;
--echo

connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
disconnect con1;
--echo

connection default;
DROP USER usr;
DROP USER usr_temp;

#-------------------------------------------------------------
# Manual password can be changed using ALTER USER
#-------------------------------------------------------------
#create user specifying the password
CREATE USER usr IDENTIFIED by 'foobar';
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo

connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;
--echo

connection default;
DROP USER usr;

#-------------------------------------------------------------
# Manual password can be changed using SET USER
#-------------------------------------------------------------
#user created by specifying required password
CREATE USER usr IDENTIFIED by 'foobar';
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo

connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;
--echo

connection default;
DROP USER usr;

#-------------------------------------------------------------
# Empty password can be changed using ALTER USER
#-------------------------------------------------------------
CREATE USER usr;
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );

connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;

connection default;
DROP USER usr;

#-------------------------------------------------------------
# Empty password can be changed using SET USER
#-------------------------------------------------------------
CREATE USER usr;
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );

connect(con1, localhost, usr, $pswd,);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;

connection default;
DROP USER usr;
--connection default

--replace_column 3 ######
CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;

--echo #-----------------------------------------------------------------------
--echo # RETAIN CURRENT PASSWORD with ALTER USER and SET PASSWORD
--echo #-----------------------------------------------------------------------
--echo
# No entry in mysql.user for usr@localhost
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
--echo
# Change password
let $qry = ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD RETAIN CURRENT PASSWORD;
let $pswd1 = query_get_value ( $qry, "generated password", 1);
--echo
# Entry should be present in mysql.user for usr@localhost
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
--echo
SET @pswd1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
--echo
# Change password
let $qry = SET PASSWORD for usr@localhost TO RANDOM RETAIN CURRENT PASSWORD;
let $pswd2 = query_get_value ( $qry, "generated password", 1);
--echo
# Entry should be present in mysql.user for usr@localhost
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
--echo
SET @pswd2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
--echo
# Assert that password stored in mysql.user.user_attributes changed
SELECT @pswd1 <> @pswd2 AS ADDITIONAL_PASSWORD_CHANGED;
--echo
connect(con1, localhost, usr, '$pswd1',);
--echo #--Logged in successfully through usr with secondary password
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;
--echo
connect(con2, localhost, usr, '$pswd2',);
--echo #--Logged in successfully through usr with primary password
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con2;
--echo
connection default;
--echo
# Change password without RETAIN CURRENT PASSWORD
# Primary password will be changed and secondary password will be intact
let $qry = ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
let $pswd3 = query_get_value ( $qry, "generated password", 1);
--echo
# Entry should still be present int mysql.user for usr@localhost
SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user
WHERE user LIKE 'usr';
--echo
SET @pswd3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
--echo
# Assert that password stored in mysql.user did not change
SELECT @pswd2 = @pswd3 AS ADDITIONAL_PASSWORD_NOT_CHANGED;
--echo
connect(con1, localhost, usr, '$pswd1',);
--echo #--Logged in successfully through usr with secondary password
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;
--echo
connect(con3, localhost, usr, '$pswd3',);
--echo #--Logged in successfully through usr with primary password2
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con3;
--echo
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(conn, localhost, usr, '$pswd2',);
--enable_query_log

--echo
connection default;

#secondary password will be discarded
ALTER USER usr@localhost DISCARD OLD PASSWORD;
--echo
connect(con1, localhost, usr, '$pswd3',);
--echo #--Logged in successfully through usr with primary password
SELECT CURRENT_USER();
--echo #--Disconnect from usr ...
disconnect con1;
--echo
connection default;
--echo
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(conn, localhost, usr, '$pswd1',);

--error ER_ACCESS_DENIED_ERROR
connect(conn, localhost, usr, '$pswd2',);

--enable_query_log
--echo
connection default;

DROP USER usr@localhost;


--echo #-----------------------------------------------------------------------
--echo # REQUIRE CURRENT
--echo #-----------------------------------------------------------------------
--echo

--connection default
# create user with random password
--replace_column 3 #####
CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;

# alter user with random password PASSWORD REQUIRE CURRENT
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT;
let $pswd = query_get_value ( $qry, "generated password", 1 );

--echo
connect(con1, localhost, usr, '$pswd',);
--echo #--Logged in successfully through usr
select CURRENT_USER();
#--Execute alter user with replace
let $qry =  ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE '$pswd' ;
let $pswd = query_get_value ( "$qry", "generated password", 1);
disconnect con1;

--echo
connect(con1, localhost, usr, '$pswd',);
--echo #--Logged in successfully through usr
select CURRENT_USER();
#--Execute alter user with replace
let $qry =  SET PASSWORD TO RANDOM REPLACE '$pswd';
let $pswd = query_get_value ( "$qry", "generated password", 1);
disconnect con1;

--echo
connect(con1, localhost, usr, '$pswd',);
--echo #--Logged in successfully through usr
select CURRENT_USER();
--echo #--Execute set password
let $qry =  SET PASSWORD FOR usr TO RANDOM REPLACE '$pswd';
let $pswd = query_get_value ( "$qry", "generated password", 1);
disconnect con1;
--echo


connect(conn, localhost, usr, '$pswd',);
# should not work
--error ER_INCORRECT_CURRENT_PASSWORD
ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE 'qrst';
--error ER_INCORRECT_CURRENT_PASSWORD
SET PASSWORD TO RANDOM REPLACE 'qrst';
--error ER_INCORRECT_CURRENT_PASSWORD
SET PASSWORD FOR usr TO RANDOM REPLACE 'qrst';
disconnect conn;

-- connection default
DROP USER usr;

--echo #-----------------------------------------------------------------------
--echo # Plugin change
--echo #-----------------------------------------------------------------------
--echo
--connection default
--replace_column 3 ######
CREATE USER usr1@localhost IDENTIFIED BY RANDOM PASSWORD, usr2@localhost IDENTIFIED BY RANDOM PASSWORD, usr3@localhost IDENTIFIED BY RANDOM PASSWORD ;

--replace_column 3 ######
let $qry = ALTER USER usr1@localhost IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
let $pswd1 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con1, localhost, usr1, '$pswd1',);
--echo #--Logged in successfully through usr1
SELECT CURRENT_USER();
--echo "Disconnect from usr1 ..."
disconnect con1;
--echo

connection default;

--replace_column 3 ######
let $qry = ALTER USER usr2@localhost IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD;
let $pswd2 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con2, localhost, usr2, '$pswd2',);
--echo #--Logged in successfully through usr2
SELECT CURRENT_USER();
--echo #--Disconnect from usr2 ...
disconnect con2;
--echo

connection default;

--replace_column 3 ######
let $qry = ALTER USER usr3@localhost IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
let $pswd3 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con3, localhost, usr3, '$pswd3',);
--echo #--Logged in successfully through usr3
SELECT CURRENT_USER();
--echo --echo #--Disconnect from usr3 ...
disconnect con3;
--echo
--echo Plugin change for usr1, usr2, usr3
--echo
connection default;

--replace_column 3 ######
let $qry = ALTER USER usr1@localhost IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD;
let $pswd1 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con1, localhost, usr1, '$pswd1',);
--echo #--Logged in successfully through usr1
SELECT CURRENT_USER();
--echo "Disconnect from usr1 ..."
disconnect con1;
--echo

connection default;

--replace_column 3 ######
let $qry = ALTER USER usr2@localhost IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
let $pswd2 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con2, localhost, usr2, '$pswd2',);
--echo #--Logged in successfully through usr2
SELECT CURRENT_USER();
--echo #--Disconnect from usr2 ...
disconnect con2;
--echo

connection default;

--replace_column 3 ######
let $qry = ALTER USER usr3@localhost IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
let $pswd3 = query_get_value ( $qry, "generated password", 1);

--echo
connect(con3, localhost, usr3, '$pswd3',);
--echo #--Logged in successfully through usr3
SELECT CURRENT_USER();
--echo --echo #--Disconnect from usr3 ...
disconnect con3;
--echo

connection default;

DROP USER usr1@localhost;
DROP USER usr2@localhost;
DROP USER usr3@localhost;

--echo #-----------------------------------------------------------------------
--echo # General log
--echo #-----------------------------------------------------------------------

# Keep this test at the end as it modifies many user properities

--connection default
# Clear the general_log file
TRUNCATE TABLE mysql.general_log;

SET @old_log_output = @@global.log_output;
-- echo @old_log_output
SET @old_general_log = @@global.general_log;
-- echo @old_general_log
SET @old_general_log_file = @@global.general_log_file;
--echo @old_general_log_file

let $general_file_off = $MYSQLTEST_VARDIR/log/random_password_general.log;
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval SET GLOBAL general_log_file = '$general_file_off';
SET GLOBAL log_output =       'FILE,TABLE';
SET GLOBAL general_log=       'ON';

--replace_column 3 ######
CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
--replace_column 3 ######
ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;

let $qry = SET PASSWORD FOR usr@localhost to RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1);

--echo
connect(con1, localhost, usr, '$pswd',);
--echo #--Logged in successfully through usr
SELECT CURRENT_USER();
--replace_column 3 ######

SET PASSWORD TO RANDOM;
--echo #--Disconnect from usr ...
disconnect con1;

--echo
--connection default
--echo

--echo Display general_log content
--echo ------ rewrite ------
SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE USER %' AND command_type NOT LIKE 'Prepare';
SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND command_type NOT LIKE 'Prepare';
SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
--echo ------ done ------

DROP USER usr@localhost;

SET GLOBAL general_log_file = @old_general_log_file;
SET GLOBAL log_output = @old_log_output;
SET GLOBAL general_log= @old_general_log;

--echo #-----------------------------------------------------------------------
--echo # Binary log
--echo #-----------------------------------------------------------------------

--connection default
--source include/save_binlog_position.inc
--replace_column 3 #####
CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
--let $event= !Q(CREATE USER.*usr1.*usr2.*)
--source ../include/auth_sec_assert_binlog_events.inc
--source include/save_binlog_position.inc

--replace_column 3 #####
ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
--let $event= !Q(ALTER USER.*usr1.*usr2.*)
--source ../include/auth_sec_assert_binlog_events.inc
--source include/save_binlog_position.inc

--replace_column 3 #####
SET PASSWORD FOR usr1 TO RANDOM;
--let $event= !Q(ALTER USER.*usr1.*)
--source ../include/auth_sec_assert_binlog_events.inc
--source include/save_binlog_position.inc

DROP USER usr1, usr2;

--echo #-----------------------------------------------------------------------
--echo # Test NEW variable generated_random_password_length
--echo #-----------------------------------------------------------------------

--echo #--Print default values of the variable
SHOW VARIABLES LIKE 'generated_random_password_length';
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@LOCAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
SELECT @@generated_random_password_length;
--echo
--echo #--set global value to 10
SET GLOBAL generated_random_password_length = 10;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set session value to 30
SET SESSION generated_random_password_length = 30;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set session value to 40
SET @@GLOBAL.generated_random_password_length = 40;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set session value to 50
SET @@SESSION.generated_random_password_length = 50;
SELECT @@SESSION.generated_random_password_length;
#--echo
#--echo #--set persist value to 70
#SET @@PERSIST.generated_random_password_length = 70;
#SELECT @@GLOBAL.generated_random_password_length;
#--echo
#RESET PERSIST generated_random_password_length;
--echo
--echo #--set session value to 80
SET @@generated_random_password_length = 80;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set session value to 90
SET generated_random_password_length = 90;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set session value to DEFAULT
SET SESSION generated_random_password_length = DEFAULT;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set global value to DEFAULT
SET GLOBAL generated_random_password_length = DEFAULT;
SELECT @@GLOBAL.generated_random_password_length;

#-------------------------------
# Min value of the variable
#-------------------------------
--echo
--echo #--Print min values of the variable
--echo #--set global value to 5
SET @@GLOBAL.generated_random_password_length = 5;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set global value to 4
SET @@GLOBAL.generated_random_password_length = 4;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set session value to 5
SET @@SESSION.generated_random_password_length = 5;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set session value to 4
SET @@SESSION.generated_random_password_length = 4;
SELECT @@SESSION.generated_random_password_length;

--echo
--echo #--Test invalid values
--error ER_PARSE_ERROR
SET @@GLOBAL.generated_random_password_length =;

--error ER_WRONG_TYPE_FOR_VAR
SET @@GLOBAL.generated_random_password_length = b;

--error ER_WRONG_TYPE_FOR_VAR
SET GLOBAL generated_random_password_length = b;

--error ER_WRONG_TYPE_FOR_VAR
SET @@LOCAL.generated_random_password_length = '4';

--error ER_WRONG_TYPE_FOR_VAR
SET LOCAL generated_random_password_length = '10';

--error ER_WRONG_TYPE_FOR_VAR
SET @@GLOBAL.generated_random_password_length = 800000000000000000000000000000000000;

--error ER_WRONG_TYPE_FOR_VAR
SET SESSION generated_random_password_length = 800000000000000000000000000000000000;

--error ER_WRONG_TYPE_FOR_VAR
SET @@GLOBAL.generated_random_password_length = 3.5;

SET @@GLOBAL.generated_random_password_length = -4;
SELECT @@GLOBAL.generated_random_password_length;

SET LOCAL generated_random_password_length = -10;
SELECT @@LOCAL.generated_random_password_length;

#-------------------------------
# Max value of the variable
#-------------------------------
--echo
--echo #--Print Max values of the variable
--echo #--set global value to 255
SET @@GLOBAL.generated_random_password_length = 255;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set global value to 256
SET @@GLOBAL.generated_random_password_length = 256;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--set session value to 255
SET @@SESSION.generated_random_password_length = 255;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set session value to 256
SET @@SESSION.generated_random_password_length = 256;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--set global and session to DEFAULT
SET GLOBAL generated_random_password_length = DEFAULT;
SET SESSION generated_random_password_length = DEFAULT;

#---------------------------------------------------------------------
# create user/alter user/set password with various password lengths
#---------------------------------------------------------------------
--echo #--Test GLOBAL setting
--echo #-- 1) Test with a lower value
--echo #--set global to value 5
SET @@GLOBAL.generated_random_password_length = 5;
SELECT @@GLOBAL.generated_random_password_length;

--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--echo
--echo #--alter user usr
--connection default
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--set password for usr
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;
SET @@GLOBAL.generated_random_password_length = DEFAULT;

--echo
--echo #-- 2) Test with the max value
--echo #--set global to value 255
SET @@GLOBAL.generated_random_password_length = 255;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #-- create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );

--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--alter user usr
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );

--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--set password user usr
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;

SET @@GLOBAL.generated_random_password_length = DEFAULT;

--echo
--echo #-- 3) Test with lower than min value
--echo #--set session to value 0
SET @@GLOBAL.generated_random_password_length = 0;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to user usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--create user usr
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--login to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--echo
--echo #--set password for usr
--connection default
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );

--echo
--echo #--login to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default

DROP USER usr;

SET @@GLOBAL.generated_random_password_length = DEFAULT;

--echo
--echo #-- 3) Test with a value which has crossed its max value
--echo #--set global to value 256

SET @@GLOBAL.generated_random_password_length = 256;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--alter user usr
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--set password
--connection default
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;

--echo #--Test SESSION setting
--echo #-- 1) Test with a lower value
--echo #--set session to value 5
SET @@SESSION.generated_random_password_length = 5;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--alter user usr
--connection default
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--set password
--connection default
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;

--echo #-- 2) Test with the max value
--echo #--set session to value 255
SET @@SESSION.generated_random_password_length = 255;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
--echo
--echo #--alter user usr
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--set password
--connection default
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;

--echo #-- 3) Test will value less than min value
--echo #--set session to value 0
SET @@SESSION.generated_random_password_length = 0;
SELECT @@SESSION.generated_random_password_length;
--echo
--echo #--create user usr
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--alter user usr
--connection default
let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;
--echo
--echo #--set password
--connection default
let $qry = SET PASSWORD FOR usr TO RANDOM;
let $pswd = query_get_value ( $qry, "generated password", 1 );
--echo
--echo #--connect to usr
connect(con1, localhost, usr, $pswd,);
--echo Logged in successfully through usr
SELECT CURRENT_USER();
SELECT @@GLOBAL.generated_random_password_length;
SELECT @@SESSION.generated_random_password_length;
--echo "Disconnect from usr ..."
disconnect con1;

--connection default
DROP USER usr;

--echo
#------------------------------
# User without privileges
#------------------------------
--echo #--Test variable value setting with/without user privileges
--echo
let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
let $pswd = query_get_value ( $qry, "generated password", 1);

connect(con1, localhost, usr, '$pswd',);
--echo #--Non-privileged user unable to set global value
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@GLOBAL.generated_random_password_length = 48;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--Non-privileged user able to set session value
SET @@SESSION.generated_random_password_length = 48;
SELECT @@SESSION.generated_random_password_length;
disconnect con1;
--echo
connection default;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO usr;
FLUSH PRIVILEGES;

# User with privilege
connect(con1, localhost, usr, '$pswd',);
--echo
--echo #--Privileged user able to set global value
SET @@GLOBAL.generated_random_password_length = 48;
SELECT @@GLOBAL.generated_random_password_length;
--echo
--echo #--Privileged user able to set session value
SET @@SESSION.generated_random_password_length = 48;
SELECT @@SESSION.generated_random_password_length;

disconnect con1;

# Cleanup
connection default;
REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM usr;
FLUSH PRIVILEGES;
drop user usr;
SET GLOBAL generated_random_password_length = DEFAULT;