[How-To] Show & Delete Databases and Users in MariaDB

Purpose

Show the commands that will show and delete databases and show and delete users in MariaDB.

Prerequisites

List of prerequisites:

• Root user or sudo user
• Server with MariaDB installed

Instructions

Step 1: Log in to MariaDB

Log in to MariaDB as a user with sufficient privileges (e.g., root):

mysql -u root -p

Enter the password when prompted.

---

Step 2: Show All Users

Run the following query to list all users:

SELECT User, Host FROM mysql.user;

---

Step 3: Delete a User

To delete a user, use the DROP USER command:

DROP USER 'username'@'host';

Replace username with the user's name and host with the correct host value from the mysql.user table.

If the user is allowed to connect from any host (%), the command would be:

---

Step 4: Verify User Deletion

Run the query again to confirm the user has been deleted:

SELECT User, Host FROM mysql.user;

---

Notes

1. Permissions Required: Ensure you have CREATE USER or DROP USER privileges to perform these actions.
2. Clean-Up (Optional): After deleting a user, you can remove their privileges explicitly (if any exist) with:

   FLUSH PRIVILEGES; 

   This updates the in-memory privilege table to reflect changes immediately.