364 lines
18 KiB
Plaintext
364 lines
18 KiB
Plaintext
CREATE USER unlocked_user@localhost IDENTIFIED BY 'pas';
|
|
SELECT account_locked FROM mysql.user
|
|
WHERE user='unlocked_user' and host = 'localhost';
|
|
account_locked
|
|
N
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user@localhost
|
|
# lock the user in the database
|
|
UPDATE mysql.user SET account_locked='Y'
|
|
WHERE user='unlocked_user' and host = 'localhost';
|
|
# without FLUSH the field has no effect
|
|
# must not throw an error
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user@localhost
|
|
FLUSH PRIVILEGES;
|
|
# existing connections continue as before even after flush
|
|
# must not throw an error
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user@localhost
|
|
# Lowercase 'y' should disable login as well
|
|
UPDATE mysql.user SET account_locked='y'
|
|
WHERE user='unlocked_user' and host = 'localhost';
|
|
FLUSH PRIVILEGES;
|
|
connect(localhost,unlocked_user,pas,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'unlocked_user'@'localhost'. Account is locked.
|
|
# new connections are blocked
|
|
connect(localhost,unlocked_user,pas,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'unlocked_user'@'localhost'. Account is locked.
|
|
# unlock the user using ALTER USER command
|
|
ALTER USER unlocked_user@localhost ACCOUNT UNLOCK;
|
|
SELECT account_locked FROM mysql.user
|
|
WHERE user = 'unlocked_user' and host = 'localhost';
|
|
account_locked
|
|
N
|
|
# connection should work now
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user@localhost
|
|
# lock the user account using ALTER USER command
|
|
ALTER USER unlocked_user@localhost ACCOUNT LOCK;
|
|
SELECT account_locked FROM mysql.user
|
|
WHERE user = 'unlocked_user' and host = 'localhost';
|
|
account_locked
|
|
Y
|
|
# connection should not work
|
|
connect(localhost,unlocked_user,pas,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user 'unlocked_user'@'localhost'. Account is locked.
|
|
# create another user
|
|
CREATE USER unlocked_user2@localhost IDENTIFIED BY 'pas';
|
|
# newly created user should be able to log in
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user2@localhost
|
|
# toogle access permission with one SQL statement
|
|
ALTER USER unlocked_user@localhost,
|
|
unlocked_user2@localhost ACCOUNT UNLOCK;
|
|
SELECT user, account_locked FROM mysql.user
|
|
WHERE (user = 'unlocked_user' and host = 'localhost') or
|
|
(user = 'unlocked_user2' and host = 'localhost') ORDER BY user;
|
|
user account_locked
|
|
unlocked_user N
|
|
unlocked_user2 N
|
|
# unlocked_user2 should be able to log in
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user2@localhost
|
|
# unlocked_user should be able to log in
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
unlocked_user@localhost
|
|
# Basic SHOW CREATE USER
|
|
# Should appear without ACCOUNT UNLOCK
|
|
SHOW CREATE USER unlocked_user@localhost;
|
|
CREATE USER for unlocked_user@localhost
|
|
CREATE USER 'unlocked_user'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
ALTER USER unlocked_user@localhost ACCOUNT LOCK;
|
|
# Should appear with ACCOUNT LOCK
|
|
SHOW CREATE USER unlocked_user@localhost;
|
|
CREATE USER for unlocked_user@localhost
|
|
CREATE USER 'unlocked_user'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# roll back changes
|
|
DROP USER unlocked_user@localhost;
|
|
DROP USER unlocked_user2@localhost;
|
|
# Create anonymous user
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass';
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
@localhost
|
|
DROP USER ''@localhost;
|
|
# Create anonymous user - explicit UNLOCK
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass' ACCOUNT UNLOCK;
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
@localhost
|
|
DROP USER ''@localhost;
|
|
# Create anonymous user - LOCK
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
connect(localhost,,pass,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user '(null)'@'localhost'. Account is locked.
|
|
DROP USER ''@localhost;
|
|
# Disabling anonymous user
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass';
|
|
ALTER USER ''@localhost ACCOUNT LOCK;
|
|
connect(localhost,,pass,test,MASTER_PORT,MASTER_SOCKET);
|
|
ERROR HY000: Access denied for user '(null)'@'localhost'. Account is locked.
|
|
DROP USER ''@localhost;
|
|
# Enabling anonymous user
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
ALTER USER ''@localhost ACCOUNT UNLOCK;
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
@localhost
|
|
DROP USER ''@localhost;
|
|
# Expiring password and disabing anynymous user
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass';
|
|
ALTER USER ''@localhost PASSWORD EXPIRE ACCOUNT LOCK;
|
|
ERROR HY000: The password for anonymous user cannot be expired.
|
|
DROP USER ''@localhost;
|
|
# Login as anonymous user and try to manipulate mysql.user table
|
|
# and users.
|
|
CREATE user ''@localhost IDENTIFIED BY 'pass';
|
|
CREATE USER 'unlocked_user'@localhost IDENTIFIED BY 'pass';
|
|
UPDATE mysql.user SET account_locked='Y'
|
|
WHERE user='unlocked_user' and host = 'localhost';
|
|
ERROR 42000: UPDATE command denied to user ''@'localhost' for table 'user'
|
|
ALTER USER unlocked_user@localhost ACCOUNT LOCK;
|
|
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
|
|
DROP USER unlocked_user@localhost;
|
|
DROP USER ''@localhost;
|
|
# Create stored procedure and users for test
|
|
CREATE USER u1@localhost IDENTIFIED BY 'pass';
|
|
CREATE USER u2@localhost IDENTIFIED BY 'pass';
|
|
GRANT ALL ON *.* TO u1@localhost;
|
|
CREATE TABLE mysql.t1(counter INT)|
|
|
INSERT INTO mysql.t1 VALUES(0)|
|
|
CREATE DEFINER = u1@localhost PROCEDURE mysql.p1()
|
|
BEGIN
|
|
UPDATE mysql.t1 SET counter = counter + 1;
|
|
SELECT counter FROM mysql.t1;
|
|
END|
|
|
CALL mysql.p1();
|
|
counter
|
|
1
|
|
GRANT EXECUTE ON PROCEDURE mysql.p1 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 as u1
|
|
SELECT CURRENT_USER();
|
|
CURRENT_USER()
|
|
u2@localhost
|
|
CALL mysql.p1();
|
|
counter
|
|
2
|
|
# Locked_connects STATUS VARIABLE
|
|
FLUSH STATUS;
|
|
CREATE USER 'u3'@'localhost' IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
SHOW STATUS LIKE 'Locked_connects';
|
|
Variable_name Value
|
|
Locked_connects 0
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
ERROR HY000: Access denied for user 'u3'@'localhost'. Account is locked.
|
|
SHOW STATUS LIKE 'Locked_connects';
|
|
Variable_name Value
|
|
Locked_connects 10
|
|
FLUSH STATUS;
|
|
# Invalid SQL syntax should fail
|
|
CREATE USER u1@localhost REQUIRE NONE ACCOUNT LOCK WITH MAX_QUERIES_PER_HOUR 100;
|
|
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 MAX_QUERIES_PER_HOUR 100' at line 1
|
|
# CREATE USER SQL syntax check
|
|
DROP USER u1@localhost;
|
|
CREATE USER u1@localhost REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# ALTER USER correct SQL syntax 1
|
|
ALTER USER u1@localhost PASSWORD EXPIRE ACCOUNT UNLOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# ALTER USER correct SQL syntax 2
|
|
ALTER USER u1@localhost ACCOUNT LOCK PASSWORD EXPIRE;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# ALTER USER correct SQL syntax 3
|
|
ALTER USER u1@localhost REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 99 ACCOUNT UNLOCK PASSWORD EXPIRE NEVER;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 99 PASSWORD EXPIRE ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# ALTER USER correct SQL syntax 4
|
|
ALTER USER u1@localhost REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 98 PASSWORD EXPIRE INTERVAL 5 DAY ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 98 PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# ALTER USER invalid SQL syntax
|
|
ALTER USER u1@localhost ACCOUNT UNLOCK WITH MAX_QUERIES_PER_HOUR 97 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 'WITH MAX_QUERIES_PER_HOUR 97 PASSWORD EXPIRE' at line 1
|
|
DROP USER u1@localhost;
|
|
# Duplicated ACCOUNT statement 1
|
|
CREATE USER u1@localhost IDENTIFIED BY 'PASS' ACCOUNT LOCK ACCOUNT UNLOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
DROP USER u1@localhost;
|
|
# Duplicated ACCOUNT statement 2
|
|
CREATE USER u1@localhost ACCOUNT UNLOCK ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
DROP USER u1@localhost;
|
|
# Duplicated ACCOUNT statement 3
|
|
CREATE USER u1@localhost ACCOUNT LOCK PASSWORD EXPIRE ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Duplicated ACCOUNT statement 4
|
|
ALTER USER u1@localhost IDENTIFIED BY 'PASS' ACCOUNT LOCK ACCOUNT UNLOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Duplicated ACCOUNT statement 5
|
|
ALTER USER u1@localhost ACCOUNT UNLOCK ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Duplicated ACCOUNT statement 6
|
|
ALTER USER u1@localhost ACCOUNT LOCK PASSWORD EXPIRE ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Duplicated ACCOUNT statement 7
|
|
ALTER USER u1@localhost PASSWORD EXPIRE ACCOUNT LOCK PASSWORD EXPIRE NEVER;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# Duplicated ACCOUNT statement 8
|
|
ALTER USER u1@localhost PASSWORD EXPIRE NEVER ACCOUNT LOCK PASSWORD EXPIRE INTERVAL 5 DAY ACCOUNT LOCK;
|
|
SHOW CREATE USER u1@localhost;
|
|
CREATE USER for u1@localhost
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
# MySQL user table layout from the before ACCOUNT LOCK option
|
|
call mtr.add_suppression("Column count of mysql.* is wrong. "
|
|
"Expected .*, found .*. "
|
|
"The table is probably corrupted");
|
|
CREATE TABLE mysql.temp_user LIKE mysql.user;
|
|
INSERT INTO mysql.temp_user SELECT * FROM mysql.user;
|
|
ALTER TABLE mysql.user DROP COLUMN account_locked;
|
|
ALTER TABLE mysql.user DROP COLUMN create_role_priv;
|
|
ALTER TABLE mysql.user DROP COLUMN drop_role_priv;
|
|
CREATE USER backuser@localhost IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
SELECT COUNT(*) FROM mysql.user WHERE (user = 'backuser' and host = 'localhost');
|
|
COUNT(*)
|
|
0
|
|
CREATE USER backuser@localhost IDENTIFIED BY 'pass' ACCOUNT UNLOCK;
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
SELECT COUNT(*) FROM mysql.user WHERE (user = 'backuser' and host = 'localhost');
|
|
COUNT(*)
|
|
0
|
|
CREATE USER backuser@localhost IDENTIFIED BY 'pass';
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
SELECT COUNT(*) FROM mysql.user WHERE (user = 'backuser' and host = 'localhost');
|
|
COUNT(*)
|
|
0
|
|
SELECT user, account_locked FROM mysql.user WHERE (user = 'backuser' and host = 'localhost');
|
|
ERROR 42S22: Unknown column 'account_locked' in 'field list'
|
|
ALTER USER backuser@localhost ACCOUNT LOCK;
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
ALTER USER backuser@localhost ACCOUNT UNLOCK;
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
DROP USER backuser@localhost;
|
|
ERROR HY000: Column count of mysql.user is wrong. Expected 51, found 48. The table is probably corrupted
|
|
DROP TABLE mysql.user;
|
|
ALTER TABLE mysql.temp_user RENAME mysql.user;
|
|
FLUSH PRIVILEGES;
|
|
# Check whether 'account' can be used as the identifier.
|
|
CREATE DATABASE account;
|
|
CREATE TABLE account.`account` (account text(20));
|
|
INSERT INTO `account`.account VALUES("account");
|
|
SELECT account FROM account.account WHERE account = 'account';
|
|
account
|
|
account
|
|
SET @account = "account_before";
|
|
CREATE PROCEDURE account.account(OUT ac CHAR(20))
|
|
BEGIN
|
|
SELECT account.`account`.account INTO ac FROM account.account;
|
|
END|
|
|
CALL account.account(@account);
|
|
SELECT @account;
|
|
@account
|
|
account
|
|
DROP DATABASE account;
|
|
CREATE USER ACCOUNT@localhost ACCOUNT LOCK;
|
|
DROP USER ACCOUNT@localhost;
|
|
DROP PROCEDURE mysql.p1;
|
|
DROP TABLE mysql.t1;
|
|
DROP USER u1@localhost;
|
|
DROP USER u2@localhost;
|
|
DROP USER u3@localhost;
|
|
#
|
|
# Bug #20814051 CREATE USER BINLOG EVENTS INCLUDE NEW ACCOUNT KEYWORD
|
|
#
|
|
CREATE USER u1 IDENTIFIED BY 'pass';
|
|
SHOW CREATE USER u1;
|
|
CREATE USER for u1@%
|
|
CREATE USER 'u1'@'%' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
ALTER USER u1 ACCOUNT LOCK;
|
|
SHOW CREATE USER u1;
|
|
CREATE USER for u1@%
|
|
CREATE USER 'u1'@'%' IDENTIFIED WITH '<default_authentication>' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
|
DROP USER u1;
|
|
# Restart server.
|
|
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 = '.../log/rewrite_general.log';
|
|
SET GLOBAL log_output = 'FILE,TABLE';
|
|
SET GLOBAL general_log= 'ON';
|
|
CREATE USER u1 IDENTIFIED BY 'pass';
|
|
CREATE USER u2 IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
CREATE USER u3 IDENTIFIED BY 'pass' ACCOUNT UNLOCK;
|
|
ALTER USER u1 IDENTIFIED BY 'pass';
|
|
ALTER USER u1 IDENTIFIED BY 'pass' ACCOUNT LOCK;
|
|
ALTER USER u1 IDENTIFIED BY 'pass' ACCOUNT UNLOCK;
|
|
#
|
|
# BUG #20996273 ALTER USER REWRITE CAUSES DIFFERENCES ON SLAVE
|
|
#
|
|
ALTER USER u2 IDENTIFIED BY 'pass';
|
|
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 BY <secret>
|
|
CREATE USER 'u2'@'%' IDENTIFIED BY <secret> ACCOUNT LOCK
|
|
CREATE USER 'u3'@'%' IDENTIFIED BY <secret> ACCOUNT UNLOCK
|
|
SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %'AND
|
|
command_type NOT LIKE 'Prepare';
|
|
argument
|
|
ALTER USER 'u1'@'%' IDENTIFIED BY <secret>
|
|
ALTER USER 'u1'@'%' IDENTIFIED BY <secret> ACCOUNT LOCK
|
|
ALTER USER 'u1'@'%' IDENTIFIED BY <secret> ACCOUNT UNLOCK
|
|
ALTER USER 'u2'@'%' IDENTIFIED BY <secret>
|
|
------ done ------
|
|
DROP USER u1, u2, u3;
|
|
SET GLOBAL general_log_file= @old_general_log_file;
|
|
SET GLOBAL general_log= @old_general_log;
|
|
SET GLOBAL log_output= @old_log_output;
|