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