How to Create User and Grant Permission in MySQL

Written by Gaurav | May 5, 2018

How to Create MySQL User and Grant Permission. 

For the good security implementation, make sure to create separate user account rather than root to access database for each application. This will ensure that application can’t access other application’s database. You need MySQL administrator (root) privileges To create user accounts and assign privileges to the database. For your information MySQL root account is different than system root account, there are no relations between them.

1. Create New User in MySQL

Login to the MySQL server with root user with shell access and create a new user named “gaurav”. Below command will allow accessing MySQL server to user gaurav from localhost system only.

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

Now assign the privileges to the specific database. Below command will allow all privileges on database “mydb” to user gaurav.

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

After creating user and assigning proper privileges, make sure to reload privileges.

mysql> FLUSH PRIVILEGES;

2. Create MySQL User Remote Accessible

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

mysql> CREATE USER 'gaurav'@'public Ip' IDENTIFIED BY 'password';

mysql> CREATE USER 'gaurav'@'%' 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 ‘gaurav’@’localhost’ on database mydb.

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

mysql> FLUSH PRIVILEGES;

4. Revoke User Permissions in MySQL

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

mysql> REVOKE DELETE ON mydb.* TO 'gaurav'@'localhost';

mysql> FLUSH PRIVILEGES;

5. Drop User in MySQL

You can simply drop any user from MySQL using DROP command. For example to delete user ‘gaurav’@’localhost’, use following command.

mysql> DROP USER 'gaurav'@'localhost';

mysql> FLUSH PRIVILEGES;