MySQL Alter User

  • The ALTER USER used to modify MySQL accounts like password change, lock or unlock account(user). The ALTER USER provides control over account password expiration.
  • To use ALTER USER, we must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.
  • When the read_only system variable is enabled, ALTER USER additionally requires the SUPER privilege.
  • Users information stored in mysq.user table.To alter user, we must be in mysql database.

MySQL Alter User Examples:

1. Change user password:

mysql> ALTER USER admin1@localhost -> IDENTIFIED BY 'new_password'; Query OK, 0 rows affected (0.07 sec) mysql> flush privileges; Query OK, 0 rows affected (0.13 sec)

2. Lock or unlock an account:

mysql> ALTER USER admin1@localhost ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER admin1@localhost ACCOUNT UNLOCK; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)