1749 lines
72 KiB
Plaintext
1749 lines
72 KiB
Plaintext
#
|
|
# WL#6409: CREATE/ALTER USER
|
|
#
|
|
call mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] Server shutdown in progress");
|
|
# CREATE USER
|
|
|
|
CREATE USER user1;
|
|
SELECT host,user,plugin FROM mysql.user
|
|
WHERE USER='user1';
|
|
host %
|
|
user user1
|
|
plugin caching_sha2_password
|
|
|
|
CREATE USER user3@%;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1
|
|
CREATE USER user3@localhost BY 'auth_string' WITH 'sha_256_password';
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY 'auth_string' WITH 'sha_256_password'' at line 1
|
|
CREATE USER "user2"@'%';
|
|
SELECT host,user,plugin FROM mysql.user
|
|
WHERE USER='user2';
|
|
host %
|
|
user user2
|
|
plugin caching_sha2_password
|
|
CREATE USER user9@localhost IDENTIFIED WITH 'mysql_native_password' AS 'auth_string';
|
|
ERROR HY000: The password hash doesn't have the expected format.
|
|
|
|
CREATE USER user10@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$ABCDEFGHIJKLMNOPQRSTabcdefgh01234567ijklmnop89012345ABCDEFGH678';
|
|
|
|
CREATE USER u1@localhost;
|
|
SELECT User,plugin FROM mysql.user
|
|
WHERE USER='u1';
|
|
User u1
|
|
plugin caching_sha2_password
|
|
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
|
|
SELECT User,plugin FROM mysql.user
|
|
WHERE USER='u2';
|
|
User u2
|
|
plugin caching_sha2_password
|
|
|
|
CREATE USER user5@localhost IDENTIFIED AS 'auth_string';
|
|
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 'AS 'auth_string'' at line 1
|
|
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
|
|
SELECT USER();
|
|
USER()
|
|
u4@localhost
|
|
|
|
CREATE USER user4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
SELECT User,plugin FROM mysql.user
|
|
WHERE USER='user4';
|
|
User user4
|
|
plugin sha256_password
|
|
|
|
CREATE USER user6@localhost IDENTIFIED WITH 'mysql_native_password';
|
|
SELECT User,plugin FROM mysql.user
|
|
WHERE USER='user6';
|
|
User user6
|
|
plugin mysql_native_password
|
|
|
|
CREATE USER user7@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
SELECT User,plugin FROM mysql.user
|
|
WHERE USER='user7';
|
|
User user7
|
|
plugin mysql_native_password
|
|
|
|
SELECT USER();
|
|
USER()
|
|
user7@localhost
|
|
CREATE USER user8@localhost
|
|
IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
PASSWORD EXPIRE NEVER;
|
|
SELECT User,plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='user8';
|
|
User user8
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
password_lifetime 0
|
|
SELECT USER();
|
|
USER()
|
|
user8@localhost
|
|
# Tesing with test_plugin_server plugin
|
|
CREATE USER tu1@localhost IDENTIFIED WITH 'test_plugin_server';
|
|
SELECT User,plugin,password_expired,
|
|
password_expired FROM mysql.user WHERE USER='tu1';
|
|
User tu1
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
password_expired N
|
|
CREATE USER tu2@localhost IDENTIFIED WITH 'test_plugin_server'
|
|
BY 'auth_@13*' PASSWORD EXPIRE;
|
|
SELECT User,plugin,password_expired
|
|
FROM mysql.user WHERE USER='tu2';
|
|
User tu2
|
|
plugin test_plugin_server
|
|
password_expired Y
|
|
|
|
CREATE USER tu3@localhost IDENTIFIED WITH 'test_plugin_server' AS '%auth_O0s-tring';
|
|
SELECT User,plugin,password_expired
|
|
FROM mysql.user WHERE USER='tu3';
|
|
User tu3
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
|
|
CREATE USER u5@localhost REQUIRE SSL;
|
|
SELECT User,plugin,ssl_type
|
|
FROM mysql.user WHERE USER='u5';
|
|
User u5
|
|
plugin caching_sha2_password
|
|
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 caching_sha2_password
|
|
ssl_type X509
|
|
CREATE USER tu4@localhost IDENTIFIED WITH 'test_plugin_server' BY 'djgsj743$'
|
|
REQUIRE SSL;
|
|
SELECT User,plugin,ssl_type
|
|
FROM mysql.user WHERE USER='tu4';
|
|
User tu4
|
|
plugin test_plugin_server
|
|
ssl_type ANY
|
|
CREATE USER tu5@localhost IDENTIFIED WITH 'test_plugin_server' AS 'dwh@#ghd$!'
|
|
REQUIRE X509;
|
|
SELECT User,plugin,ssl_type
|
|
FROM mysql.user WHERE USER='tu5';
|
|
User tu5
|
|
plugin test_plugin_server
|
|
ssl_type X509
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER 'cipher';
|
|
SELECT User,plugin,ssl_type,ssl_cipher,
|
|
x509_issuer,x509_subject FROM mysql.user WHERE USER='u7';
|
|
User u7
|
|
plugin sha256_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher cipher
|
|
x509_issuer
|
|
x509_subject
|
|
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA'
|
|
PASSWORD EXPIRE NEVER;
|
|
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime FROM mysql.user WHERE USER='u8';
|
|
User u8
|
|
plugin sha256_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject
|
|
password_expired N
|
|
password_lifetime 0
|
|
SELECT USER();
|
|
USER()
|
|
u8@localhost
|
|
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 caching_sha2_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject sub
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string#y'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE DEFAULT;
|
|
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime 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=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
password_expired N
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
USER()
|
|
u10@localhost
|
|
CREATE USER tu6@localhost IDENTIFIED WITH 'test_plugin_server' AS '#hGrt0O6'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='tu6';
|
|
User tu6
|
|
plugin test_plugin_server
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
|
|
SELECT User,plugin,max_questions
|
|
FROM mysql.user WHERE USER='u11';
|
|
User u11
|
|
plugin caching_sha2_password
|
|
max_questions 2
|
|
CREATE USER u12@localhost IDENTIFIED BY 'auth_string'
|
|
WITH MAX_QUERIES_PER_HOUR 2 PASSWORD EXPIRE NEVER;
|
|
SELECT User,plugin,max_questions,
|
|
password_expired,password_lifetime FROM mysql.user WHERE USER='u12';
|
|
User u12
|
|
plugin caching_sha2_password
|
|
max_questions 2
|
|
password_expired N
|
|
password_lifetime 0
|
|
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 999 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 999
|
|
CREATE USER u15@localhost,
|
|
u16@localhost IDENTIFIED BY 'auth_string',
|
|
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='u15';
|
|
User u15
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u16';
|
|
User u16
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u17';
|
|
User u17
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
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',
|
|
u22@localhost IDENTIFIED WITH 'test_plugin_server',
|
|
u23@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_&string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
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='u18';
|
|
User u18
|
|
plugin caching_sha2_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
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='u19';
|
|
User u19
|
|
plugin caching_sha2_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
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='u20';
|
|
User u20
|
|
plugin sha256_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
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='u21';
|
|
User u21
|
|
plugin sha256_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
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='u22';
|
|
User u22
|
|
plugin test_plugin_server
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
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='u23';
|
|
User u23
|
|
plugin mysql_native_password
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
DROP USER tu1@localhost,tu2@localhost,tu3@localhost,tu4@localhost,tu5@localhost,
|
|
tu6@localhost,user1@'%',user2@'%',user4@localhost,user6@localhost,
|
|
user7@localhost,user8@localhost,user10@localhost,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,u22@localhost,u23@localhost;
|
|
# ALTER USER
|
|
CREATE USER u1@localhost;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime
|
|
FROM mysql.user WHERE USER='u1';
|
|
User u1
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
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_last_changed,password_lifetime
|
|
FROM mysql.user WHERE USER='u1';
|
|
User u1
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
CREATE USER u2@localhost IDENTIFIED WITH 'mysql_native_password';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
|
|
User u2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u2@localhost IDENTIFIED WITH 'mysql_native_password' PASSWORD EXPIRE NEVER;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
|
|
User u2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired Y
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
ALTER USER USER() IDENTIFIED BY 'abc';
|
|
|
|
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
|
|
User u3
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
USER()
|
|
u3@localhost
|
|
ALTER USER u3@localhost IDENTIFIED WITH 'test_plugin_server' BY 'auth_string';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
|
|
User u3
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u3@localhost
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
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,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
|
|
User u3
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
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_last_changed,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_last_changed #
|
|
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_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
CREATE USER u5@localhost REQUIRE SSL;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u5';
|
|
User u5
|
|
ssl_type ANY
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
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_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
SET PASSWORD='new_auth_string';
|
|
SELECT USER();
|
|
USER()
|
|
u5@localhost
|
|
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';
|
|
User u6
|
|
ssl_type X509
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u6@localhost IDENTIFIED WITH 'test_plugin_server'
|
|
AS 'new_auth_string' REQUIRE SSL;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';
|
|
User u6
|
|
ssl_type ANY
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER 'DHE-RSA-AES256-SHA';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';
|
|
User u7
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u7@localhost IDENTIFIED WITH 'sha256_password' BY 'new_auth_string'
|
|
REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';
|
|
User u7
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
USER()
|
|
u7@localhost
|
|
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
|
|
User u8
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA";
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
|
|
User u8
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
USER()
|
|
u8@localhost
|
|
CREATE USER tu1@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
BY 'auth_string' REQUIRE ISSUER 'issuer';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';
|
|
User tu1
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER tu1@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA";
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';
|
|
User tu1
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired Y
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
|
|
User u9
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject sub
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u9@localhost
|
|
REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
|
|
User u9
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u9@localhost IDENTIFIED WITH 'test_plugin_server' BY 'auth_string77hg'
|
|
REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
|
|
User u9
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject
|
|
plugin test_plugin_server
|
|
password_expired N
|
|
password_last_changed #
|
|
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=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u10';
|
|
User u10
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
password_last_changed #
|
|
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_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
USER()
|
|
u10@localhost
|
|
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
|
|
ALTER USER u12@localhost IDENTIFIED WITH 'test_plugin_server'
|
|
WITH MAX_QUERIES_PER_HOUR 1000;
|
|
SELECT User,max_questions FROM mysql.user WHERE USER='u12';
|
|
User u12
|
|
max_questions 1000
|
|
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_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u13@localhost PASSWORD EXPIRE;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,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_last_changed #
|
|
password_lifetime NULL
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
ALTER USER USER() IDENTIFIED BY 'new_pwd_string';
|
|
SELECT USER();
|
|
USER()
|
|
u13@localhost
|
|
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_last_changed,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_last_changed #
|
|
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_last_changed,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_last_changed #
|
|
password_lifetime 365
|
|
CREATE USER tu2@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,max_questions,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';
|
|
User tu2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 0
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER tu2@localhost WITH MAX_USER_CONNECTIONS 12 MAX_QUERIES_PER_HOUR 543
|
|
PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,max_user_connections,max_questions,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';
|
|
User tu2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 12
|
|
max_questions 543
|
|
password_last_changed #
|
|
password_lifetime 365
|
|
SELECT USER();
|
|
USER()
|
|
tu2@localhost
|
|
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='u15';
|
|
User u15
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired #
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u16';
|
|
User u16
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired #
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u17';
|
|
User u17
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired #
|
|
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='u15';
|
|
User u15
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired #
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u16';
|
|
User u16
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired #
|
|
password_lifetime NULL
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER='u17';
|
|
User u17
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin sha256_password
|
|
password_expired #
|
|
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 '!Y_TOdh6)',
|
|
u22@localhost IDENTIFIED WITH 'sha256_password',
|
|
u23@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_&string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
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_last_changed,password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u18' AND 'u23' order by 1;
|
|
User u18
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u19
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
max_user_connections 0
|
|
max_questions 0
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u20
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u21
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u22
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u23
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
ALTER USER u18@localhost,
|
|
u19@localhost,
|
|
u20@localhost,
|
|
u21@localhost,
|
|
u22@localhost,
|
|
u23@localhost
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
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_last_changed,password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u18' AND 'u23' order by 1;
|
|
User u18
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
User u19
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
User u2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin mysql_native_password
|
|
password_expired N
|
|
max_user_connections 0
|
|
max_questions 0
|
|
password_last_changed #
|
|
password_lifetime NULL
|
|
User u20
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
User u21
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
User u22
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
User u23
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
plugin sha256_password
|
|
password_expired N
|
|
max_user_connections 2
|
|
max_questions 2
|
|
password_last_changed #
|
|
password_lifetime 0
|
|
DROP USER tu1@localhost,tu2@localhost,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,u22@localhost,u23@localhost;
|
|
# ALTER USER with current 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 IDENTIFIED BY 'npwd';
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY 'npwd'' at line 1
|
|
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
|
|
ALTER USER u2@localhost IDENTIFIED BY 'auth_string'
|
|
PASSWORD EXPIRE INTERVAL 45 DAY;
|
|
SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
|
|
User u2
|
|
ssl_type
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject
|
|
plugin caching_sha2_password
|
|
password_expired N
|
|
password_last_changed #
|
|
password_lifetime 45
|
|
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 'caching_sha2_password' 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 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# DDL output for SHOW CREATE USER u2@localhost and recreating user
|
|
DROP USER u2@localhost;
|
|
CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE NONE;
|
|
SELECT USER();
|
|
USER()
|
|
u2@localhost
|
|
|
|
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 IDENTIFIED BY 'auth_string';
|
|
SHOW CREATE USER u3@localhost;
|
|
CREATE USER for u3@localhost
|
|
CREATE USER 'u3'@'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
|
|
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
|
|
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
SHOW CREATE USER user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER 'user1'@'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
|
|
|
|
CREATE USER user2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
|
|
SHOW CREATE USER user2@localhost;
|
|
CREATE USER for user2@localhost
|
|
CREATE USER 'user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' 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 u5@localhost REQUIRE SSL;
|
|
SHOW CREATE USER u5@localhost;
|
|
CREATE USER for u5@localhost
|
|
CREATE USER 'u5'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
ALTER USER u5@localhost REQUIRE X509;
|
|
# SHOW CREATE USER after ALTER user attributes
|
|
SHOW CREATE USER u5@localhost;
|
|
CREATE USER for u5@localhost
|
|
CREATE USER 'u5'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE X509 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 PASSWORD EXPIRE INTERVAL 5 DAY;
|
|
SHOW CREATE USER u6@localhost;
|
|
CREATE USER for u6@localhost
|
|
CREATE USER 'u6'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE X509 PASSWORD EXPIRE INTERVAL 5 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
ALTER USER u6@localhost IDENTIFIED BY 'auth_string'
|
|
REQUIRE X509 PASSWORD EXPIRE INTERVAL 19 DAY;
|
|
# SHOW CREATE USER after ALTER user attributes
|
|
SHOW CREATE USER u6@localhost;
|
|
CREATE USER for u6@localhost
|
|
CREATE USER 'u6'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE X509 PASSWORD EXPIRE INTERVAL 19 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Recreating user from SHOW CREATE output
|
|
DROP USER u6@localhost;
|
|
CREATE USER 'u6'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE X509 PASSWORD EXPIRE INTERVAL 19 DAY;
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER 'cipher';
|
|
SHOW CREATE USER u7@localhost;
|
|
CREATE USER for u7@localhost
|
|
CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER 'cipher' 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>' 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 'caching_sha2_password' 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=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
|
|
SHOW CREATE USER u10@localhost;
|
|
CREATE USER for u10@localhost
|
|
CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
ALTER USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_CONNECTIONS_PER_HOUR 1000
|
|
MAX_USER_CONNECTIONS 20 MAX_QUERIES_PER_HOUR 60
|
|
MAX_UPDATES_PER_HOUR 100;
|
|
# SHOW CREATE USER after ALTER user attributes
|
|
SHOW CREATE USER u10@localhost;
|
|
CREATE USER for u10@localhost
|
|
CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20 PASSWORD EXPIRE DEFAULT 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 'caching_sha2_password' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 2 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 'caching_sha2_password' 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
|
|
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
|
|
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
|
|
CREATE USER u15@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
CIPHER 'cipher' 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=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'cipher' 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 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
|
|
WITH MAX_CONNECTIONS_PER_HOUR 1000
|
|
MAX_USER_CONNECTIONS 20 MAX_QUERIES_PER_HOUR 60
|
|
MAX_UPDATES_PER_HOUR 100;
|
|
# SHOW CREATE USER after ALTER user attributes
|
|
SHOW CREATE USER u15@localhost;
|
|
CREATE USER for u15@localhost
|
|
CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'cipher' WITH MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Recreating user from SHOW CREATE output
|
|
DROP USER u15@localhost;
|
|
CREATE USER 'u15'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA'
|
|
CIPHER 'cipher'
|
|
WITH MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100
|
|
MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
|
|
PASSWORD EXPIRE DEFAULT;
|
|
DROP USER user1@localhost,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;
|
|
CREATE USER user1@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
|
|
MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;
|
|
SELECT user,plugin,ssl_type,ssl_cipher,
|
|
x509_issuer,x509_subject,max_user_connections,
|
|
max_questions,max_updates,max_connections FROM mysql.user
|
|
WHERE USER='user1';
|
|
user user1
|
|
plugin mysql_native_password
|
|
ssl_type #
|
|
ssl_cipher
|
|
x509_issuer /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
max_user_connections 20
|
|
max_questions 60
|
|
max_updates 100
|
|
max_connections 1000
|
|
|
|
GRANT SELECT ON *.* TO user1@localhost;
|
|
GRANT SELECT,INSERT,UPDATE ON mysql.* TO user1@localhost;
|
|
|
|
SHOW GRANTS FOR user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT SELECT ON *.* TO `user1`@`localhost`
|
|
GRANT SELECT, INSERT, UPDATE ON `mysql`.* TO `user1`@`localhost`
|
|
|
|
REVOKE INSERT,UPDATE ON mysql.* FROM user1@localhost;
|
|
SHOW GRANTS FOR user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT SELECT ON *.* TO `user1`@`localhost`
|
|
GRANT SELECT ON `mysql`.* TO `user1`@`localhost`
|
|
|
|
CREATE USER user3@localhost IDENTIFIED BY 'auth_string';
|
|
# Cleanup
|
|
DROP USER user1@localhost,user2@localhost,user3@localhost;
|
|
|
|
# SET PASSWORD Tests
|
|
|
|
CREATE USER user1@localhost IDENTIFIED BY 'auth_string';
|
|
CREATE USER user2@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string1';
|
|
SET PASSWORD FOR user1@localhost='auth_xyz@';
|
|
SET PASSWORD FOR user2@localhost='gd636@gj';
|
|
|
|
CREATE USER user4@localhost;
|
|
ALTER USER user4@localhost PASSWORD EXPIRE;
|
|
SET PASSWORD FOR user4@localhost='';
|
|
CREATE USER user3@localhost IDENTIFIED BY 'auth_string';
|
|
CREATE USER user5@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string1';
|
|
SET PASSWORD FOR user3@localhost='auth_abc';
|
|
GRANT SELECT ON *.* TO user3@localhost;
|
|
SELECT USER();
|
|
USER()
|
|
user3@localhost
|
|
SET PASSWORD='auth_xyz';
|
|
SELECT user,plugin,ssl_type,password_expired,
|
|
password_lifetime FROM mysql.user where user='user3';
|
|
user user3
|
|
plugin caching_sha2_password
|
|
ssl_type #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
|
|
SET PASSWORD FOR user4@localhost='auth_xyz';
|
|
SELECT user,plugin,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user where user='user4';
|
|
user user4
|
|
plugin caching_sha2_password
|
|
ssl_type #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
|
|
SET PASSWORD FOR user5@localhost='auth_dhsga5';
|
|
SELECT user,plugin,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user where user='user5';
|
|
user user5
|
|
plugin sha256_password
|
|
ssl_type #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
GRANT ALL ON *.* TO user4@localhost;
|
|
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
|
|
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
|
|
SELECT USER();
|
|
USER()
|
|
user4@localhost
|
|
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
|
|
SELECT USER();
|
|
USER()
|
|
user4@localhost
|
|
SELECT user,plugin,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user where user='user4';
|
|
user user4
|
|
plugin caching_sha2_password
|
|
ssl_type #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
|
|
CREATE USER user6@localhost IDENTIFIED BY 'auth_string';
|
|
SELECT user,plugin,
|
|
password_expired FROM mysql.user WHERE USER='user6';
|
|
user user6
|
|
plugin caching_sha2_password
|
|
password_expired #
|
|
ALTER USER user6@localhost IDENTIFIED WITH 'sha256_password';
|
|
SELECT user,plugin,
|
|
password_expired FROM mysql.user WHERE USER='user6';
|
|
user user6
|
|
plugin sha256_password
|
|
password_expired #
|
|
SET PASSWORD FOR user6@localhost='plaintext_password';
|
|
SELECT user,plugin
|
|
FROM mysql.user WHERE USER='user6';
|
|
user user6
|
|
plugin sha256_password
|
|
SELECT USER();
|
|
USER()
|
|
user6@localhost
|
|
|
|
# Cleanup
|
|
DROP USER user1@localhost,user2@localhost,user3@localhost,
|
|
user4@localhost,user5@localhost,user6@localhost;
|
|
# Restart server.
|
|
SHOW GLOBAL VARIABLES LIKE 'log_bin';
|
|
Variable_name Value
|
|
log_bin ON
|
|
CREATE USER u1 IDENTIFIED WITH 'mysql_native_password' BY 'azundris1';
|
|
CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE SSL;
|
|
CREATE USER user11@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
PASSWORD EXPIRE NEVER ACCOUNT LOCK;
|
|
CREATE USER user12@localhost IDENTIFIED WITH 'sha256_password'
|
|
PASSWORD EXPIRE NEVER;
|
|
CREATE USER u2@localhost IDENTIFIED BY 'meow';
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
ALTER USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
ALTER USER user11@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
|
|
ALTER USER user12@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE INTERVAL 90 DAY;
|
|
CREATE USER user13@localhost IDENTIFIED BY 'auth_string' ACCOUNT UNLOCK;
|
|
ALTER USER user13@localhost WITH MAX_QUERIES_PER_HOUR 22
|
|
MAX_USER_CONNECTIONS 4 ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
CREATE USER user14@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK;
|
|
CREATE USER user15@localhost IDENTIFIED WITH 'mysql_native_password' BY 'azundris1'
|
|
PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
|
|
ALTER USER user15@localhost PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
CREATE USER user16@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
ALTER USER user16@localhost PASSWORD EXPIRE INTERVAL 10 DAY ACCOUNT LOCK;
|
|
SELECT USER();
|
|
USER()
|
|
u1@localhost
|
|
ALTER USER USER() IDENTIFIED BY 'new-auth';
|
|
CREATE USER user10@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
# Show binlog events
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'u1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8D15E74BC850D11573A52BE84B504DD0BA40C1E3'
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user8'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE SSL
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user11'@'localhost' IDENTIFIED WITH 'mysql_native_password' PASSWORD EXPIRE NEVER ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user12'@'localhost' IDENTIFIED WITH 'sha256_password' PASSWORD EXPIRE NEVER
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'u10'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE SSL
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'user11'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'user12'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE INTERVAL 90 DAY ACCOUNT UNLOCK
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user13'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' ACCOUNT UNLOCK
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'user13'@'localhost' WITH MAX_QUERIES_PER_HOUR 22 MAX_USER_CONNECTIONS 4 PASSWORD EXPIRE NEVER ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user14'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user15'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*8D15E74BC850D11573A52BE84B504DD0BA40C1E3' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'user15'@'localhost' PASSWORD EXPIRE DEFAULT ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user16'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' PASSWORD EXPIRE NEVER ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'user16'@'localhost' PASSWORD EXPIRE INTERVAL 10 DAY ACCOUNT LOCK
|
|
binlog.000001 # Query # # use `test`; ALTER USER 'u1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*474F9F196851C7025F166095EDBBD42CFAB1DA6B'
|
|
binlog.000001 # Query # # use `test`; CREATE USER 'user10'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
DROP USER user1@localhost,user8@localhost,user10@localhost,user11@localhost,
|
|
user12@localhost,user13@localhost,user14@localhost,user15@localhost,
|
|
user16@localhost,u10@localhost,u1,u2@localhost;
|
|
TRUNCATE TABLE mysql.general_log;
|
|
--------------- 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/create_or_alter_user.log';
|
|
SET GLOBAL log_output = 'FILE,TABLE';
|
|
SET GLOBAL general_log= 'ON';
|
|
CREATE USER u1 IDENTIFIED WITH 'mysql_native_password' BY 'azundris1';
|
|
CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password' REQUIRE SSL;
|
|
CREATE USER user11@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
PASSWORD EXPIRE NEVER ACCOUNT LOCK;
|
|
CREATE USER user12@localhost IDENTIFIED WITH 'sha256_password'
|
|
PASSWORD EXPIRE NEVER;
|
|
CREATE USER u2@localhost IDENTIFIED BY 'meow';
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
ALTER USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
ALTER USER user11@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
|
|
ALTER USER user12@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE INTERVAL 90 DAY;
|
|
CREATE USER user13@localhost IDENTIFIED BY 'auth_string' ACCOUNT UNLOCK;
|
|
ALTER USER user13@localhost WITH MAX_QUERIES_PER_HOUR 22
|
|
MAX_USER_CONNECTIONS 4 ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
CREATE USER user14@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK;
|
|
CREATE USER user15@localhost IDENTIFIED WITH 'mysql_native_password' BY 'azundris1'
|
|
PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
|
|
ALTER USER user15@localhost PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
CREATE USER user16@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
ALTER USER user16@localhost PASSWORD EXPIRE INTERVAL 10 DAY ACCOUNT LOCK;
|
|
SELECT USER();
|
|
USER()
|
|
u1@localhost
|
|
ALTER USER USER() IDENTIFIED BY 'new-auth';
|
|
CREATE USER user10@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
Show what is logged:
|
|
------ rewrite ------
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE USER %' AND
|
|
command_type NOT LIKE 'Prepare';
|
|
argument
|
|
CREATE USER 'u1'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>
|
|
CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password' REQUIRE SSL
|
|
CREATE USER user11@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
PASSWORD EXPIRE NEVER ACCOUNT LOCK
|
|
CREATE USER user12@localhost IDENTIFIED WITH 'sha256_password'
|
|
PASSWORD EXPIRE NEVER
|
|
CREATE USER 'u2'@'localhost' IDENTIFIED BY <secret>
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
|
|
CREATE USER 'user13'@'localhost' IDENTIFIED BY <secret> ACCOUNT UNLOCK
|
|
CREATE USER user14@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK
|
|
CREATE USER 'user15'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY <secret> PASSWORD EXPIRE NEVER ACCOUNT UNLOCK
|
|
CREATE USER user16@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER
|
|
CREATE USER user10@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND
|
|
command_type NOT LIKE 'Prepare';
|
|
argument
|
|
ALTER USER 'u10'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY <secret> REQUIRE SSL
|
|
ALTER USER user11@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
|
|
ALTER USER user12@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE INTERVAL 90 DAY
|
|
ALTER USER user13@localhost WITH MAX_QUERIES_PER_HOUR 22
|
|
MAX_USER_CONNECTIONS 4 ACCOUNT LOCK PASSWORD EXPIRE NEVER
|
|
ALTER USER user15@localhost PASSWORD EXPIRE DEFAULT ACCOUNT LOCK
|
|
ALTER USER user16@localhost PASSWORD EXPIRE INTERVAL 10 DAY ACCOUNT LOCK
|
|
ALTER USER 'u1'@'%' IDENTIFIED BY <secret>
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
|
|
argument
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'GRANT %'AND
|
|
command_type NOT LIKE 'Prepare';
|
|
argument
|
|
------ done ------
|
|
DROP USER user1@localhost,user8@localhost,user10@localhost,user11@localhost,
|
|
user12@localhost,user13@localhost,user14@localhost,user15@localhost,
|
|
user16@localhost,u10@localhost,u1,u2@localhost;
|
|
SET GLOBAL general_log_file= @old_general_log_file;
|
|
SET GLOBAL general_log= @old_general_log;
|
|
SET GLOBAL log_output= @old_log_output;
|
|
|
|
Bug #20602525 ALTER USER DOES NOT VERIFY AUTH PLUGIN
|
|
|
|
CREATE USER u1;
|
|
ALTER USER u1 IDENTIFIED WITH 'invalid_plugin';
|
|
ERROR HY000: Plugin 'invalid_plugin' is not loaded
|
|
ALTER USER u1 IDENTIFIED WITH 'invalid_plugin' BY 'secret';
|
|
ERROR HY000: Plugin 'invalid_plugin' is not loaded
|
|
ALTER USER u1 IDENTIFIED WITH 'invalid_plugin' AS 'secret';
|
|
ERROR HY000: Plugin 'invalid_plugin' is not loaded
|
|
DROP USER u1;
|
|
|
|
Bug #20364862 MYSQLADMIN PASSWORD AFFECTS EXTERNAL AUTH ACCOUNTS
|
|
|
|
CREATE USER bug20364862_user@localhost IDENTIFIED WITH test_plugin_server AS 'bug20364862_dest';
|
|
CREATE USER bug20364862_dest@localhost IDENTIFIED BY 'dest_password';
|
|
GRANT PROXY ON bug20364862_dest@localhost TO bug20364862_user@localhost;
|
|
CREATE DATABASE db1;
|
|
GRANT ALL PRIVILEGES ON db1.* TO bug20364862_dest@localhost WITH GRANT OPTION;
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
ERROR 1045 (28000) at line 1: Access denied for user 'bug20364862_dest'@'localhost' (using password: YES)
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
Level Code Message
|
|
Note 1699 SET PASSWORD has no significance for user 'bug20364862_user'@'localhost' as authentication plugin does not support it.
|
|
SELECT USER, AUTHENTICATION_STRING FROM mysql.user WHERE user like 'bug20364862_user';
|
|
USER AUTHENTICATION_STRING
|
|
bug20364862_user bug20364862_dest
|
|
GRANT UPDATE ON mysql.* TO bug20364862_dest@localhost;
|
|
FLUSH PRIVILEGES;
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
Level Code Message
|
|
Note 1699 SET PASSWORD has no significance for user 'bug20364862_user'@'localhost' as authentication plugin does not support it.
|
|
SELECT USER, AUTHENTICATION_STRING FROM mysql.user WHERE user like 'bug20364862_user';
|
|
USER AUTHENTICATION_STRING
|
|
bug20364862_user bug20364862_dest
|
|
REVOKE UPDATE ON mysql.* FROM bug20364862_dest@localhost;
|
|
GRANT CREATE USER ON *.* TO bug20364862_dest@localhost;
|
|
FLUSH PRIVILEGES;
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
Level Code Message
|
|
Note 1699 SET PASSWORD has no significance for user 'bug20364862_user'@'localhost' as authentication plugin does not support it.
|
|
SELECT USER, AUTHENTICATION_STRING FROM mysql.user WHERE user like 'bug20364862_user';
|
|
USER AUTHENTICATION_STRING
|
|
bug20364862_user bug20364862_dest
|
|
DROP DATABASE db1;
|
|
DROP USER bug20364862_user@localhost;
|
|
DROP USER bug20364862_dest@localhost;
|
|
|
|
Bug #20625566 SHOW CREATE USER ALLOWS ACCESS TO OTHER USERS PASSWORD HASH
|
|
|
|
# Restarting the server
|
|
# Restart server.
|
|
CREATE USER user1@localhost;
|
|
CREATE USER user1@47.9.9.9 IDENTIFIED BY 'pass1';
|
|
CREATE USER user2 IDENTIFIED BY 'pass2';
|
|
# case1 without any privileges
|
|
SELECT USER(), CURRENT_USER();
|
|
USER() CURRENT_USER()
|
|
user1@localhost user1@localhost
|
|
SHOW CREATE USER user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER user1@47.9.9.9;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
SHOW CREATE USER user2;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
SHOW CREATE USER invalid_user;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
# case2 with SELECT privilege on mysql db
|
|
GRANT SELECT ON mysql.* TO user1@localhost;
|
|
SELECT USER(), CURRENT_USER();
|
|
USER() CURRENT_USER()
|
|
user1@localhost user1@localhost
|
|
SHOW CREATE USER user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER user1@47.9.9.9;
|
|
CREATE USER for user1@47.9.9.9
|
|
CREATE USER 'user1'@'47.9.9.9' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER user2;
|
|
CREATE USER for user2@%
|
|
CREATE USER 'user2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER invalid_user;
|
|
ERROR HY000: Operation SHOW CREATE USER failed for 'invalid_user'@'%'
|
|
# case3 with all global privileges except SELECT
|
|
GRANT ALL ON *.* TO user1@localhost;
|
|
REVOKE SELECT ON *.* FROM user1@localhost;
|
|
REVOKE CREATE USER ON *.* FROM user1@localhost;
|
|
REVOKE SELECT ON mysql.* FROM user1@localhost;
|
|
SHOW GRANTS FOR user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user1`@`localhost`
|
|
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `user1`@`localhost`
|
|
SELECT USER(), CURRENT_USER();
|
|
USER() CURRENT_USER()
|
|
user1@localhost user1@localhost
|
|
SHOW CREATE USER user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER user1@47.9.9.9;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
# case4 with irrelevant privilege on mysql db
|
|
REVOKE ALL ON *.* FROM user1@localhost;
|
|
SHOW GRANTS FOR user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT USAGE ON *.* TO `user1`@`localhost`
|
|
GRANT UPDATE ON mysql.* TO user1@localhost;
|
|
SHOW GRANTS FOR user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT USAGE ON *.* TO `user1`@`localhost`
|
|
GRANT UPDATE ON `mysql`.* TO `user1`@`localhost`
|
|
SELECT USER(), CURRENT_USER();
|
|
USER() CURRENT_USER()
|
|
user1@localhost user1@localhost
|
|
SHOW CREATE USER user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SHOW CREATE USER user1@47.9.9.9;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
SHOW CREATE USER user2;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
SHOW CREATE USER invalid_user;
|
|
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'mysql'
|
|
# case4 test for proxy user
|
|
CREATE USER bug20625566_user@localhost IDENTIFIED WITH test_plugin_server
|
|
AS 'bug20625566_dest';
|
|
CREATE USER bug20625566_dest@localhost IDENTIFIED BY 'dest_password';
|
|
GRANT PROXY ON bug20625566_dest@localhost TO bug20625566_user@localhost;
|
|
SELECT USER(), CURRENT_USER();
|
|
USER() CURRENT_USER()
|
|
bug20625566_user@localhost bug20625566_dest@localhost
|
|
SHOW CREATE USER bug20625566_user@localhost;
|
|
ERROR 42000: Access denied for user 'bug20625566_dest'@'localhost' to database 'mysql'
|
|
SHOW CREATE USER bug20625566_dest@localhost;
|
|
CREATE USER for bug20625566_dest@localhost
|
|
CREATE USER 'bug20625566_dest'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS <secret> REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
DROP USER user1@localhost, user1@47.9.9.9, user2,
|
|
bug20625566_user@localhost, bug20625566_dest@localhost;
|