Create Mysql User and Grant Privileges

In this tutorial, we are going to learn how we can create MySQL users and grant privileges to that users.

For MySQL installation please follow the tutorial, Install Mysql Specific Version on Ubuntu.

Create MySQL User:

Before creating a new user we need to first log in with the root user, for this, use the following command.

mysql -u username -puserpassword

Here, username is the root user's username and userpassword is the root user's password, use your root user's username and password.

Now, let's create the user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace the username with your desired new user name and password as desired strong password. Here we are using localhost as we are considering the MySQL setup from the same server, if it is for the remote server then use IP address of host/server as below.

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

For this created user, they can access the database from that server not remotely. If we want to create user that can connect from any server or machine as below.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant Privileges:

The general syntax for grant privileges is

GRANT <privileges_type> ON <database_table> TO 'username'@'localhost';

Let's look into some examples

Grant all privileges to user for all database

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

Here, *.* will grant for all databases, and "ALL PRIVILEGES" will grant all the privileges

Grant all privileges to the user for a particular database

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

database_name.* will grant all privileges for all the tables of that database with the database name "database_name"

Grant all privileges to user for a particular database table

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';

We can do database query-specific privileges like Insert, Delete, Create, Drop, Select, Update, etc. For example

GRANT INSERT ON *.* TO 'username'@'localhost';

Here, the user can insert rows into tables for all the databases but can't do other operations like drop, create, update, etc.

Flush Privileges:

After we change the user privileges we need to flush it to reflect the changes. We can do so using following command

FLUSH PRIVILEGES;

Revoke Privileges:

We can revoke the grant privileges for the user as below

REVOKE <privileges_type> ON <database_table> FROM 'username'@'localhost';

Remove User:

We can remove the MySQL user as well by using the following command

DROP USER 'username'@'localhost';