1092 lines
42 KiB
Plaintext
1092 lines
42 KiB
Plaintext
|
|
# CREATE USER,ALTER USER,SHOW CREATE USER tests with ACCOUNT UNLOCK/LOCK
|
|
FLUSH STATUS;
|
|
CREATE USER user1;
|
|
SELECT host,user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='user1';
|
|
host %
|
|
user user1
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
user1@localhost
|
|
ALTER USER user1;
|
|
SELECT host,user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='user1';
|
|
host %
|
|
user user1
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
|
|
SHOW CREATE USER user1;
|
|
CREATE USER for user1@%
|
|
CREATE USER 'user1'@'%' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
user1@localhost
|
|
CREATE USER user2@localhost ACCOUNT UNLOCK;
|
|
SELECT host,user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='user2';
|
|
host localhost
|
|
user user2
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
# Current user ACCOUNT LOCK within own running session.
|
|
SELECT USER();
|
|
USER()
|
|
user2@localhost
|
|
ALTER USER user() IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
|
|
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 'ACCOUNT LOCK' at line 1
|
|
GRANT CREATE USER ON *.* TO user2@localhost;
|
|
FLUSH PRIVILEGES;
|
|
SELECT USER();
|
|
USER()
|
|
user2@localhost
|
|
ALTER USER user2@localhost IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
|
|
SELECT USER();
|
|
USER()
|
|
user2@localhost
|
|
connect(localhost,user2,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'user2'@'localhost'. Account is locked.
|
|
ALTER USER user2@localhost ACCOUNT LOCK;
|
|
SELECT host,user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='user2';
|
|
host localhost
|
|
user user2
|
|
plugin #
|
|
authentication_string #
|
|
account_locked Y
|
|
connect(localhost,user2,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'user2'@'localhost'. Account is locked.
|
|
CREATE USER 'user8'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT UNLOCK;
|
|
ALTER USER 'user8'@'localhost' ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
CREATE USER 'user9'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK;
|
|
CREATE USER 'user10'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
|
|
ALTER USER 'user10'@'localhost' PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
CREATE USER 'user11'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
ALTER USER 'user11'@'localhost' PASSWORD EXPIRE INTERVAL 10 DAY ACCOUNT LOCK;
|
|
CREATE USER ACCOUNT UNLOCK user5@localhost;
|
|
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 'UNLOCK user5@localhost' at line 1
|
|
CREATE USER ACCOUNT LOCK user6@localhost;
|
|
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 'LOCK user6@localhost' at line 1
|
|
ALTER USER ACCOUNT LOCK user2@localhost;
|
|
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 'LOCK user2@localhost' at line 1
|
|
ALTER USER ACCOUNT UNLOCK user2@localhot;
|
|
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 'UNLOCK user2@localhot' at line 1
|
|
CREATE USER user3,user4@localhost ACCOUNT LOCK;
|
|
CREATE USER user6@'%',user7@localhost ACCOUNT LOCK;
|
|
ALTER USER user3,user4@localhost ACCOUNT UNLOCK;
|
|
ALTER USER user7@localhost,user6@'%' ACCOUNT UNLOCK;
|
|
SELECT host,user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user3';
|
|
host %
|
|
user user3
|
|
plugin #
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT host,user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user4';
|
|
host localhost
|
|
user user4
|
|
plugin #
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
ALTER USER user4@localhost,user3 ACCOUNT LOCK;
|
|
SELECT host,user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user3';
|
|
host %
|
|
user user3
|
|
plugin #
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
SELECT host,user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user4';
|
|
host localhost
|
|
user user4
|
|
plugin #
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
SHOW STATUS LIKE 'locked_connects';
|
|
Variable_name Value
|
|
Locked_connects 2
|
|
FLUSH STATUS;
|
|
SELECT * FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE '%user%' AND GRANTEE NOT IN ("'mysql.session'@'localhost'");
|
|
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
|
|
'user2'@'localhost' def CREATE USER NO
|
|
'user8'@'localhost' def USAGE NO
|
|
'user9'@'localhost' def USAGE NO
|
|
'user10'@'localhost' def USAGE NO
|
|
'user11'@'localhost' def USAGE NO
|
|
'user4'@'localhost' def USAGE NO
|
|
'user7'@'localhost' def USAGE NO
|
|
'user1'@'%' def USAGE NO
|
|
'user3'@'%' def USAGE NO
|
|
'user6'@'%' def USAGE NO
|
|
DROP USER user1,user2@localhost,user3,user4@localhost,user6,user7@localhost,
|
|
user8@localhost,user9@localhost,user10@localhost,user11@localhost;
|
|
|
|
|
|
CREATE USER u1@localhost ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u1';
|
|
user u1
|
|
plugin #
|
|
authentication_string #
|
|
account_locked Y
|
|
connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u1'@'localhost'. Account is locked.
|
|
ALTER USER u1@localhost;
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u1';
|
|
user u1
|
|
plugin #
|
|
authentication_string #
|
|
account_locked Y
|
|
connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u1'@'localhost'. Account is locked.
|
|
CREATE USER u2@localhost IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u2';
|
|
user u2
|
|
plugin #
|
|
authentication_string #
|
|
account_locked Y
|
|
connect(localhost,u2,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u2'@'localhost'. Account is locked.
|
|
|
|
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 LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Recreating user using SHOW CREATE USER output for u2@localhost
|
|
DROP USER u2@localhost;
|
|
CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
connect(localhost,u2,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u2'@'localhost'. Account is locked.
|
|
ALTER USER u2@localhost IDENTIFIED BY 'auth_string' ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u2';
|
|
user u2
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
u2@localhost
|
|
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
|
|
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password'
|
|
ACCOUNT UNLOCK ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u3';
|
|
user u3
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
|
|
SHOW CREATE USER u3@localhost;
|
|
CREATE USER for u3@localhost
|
|
CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u3,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ALTER USER u3@localhost IDENTIFIED WITH 'sha256_password'
|
|
ACCOUNT LOCK ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u3';
|
|
user u3
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired Y
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
SET PASSWORD = 'def';
|
|
|
|
SHOW CREATE USER u3@localhost;
|
|
CREATE USER for u3@localhost
|
|
CREATE USER 'u3'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE CURRENT DEFAULT
|
|
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u4';
|
|
user u4
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
ALTER USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u4';
|
|
user u4
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
|
|
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
|
|
SELECT USER();
|
|
USER()
|
|
u4@localhost
|
|
|
|
CREATE USER user4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
ACCOUNT LOCK PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD EXPIRE INTERVAL 90 DAY;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user4';
|
|
user user4
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime 90
|
|
account_locked N
|
|
CREATE USER user5@localhost IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE NEVER;
|
|
SELECT USER();
|
|
USER()
|
|
user5@localhost
|
|
|
|
CREATE USER user6@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
ACCOUNT UNLOCK ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user6';
|
|
user user6
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked Y
|
|
|
|
SHOW CREATE USER user6@localhost;
|
|
CREATE USER for user6@localhost
|
|
CREATE USER 'user6'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,user6,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'user6'@'localhost'. Account is locked.
|
|
ALTER USER user6@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
ACCOUNT LOCK ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user6';
|
|
user user6
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
password_expired Y
|
|
password_lifetime 0
|
|
account_locked N
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
|
|
CREATE USER user7@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y'
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user7';
|
|
user user7
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
SHOW CREATE USER user7@localhost;
|
|
CREATE USER for user7@localhost
|
|
CREATE USER 'user7'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1643D320B9787E87A3C6CE2C953B9E4FDBC048BC' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
user7@localhost
|
|
ALTER USER user7@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string_sha256'
|
|
PASSWORD EXPIRE NEVER ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user7';
|
|
user user7
|
|
plugin sha256_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked Y
|
|
|
|
# Testing connection with wrong password and ACCOUNT LOCK state
|
|
connect(localhost,user7,auth_string#%y,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR 28000: Access denied for user 'user7'@'localhost' (using password: YES)
|
|
# Testing connection with correct password and ACCOUNT LOCK state
|
|
connect(localhost,user7,auth_string_sha256,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'user7'@'localhost'. Account is locked.
|
|
CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' ACCOUNT UNLOCK PASSWORD EXPIRE NEVER;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user8';
|
|
user user8
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
SHOW CREATE USER user8@localhost;
|
|
CREATE USER for user8@localhost
|
|
CREATE USER 'user8'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
user8@localhost
|
|
ALTER USER user8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'new_auth_string'
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE;
|
|
SELECT user,plugin,authentication_string,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='user8';
|
|
user user8
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
password_expired Y
|
|
password_lifetime 0
|
|
account_locked N
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
SET PASSWORD='auth_string';
|
|
SELECT USER();
|
|
USER()
|
|
user8@localhost
|
|
|
|
CREATE USER u5@localhost REQUIRE SSL ACCOUNT LOCK PASSWORD EXPIRE;
|
|
SELECT user,plugin,authentication_string,ssl_type,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u5';
|
|
user u5
|
|
plugin #
|
|
authentication_string #
|
|
ssl_type ANY
|
|
password_expired Y
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
|
|
SHOW CREATE USER u5@localhost;
|
|
CREATE USER for u5@localhost
|
|
CREATE USER 'u5'@'localhost' IDENTIFIED WITH '<default_authentication_plugin>' REQUIRE SSL PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u5,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u5'@'localhost'. Account is locked.
|
|
ALTER USER u5@localhost REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509
|
|
ACCOUNT LOCK PASSWORD EXPIRE PASSWORD EXPIRE NEVER;
|
|
SELECT user,plugin,authentication_string,ssl_type,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u6';
|
|
user u6
|
|
plugin #
|
|
authentication_string #
|
|
ssl_type X509
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked Y
|
|
|
|
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 NEVER ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u6,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u6'@'localhost'. Account is locked.
|
|
ALTER USER u6@localhost ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u6';
|
|
user u6
|
|
plugin #
|
|
authentication_string #
|
|
ssl_type X509
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
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 NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u6@localhost
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA"
|
|
PASSWORD EXPIRE NEVER PASSWORD EXPIRE NEVER;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u7';
|
|
user u7
|
|
plugin sha256_password
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
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 NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u7@localhost
|
|
ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u7';
|
|
user u7
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
password_expired Y
|
|
password_lifetime 0
|
|
account_locked Y
|
|
|
|
SHOW CREATE USER u7@localhost;
|
|
CREATE USER for u7@localhost
|
|
CREATE USER 'u7'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u7,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u7'@'localhost'. Account is locked.
|
|
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 ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u8';
|
|
user u8
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
account_locked N
|
|
|
|
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 '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u8@localhost
|
|
ALTER 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 ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u8';
|
|
user u8
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
connect(localhost,u8,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u8'@'localhost'. Account is locked.
|
|
CREATE USER u9@localhost
|
|
REQUIRE SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u9';
|
|
user u9
|
|
plugin #
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked Y
|
|
connect(localhost,u9,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u9'@'localhost'. Account is locked.
|
|
ALTER USER u9@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u9';
|
|
user u9
|
|
plugin sha256_password
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher
|
|
x509_issuer
|
|
x509_subject /C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
SHOW CREATE USER u9@localhost;
|
|
CREATE USER for u9@localhost
|
|
CREATE USER 'u9'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u9@localhost
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string#y'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA"
|
|
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"
|
|
ACCOUNT LOCK PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u10';
|
|
user u10
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
account_locked N
|
|
|
|
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
|
|
SELECT USER();
|
|
USER()
|
|
u10@localhost
|
|
ALTER USER u10@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA"
|
|
ACCOUNT UNLOCK PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,password_expired,password_lifetime,account_locked FROM mysql.user WHERE USER='u10';
|
|
user u10
|
|
plugin #
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
|
|
SHOW CREATE USER u10@localhost;
|
|
CREATE USER for u10@localhost
|
|
CREATE USER 'u10'@'localhost' IDENTIFIED WITH 'sha256_password' AS '<non-deterministic-password-hash>' REQUIRE CIPHER 'DHE-RSA-AES256-SHA' PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u10,auth_string#y,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u10'@'localhost'. Account is locked.
|
|
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2 ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u11';
|
|
user u11
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
|
|
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 LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
connect(localhost,u11,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u11'@'localhost'. Account is locked.
|
|
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6 ACCOUNT UNLOCK ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u11';
|
|
user u11
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 6
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
|
|
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 6 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
DROP USER u11@localhost;
|
|
CREATE USER 'u11'@'localhost' IDENTIFIED WITH 'mysql_native_password'
|
|
REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 6 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u11';
|
|
user u11
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 6
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
u11@localhost
|
|
CREATE USER u12@localhost IDENTIFIED BY 'auth_string'
|
|
WITH MAX_QUERIES_PER_HOUR 4
|
|
ACCOUNT LOCK PASSWORD EXPIRE NEVER PASSWORD EXPIRE ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u12';
|
|
user u12
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 4
|
|
password_expired Y
|
|
password_lifetime NULL
|
|
account_locked N
|
|
|
|
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 4 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
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()
|
|
u12@localhost
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u12';
|
|
user u12
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 4
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
ALTER USER u12@localhost ACCOUNT LOCK PASSWORD EXPIRE NEVER
|
|
PASSWORD EXPIRE NEVER ACCOUNT UNLOCK
|
|
ACCOUNT LOCK ACCOUNT LOCK ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,max_questions,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u12';
|
|
user u12
|
|
plugin #
|
|
authentication_string #
|
|
max_questions 4
|
|
password_expired N
|
|
password_lifetime 0
|
|
account_locked N
|
|
|
|
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 4 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u12@localhost
|
|
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
|
|
WITH MAX_CONNECTIONS_PER_HOUR 2 ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,max_connections,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u13';
|
|
user u13
|
|
plugin sha256_password
|
|
authentication_string #
|
|
max_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked Y
|
|
connect(localhost,u13,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u13'@'localhost'. Account is locked.
|
|
ALTER USER u13@localhost PASSWORD EXPIRE INTERVAL 20 DAY ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,max_connections,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u13';
|
|
user u13
|
|
plugin sha256_password
|
|
authentication_string #
|
|
max_connections 2
|
|
password_expired N
|
|
password_lifetime 20
|
|
account_locked N
|
|
|
|
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 INTERVAL 20 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u13@localhost
|
|
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA"
|
|
WITH MAX_USER_CONNECTIONS 2 ACCOUNT LOCK PASSWORD EXPIRE INTERVAL 999 DAY ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_user_connections,password_expired,password_lifetime,account_locked
|
|
FROM mysql.user WHERE USER='u14';
|
|
user u14
|
|
plugin sha256_password
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
ssl_cipher DHE-RSA-AES256-SHA
|
|
x509_issuer
|
|
x509_subject
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime 999
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
u14@localhost
|
|
ALTER USER u14@localhost
|
|
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_USER_CONNECTIONS 2 ACCOUNT LOCK PASSWORD EXPIRE INTERVAL 999 DAY ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_user_connections,password_expired,password_lifetime,account_locked
|
|
FROM mysql.user WHERE USER='u14';
|
|
user u14
|
|
plugin sha256_password
|
|
authentication_string #
|
|
ssl_type SPECIFIED
|
|
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 2
|
|
password_expired N
|
|
password_lifetime 999
|
|
account_locked N
|
|
|
|
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=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client' ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 999 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
SELECT USER();
|
|
USER()
|
|
u14@localhost
|
|
CREATE USER u15@localhost,
|
|
u16@localhost IDENTIFIED BY 'auth_string',
|
|
u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u15';
|
|
user u15
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u16';
|
|
user u16
|
|
plugin #
|
|
authentication_string #
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,account_locked
|
|
FROM mysql.user WHERE USER='u17';
|
|
user u17
|
|
plugin sha256_password
|
|
authentication_string #
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
u15@localhost
|
|
SELECT USER();
|
|
USER()
|
|
u16@localhost
|
|
SELECT USER();
|
|
USER()
|
|
u17@localhost
|
|
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 'mysql_native_password',
|
|
u23@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*318C29553A414C4A571A077BC9E9A9F67D5E5634'
|
|
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 ACCOUNT LOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u18';
|
|
user u18
|
|
plugin #
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u19';
|
|
user u19
|
|
plugin #
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u20';
|
|
user u20
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u21';
|
|
user u21
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
|
|
x509_issuer,x509_subject,max_questions,max_user_connections,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u22';
|
|
user u22
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
|
|
x509_issuer,x509_subject,max_questions,max_user_connections,password_expired,
|
|
password_lifetime,account_locked FROM mysql.user WHERE USER='u23';
|
|
user u23
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
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
|
|
account_locked Y
|
|
connect(localhost,u18,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u18'@'localhost'. Account is locked.
|
|
connect(localhost,u19,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u19'@'localhost'. Account is locked.
|
|
connect(localhost,u20,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u20'@'localhost'. Account is locked.
|
|
connect(localhost,u21,auth_string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u21'@'localhost'. Account is locked.
|
|
connect(localhost,u22,,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u22'@'localhost'. Account is locked.
|
|
connect(localhost,u23,auth_&string,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u23'@'localhost'. Account is locked.
|
|
ALTER 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 'mysql_native_password',
|
|
u23@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*318C29553A414C4A571A077BC9E9A9F67D5E5634'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
ACCOUNT UNLOCK;
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u18';
|
|
user u18
|
|
plugin #
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u19';
|
|
user u19
|
|
plugin #
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u20';
|
|
user u20
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired Y
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u21';
|
|
user u21
|
|
plugin sha256_password
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u22';
|
|
user u22
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired Y
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
|
|
x509_subject,max_questions,max_user_connections,password_expired,password_lifetime,
|
|
account_locked FROM mysql.user WHERE USER='u23';
|
|
user u23
|
|
plugin mysql_native_password
|
|
authentication_string #
|
|
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
|
|
max_questions 2
|
|
max_user_connections 2
|
|
password_expired N
|
|
password_lifetime NULL
|
|
account_locked N
|
|
SELECT USER();
|
|
USER()
|
|
u18@localhost
|
|
SELECT USER();
|
|
USER()
|
|
u19@localhost
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
SELECT USER();
|
|
USER()
|
|
u21@localhost
|
|
SELECT USER();
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
SELECT USER();
|
|
USER()
|
|
u23@localhost
|
|
# Locked_connects variable count
|
|
SHOW STATUS LIKE 'Locked_connects';
|
|
Variable_name Value
|
|
Locked_connects 21
|
|
SHOW GLOBAL STATUS LIKE 'LOCKED_Connects';
|
|
Variable_name Value
|
|
Locked_connects 21
|
|
SHOW SESSION STATUS LIKE 'locked_connects';
|
|
Variable_name Value
|
|
Locked_connects 21
|
|
FLUSH STATUS;
|
|
# Cleanup
|
|
DROP USER user4@localhost,user5@localhost,user6@localhost,user7@localhost,user8@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;
|
|
# Create stored procedure,trigger,function and test with lock/unlock users.
|
|
CREATE USER u1@localhost IDENTIFIED BY 'pass';
|
|
CREATE USER u2@localhost IDENTIFIED BY 'pass';
|
|
GRANT ALL ON *.* TO u1@localhost;
|
|
CREATE TABLE test.t1(counter INT);
|
|
INSERT INTO test.t1 VALUES(0);
|
|
CREATE TABLE test.t2(update_count INT);
|
|
# TRIGGER
|
|
CREATE DEFINER = u1@localhost TRIGGER test.t1_update_count
|
|
BEFORE UPDATE ON test.t1 FOR EACH ROW
|
|
BEGIN
|
|
UPDATE test.t2 SET update_count = update_count + 1;
|
|
END|
|
|
# PROCEDURE
|
|
CREATE DEFINER = u1@localhost PROCEDURE test.p1()
|
|
BEGIN
|
|
UPDATE test.t1 SET counter= counter + 1;
|
|
UPDATE test.t1 SET counter= counter + 1;
|
|
UPDATE test.t1 SET counter= counter + 1;
|
|
SELECT counter FROM test.t1;
|
|
END|
|
|
CALL test.p1();
|
|
counter
|
|
3
|
|
SELECT update_count FROM test.t2;
|
|
update_count
|
|
# FUNCTION
|
|
CREATE DEFINER = u1@localhost FUNCTION test.myfunc() RETURNS CHAR(50)
|
|
BEGIN
|
|
RETURN 'wl6054_test';
|
|
END|
|
|
GRANT EXECUTE ON PROCEDURE test.p1 TO u2@localhost;
|
|
GRANT EXECUTE ON FUNCTION test.myfunc TO u2@localhost;
|
|
ALTER USER u1@localhost ACCOUNT LOCK;
|
|
# Login for u1@localhost should fail.
|
|
connect(localhost,u1,pass,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'u1'@'localhost'. Account is locked.
|
|
# Login as u2 and run stored procedure and trigger as u1.
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
u2@localhost
|
|
CALL test.p1();
|
|
counter
|
|
6
|
|
SELECT update_count FROM test.t2;
|
|
update_count
|
|
SELECT update_count,myfunc() FROM test.t2;
|
|
update_count myfunc()
|
|
# Cleanup
|
|
DROP PROCEDURE IF EXISTS test.p1;
|
|
DROP TRIGGER IF EXISTS t1_update_count;
|
|
DROP FUNCTION IF EXISTS test.myfunc;
|
|
DROP TABLE test.t1,test.t2;
|
|
DROP USER u1@localhost,u2@localhost;
|
|
|
|
End of 5.7 tests!
|
|
|