#-----------------------------------------------------------------------
# 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;
#-----------------------------------------------------------------------