Setting user account passwords in MySQL

It is important to have passwords for all MySQL user accounts for securing the database. If anyone knows the username of an account with no password then he can successfully connect to the database server.

To assign or change a password for an existing account

mysql> SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('mypass');

NOTE: Only users such as root that have update access to the mysql database can change the password for other users.

If you are not connected as an anonymous user, you can change your own password

mysql> SET PASSWORD = PASSWORD('mypass');

Using “GRANT USAGE” statement at the global level (ON *.*) to assign a password to an account without affecting the account’s current privileges

mysql> GRANT USAGE ON *.* TO 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

Using mysqladmin client

# mysqladmin -u user_name -h host_name password "newpassword";

MySQL stores passwords in the user table in the mysql database. So we can directly insert or update passwords as below:

Adding a new user account with password

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','newuser',PASSWORD('newpassword'));
mysql> FLUSH PRIVILEGES;

Changing password of an existing account

mysql> UPDATE mysql.user SET Password = PASSWORD('newpassword') WHERE Host = 'localhost' AND User = 'existingser';
mysql> FLUSH PRIVILEGES;

Leave a Comment

Back to top