1177 lines
34 KiB
Plaintext
1177 lines
34 KiB
Plaintext
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;
|