Managing users is a crucial aspect of database administration. Over time, there may be users in the database that no longer require access or are perhaps obsolete due to staff changes or project shifts.
MySQL and MariaDB provide a simple syntax for adding, updating, and deleting users. If you need to revoke access to a database, one of the steps may involve deleting a user. It's crucial to remember that removing a user will also revoke all the privileges associated with that user. So, make sure the user is not essential for other applications or processes.
Before executing any commands, ensure that you have administrative privileges in your MySQL or MariaDB installation. You also need to be logged in to the server hosting the database to execute the following steps.
Steps to delete user from MySQL/MariaDB server:
- Log in to your MySQL or MariaDB server with an administrative account using the mysql client.
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.30 Source distribution Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
- Select the appropriate database.
USE mysql;
By default, MySQL and MariaDB store user information in the 'mysql' database.
- Confirm the user exists.
SELECT User, Host FROM user;
- Look for the user you want to delete in the resulting output.
- Revoke all privileges to prevent any potential issues.
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';
- Revoke any additional grants if the user has the ability to grant privileges to other users.
REVOKE GRANT OPTION ON *.* FROM 'username'@'hostname';
- Delete the user.
DROP USER 'username'@'hostname';
- Flush privileges to ensure the changes take effect.
FLUSH PRIVILEGES;
- Verify the user has been deleted.
SELECT User, Host FROM user;
- Exit the MySQL or MariaDB prompt.
EXIT;
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
Comment anonymously. Login not required.