SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
20
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
20
CREATE USER u1 IDENTIFIED BY RANDOM PASSWORD, u2 IDENTIFIED BY RANDOM PASSWORD, u3 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
u1	%	random
u2	%	random
u3	%	random
CREATE USER u4 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
u4	%	random
select current_user;
current_user
foo@%
** Verify that REPLACE-syntax works
user	host	generated password
foo	%	random
** Verify that RETAIN-syntax works
user	host	generated password
foo	%	random
** Verify that ALTER USER works as expected.
select current_user;
current_user
foo@%
** Verify that REPLACE-syntax works
user	host	generated password
foo	%	random
SET PASSWORD FOR u1 = 'foo';
** Verify that it works for current user.
SET PASSWORD TO RANDOM;
user	host	generated password
u1	%	random
SELECT CURRENT_USER();
CURRENT_USER()
u1@%
** Test password length
set session generated_random_password_length=6;
Password length
6
set session generated_random_password_length=66;
Password length
66
DROP USER u1,u2,u3,u4,foo;
CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	random
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
"Disconnect from usr ..."
DROP USER usr;
Logged in successfully through usr1
SELECT CURRENT_USER();
CURRENT_USER()
usr1@%
"Disconnect from usr1 ..."
DROP USER usr1;

#-- Testing Syntax of the new CREATE USER/ALTER USER/SET PASSWORD extension

#--Valid combinations/ syntaxes of RANDOM PASSWORD
CREATE USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
SET PASSWORD for 'usr'@'localhost' TO RANDOM;
user	host	generated password
usr	localhost	#####
DROP USER 'usr'@'localhost';

CREATE USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
ALTER USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
SET PASSWORD for 'usr' TO RANDOM;
user	host	generated password
usr	%	#####
DROP USER 'usr';

CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
ALTER USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
SET PASSWORD for 'usr'@'%' TO RANDOM;
user	host	generated password
usr	%	#####
DROP USER 'usr'@'%';

CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
SET PASSWORD for usr TO RANDOM;
user	host	generated password
usr	%	#####
DROP USER usr;

CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
SET PASSWORD for usr@localhost TO RANDOM;
user	host	generated password
usr	localhost	#####
DROP USER usr@localhost;

CREATE USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
ALTER USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####
SET PASSWORD for usr@'%' TO RANDOM;
user	host	generated password
usr	%	#####
DROP USER usr@'%';

CREATE USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
ALTER USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
SET PASSWORD for 'usr'@localhost TO RANDOM;
user	host	generated password
usr	localhost	#####
DROP USER 'usr'@localhost;

CREATE USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
ALTER USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	#####
SET PASSWORD for usr@'localhost' TO RANDOM;
user	host	generated password
usr	localhost	#####
DROP USER usr@'localhost';

CREATE USER usr IDENTIFIED BY Random Password;
user	host	generated password
usr	%	#####
DROP USER usr;
CREATE USER usr IDENTIFIED BY random password;
user	host	generated password
usr	%	#####
DROP USER usr;
CREATE USER usr IDENTIFIED BY rANDOm PASSword;
user	host	generated password
usr	%	#####
DROP USER usr;

CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD,
usr2 IDENTIFIED BY RANDOM PASSWORD,
usr3 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr1	%	#####
usr2	%	#####
usr3	%	#####
ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD,
usr2 IDENTIFIED BY RANDOM PASSWORD,
usr3 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr1	%	#####
usr2	%	#####
usr3	%	#####
DROP USER usr1;
DROP USER usr2;
DROP USER usr3;

#--Invalid combinations/ syntaxes of RANDOM PASSWORD
CREATE USER usr IDENTIFIED BY RANDOM;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

CREATE USER usr IDENTIFIED BY RANDOM PASSWD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWD' at line 1

CREATE USER usr IDENTIFIED RANDOM PASSWORD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM PASSWORD' at line 1

CREATE USER usr BY RANDOM PASSWORD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY RANDOM PASSWORD' at line 1

CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	######

ALTER USER usr IDENTIFIED BY RANDOM;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

ALTER USER usr IDENTIFIED BY RANDOM PASSWD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWD' at line 1

ALTER USER usr IDENTIFIED RANDOM PASSWORD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM PASSWORD' at line 1

ALTER USER usr BY RANDOM PASSWORD;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY RANDOM PASSWORD' at line 1

SET PASSWORD for usr RANDOM;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM' at line 1
DROP USER usr;

ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
ERROR HY000: Operation ALTER USER failed for 'usr'@'%'

CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	######

ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
ERROR HY000: Operation ALTER USER failed for 'usr'@'localhost'
DROP USER usr;

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


#--CREATE temp user with RANDOM PASSWORD
CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr_temp	%	######
#--CREATE user with RANDOM PASSWORD

#--Login to usr with RANDOM PASSWORD
#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--Disconnect from usr ...
SET PASSWORD FOR usr_temp TO RANDOM;
ERROR 42000: Access denied for user 'usr'@'%' to database 'mysql'
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
#--Disconnect from usr ...
DROP USER usr;
DROP USER usr_temp;

#--CREATE temp user with RANDOM PASSWORD
CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr_temp	%	#####

#--CREATE user with RANDOM PASSWORD

#--GRANT ALL PRIVILEGES TO user
GRANT ALL ON *.* TO usr;
FLUSH PRIVILEGES;

#--Login to usr with RANDOM PASSWORD
#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--SET PASSWORD for user with RANDOM PASSWORD
#--Disconnect from usr ...

#--Login to usr with RANDOM PASSWORD
#--SET PASSWORD for user with RANDOM PASSWORD
SET PASSWORD FOR usr_temp TO RANDOM;
user	host	generated password
usr_temp	%	#####

#--ALTER user with RANDOM PASSWORD
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr_temp	%	#####

#--CREATE tempuser with RANDOM PASSWORD
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr_ntemp	%	#####
#--Disconnect from usr ...

#--ALTER user with RANDOM PASSWORD

#--Login to usr with RANDOM PASSWORD
#--SET PASSWORD for user with RANDOM PASSWORD
#--Disconnect from usr ...


#--Login to usr with RANDOM PASSWORD

#--SET PASSWORD for user with RANDOM PASSWORD
SET PASSWORD FOR root TO RANDOM;
ERROR 42000: Can't find any matching row in the user table
#--ALTER root with RANDOM PASSWORD
ALTER USER root IDENTIFIED BY RANDOM PASSWORD;
ERROR HY000: Operation ALTER USER failed for 'root'@'%'
DROP USER usr;
DROP USER usr_temp;
DROP USER usr_ntemp;

CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr_temp	%	#####
GRANT INSERT ON *.* TO usr_temp;
FLUSH PRIVILEGES;

#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

DROP USER usr;
DROP USER usr_temp;
CREATE USER usr IDENTIFIED by 'foobar';

#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--Disconnect from usr ...

DROP USER usr;
CREATE USER usr IDENTIFIED by 'foobar';

#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--Disconnect from usr ...

DROP USER usr;
CREATE USER usr;
#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--Disconnect from usr ...
DROP USER usr;
CREATE USER usr;
#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
#--Disconnect from usr ...
DROP USER usr;
CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	######
#-----------------------------------------------------------------------
# RETAIN CURRENT PASSWORD with ALTER USER and SET PASSWORD
#-----------------------------------------------------------------------

SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
ADDITIONAL_PASSWORD_FOR_USR
0


SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
ADDITIONAL_PASSWORD_FOR_USR
1

SET @pswd1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');


SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
ADDITIONAL_PASSWORD_FOR_USR
1

SET @pswd2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');

SELECT @pswd1 <> @pswd2 AS ADDITIONAL_PASSWORD_CHANGED;
ADDITIONAL_PASSWORD_CHANGED
1

#--Logged in successfully through usr with secondary password
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
#--Disconnect from usr ...

#--Logged in successfully through usr with primary password
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
#--Disconnect from usr ...



SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user
WHERE user LIKE 'usr';
ADDITIONAL_PASSWORD_FOR_USR
1

SET @pswd3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
'$.additional_password') FROM mysql.user WHERE user LIKE 'usr');

SELECT @pswd2 = @pswd3 AS ADDITIONAL_PASSWORD_NOT_CHANGED;
ADDITIONAL_PASSWORD_NOT_CHANGED
1

#--Logged in successfully through usr with secondary password
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
#--Disconnect from usr ...

#--Logged in successfully through usr with primary password2
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
#--Disconnect from usr ...

ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)

ALTER USER usr@localhost DISCARD OLD PASSWORD;

#--Logged in successfully through usr with primary password
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
#--Disconnect from usr ...


ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)
ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)

DROP USER usr@localhost;
#-----------------------------------------------------------------------
# REQUIRE CURRENT
#-----------------------------------------------------------------------

CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	%	#####

#--Logged in successfully through usr
select CURRENT_USER();
CURRENT_USER()
usr@%

#--Logged in successfully through usr
select CURRENT_USER();
CURRENT_USER()
usr@%

#--Logged in successfully through usr
select CURRENT_USER();
CURRENT_USER()
usr@%
#--Execute set password

ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE 'qrst';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
SET PASSWORD TO RANDOM REPLACE 'qrst';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
SET PASSWORD FOR usr TO RANDOM REPLACE 'qrst';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
DROP USER usr;
#-----------------------------------------------------------------------
# Plugin change
#-----------------------------------------------------------------------

CREATE USER usr1@localhost IDENTIFIED BY RANDOM PASSWORD, usr2@localhost IDENTIFIED BY RANDOM PASSWORD, usr3@localhost IDENTIFIED BY RANDOM PASSWORD ;
user	host	generated password
usr1	localhost	######
usr2	localhost	######
usr3	localhost	######

#--Logged in successfully through usr1
SELECT CURRENT_USER();
CURRENT_USER()
usr1@localhost
"Disconnect from usr1 ..."


#--Logged in successfully through usr2
SELECT CURRENT_USER();
CURRENT_USER()
usr2@localhost
#--Disconnect from usr2 ...


#--Logged in successfully through usr3
SELECT CURRENT_USER();
CURRENT_USER()
usr3@localhost
--echo #--Disconnect from usr3 ...

Plugin change for usr1, usr2, usr3


#--Logged in successfully through usr1
SELECT CURRENT_USER();
CURRENT_USER()
usr1@localhost
"Disconnect from usr1 ..."


#--Logged in successfully through usr2
SELECT CURRENT_USER();
CURRENT_USER()
usr2@localhost
#--Disconnect from usr2 ...


#--Logged in successfully through usr3
SELECT CURRENT_USER();
CURRENT_USER()
usr3@localhost
--echo #--Disconnect from usr3 ...

DROP USER usr1@localhost;
DROP USER usr2@localhost;
DROP USER usr3@localhost;
#-----------------------------------------------------------------------
# General log
#-----------------------------------------------------------------------
TRUNCATE TABLE mysql.general_log;
SET @old_log_output = @@global.log_output;
@old_log_output
SET @old_general_log = @@global.general_log;
@old_general_log
SET @old_general_log_file = @@global.general_log_file;
@old_general_log_file
SET GLOBAL general_log_file = 'MYSQLTEST_VARDIR/log/random_password_general.log';
SET GLOBAL log_output =       'FILE,TABLE';
SET GLOBAL general_log=       'ON';
CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	######
ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr	localhost	######

#--Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@localhost
SET PASSWORD TO RANDOM;
user	host	generated password
usr	localhost	######
#--Disconnect from usr ...


Display general_log content
------ rewrite ------
SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE USER %' AND command_type NOT LIKE 'Prepare';
argument
CREATE USER 'usr'@'localhost' IDENTIFIED BY <secret>
SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND command_type NOT LIKE 'Prepare';
argument
ALTER USER 'usr'@'localhost' IDENTIFIED BY <secret>
SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
argument
SET PASSWORD FOR `usr`@`localhost`=<secret>
SET PASSWORD FOR `usr`@`localhost`=<secret>
------ 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;
#-----------------------------------------------------------------------
# Binary log
#-----------------------------------------------------------------------
include/save_binlog_position.inc
CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr1	%	#####
usr2	%	#####
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER.*usr1.*usr2.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
user	host	generated password
usr1	%	#####
usr2	%	#####
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*usr1.*usr2.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
SET PASSWORD FOR usr1 TO RANDOM;
user	host	generated password
usr1	%	#####
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*usr1.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
DROP USER usr1, usr2;
#-----------------------------------------------------------------------
# Test NEW variable generated_random_password_length
#-----------------------------------------------------------------------
#--Print default values of the variable
SHOW VARIABLES LIKE 'generated_random_password_length';
Variable_name	Value
generated_random_password_length	66
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
20
SELECT @@LOCAL.generated_random_password_length;
@@LOCAL.generated_random_password_length
66
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
66
SELECT @@generated_random_password_length;
@@generated_random_password_length
66

#--set global value to 10
SET GLOBAL generated_random_password_length = 10;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
10

#--set session value to 30
SET SESSION generated_random_password_length = 30;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
30

#--set session value to 40
SET @@GLOBAL.generated_random_password_length = 40;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
40

#--set session value to 50
SET @@SESSION.generated_random_password_length = 50;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
50

#--set session value to 80
SET @@generated_random_password_length = 80;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
80

#--set session value to 90
SET generated_random_password_length = 90;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
90

#--set session value to DEFAULT
SET SESSION generated_random_password_length = DEFAULT;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
40

#--set global value to DEFAULT
SET GLOBAL generated_random_password_length = DEFAULT;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
20

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

#--set global value to 4
SET @@GLOBAL.generated_random_password_length = 4;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '4'
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5

#--set session value to 5
SET @@SESSION.generated_random_password_length = 5;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5

#--set session value to 4
SET @@SESSION.generated_random_password_length = 4;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '4'
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5

#--Test invalid values
SET @@GLOBAL.generated_random_password_length =;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SET @@GLOBAL.generated_random_password_length = b;
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET GLOBAL generated_random_password_length = b;
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET @@LOCAL.generated_random_password_length = '4';
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET LOCAL generated_random_password_length = '10';
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET @@GLOBAL.generated_random_password_length = 800000000000000000000000000000000000;
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET SESSION generated_random_password_length = 800000000000000000000000000000000000;
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET @@GLOBAL.generated_random_password_length = 3.5;
ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
SET @@GLOBAL.generated_random_password_length = -4;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '-4'
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SET LOCAL generated_random_password_length = -10;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '-10'
SELECT @@LOCAL.generated_random_password_length;
@@LOCAL.generated_random_password_length
5

#--Print Max values of the variable
#--set global value to 255
SET @@GLOBAL.generated_random_password_length = 255;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255

#--set global value to 256
SET @@GLOBAL.generated_random_password_length = 256;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '256'
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255

#--set session value to 255
SET @@SESSION.generated_random_password_length = 255;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255

#--set session value to 256
SET @@SESSION.generated_random_password_length = 256;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '256'
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255

#--set global and session to DEFAULT
SET GLOBAL generated_random_password_length = DEFAULT;
SET SESSION generated_random_password_length = DEFAULT;
#--Test GLOBAL setting
#-- 1) Test with a lower value
#--set global to value 5
SET @@GLOBAL.generated_random_password_length = 5;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5

#--create user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."

#--alter user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."

#--set password for usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."
DROP USER usr;
SET @@GLOBAL.generated_random_password_length = DEFAULT;

#-- 2) Test with the max value
#--set global to value 255
SET @@GLOBAL.generated_random_password_length = 255;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255

#-- create user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--alter user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--set password user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."
DROP USER usr;
SET @@GLOBAL.generated_random_password_length = DEFAULT;

#-- 3) Test with lower than min value
#--set session to value 0
SET @@GLOBAL.generated_random_password_length = 0;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '0'
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5

#--create user usr

#--login to user usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."

#--create user usr

#--login to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."

#--set password for usr

#--login to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
5
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5
"Disconnect from usr ..."
DROP USER usr;
SET @@GLOBAL.generated_random_password_length = DEFAULT;

#-- 3) Test with a value which has crossed its max value
#--set global to value 256
SET @@GLOBAL.generated_random_password_length = 256;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '256'
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255

#--create user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--alter user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--set password

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."
DROP USER usr;
#--Test SESSION setting
#-- 1) Test with a lower value
#--set session to value 5
SET @@SESSION.generated_random_password_length = 5;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5

#--create user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--alter user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--set password

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."
DROP USER usr;
#-- 2) Test with the max value
#--set session to value 255
SET @@SESSION.generated_random_password_length = 255;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255

#--create user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--alter user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--set password

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."
DROP USER usr;
#-- 3) Test will value less than min value
#--set session to value 0
SET @@SESSION.generated_random_password_length = 0;
Warnings:
Warning	1292	Truncated incorrect generated_random_password_length value: '0'
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
5

#--create user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--alter user usr

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."

#--set password

#--connect to usr
Logged in successfully through usr
SELECT CURRENT_USER();
CURRENT_USER()
usr@%
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
255
"Disconnect from usr ..."
DROP USER usr;

#--Test variable value setting with/without user privileges

#--Non-privileged user unable to set global value
SET @@GLOBAL.generated_random_password_length = 48;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
255

#--Non-privileged user able to set session value
SET @@SESSION.generated_random_password_length = 48;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
48

GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO usr;
FLUSH PRIVILEGES;

#--Privileged user able to set global value
SET @@GLOBAL.generated_random_password_length = 48;
SELECT @@GLOBAL.generated_random_password_length;
@@GLOBAL.generated_random_password_length
48

#--Privileged user able to set session value
SET @@SESSION.generated_random_password_length = 48;
SELECT @@SESSION.generated_random_password_length;
@@SESSION.generated_random_password_length
48
REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM usr;
FLUSH PRIVILEGES;
drop user usr;
SET GLOBAL generated_random_password_length = DEFAULT;