How to Create New User and Grant Permissions in MySQL

MySQL is a relational database management system, used for storing data in form of tables and records. You can insert, modify or retrieve data using SQL statements or programming languages. It allows us to create new users and grant permissions on tables of the database. As a good practice always use a separate user for all databases. This will ensure that the application can’t access other application’s databases.

The purpose of this tutorial is to create a new user in the MySQL server and grant permissions on databases. This tutorial includes instructions to create users, grant permission on all tables of specific databases or all tables of all databases.

Prerequisites

Access to MySQL server with superuser or root account access. This will allow you to create new users and grant permissions in MySQL.

Firstly, connect to MySQL server to perform further instructions.

1. Create New User in MySQL

Login to the MySQL server with root user with shell access and create a new user named “rahul”. The below statement will allow accessing MySQL server to user rahul from the localhost only.

mysql> CREATE USER 'rahul'@'localhost' IDENTIFIED BY 'password';

Now assign the privileges to the specific database. The below command will allow all privileges on database “mydb” to user rahul.

mysql> GRANT ALL ON mydb.* TO 'rahul'@'localhost';

To grant all privileges on all databases, use below query:

mysql> GRANT ALL ON *.* TO 'rahul'@'localhost';

After creating or making any changes, make sure to reload privileges with the below SQL query.

mysql> FLUSH PRIVILEGES; 

• How to Reset MySQL root User Password

2. Create MySQL User Remote Accessible

To allow any user to connect MySQL server from the remote system. You need to specify the hostname or IP address of the remote system. You can also use % to allow any host

mysql> CREATE USER 'rahul'@'123.45.67.89' IDENTIFIED BY 'password';
mysql> CREATE USER 'rahul'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES; 

3. Grant Specific User Permissions in MySQL

Please find below list of frequently used privileges in MySQL user. Visit here to get full list of privileges for MySQL user.

• ALL [PRIVILEGES] – Grant all privileges to user.
• CREATE – Grant user to create new databases and tables.
• DROP – Grant user to delete (drop) databases and tables.
• DELETE – Grant user to delete rows from tables.
• ALTER – Grant user to modify table structure.
• INSERT – Grant user to insert (add) rows into tables.
• SELECT – Grant user to run select command to read data from tables.
• UPDATE – Grant user to update data in tables.
• EXECUTE – Grant user to execute stored routines.
• FILE – Grant user to access file on server host.
• GRANT OPTION – Grant user to grant or remove other users’ privileges.

Here, you can specify privileges separated by a comma in place of ALL. For example to allow CREATE, DELETE, INSERT, UPDATE access to ‘rahul’@’localhost’ on database mydb.

mysql> GRANT CREATE,DELETE,INSERT,UPDATE ON mydb.* TO 'rahul'@'localhost';
mysql> FLUSH PRIVILEGES; 

4. Revoke User Permissions in MySQL

Use the REVOKE statement to remove any specific privilege from the user. For example to remove DELETE privilege from user ‘rahul’@’localhost’ on mydb database.

mysql> REVOKE DELETE ON mydb.* TO 'rahul'@'localhost';
mysql> FLUSH PRIVILEGES; 

5. Drop User in MySQL

Use MySQL DROP statement to delete an existing user from server. For example to delete user ‘rahul’@’localhost’, execute the following query:

mysql> DROP USER 'rahul'@'localhost';
mysql> FLUSH PRIVILEGES; 

Conclusion

In this tutorial, you have learned to create a new user and grant permissions in the MySQL server. Also provided the instructions to change or revoke permissions of any MySQL user.