MySQL is open-source database management software that enables users to store, manage, and retrieve data later. It offers multiple options to grant specific users permissions within the tables and databases.
In this tutorial, you will learn to create a MySQL user and several commands to grant permissions, revoke them and delete existing users.
At first ensure that you have installed MySQL.
When you start using, you are provided with a username and a password. These credentials will help you to get root access or full control of all your databases and tables.
But sometimes you will need to grant the database access to someone else without giving them complete control.
Suppose you hire developers for your database management but don’t want them to delete or modify any sensitive information.
In this case, you will need to provide them with the credentials of a non-root user. With this, you can keep a watch on the activity developers are doing with your data.
Create a New User Account in MySQL
At first, access the command line and enter your MySQL server using the below command:
mysql
This will return the result below which confirms that you are accessing a MySQL server:
mysql>
Now execute the below command to create a new user:
CREATE USER ‘new_user’@‘localhost’ IDENTIFIED BY ‘password’;
Here the ‘new_user’ represents the name of the user and IDENTIFIED BY ‘password’ represents the password for that user. You can replace these values with your own.
Execute the below command to grant all the privileges to the newly created user:
GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';
If you want the changes to take effect immediately, flush these privileges by running the below command:
FLUSH PRIVILEGES;
Now, your new user account will have the same privileges as the root user has.
Grant Privileges Separately for a MySQL User
It is important that you mention the database name and table name and separate them with a . (period) and without spaces. With this, the root user will have more control over particular data.
Also, replace the PERMISSION_TYPE value with the type of access you want to give to the new user account.
Below are the commonly used commands in MySQL:
- CREATE — helps users to create a database or table
- SELECT — allows users to retrieve data
- INSERT — allows users to add new entries in tables
- UPDATE — allows users to modify existing entries in tables
- DELETE — enables users to erase table entries
- DROP — enables users delete entire database tables
Note: If you use ALL PRIVILEGES permission type, it will allow all of the permissions listed above to the new user.
For using any option from the above list, just replace PERMISSION_TYPE with the appropriate keyword. To grant multiple privileges, separate them with a comma. For example, you can assign CREATE and SELECT to your non-root MySQL user account with the below command:
GRANT CREATE, SELECT ON * . * TO 'user_name'@'localhost';
If there is a situation wherein you need to revoke given privileges from a user, use the below command:
REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;
For example, to remove all privileges for your non-root user run the below command:
REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost';
Finally, to completely delete an existing user account run the following command:
DROP USER ‘user_name’@‘localhost’;
Note: You should have root access to run any of these commands. Ensure to execute FLUSH PRIVILEGES; command after making the changes.
Display Account Privileges for a MySQL User
To find out the privileges already granted to a MySQL user, use the SHOW GRANTS command:
SHOW GRANTS FOR 'user_name'@'localhost';
The output will look similar to the below one:
Conclusion
All user data is stored in the form of database and so, is a significant part of every website and web application. A database management system helps in managing and communicating with the database more efficiently. Therefore, you should use the most popular and recommended option — MySQL, because it is reliable as well as user-friendly.
Hope the above commands will help you to create a new MySQL user and grant privileges to its account.
Also Read:
Disable SSH Login for the Root User
