diff options
Diffstat (limited to 'mysql-test/main/lock_user.result')
| -rw-r--r-- | mysql-test/main/lock_user.result | 173 |
1 files changed, 173 insertions, 0 deletions
diff --git a/mysql-test/main/lock_user.result b/mysql-test/main/lock_user.result new file mode 100644 index 00000000..24fff210 --- /dev/null +++ b/mysql-test/main/lock_user.result @@ -0,0 +1,173 @@ +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; |