1450 lines
44 KiB
Plaintext
1450 lines
44 KiB
Plaintext
#
|
|
# 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;
|