Create User Accounts

Updated on 28 Dec 2018

Creating a User Account on MySQL is a 2 step process.

  • Create the account
  • Assign privileges

View the user accounts

You can view the user accounts with the following SQL.

select user, host, authentication_string from user;

Create the Account

The syntax for creating a user account on MySQL is as follows:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Assign Privileges

Once an account has been created we can assign the privileges.

GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost'

A common variation includes specifying the actual privileges, i.e.

GRANT select, update, delete, insert ON database.* TO 'username'@'localhost'

A list of privileges available that you can use: mysql privileges

Delete a User Account

You can remove a user account from MySQL with the drop command.

DROP USER 'user1'@'localhost';

Alter password for existing Account

You can alter a password for an existing account with the alter keyword.

ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new-password';

Reset root password - MySql 5.7

You can reset the root password with the following steps. N.B. the commands below assume that the mysql server is still running. I.e. you don’t turn it off to reset the password!

sudo mysqld_safe --skip-grant-tables&
sudo mysql --user=root mysql

mysql> update user 
       set authentication_string=PASSWORD("new-password"),
	       plugin="mysql_native_password"    
       where user='root';

       flush privileges;
       quit

sudo service mysql restart

Reset root password - MySql 8

Similar steps as before, but we are not updating the tables directly - but rather using the alter user command.

sudo mysqld_safe --skip-grant-tables&
sudo mysql --user=root mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

       flush privileges;
       quit

sudo service mysql restart