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.
$ 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>
USE mysql;
By default, MySQL and MariaDB store user information in the 'mysql' database.
SELECT User, Host FROM user;
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';
REVOKE GRANT OPTION ON *.* FROM 'username'@'hostname';
DROP USER 'username'@'hostname';
FLUSH PRIVILEGES;
SELECT User, Host FROM user;
EXIT;
Comment anonymously. Login not required.