Do you want to understand the basics of trading? Keep reading as this article will show you how to create a user in MySQL. You will also learn about various commands to grant privileges, revoke privileges, and remove existing users.
Before continuing to the next section of the tutorial, make sure you have installed MySQL. If you haven’t, we have great tutorials on how to install it on and .
NOTE: We will use the command line to access our Linux VPS as root. You can use (Windows) or your terminal (macOS, Linux) and log in with the root SSH login information provided by your hosting provider.
Create a MySQL user account and grant all privileges
Right when you start using MySQL, you will receive a username and password. These initial credentials will grant you access root o Full control of all your databases and tables.
However, there are times when you will need to grant someone else access to the database without giving them full control.
For example, if you have to hire developers to maintain your databases, but you don’t want to give them the ability to delete or modify any sensitive data.
In that case, you must give them the credentials of a non-root user. This way you can keep track of what developers can and can’t do with your data.
In this part, we will explain how to create a user account in MySQL and grant all the privileges of your database. In a practical sense, it is not advisable to give full control to a non-root user. However, it is still a good entry point to learn about the user’s privileges.
To create a new user account in MySQL, follow these steps:
- Access the command line and enter the MySQL server: mysql
- The script will return this result, which confirms that you are accessing a MySQL server. mysql>
- Then run the following command: CREATE USER ‘new_user’@’localhost’ IDENTIFIED BY ‘password’;
- new user is the name we have given to our new user account and the section IDENTIFIED BY “password” sets an access code for this user. You can replace these values with your own, inside the quotes.
- To grant all database privileges for a newly created user, run the following command: GRANT ALL PRIVILEGES ON * . * TO ‘new_user’@’localhost’;
- For the changes to take effect immediately, remove these privileges by typing the command: FLUSH PRIVILEGES;
Once this is done, your new user account will have the same access to the database as the root user.
Grant separate privileges for a MySQL user
Remember to specify the database name and the table name and separate them with a . (dot) and no spaces. This will give the root user fine-grained control over certain data.
These are the most used commands in MySQL:
- CREATE: allows users to create a database or a table
- SELECT: allows users to recover data
- INSERT: allows users to add new entries in tables
- UPDATE: allows users to modify existing entries in tables
- DELETE: allows users to delete table entries
- DROP– Allows users to drop entire database tables
NOTE: The use of permission type ALL PRIVILEGES (which we used in the example above) will allow all the permissions listed above.
To use any of these options, simply replace PERMIT_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, you can assign CREATE Y SELECT to your non-root MySQL user account with this command:
GRANT CREATE, SELECT ON * . * TO ‘user_name’@’localhost’;
Sometimes you may come across a situation where you need to revoke the privileges granted to a user. You can do it by entering:
REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@’localhost’;
For example, to remove all privileges for a non-root user, you would use:
REVOKE ALL PRIVILEGES ON * . * FROM ‘user_name’@’localhost’;
Finally, you can completely remove an existing user account using the following command:
DROP USER ‘user_name’@’localhost’;
NOTE: Remember, you must have root access to run any of these commands. Make sure to run the command FLUSH PRIVILEGES; after making your changes.
Show the account privileges of a MySQL user
To find what privileges have already been granted to a MySQL user, you can use the command SHOW GRANTS:
SHOW GRANTS FOR ‘user_name’@’localhost’;
The result will be 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?
To understand MySQL, you will need to know what a database is. It is a virtual storage where you can save the data needed to create websites and web applications.
The MySQL database can store user account details such as usernames, passwords, email addresses, and any other information you want to keep for later use.
However, the stored data must 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 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 like and even use MySQL to some extent.
conclusion
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’ll need a database management system. That is 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 some basic commands to perform various tasks in MySQL, including:
- How to create a MySQL user and give it all privileges.
- Grant specific privileges to a user, revoke them, and remove a user from MySQL entirely.
- How to see what privileges a MySQL user already has.
Good luck, and feel free to leave a comment below if you have any questions.
Deyi is a digital marketing enthusiast, with a background in web design, content creation, copywriting, and SEO. She is part of ‘s SEO & Localization team. In her free time, she likes to develop projects, read a book or watch a good movie.
Facebook Comments
Disqus