How to Remove User in MariaDB

In this MariaDB Tutorial, Let us see, how to remove user in MariaDB. Here we will see a few examples of MariaDB Remove User and will cover the following topics.

  • MariaDB Remove User
  • MariaDB Remove User Password
  • MariaDB Remove User Grants
  • MariaDB Remove User From Database
  • MariaDB Remove Anonymous Users
  • MariaDB Remove All Users
  • MariaDB Remove Root User
  • MariaDB Remove User ERROR 1396

MariaDB Remove User

Here, you will learn how to remove a user in MariaDB with syntax and examples.

For this, you need to use the DROP USER statement which is used to remove a user.

The syntax for the DROP USER statement is as follows.

DROP USER user_name;

Here, the user_name is the name of the user you want to remove from MariaDB.

The steps to remove a user from MariaDB are as follows.

Step-1: Firstly, you need to have a look at all the users by typing the following command.

SELECT User FROM mysql.user;

Now, select any user from the list which you want to remove from MariaDB.

Step-2: Now, you need to type the following command to remove the user you want.

DROP USER 'James'@'localhost';
MariaDB Remove User
MariaDB Remove User

Step-3: The selected user have been removed but to ensure the proper completion of the task, you can type the following command to list all the users.

SELECT User FROM mysql.user;
How to Remove User in MariaDB
MariaDB User Removed

The user i.e. James is not present in the list of all users and thus, this ensures that the process has been completed successfully completed.

Note – You can also remove more than one user by using the following command.

DROP USER 'Natasha'@'localhost', 'Tony'@'localhost';

And make sure you flush the privilege using the following command.

FLUSH PRIVILEGES;
MariaDB Remove Multiple User
MariaDB Remove Multiple User

This is how you can remove Users from MariaDB.

Read How to create a user in MariaDB

MariaDB Remove User Password

Let us see how to remove User Passwords in MariaDB.

You can change the password instead of removing it by using ALTER statement due to security reasons.

You can change the password by typing the following command.

ALTER USER 'Ross'@'localhost' IDENTIFIED BY 'ross456';

Your password will be changed.

And make sure you flush the privilege using the following command.

FLUSH PRIVILEGES;
MariaDB Remove User Password
MariaDB Remove User Password

This is how to remove User Password from MariaDB.

Read How to Create Database in MariaDB

MariaDB Remove User Grants

Here, you will learn how to remove user grants by the use of the REVOKE statement. It enables system administrators to revoke privileges (or roles) from MariaDB.

The syntax for removing user grants by using the REVOKE statement is as follows.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user 'Username'@'localhost';

Here, the Username is the name of the user whose privileges you want to revoke.

Note – You can use ‘%‘ in place of ‘localhost’ in the case of a user with remote access.

For example, you can remove any user grants by using the following command.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'Sophia'@'localhost';
MariaDB Remove User Grant
MariaDB Remove User Grant

Here, Sophia is the name of the user.

Read How to Grant User Access to a MariaDB Database

MariaDB Remove User From Database

In this, you will learn how to remove a user from a database in MariaDB with syntax and examples.

For this, you need to use the DROP USER statement which is used to remove a user from a database.

The syntax for the DROP USER statement is as follows.

DROP USER user_name;

Here, the user_name is the name of the user you want to remove from MariaDB.

The steps to remove a user from MariaDB are as follows.

Step 1 – Firstly, you need to have a look at all the users by typing the following command.

SELECT User FROM mysql.user;

Now, select any user from the list which you want to remove from MariaDB.

Step 2 – Now, you need to type the following command to remove the user you want.

DROP USER 'Katherine'@'%';

Here, ‘%‘ is there in place of ‘localhost‘ as the user i.e. Katherine is a user with remote access.

Step 3 – The selected user have been removed but to ensure the proper completion of the task, you can type the following command to list all the users.

SELECT User FROM mysql.user;
MariaDB Remove User From Database
MariaDB Remove User From Database

The user i.e. Katherine is not present in the list of all users and thus, this ensures that the process has been completed successfully completed.

Note – You can also remove more than one user by using the following command.

DROP USER 'Chris'@'localhost', 'Caroline'@'localhost';

And make sure you flush the privilege tables using the following command.

FLUSH PRIVILEGES;
MariaDB Remove User From Database
MariaDB Remove Multiple User From Database

This is how you can remove Users from the Database in MariaDB.

Read How to Create Table in MariaDB

MariaDB Remove Anonymous Users

MariaDB includes an Anonymous User account that allows anyone to connect to the MariaDB server without having a user account created for them.

It is intended only for testing and to make installation a bit smoother but it should be removed before moving towards a production environment.

Here, you can remove the anonymous user by typing the following command.

DELETE FROM mysql.user WHERE User='';

Now, all the anonymous users will be deleted.

And make sure you flush the privilege using the following command.

FLUSH PRIVILEGES;
MariaDB Remove Anonymous Users
MariaDB Remove Anonymous Users

This is how you can remove Anonymous Users from MariaDB.

Read How to Add Column in MariaDB

MariaDB Remove All Users

In this, you will learn how to remove all users in MariaDB.

You can remove all the users by using the DROP statement along with the names of all users separated by commas.

The syntax for removing multiple users at once is as shown below.

DROP USER 'user1'@'localhost', 'user2'@'localhost',......;

You can remove all the users using this method.

MariaDB Remove Root User

In this, you will learn how to remove root user in MariaDB.

Removing the root user will throw connection refused errors which will result in malfunctioning of the server. So, it is suggested not to remove the root user even if you want to remove it for experiment purposes then you can create a new user with all the privileges the same as the root user.

For this, you need to use the DROP USER statement which is used to remove the root user.

DROP USER 'root'@'localhost';

Now, the root user will be deleted.

And make sure you flush the privilege tables using the following command.

FLUSH PRIVILEGES;

This is how you can remove Root User from MariaDB.

Read How to Grant All Privileges in MariaDB

MariaDB Remove User ERROR 1396

In this, we will learn about ERROR code 1396 related to removing users’ accounts.

The ERROR 1396 usually occurs when it fails in executing statements related to user management like CREATE USER or DROP USER.

It occurs during the Drop statement when the user we want to delete does not exist in MariaDB.

For example, you can try to remove a user with a name like Jack or any other user who does not exist in the Users list.

DROP USER 'Jack'@'localhost';
MariaDB Remove User ERROR 1396
MariaDB Remove User ERROR 1396

Here, you can see the ERROR 1396 as the user i.e. Jack does not exist.

You may like the following MariaDB tutorials:

In this MariaDB Tutorial, we have learned about MariaDB Remove User. Additionally, we have covered the following topics.

  • MariaDB Remove User
  • MariaDB Remove User Password
  • MariaDB Remove User Grants
  • MariaDB Remove User From Database
  • MariaDB Remove Anonymous Users
  • MariaDB Remove All Users
  • MariaDB Remove User ERROR 1396