How to create User in MySQL with Permissions.

Introduction.

This article will cover “How to create a User in MySQL, manage the permissions, and privileges”. Creating users in the MySQL database is very much crucial for security.

We have control over which user has access to the database. So there is a developer who wants read access and another developer who wants write access.

A DBA wants the User management, Backup, Restore, and replication access. We can control with the help of user creation.

We are going to discuss the following topics in this article,

1. How to create a new user in MySQL.
2. How to grant permissions in MySQL.
3. Revoke privileges for User accounts in MySQL.
4. Drop the user account in MySQL.

How to create a new user in MySQL.

So to create the new user you need to log in via MySQL command-line shell.

$ mysql -h localhost -uUsername -pPassword

Now we are in the MySQL shell> use the below command to create the user.

Syntax:

> CREATE USER 'username' IDENTIFIED BY 'PASSWORD';

Example:

> CREATE USER 'tastethelinux' IDENTIFIED BY 'Tastethelinux1234';

So let’s suppose you want to create a user for localhost. Then executed queries will be ‘username’@’localhost’.

> CREATE USER 'tastethelinux'@localhost IDENTIFIED BY 'PASSWORD';

If you want to create a user for a remote host then execute the queries with ‘username’@’IP’.

> CREATE USER 'tastethelinux'@'remote_IP' IDENTIFIED BY 'PASSWORD';

Let’s suppose you have to access the database from any machine. Then executes the queries with ‘username’@’%’.

> CREATE USER 'tastethelinux'@'%' IDENTIFIED BY 'PASSWORD';

How to Grant Permission in MySQL.

What type of access and privileges the user has, we have to grant the same to the user. Whether we have to give the read access, full access or the write access.

There are several types of permissions that you required for users, below is the list.

1. All privileges: It will give full access to the database.
2. Select: It will give the read only access to the database or a table.
3. Insert: It will give them access to insert the data into the table.
4. Update: It will allow you to change the data in the table.
5. Delete: It will allow you to delete the data into the table.
6. Create: It will give them access to create a new table or database.
7. Drop: It will give the access to remove the table or the database.

Other than this there are several types of permission which can be used while creating a user.

Syntax:

> GRANT permission_type ON database_name.table_name TO 'username'@'localhost';

To GRANT ALL Privileges to all database.

> GRANT ALL PRIVILEGES ON *.* TO 'Username'@'localhost';

*.* means access to all the databases and tables in the MySQL server.

To GRANT ALL Privileges to the specific database with all tables. We have used tastethelinux as a database.

> GRANT ALL PRIVILEGES ON tastethelinux.* TO 'Username'@'localhost';

Give access to insert the data on all the databases and tables.

> GRANT INSERT ON *.* to 'Username'@'localhost';

Give Access to READ-ONLY to all the databases and tables.

> GRANT SELECT ON *.* to 'Username'@'localhost';

You can use all the permissions by using the above syntax. You can use “SHOW GRANTS” to check the permission of the user.

> SHOW GRANTS for username;

How to revoke privileges for a User accounts in MySQL.

Let’s suppose we have to revoke the given permission for a specific user.

Syntax: 

> REVOKE permission_type ON database_name.table_name TO 'username'@'localhost';

Example:

> REVOKE UPDATE ON tastethelinux.courses TO 'Username'@'localhost'; 

This will remove the update permission for the user. On the table courses which is inside the tastethelinux database.

How to DROP user account in MySQL.

We drop or remove the User when the user is not part of an organisation or is a temporary user.

Syntax: 

> DROP USER 'username'@'localhost';

Example:

> DROP USER 'tastethelinux'@'localhost'; 

Drop the user will remove the user from the MySQL account. You should always take care while dropping any user.

Conclusion

So after reading this article you can create a new user, modify the user, revoke, and drop the user. There are several types of permission which can be used by users to limit access.

This is a very important aspect of MySQL security. This will limit the access for every user and will not depend on a single resource to do everything.

DBA Team, Backup Team, and Developer Team have their set of permissions. If you want to Download and Install MySQL then follow the article.