May 08, 2023
How to Create MySQL User and Grant Privileges: A Beginner’s Guide
Do you want to understand the basics of MySQL operations? Read on, as this article will show you how to create a user in MySQL. You’ll also learn about several commands to grant privileges, revoke privileges, and delete existing users.
Before we continue to the next section of the tutorial, make sure that you have installed MySQL. If you haven’t, we have great tutorials on how to install it on both Ubuntu and CentOS.
We will use the command line to access our Linux VPS as root. You can either use PuTTY (Windows) or your terminal (macOS, Linux) and log in using your SSH root login information provided by your hosting provider.
Create a MySQL User Account and Grant All Privileges
Just as you start using MySQL, you’ll be given a username and a password. These initial credentials will grant you root access or full control of all your databases and tables.
However, there are times when you’ll need to give the database access to someone else without granting them full control.
For instance, you hire developers to maintain your databases, but you don’t want to provide them with the ability to delete or modify any sensitive information.
In that case, you should give them the credentials of a non-root user. This way, you can keep track of what the developers can and cannot do with your data.
In this part, we will explain how to create a user account in MySQL and grant all privileges to your database. In a practical sense, it’s not wise to give full control to a non-root user. However, it’s still a good entry-point to learn about user privileges.
To create a new user account in MySQL, follow these steps:
- Access command line and enter MySQL server:
- The script will return this result, which verifies that you are accessing a MySQL server.
- Then, execute the following command:
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
- new_user is the name we’ve given to our new user account and the IDENTIFIED BY ‘password’ section sets a passcode for this user. You can replace these values with your own, inside the quotation marks.
- In order to grant all privileges of the database for a newly created user, execute the following command:
GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';
- For changes to take effect immediately flush these privileges by typing in the command:
Once that is done, your new user account has the same access to the database as the root user.
Grant Privileges Separately for a MySQL User
In this part, we will explain how to grant privileges separately for a user account in MySQL.
When specifying the database name and table name, separate them with a . (period) and no spaces. This will give the root user fine-grain control over certain data.
Also, replace the PERMISSION_TYPE value with the kind of access you want to grant to your new user account.
Here are the most used commands in MySQL:
- CREATE — enable users to create a database or table
- SELECT — permit users to retrieve data
- INSERT — let users add new entries in tables
- UPDATE — allow users to modify existing entries in tables
- DELETE — enable users to erase table entries
- DROP — let users delete entire database tables
Using the ALL PRIVILEGES permission type from before will allow all of the permissions listed above.
To use any of these options, simply replace PERMISSION_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, we can assign CREATE and SELECT to our non-root MySQL user account with this command:
GRANT CREATE, SELECT ON * . * TO 'user_name'@'localhost';
Sometimes, you might come across a situation where you need to revoke given privileges from a user. You can do so by entering:
REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;
For example, to withdraw all privileges for our non-root user we should use:
REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost';
Finally, you can entirely delete an existing user account by using the following command:
DROP USER ‘user_name’@‘localhost’;
Remember, you need to have root access to run any of these commands. Be sure to execute FLUSH PRIVILEGES; command after making your changes
Display Account Privileges for a MySQL User
In order to find what privileges have already been granted to a MySQL user, you can use the SHOW GRANTS command:
SHOW GRANTS FOR 'user_name'@'localhost';
The output will look similar to this:
+---------------------------------------------------------------------------+ | Grants for user_name@localhost | +---------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user_name'@'localhost' | | GRANT ALL PRIVILEGES ON 'database_name'.* TO 'user_name'@'localhost'. | | REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost'; |
+---------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
What is a MySQL Database?
In order to understand MySQL, you’ll need to know what a database is. It’s a virtual storage where you can save necessary data for building websites and web applications.
MySQL database can store user account details, such as usernames, passwords, email addresses, and any type of information that you want to keep for later use.
However, the stored data should be in some kind of order. That’s why we have database management systems. These tools are used to communicate with the database and allow developers to structure, store, dump, and modify the data.
MySQL is one of the most popular names when it comes to database management systems. This is due to its ease of use and community support. Platforms such as Facebook and Twitter even use MySQL to some extent.
A database is an essential part of every website and web application because it stores all user data. To manage and communicate with it more efficiently, you will need a database management system. That’s why we recommend using the most popular and recommended option — MySQL, due to its reliability and ease of use.
In this tutorial, you have learned about some basic commands to perform several tasks in MySQL, including:
- How to create MySQL user and grant it all privileges
- How to give specific privileges to a user, revoke them, and remove a MySQL user entirely
- How to view what privileges a MySQL user already has
Good luck, and feel free to leave a comment below if you have any questions.
August 15 2020
I am getting below error. how can fix Failed to drop or create the database. Do it yourself before installing. ERROR 1045 (28000): Access denied for user 'new_user;'@'localhost' (using password: YES)
November 06 2020
Hey there Sachin! Thanks for messaging us! :) The error you are getting happens due to using the wrong password or not having the correct permissions. Make sure the password and permissions are set correctly :)
January 21 2023
I have a Shared Web Hosting account. I log in to PuTTY as instructed. Following Step 1: -bash-4.2$ mysql ERROR 1045 (28000): Access denied for user 'u1393xxxxx'@'localhost' (using password: NO) I have also tried "mysql -u root" and "mysql -u -root -p" with various passwords with no success. When I log in with an existing db user credentials, the user does not have permission to grant privileges. Other help articles speak of a ".db_password" file which does not exist in my folders. Please help.
January 26 2023
Hi there. Based on the error you're getting, it seems that you're accessing PuTTY with wrong password, hence I would suggest either resetting your SSH password, or check this article to double-check if PuTTY was configured correctly. As for all privileges, I'd like to note that it's not supported on Shared and Cloud Hosting plans, you can grant super privileges on VPS hosting. If you want to update certain privileges for your database for your current hosting plan, you can refer to this article.