mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
173 lines
6.2 KiB
Text
173 lines
6.2 KiB
Text
create user user1@localhost;
|
|
create user user2@localhost;
|
|
#
|
|
# Only privileged users should be able to lock/unlock.
|
|
#
|
|
alter user user1@localhost account lock;
|
|
alter user user1@localhost account unlock;
|
|
create user user3@localhost account lock;
|
|
drop user user3@localhost;
|
|
connect con1,localhost,user1;
|
|
connection con1;
|
|
alter user user2@localhost account lock;
|
|
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
|
|
disconnect con1;
|
|
connection default;
|
|
#
|
|
# ALTER USER USER1 ACCOUNT LOCK should deny the connection of user1,
|
|
# but it should allow user2 to connect.
|
|
#
|
|
alter user user1@localhost account lock;
|
|
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
|
|
connect con1,localhost,user1;
|
|
ERROR HY000: Access denied, this account is locked
|
|
connect con2,localhost,user2;
|
|
disconnect con2;
|
|
connection default;
|
|
alter user user1@localhost account unlock;
|
|
#
|
|
# Passing an incorrect user should return an error unless
|
|
# IF EXISTS is used
|
|
#
|
|
alter user inexistentUser@localhost account lock;
|
|
ERROR HY000: Operation ALTER USER failed for 'inexistentUser'@'localhost'
|
|
alter user if exists inexistentUser@localhost account lock;
|
|
Warnings:
|
|
Error 1133 Can't find any matching row in the user table
|
|
Note 1396 Operation ALTER USER failed for 'inexistentUser'@'localhost'
|
|
#
|
|
# Passing an existing user to CREATE should not be allowed
|
|
# and it should not change the locking state of the current user
|
|
#
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost`
|
|
create user user1@localhost account lock;
|
|
ERROR HY000: Operation CREATE USER failed for 'user1'@'localhost'
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost`
|
|
#
|
|
# Passing multiple users should lock them all
|
|
#
|
|
alter user user1@localhost, user2@localhost account lock;
|
|
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
|
|
connect con1,localhost,user1;
|
|
ERROR HY000: Access denied, this account is locked
|
|
connect(localhost,user2,,test,MYSQL_PORT,MYSQL_SOCK);
|
|
connect con2,localhost,user2;
|
|
ERROR HY000: Access denied, this account is locked
|
|
alter user user1@localhost, user2@localhost account unlock;
|
|
#
|
|
# The locking state is preserved after acl reload
|
|
#
|
|
alter user user1@localhost account lock;
|
|
flush privileges;
|
|
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
|
|
connect con1,localhost,user1;
|
|
ERROR HY000: Access denied, this account is locked
|
|
alter user user1@localhost account unlock;
|
|
#
|
|
# JSON functions on global_priv reflect the locking state of an account
|
|
#
|
|
alter user user1@localhost account lock;
|
|
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
|
|
host user JSON_VALUE(Priv, '$.account_locked')
|
|
localhost user1 1
|
|
alter user user1@localhost account unlock;
|
|
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
|
|
host user JSON_VALUE(Priv, '$.account_locked')
|
|
localhost user1 0
|
|
#
|
|
# SHOW CREATE USER correctly displays the locking state of an user
|
|
#
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost`
|
|
alter user user1@localhost account lock;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` ACCOUNT LOCK
|
|
alter user user1@localhost account unlock;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost`
|
|
create user newuser@localhost account lock;
|
|
show create user newuser@localhost;
|
|
CREATE USER for newuser@localhost
|
|
CREATE USER `newuser`@`localhost` ACCOUNT LOCK
|
|
drop user newuser@localhost;
|
|
#
|
|
# Users should be able to lock themselves
|
|
#
|
|
grant CREATE USER on *.* to user1@localhost;
|
|
connect con1,localhost,user1;
|
|
connection con1;
|
|
alter user user1@localhost account lock;
|
|
disconnect con1;
|
|
connection default;
|
|
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
|
|
connect con1,localhost,user1;
|
|
ERROR HY000: Access denied, this account is locked
|
|
alter user user1@localhost account unlock;
|
|
#
|
|
# Users should be able to unlock themselves if the connections
|
|
# had been established before the accounts were locked
|
|
#
|
|
grant CREATE USER on *.* to user1@localhost;
|
|
connect con1,localhost,user1;
|
|
alter user user1@localhost account lock;
|
|
connection con1;
|
|
alter user user1@localhost account unlock;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost`
|
|
disconnect con1;
|
|
connection default;
|
|
#
|
|
# COM_CHANGE_USER should return error if the destination
|
|
# account is locked
|
|
#
|
|
alter user user1@localhost account lock;
|
|
ERROR HY000: Access denied, this account is locked
|
|
#
|
|
# MDEV-24098 SHOW CREATE USER invalid for both PASSWORD EXPIRE and
|
|
# and LOCKED
|
|
#
|
|
alter user user1@localhost PASSWORD EXPIRE;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
|
|
drop user user1@localhost;
|
|
#
|
|
# MDEV-24098 CREATE USER/ALTER USER PASSWORD EXPIRE/LOCK in
|
|
# either order.
|
|
#
|
|
create user user1@localhost PASSWORD EXPIRE ACCOUNT LOCK;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
|
|
drop user user1@localhost;
|
|
create user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
|
|
alter user user1@localhost PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
|
|
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
|
|
alter user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
|
|
alter user user1@localhost PASSWORD EXPIRE INTERVAL 60 DAY ACCOUNT UNLOCK;
|
|
select * from mysql.global_priv where user='user1';
|
|
Host User Priv
|
|
localhost user1 {"access":0,"version_id":XXX,"plugin":"mysql_native_password","authentication_string":"","account_locked":false,"password_last_changed":0,"password_lifetime":60}
|
|
show create user user1@localhost;
|
|
CREATE USER for user1@localhost
|
|
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
|
|
ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 60 DAY
|
|
drop user user1@localhost;
|
|
drop user user2@localhost;
|