MySQL is one of the most widely used relational database management systems all over the IT industry. Mostly Linux-based operating systems are preferred for deployments of databases huge in size, and thus MySQL is very commonly deployed over Linux and used in the backend of an application.
Today, we will learn how to add a new user in MySQL and how to add privileges for the user.
Pre-Requisites
You should already have MySQL Client installed on your system. The client should be configured to access either your local MySQL server (in which case the MySQL server must be installed beforehand) or a remote MySQL Server.
Adding a New MySQL User
The CREATE USER statement is used to add a new user in MySQL and the basic syntax for the same is as follows:
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Here, if the MySQL server is local, ‘host‘ will be replaced with ‘localhost‘ as shown in the following example:
mysql> CREATE USER 'abhi'@'localhost' IDENTIFIED BY 'abhi123';
Now exit from MySQL, and verify if you can log in with the new user.
$ mysql -u abhi -p
Note that this is the simple way of creating a user with a normal password in MySQL. There are many other configuration options that can be used to apply a different authentication method (For example using a SHA2 password), restricting the number of queries per hour, etc.
Granting Privileges to MySQL User
When a new user is created, by default the permissions for creating a database schema and objects inside it are given. Permissions to other databases and database objects are usually restricted by the author of that database.
To grant privileges to the created user, the basic syntax is:
mysql> GRANT privilege ON database_name.table_name TO 'username'@'host';
For example, to grant SELECT and INSERT privileges for the user on a database table called ‘FOOTBALL’ in database ‘SPORTS’, we execute:
mysql> GRANT SELECT, INSERT ON SPORTS.FOOTBALL TO ‘abhi’@‘localhost’;
To grant privileges on all tables of the database SPORTS:
mysql> GRANT SELECT, INSERT ON SPORTS.* TO ‘abhi’@‘localhost’;
You can similarly grant required privileges on any other database object or the complete database (As long as you have permission to run GRANT on the respective object or database).
If you want to not only grant a privilege to a user but also allow the user to grant the same privilege to another, add the clause ‘WITH GRANT OPTION’ at the end of the GRANT statement.
mysql> GRANT SELECT, INSERT ON SPORTS TO ‘abhi'@'localhost' WITH GRANT OPTION;
Now user ‘abhi’ will be able to grant SELECT and INSERT on the database SPORTS to other users as well.
Finally, to grant all privileges on all database objects, run:
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘abhi'@'localhost';
[ You might also like: How to Get List of MySQL User Accounts in Linux ]
Conclusion
In this article, we have seen how to create a new MySQL user and grant permissions to the user. Make sure you check out the manual page of MySQL (with command ‘man mysql‘) to learn about adding users in more depth.
$ man mysql
Thanks for reading and let me know your thoughts below!
