4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/r/grant_alter_user.result

1032 lines
42 KiB
Plaintext
Raw Permalink Blame History

#
# WL#6409: CREATE/ALTER USER
#
# CREATE USER
CREATE USER u1@localhost;
SELECT User,plugin FROM mysql.user WHERE USER='u1';
User u1
plugin <default_authentication_plugin>
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
SELECT User,plugin FROM mysql.user WHERE USER='u2';
User u2
plugin <default_authentication_plugin>
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
SELECT User,plugin FROM mysql.user WHERE USER='u3';
User u3
plugin sha256_password
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SELECT User,plugin FROM mysql.user WHERE USER='u4';
User u4
plugin sha256_password
CREATE USER u5@localhost REQUIRE SSL;
SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u5';
User u5
plugin <default_authentication_plugin>
ssl_type ANY
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u6';
User u6
plugin <default_authentication_plugin>
ssl_type X509
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
REQUIRE CIPHER "DHE-RSA-AES256-SHA" PASSWORD EXPIRE NEVER;
SELECT User,plugin,ssl_type,
ssl_cipher,x509_issuer,x509_subject,password_expired,password_lifetime FROM mysql.user WHERE USER='u7';
User u7
plugin sha256_password
ssl_type SPECIFIED
ssl_cipher DHE-RSA-AES256-SHA
x509_issuer
x509_subject
password_expired N
password_lifetime 0
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE ISSUER 'issuer';
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u8';
User u8
plugin sha256_password
ssl_type SPECIFIED
ssl_cipher
x509_issuer issuer
x509_subject
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u9';
User u9
plugin <default_authentication_plugin>
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject sub
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u10';
User u10
plugin sha256_password
ssl_type SPECIFIED
ssl_cipher DHE-RSA-AES256-SHA
x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u11';
User u11
plugin <default_authentication_plugin>
max_questions 2
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u12';
User u12
plugin <default_authentication_plugin>
max_questions 2
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
WITH MAX_CONNECTIONS_PER_HOUR 2;
SELECT User,plugin,max_connections FROM mysql.user WHERE USER='u13';
User u13
plugin sha256_password
max_connections 2
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 6 DAY;
SELECT User,plugin,max_user_connections,
password_expired,password_lifetime FROM mysql.user WHERE USER='u14';
User u14
plugin sha256_password
max_user_connections 2
password_expired N
password_lifetime 6
CREATE USER u15@localhost,
u16@localhost IDENTIFIED BY 'auth_string',
u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' PASSWORD EXPIRE;
SELECT User,plugin,password_expired,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u15' AND 'u17' ORDER BY User;
User u15
plugin <authentication_plugin>
password_expired Y
password_lifetime NULL
User u16
plugin <authentication_plugin>
password_expired Y
password_lifetime NULL
User u17
plugin <authentication_plugin>
password_expired Y
password_lifetime NULL
CREATE USER u18@localhost,
u19@localhost IDENTIFIED BY 'auth_string',
u20@localhost IDENTIFIED WITH 'sha256_password',
u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
PASSWORD EXPIRE NEVER;
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject,
max_questions,max_user_connections,password_expired,password_lifetime
FROM mysql.user WHERE USER BETWEEN 'u18' AND 'u21' ORDER BY User;
User u18
plugin <authentication_plugin>
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
max_questions 2
max_user_connections 2
password_expired N
password_lifetime 0
User u19
plugin <authentication_plugin>
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
max_questions 2
max_user_connections 2
password_expired N
password_lifetime 0
User u2
plugin <authentication_plugin>
ssl_type
ssl_cipher
x509_issuer
x509_subject
max_questions 0
max_user_connections 0
password_expired N
password_lifetime NULL
User u20
plugin <authentication_plugin>
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
max_questions 2
max_user_connections 2
password_expired N
password_lifetime 0
User u21
plugin <authentication_plugin>
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
max_questions 2
max_user_connections 2
password_expired N
password_lifetime 0
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
u19@localhost, u20@localhost, u21@localhost;
# ALTER USER
CREATE USER u1@localhost;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime
FROM mysql.user WHERE USER='u1';
User u1
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
its a no op
ALTER USER u1@localhost;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime
FROM mysql.user WHERE USER='u1';
User u1
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u2';
User u2
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
ALTER USER u2@localhost IDENTIFIED BY 'new_auth_string';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u2';
User u2
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u3';
User u3
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u3';
User u3
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin mysql_native_password
password_expired Y
password_lifetime NULL
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u4';
User u4
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u4@localhost IDENTIFIED WITH 'mysql_native_password'
BY 'auth_string';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u4';
User u4
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin mysql_native_password
password_expired N
password_lifetime NULL
CREATE USER u5@localhost REQUIRE SSL;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u5';
User u5
ssl_type ANY
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
ALTER USER u5@localhost IDENTIFIED WITH 'sha256_password';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u5';
User u5
ssl_type ANY
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired Y
password_lifetime NULL
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u6';
User u6
ssl_type X509
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
ALTER USER u6@localhost IDENTIFIED BY 'new_auth_string' REQUIRE SSL;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u6';
User u6
ssl_type ANY
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
BY 'auth_string' REQUIRE CIPHER 'cipher';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u7';
User u7
ssl_type SPECIFIED
ssl_cipher cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
REQUIRE ISSUER 'issuer';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u7';
User u7
ssl_type SPECIFIED
ssl_cipher
x509_issuer issuer
x509_subject
plugin mysql_native_password
password_expired Y
password_lifetime NULL
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE ISSUER 'issuer';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u8';
User u8
ssl_type SPECIFIED
ssl_cipher
x509_issuer issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password'
REQUIRE CIPHER "DHE-RSA-AES256-SHA";
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u8';
User u8
ssl_type SPECIFIED
ssl_cipher DHE-RSA-AES256-SHA
x509_issuer
x509_subject
plugin mysql_native_password
password_expired Y
password_lifetime NULL
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u9';
User u9
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject sub
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
ALTER USER u9@localhost REQUIRE ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u9';
User u9
ssl_type SPECIFIED
ssl_cipher
x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u10';
User u10
ssl_type SPECIFIED
ssl_cipher DHE-RSA-AES256-SHA
x509_issuer /C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE SSL;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u10';
User u10
ssl_type ANY
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
SELECT User,max_questions FROM mysql.user WHERE USER='u11';
User u11
max_questions 2
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6;
SELECT User,max_questions FROM mysql.user WHERE USER='u11';
User u11
max_questions 6
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
SELECT User,max_questions FROM mysql.user WHERE USER='u12';
User u12
max_questions 2
ALTER USER u12@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_QUERIES_PER_HOUR 8;
SELECT User,max_questions FROM mysql.user WHERE USER='u12';
User u12
max_questions 8
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
WITH MAX_CONNECTIONS_PER_HOUR 2;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u13';
User u13
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
password_lifetime NULL
ALTER USER u13@localhost PASSWORD EXPIRE;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user WHERE USER='u13';
User u13
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired Y
password_lifetime NULL
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
WITH MAX_USER_CONNECTIONS 2;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,max_user_connections,
password_lifetime FROM mysql.user WHERE USER='u14';
User u14
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
max_user_connections 2
password_lifetime NULL
ALTER USER u14@localhost WITH MAX_USER_CONNECTIONS 12 PASSWORD EXPIRE INTERVAL 365 DAY;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,max_user_connections,
password_lifetime FROM mysql.user WHERE USER='u14';
User u14
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin sha256_password
password_expired N
max_user_connections 12
password_lifetime 365
CREATE USER u15@localhost,
u16@localhost IDENTIFIED WITH 'sha256_password',
u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user
WHERE USER BETWEEN 'u15' AND 'u17' order by 1;
User u15
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
User u16
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
User u17
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
ALTER USER u15@localhost IDENTIFIED WITH 'sha256_password',
u16@localhost,
u17@localhost IDENTIFIED BY 'new_auth_string'
PASSWORD EXPIRE DEFAULT;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,
password_lifetime FROM mysql.user
WHERE USER BETWEEN 'u15' AND 'u17' order by 1;
User u15
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired Y
password_lifetime NULL
User u16
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
User u17
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
password_lifetime NULL
CREATE USER u18@localhost,
u19@localhost IDENTIFIED BY 'auth_string',
u20@localhost IDENTIFIED WITH 'sha256_password',
u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,max_user_connections,
max_questions,password_lifetime FROM mysql.user
WHERE USER BETWEEN 'u18' AND 'u21' order by 1;
User u18
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime NULL
User u19
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime NULL
User u2
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
max_user_connections 0
max_questions 0
password_lifetime NULL
User u20
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime NULL
User u21
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime NULL
ALTER USER u18@localhost, u19@localhost,
u20@localhost, u21@localhost
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB'
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
PASSWORD EXPIRE NEVER;
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,password_expired,max_user_connections,
max_questions,password_lifetime FROM mysql.user
WHERE USER BETWEEN 'u18' AND 'u21' order by 1;
User u18
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime 0
User u19
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime 0
User u2
ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin <authentication_plugin>
password_expired N
max_user_connections 0
max_questions 0
password_lifetime NULL
User u20
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime 0
User u21
ssl_type SPECIFIED
ssl_cipher
x509_issuer
x509_subject /C=SE/ST=Uppsala/O=MySQL AB
plugin <authentication_plugin>
password_expired N
max_user_connections 2
max_questions 2
password_lifetime 0
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
u19@localhost, u20@localhost, u21@localhost;
# CREATE USER with password expire attributes
CREATE USER u1@localhost PASSWORD EXPIRE NEVER;
# This should report 0
SELECT password_lifetime FROM mysql.user where user='u1';
password_lifetime
0
DROP USER u1@localhost;
CREATE USER u1@localhost PASSWORD EXPIRE DEFAULT;
# This should report NULL
SELECT password_expired,password_lifetime FROM mysql.user where user='u1';
password_expired password_lifetime
N NULL
DROP USER u1@localhost;
CREATE USER u1@localhost PASSWORD EXPIRE INTERVAL 4 DAY;
# Should report 4
SELECT password_lifetime FROM mysql.user where user='u1';
password_lifetime
4
DROP USER u1@localhost;
CREATE USER u1@localhost PASSWORD EXPIRE;
# This should report Y
SELECT password_expired FROM mysql.user where user='u1';
password_expired
Y
Please use --connect-expired-password option or invoke mysql in interactive mode.
DROP USER u1@localhost;
# CREATE USER with password expire attributes for anonymous user
CREATE USER '' PASSWORD EXPIRE;
ERROR HY000: Operation CREATE USER failed for anonymous user
CREATE USER '' PASSWORD EXPIRE NEVER;
ERROR HY000: Operation CREATE USER failed for anonymous user
CREATE USER '' PASSWORD EXPIRE INTERVAL 4 DAY;
ERROR HY000: Operation CREATE USER failed for anonymous user
# ALTER USER with user()
CREATE USER u1@localhost IDENTIFIED BY 'abc';
SELECT USER();
USER()
u1@localhost
ALTER USER u1@localhost PASSWORD EXPIRE;
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SET PASSWORD = 'def';
SELECT USER();
USER()
u1@localhost
ALTER USER u1@localhost PASSWORD EXPIRE;
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER user() IDENTIFIED BY 'abc';
SELECT USER();
USER()
u1@localhost
ALTER USER u1@localhost PASSWORD EXPIRE;
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER u1@localhost IDENTIFIED BY 'def';
SELECT USER();
USER()
u1@localhost
DROP USER u1@localhost;
# ALTER USER with current user is allowed to set only credential information
CREATE USER u1@localhost, u2@localhost IDENTIFIED BY 'abc';
GRANT ALL ON *.* TO u2@localhost;
ALTER USER USER() IDENTIFIED WITH 'sha256_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 'WITH 'sha256_password'' at line 1
ALTER USER USER() IDENTIFIED BY 'def', u2@localhost PASSWORD EXPIRE;
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 ', u2@localhost PASSWORD EXPIRE' at line 1
ALTER USER USER() IDENTIFIED BY 'def' PASSWORD EXPIRE;
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 'PASSWORD EXPIRE' at line 1
ALTER USER ;
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
DROP USER u1@localhost, u2@localhost;
# SHOW CREATE USER
CREATE USER u1@localhost;
SHOW CREATE USER u1@localhost;
CREATE USER for u1@localhost
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u1@localhost IDENTIFIED BY 'auth_string';
SHOW CREATE USER u1@localhost;
CREATE USER for u1@localhost
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
SHOW CREATE USER u2@localhost;
CREATE USER for u2@localhost
CREATE USER 'u2'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u2@localhost IDENTIFIED WITH 'sha256_password';
SHOW CREATE USER u2@localhost;
CREATE USER for u2@localhost
CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
SHOW CREATE USER u3@localhost;
CREATE USER for u3@localhost
CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u3@localhost PASSWORD EXPIRE NEVER;
SHOW CREATE USER u3@localhost;
CREATE USER for u3@localhost
CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SHOW CREATE USER u4@localhost;
CREATE USER for u4@localhost
CREATE USER 'u4'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u4@localhost PASSWORD EXPIRE INTERVAL 365 DAY;
SHOW CREATE USER u4@localhost;
CREATE USER for u4@localhost
CREATE USER 'u4'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u5@localhost REQUIRE SSL;
SHOW CREATE USER u5@localhost;
CREATE USER for u5@localhost
CREATE USER 'u5'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u5@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA";
SHOW CREATE USER u5@localhost;
CREATE USER for u5@localhost
CREATE USER 'u5'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
SHOW CREATE USER u6@localhost;
CREATE USER for u6@localhost
CREATE USER 'u6'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE X509 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u6@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA" WITH MAX_QUERIES_PER_HOUR 2;
SHOW CREATE USER u6@localhost;
CREATE USER for u6@localhost
CREATE USER 'u6'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
REQUIRE CIPHER 'DHE-RSA-AES256-SHA';
SHOW CREATE USER u7@localhost;
CREATE USER for u7@localhost
CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u7@localhost REQUIRE NONE WITH MAX_USER_CONNECTIONS 12;
SHOW CREATE USER u7@localhost;
CREATE USER for u7@localhost
CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE WITH MAX_USER_CONNECTIONS 12 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE ISSUER 'issuer';
SHOW CREATE USER u8@localhost;
CREATE USER for u8@localhost
CREATE USER 'u8'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
SHOW CREATE USER u8@localhost;
CREATE USER for u8@localhost
CREATE USER 'u8'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE ISSUER 'issuer' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
SHOW CREATE USER u9@localhost;
CREATE USER for u9@localhost
CREATE USER 'u9'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SUBJECT 'sub' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u9@localhost;
SHOW CREATE USER u9@localhost;
CREATE USER for u9@localhost
CREATE USER 'u9'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SUBJECT 'sub' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
SHOW CREATE USER u10@localhost;
CREATE USER for u10@localhost
CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u10@localhost PASSWORD EXPIRE NEVER;
SHOW CREATE USER u10@localhost;
CREATE USER for u10@localhost
CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
SHOW CREATE USER u11@localhost;
CREATE USER for u11@localhost
CREATE USER 'u11'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 10;
SHOW CREATE USER u11@localhost;
CREATE USER for u11@localhost
CREATE USER 'u11'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 10 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
SHOW CREATE USER u12@localhost;
CREATE USER for u12@localhost
CREATE USER 'u12'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u12@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10;
SHOW CREATE USER u12@localhost;
CREATE USER for u12@localhost
CREATE USER 'u12'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
WITH MAX_CONNECTIONS_PER_HOUR 2;
SHOW CREATE USER u13@localhost;
CREATE USER for u13@localhost
CREATE USER 'u13'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE WITH MAX_CONNECTIONS_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u13@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10;
SHOW CREATE USER u13@localhost;
CREATE USER for u13@localhost
CREATE USER 'u13'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
WITH MAX_USER_CONNECTIONS 2;
SHOW CREATE USER u14@localhost;
CREATE USER for u14@localhost
CREATE USER 'u14'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u14@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10
PASSWORD EXPIRE;
SHOW CREATE USER u14@localhost;
CREATE USER for u14@localhost
CREATE USER 'u14'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u15@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"
CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
SHOW CREATE USER u15@localhost;
CREATE USER for u15@localhost
CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u15@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
SHOW CREATE USER u15@localhost;
CREATE USER for u15@localhost
CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u16@localhost IDENTIFIED BY 'auth_string' PASSWORD EXPIRE;
SHOW CREATE USER u16@localhost;
CREATE USER for u16@localhost
CREATE USER 'u16'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u16@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
SHOW CREATE USER u16@localhost;
CREATE USER for u16@localhost
CREATE USER 'u16'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE X509 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u17@localhost WITH MAX_QUERIES_PER_HOUR 200
MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER;
SHOW CREATE USER u17@localhost;
CREATE USER for u17@localhost
CREATE USER 'u17'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u17@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
SHOW CREATE USER u17@localhost;
CREATE USER for u17@localhost
CREATE USER 'u17'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u18@localhost IDENTIFIED WITH 'sha256_password' PASSWORD EXPIRE INTERVAL 365 DAY;
SHOW CREATE USER u18@localhost;
CREATE USER for u18@localhost
CREATE USER 'u18'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE INTERVAL 365 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u18@localhost PASSWORD EXPIRE NEVER;
SHOW CREATE USER u18@localhost;
CREATE USER for u18@localhost
CREATE USER 'u18'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
CREATE USER u19@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB'
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"
PASSWORD EXPIRE DEFAULT;
SHOW CREATE USER u19@localhost;
CREATE USER for u19@localhost
CREATE USER 'u19'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
ALTER USER u19@localhost WITH MAX_QUERIES_PER_HOUR 200
MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER;
SHOW CREATE USER u19@localhost;
CREATE USER for u19@localhost
CREATE USER 'u19'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER '/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 200 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
u19@localhost;
#
# Bug #20553132 USER WITH EXPIRED PASSWORD ABLE TO EXECUTE
# ALTER USER .. PASSWORD EXPIRE COMMAND
#
CREATE USER 20553132_u1@localhost;
CREATE USER 20553132_u2@localhost;
CREATE USER '20553132_u3'@'%';
GRANT ALL ON *.* TO 20553132_u1@localhost;
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE;
ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE;
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE NEVER;
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE DEFAULT;
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER 20553132_u1@localhost, 20553132_u2@localhost IDENTIFIED BY 'abcd' PASSWORD EXPIRE NEVER;
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED BY 'defg' PASSWORD EXPIRE NEVER;
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE;
ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'hijk' PASSWORD EXPIRE DEFAULT;
SELECT USER();
USER()
20553132_u1@localhost
ALTER USER CURRENT_USER() IDENTIFIED BY 'abcd';
SELECT CURRENT_USER();
CURRENT_USER()
20553132_u3@%
ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE;
ALTER USER '20553132_u3'@'%' IDENTIFIED BY 'abcd';
SELECT CURRENT_USER();
CURRENT_USER()
20553132_u3@%
DROP USER 20553132_u1@localhost;
DROP USER 20553132_u2@localhost;
DROP USER '20553132_u3'@'%';
End of 5.7 tests!
Bug #20600865: IDENTIFIED BY PASSWORD IS NOT DEPRECATED FOR
ALTER USER BUT DOESN\'T WORK
CREATE USER u1;
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
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 'PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'' at line 1
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
PASSWORD EXPIRE;
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 'PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
PASSWORD EXPIRE' at line 1
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
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 'PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
WITH MAX_QUERIES_PER_' at line 1
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client";
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 'PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
REQUIRE CIPHER "DHE-R' at line 1
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
PASSWORD EXPIRE DEFAULT;
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 'PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
PASSWORD EXPIRE DEFAU' at line 1
DROP USER u1;
Bug #20634154 GRANT/ALTER USER CLEARS PASSWORD EXPIRE.
CREATE USER bug20634154@localhost IDENTIFIED BY 'abc';
SELECT CURRENT_USER();
CURRENT_USER()
bug20634154@localhost
ALTER USER bug20634154@localhost PASSWORD EXPIRE;
SELECT CURRENT_USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
GRANT USAGE ON *.* TO bug20634154@localhost;
SELECT CURRENT_USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER bug20634154@localhost;
SELECT CURRENT_USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER bug20634154@localhost IDENTIFIED BY 'def';
SELECT CURRENT_USER();
CURRENT_USER()
bug20634154@localhost
ALTER USER bug20634154@localhost IDENTIFIED BY 'abc' PASSWORD EXPIRE;
SELECT CURRENT_USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER bug20634154@localhost IDENTIFIED BY 'def' PASSWORD EXPIRE INTERVAL 10 DAY;
SELECT CURRENT_USER();
CURRENT_USER()
bug20634154@localhost
DROP USER bug20634154@localhost;
Bug #22205360 ALTER USER/SET PASSWORD DO NOT WORK FOR --INIT-FILE EXECUTION
CREATE USER bug22205360@localhost;
# shutdown the server
# Restart server with init-file option
SELECT 1;
1
1
# shutdown the server
# Restart server with init-file option
SELECT 1;
1
1
DROP USER bug22205360@localhost;
Reference in New Issue View Git Blame Copy Permalink