379 lines
19 KiB
Plaintext
379 lines
19 KiB
Plaintext
#-----------------------------------------------------------------------
|
|
# Setup : Create users, grant privileges, create connections
|
|
CREATE USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
CREATE USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efghi';
|
|
CREATE USER zaphodbeeblebrox@localhost IDENTIFIED BY 'xyz';
|
|
GRANT CREATE USER ON *.* TO arthurdent@localhost;
|
|
GRANT UPDATE ON mysql.* TO zaphodbeeblebrox@localhost;
|
|
GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO marvintheparanoidandroid@localhost;
|
|
#-----------------------------------------------------------------------
|
|
# Test 1: Use RETAIN CURRENT PASSWORD with ALTER USER
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
0
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
1
|
|
SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
|
|
ALTER USER arthurdent@localhost IDENTIFIED by 'xyz' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
1
|
|
SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
|
|
SELECT @additional_password_1 <> @additional_password_2 AS
|
|
ADDITIONAL_PASSWORD_CHANGED;
|
|
ADDITIONAL_PASSWORD_CHANGED
|
|
1
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
1
|
|
SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
|
|
SELECT @additional_password_2 = @additional_password_3 AS
|
|
ADDITIONAL_PASSWORD_NOT_CHANGED;
|
|
ADDITIONAL_PASSWORD_NOT_CHANGED
|
|
1
|
|
#-----------------------------------------------------------------------
|
|
# Test 2: Use DISCARD OLD PASSWORD with ALTER USER
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
|
|
mysql.user WHERE user LIKE 'marvintheparanoidandroid';
|
|
ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
|
|
0
|
|
ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd'
|
|
RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
|
|
mysql.user WHERE user LIKE 'marvintheparanoidandroid';
|
|
ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
|
|
1
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
|
|
mysql.user WHERE user LIKE 'marvintheparanoidandroid';
|
|
ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID
|
|
0
|
|
#-----------------------------------------------------------------------
|
|
# Test 3: Use RETAIN CURRENT PASSWORD with SET PASSWORD
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
|
|
mysql.user WHERE user LIKE 'zaphodbeeblebrox';
|
|
ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
|
|
0
|
|
SET PASSWORD FOR zaphodbeeblebrox@localhost = 'abcd' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
|
|
mysql.user WHERE user LIKE 'zaphodbeeblebrox';
|
|
ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
|
|
1
|
|
SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
|
|
SET PASSWORD FOR zaphodbeeblebrox@localhost = 'efghi' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
|
|
mysql.user WHERE user LIKE 'zaphodbeeblebrox';
|
|
ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
|
|
1
|
|
SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
|
|
SELECT @additional_password_1 <> @additional_password_2 AS
|
|
ADDITIONAL_PASSWORD_CHANGED;
|
|
ADDITIONAL_PASSWORD_CHANGED
|
|
1
|
|
SET PASSWORD FOR zaphodbeeblebrox@localhost='xyz';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
|
|
mysql.user WHERE user LIKE 'zaphodbeeblebrox';
|
|
ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX
|
|
1
|
|
SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
|
|
'$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
|
|
SELECT @additional_password_2 = @additional_password_3 AS
|
|
ADDITIONAL_PASSWORD_NOT_CHANGED;
|
|
ADDITIONAL_PASSWORD_NOT_CHANGED
|
|
1
|
|
#-----------------------------------------------------------------------
|
|
# Test 4: RENAME USER
|
|
RENAME USER arthurdent@localhost TO arthurdent1@localhost;
|
|
SELECT COUNT(JSON_KEYS(mysql.user.user_attributes)) AS
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
0
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
|
|
WHERE user LIKE 'arthurdent1';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT1
|
|
1
|
|
RENAME USER arthurdent1@localhost TO arthurdent@localhost;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
|
|
WHERE user LIKE 'arthurdent';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT
|
|
1
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
|
|
WHERE user LIKE 'arthurdent1';
|
|
ADDITIONAL_PASSWORD_FOR_ARTHURDENT1
|
|
0
|
|
#-----------------------------------------------------------------------
|
|
# Test 5: DROP USER
|
|
CREATE USER u1@localhost IDENTIFIED BY 'abcd';
|
|
CREATE USER u2@localhost IDENTIFIED BY 'abcd';
|
|
ALTER USER u1@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD,
|
|
u2@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
|
|
ADDITIONAL_PASSWORD_FOR_U1
|
|
1
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
|
|
ADDITIONAL_PASSWORD_FOR_U2
|
|
1
|
|
DROP USER u1@localhost, u2@localhost;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
|
|
ADDITIONAL_PASSWORD_FOR_U1
|
|
0
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
|
|
ADDITIONAL_PASSWORD_FOR_U2
|
|
0
|
|
#-----------------------------------------------------------------------
|
|
# Test 6: Syntax
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
NO_ADDITIONAL_PASSWORDS
|
|
0
|
|
ALTER USER arthurdent@localhost IDENTIFIED by '1234' RETAIN CURRENT PASSWORD,
|
|
marvintheparanoidandroid@localhost IDENTIFIED BY '5678',
|
|
zaphodbeeblebrox@localhost IDENTIFIED BY '9012' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
TWO_ADDITIONAL_PASSWORDS
|
|
2
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
|
|
marvintheparanoidandroid@localhost IDENTIFIED BY '5678'
|
|
RETAIN CURRENT PASSWORD,
|
|
zaphodbeeblebrox@localhost IDENTIFIED BY '9012';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
TWO_ADDITIONAL_PASSWORDS
|
|
2
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY '5678',
|
|
marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
|
|
zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
NO_ADDITIONAL_PASSWORDS
|
|
0
|
|
#-----------------------------------------------------------------------
|
|
# Test 7: Permission
|
|
SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
|
|
SET PASSWORD = 'abcd' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
|
|
zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
SET PASSWORD = 'xyz' RETAIN CURRENT PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost
|
|
IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost
|
|
IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
|
|
arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
|
|
#-----------------------------------------------------------------------
|
|
# Test 8: Non built-in plugin
|
|
CREATE USER plug IDENTIFIED WITH test_plugin_server BY '123';
|
|
ALTER USER plug IDENTIFIED BY '123' RETAIN CURRENT PASSWORD;
|
|
Warnings:
|
|
Warning 13294 RETAIN CURRENT PASSWORD ignored for user 'plug'@'%' as its authentication plugin test_plugin_server does not support multiple passwords.
|
|
ALTER USER plug DISCARD OLD PASSWORD;
|
|
Warnings:
|
|
Warning 13295 DISCARD OLD PASSWORD ignored for user 'plug'@'%' as its authentication plugin test_plugin_server does not support multiple passwords.
|
|
DROP USER plug;
|
|
#-----------------------------------------------------------------------
|
|
# Test 9: Plugin change
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ZERO_ADDITIONAL_PASSWORDS
|
|
0
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh'
|
|
RETAIN CURRENT PASSWORD;
|
|
ALTER USER zaphodbeeblebrox@localhost IDENTIFIED BY 'ijkl'
|
|
RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS THREE_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
THREE_ADDITIONAL_PASSWORDS
|
|
3
|
|
ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
BY 'abcd';
|
|
ALTER USER marvintheparanoidandroid@localhost IDENTIFIED WITH 'sha256_password'
|
|
BY 'efgh';
|
|
ALTER USER zaphodbeeblebrox@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
BY 'ijkl';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ZERO_ADDITIONAL_PASSWORDS
|
|
0
|
|
#-----------------------------------------------------------------------
|
|
# Test 10: Empty first password
|
|
SET PASSWORD FOR arthurdent@localhost = '';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
|
|
ERROR HY000: Empty password can not be retained as second password for user 'arthurdent'@'localhost'.
|
|
#-----------------------------------------------------------------------
|
|
# Test 11: Binary log
|
|
CREATE USER userX IDENTIFIED BY 'abcd', userY IDENTIFIED BY 'efgh';
|
|
include/save_binlog_position.inc
|
|
ALTER USER userX IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
|
|
userY IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
ALTER USER userX DISCARD OLD PASSWORD,
|
|
userY IDENTIFIED BY 'mnop' RETAIN CURRENT PASSWORD;
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*DISCARD OLD PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
ALTER USER userX IDENTIFIED BY 'qrst' RETAIN CURRENT PASSWORD,
|
|
userY DISCARD OLD PASSWORD PASSWORD REQUIRE CURRENT;
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*DISCARD OLD PASSWORD.*PASSWORD REQUIRE CURRENT.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
DROP USER userX;
|
|
#-----------------------------------------------------------------------
|
|
# Test 12: With REPLACE clause
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' PASSWORD REQUIRE CURRENT;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'ijkl' REPLACE 'efgh';
|
|
SET PASSWORD = 'mnop' REPLACE 'ijkl';
|
|
SET PASSWORD = 'qrst' REPLACE 'mnop' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'uvwx' REPLACE 'qrst' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' REPLACE 'qrst';
|
|
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
|
|
SET PASSWORD = 'efgh' REPLACE 'qrst';
|
|
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
|
|
#-----------------------------------------------------------------------
|
|
# Test 13: ALTER USER with USER() function
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
ALTER USER USER() IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
|
|
ALTER USER USER() DISCARD OLD PASSWORD;
|
|
#-----------------------------------------------------------------------
|
|
# Test 14: Plugin change and retained password
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED WITH 'caching_sha2_password' BY 'abcd';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ONE_ADDITIONAL_PASSWORDS
|
|
1
|
|
ALTER USER arthurdent@localhost IDENTIFIED WITH caching_sha2_password BY 'ijkl';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ONE_ADDITIONAL_PASSWORDS
|
|
1
|
|
ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password' BY 'mnop';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ZERO_ADDITIONAL_PASSWORDS
|
|
0
|
|
ALTER USER arthurdent@localhost IDENTIFIED WITH 'sha256_password' BY 'qrst' RETAIN CURRENT PASSWORD;
|
|
ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because authentication plugin is being changed.
|
|
#-----------------------------------------------------------------------
|
|
# Test 15: Setting empty password
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd';
|
|
ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
TWO_ADDITIONAL_PASSWORDS
|
|
2
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY '';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ONE_ADDITIONAL_PASSWORDS
|
|
1
|
|
SET PASSWORD FOR marvintheparanoidandroid@localhost = '';
|
|
SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
|
|
AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
|
|
ZERO_ADDITIONAL_PASSWORDS
|
|
0
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY '' RETAIN CURRENT PASSWORD;
|
|
ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because new password is empty.
|
|
SET PASSWORD FOR arthurdent@localhost = '' RETAIN CURRENT PASSWORD;
|
|
ERROR HY000: Current password can not be retained for user 'arthurdent'@'localhost' because new password is empty.
|
|
CREATE USER userX;
|
|
SET PASSWORD FOR userX = 'abcd' RETAIN CURRENT PASSWORD;
|
|
ERROR HY000: Empty password can not be retained as second password for user 'userX'@'%'.
|
|
DROP USER userX;
|
|
#-----------------------------------------------------------------------
|
|
# Test 16: General log
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
|
|
TRUNCATE TABLE mysql.general_log;
|
|
SET @old_log_output = @@global.log_output;
|
|
SET @old_general_log = @@global.general_log;
|
|
SET @old_general_log_file = @@global.general_log_file;
|
|
SET GLOBAL general_log_file = 'MYSQLTEST_VARDIR/log/multiple_passwords_general.log';
|
|
SET GLOBAL log_output = 'FILE,TABLE';
|
|
SET GLOBAL general_log= 'ON';
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
|
|
SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
|
|
SET PASSWORD = 'efgh' REPLACE 'abcd' RETAIN CURRENT PASSWORD;
|
|
ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
|
|
marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
|
|
zaphodbeeblebrox@localhost IDENTIFIED BY 'abcd'
|
|
REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 22 PASSWORD EXPIRE DEFAULT
|
|
PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 10 DAY
|
|
PASSWORD REQUIRE CURRENT OPTIONAL ACCOUNT UNLOCK;
|
|
Show what is logged:
|
|
------ rewrite ------
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND
|
|
command_type NOT LIKE 'Prepare';
|
|
argument
|
|
ALTER USER 'arthurdent'@'localhost' IDENTIFIED BY <secret> RETAIN CURRENT PASSWORD
|
|
ALTER USER arthurdent@localhost DISCARD OLD PASSWORD
|
|
ALTER USER 'arthurdent'@'localhost' IDENTIFIED BY <secret> RETAIN CURRENT PASSWORD,'marvintheparanoidandroid'@'localhost' DISCARD OLD PASSWORD,'zaphodbeeblebrox'@'localhost' IDENTIFIED BY <secret> REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 22 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 10 DAY PASSWORD REQUIRE CURRENT OPTIONAL
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
|
|
argument
|
|
SET PASSWORD FOR `arthurdent`@`localhost`=<secret> RETAIN CURRENT PASSWORD
|
|
SET PASSWORD FOR `arthurdent`@`localhost`=<secret> REPLACE <secret> RETAIN CURRENT PASSWORD
|
|
------ done ------
|
|
#-----------------------------------------------------------------------
|
|
# Cleanup : Destroy connections, Drop users
|
|
DROP USER arthurdent@localhost;
|
|
DROP USER marvintheparanoidandroid@localhost;
|
|
DROP USER zaphodbeeblebrox@localhost;
|
|
#-----------------------------------------------------------------------
|