The ALTER USER command is used to modify a user's account attributes, including roles, passwords, and password policies.
ALTER USER [IF EXISTS] user_identity [IDENTIFIED BY 'password']
[DEFAULT ROLE 'role_name']
1. PASSWORD_HISTORY [n|DEFAULT]
2. PASSWORD_EXPIRE [DEFAULT|NEVER|INTERVAL n DAY/HOUR/SECOND]
3. FAILED_LOGIN_ATTEMPTS n
4. PASSWORD_LOCK_TIME [n DAY/HOUR/SECOND|UNBOUNDED]
password_policy, please refer to
ACCOUNT_UNLOCK is used to unlock a locked user account.
You can only modify one of the following account attributes by one ALTER USER command at a time:
- Change password
- Change the role
- Unlock a user account
Change the user's password
ALTER USER jack@‘%’ IDENTIFIED BY "12345";
Change the role of the user
ALTER USER jack@'192.168.%' DEFAULT ROLE "role2";
Modify the user's password policy
ALTER USER jack@'%' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 DAY;
Unlock a user account
ALTER USER jack@'%' ACCOUNT_UNLOCK
Change the role
If a user was assigned Role A and you need to change that, firstly, the system will revoke all privileges corresponding to Role A from the user, and replace them with the privileges corresponding to the new role.
Note that if the user has been individually granted a certain privilege before, which is included in Role A, that privilege will also be revoked in the role change.
Supposing that Role A has the following privilege:
select_priv on db1.*, you assign Role A to User 1.
Then you separately grant the following privilege to User 1:
GRANT select_priv, load_priv on db1.* to user1
Supposing that Role B has the following privilege:
alter_priv on db1.tbl1, you change the role of User 1 to Role B.
Then at the end, User 1 will have the following privileges:
alter_priv on db1.tbl1and
load_priv on db1.*
Modify the password policy
PASSWORD_EXPIRE, you can reset the timing of password expiration.
PASSWORD_LOCK_TIME, you can unlock the user.