ALTER USER - MySQL

Overview

ALTER USER modifies user accounts in a MySQL database, including their privileges, password, roles, and other attributes. It allows administrators to manage user access, permissions, and account settings effectively.

Syntax

ALTER USER [IF EXISTS] username [IDENTIFIED BY 'new_password']
[options] ...

Options/Flags

  • IDENTIFIED BY ‘new_password’: Sets a new password for the user.
  • PASSWORD EXPIRE [NEVER | DEFAULT]: Sets the password expiration policy.
    • NEVER: Password never expires (default).
    • DEFAULT: Adheres to server-defined default password expiration policy.
  • RENAME TO new_username: Changes the username.
  • SET DEFAULT ROLE role_name[, role_name] …: Assigns default roles to the user. Roles control permissions.
  • RELOAD: Reloads grant tables to reflect recent changes made using the command.
  • GRANT[/REVOKE/SET] privilege_type (ON object_type [object_name]) TO username: Modifies user permissions. Grants, revokes, or sets specific privileges.

Examples

  • Set a new password: ALTER USER 'alice' IDENTIFIED BY 'secret';
  • Add a default role: ALTER USER 'bob' SET DEFAULT ROLE 'admin';
  • Grant permissions: ALTER USER 'charlie' GRANT SELECT ON database.table TO 'charlie';

Common Issues

  • User not found: IF EXISTS can be used to avoid errors if the user does not exist.
  • Insufficient privileges: Ensure that the current user has sufficient privileges to modify other user accounts.
  • Invalid password: The new password must meet server password policies.

Integration

  • With GRANT: ALTER USER ... SET DEFAULT ROLE 'role_with_grant' can grant specific permissions through roles.
  • With FLUSH PRIVILEGES: FLUSH PRIVILEGES; can be used after ALTER USER to refresh user permissions.
  • Automating with scripts: Combine ALTER USER commands with control flow and other MySQL commands to automate user management tasks.
  • CREATE USER
  • GRANT
  • REVOKE
  • SHOW GRANTS FOR