Although there are many guides on MySQL online, they all seem to be very scattered. The commands are sometimes confusing and other times, they don’t work. In this tutorial, we’re going to show you how to create a new user in MySQL and grant permissions (access).
What you will want to do is to first log into MySQL:
mysql -u root -p
Then, you will want to put in your password.
After putting in your password and logging into MySQL successfully in SSH, you will want to create a new user using this command:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newuserpassword';
Replace “newuser” with the username you want the user to have. Replace “newuserpassword” with the user’s password. After changing those values, press “Enter” and run the command.
Now, we’re going to grant permissions to the user you just created. This means that the user will have specific permissions to alter or modify in a table.
GRANT ALL PRIVILEGES ON databasename.* TO 'newuser'@'localhost';
You will want to change “databasename” to your database’s name. You will also want to change the “newuser” to the user you just created.
So let’s break down that command. GRANT is pretty obvious, we’re granting permissions to a database. ALL PRIVILEGES can be changed to any of the following:
- ALL PRIVILEGES allows a MySQL user full access to a designated database (or if no database is selected, global access across the system)
- CREATE allows them to create new tables or databases
- DROP allows them to them to delete tables or databases
- DELETEÂ allows them to delete rows from tables
- INSERTÂ allows them to insert rows into tables
- SELECTÂ allows them to use the SELECT command to read through databases
- UPDATEÂ allow them to update table rows
- GRANT OPTIONÂ allows them to grant or remove other users’ privileges
Don’t forget the last step which is to update/refresh/reload the privileges:
FLUSH PRIVILEGES;
That pretty much sums it up. If you want to know more or have any questions about this, please leave a comment. 🙂
