How to Create or Delete/Remove Users in MySQL Server

MySQL is an open source relational database management system. As a database administrator, you will have to create and delete users in mysql database server. In this tutorial, you will learn how to Create or Delete/Remove Users Account in MySQL Server.

Prerequisite

  • MySql Database Server
  • Root User Account for Administration

How to Create a New User

Let’s start by crating a new user with the following command:

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

Make sure you provide a strong password for sake of data security

Grant User Permissions

After creating a user, that user has no permission to do anything with the database. In fact, if testuser tries to login, it will not be able to connect the MySQL shell.

Below is a list of other common permissions that can be granted to a new user.

  • ALL PRIVILEGES – This would allow a MySQL user full access to a database (if none database is selected, global access across the databases)
  • CREATE – Allows user to create new tables or databases
  • SELECT – Allows user to use the SELECT command to read through databases
  • DELETE – Allows user to delete rows from tables
  • DROP – Allows user to delete tables or databases
  • INSERT – Allows user to insert rows into tables
  • UPDATE – Allow user to update table rows
  • GRANT OPTION – allows user to grant or remove other users privileges
GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'localhost';

Here we are granting testuser full root access to everything in our database. it is impractical to provide all permissions to normal user and you could put your database’s security at high risk.

To provide a specific permission, you can use this syntax:

GRANT <type-of-permission> ON <database-name>.<table-name> TO ‘username’@'localhost’;

Always make sure to reload all the privileges so your changes in database will be in effect.

FLUSH PRIVILEGES;

Each time you update or change a permission make sure to execute the Flush Privileges command in mysql shell.

Revoke User Permissions

If you want to revoke a permission, the syntax is almost same. Please make a note while revoking permissions, the syntax requires FROM  instead of TO as we used while granting permissions.

REVOKE <type-of-permission> ON <database-name>.<table-name> FROM ‘username’@‘localhost’;

user’s current permissions can be checked by running the following command:

SHOW GRANTS FOR 'username'@'localhost';

How to Delete an Existing User

Deleting an existing user is as easy as creating a new user. Before that you can list the existing user in database with the following command:

SELECT User, Host FROM mysql.user;

An existing user can be removed by using DROP command just like dropping a database;

DROP USER ‘username’@‘localhost’;

Using below command you can delete a database:

DROP DATABASE <database-name>;

Conclusion

After finishing this tutorial, you should have an idea of how to Create or Delete/Remove Users Account in MySQL Server.

Read Also : Top 20 Linux Interview Questions for Freshers

Share on:

Ravindra Kumar

Hi, This is Ravindra. I am founder of TheCodeCloud. I am AWS Certified Solutions Architect Associate & Certified in Oracle Cloud as well. I am DevOps and Data Science Enthusiast.

Recommended Reading..

1 thought on “How to Create or Delete/Remove Users in MySQL Server”

Leave a Comment