How to remove permissions from MySQL or MariaDB users

MySQL and MariaDB are popular open-source relational database management systems that are widely used for web development and other applications. They allow for the creation and management of users with different levels of permissions to access and manipulate the data stored within the databases. When it comes to managing user privileges, it's important to ensure that users have the appropriate level of access to maintain the security and integrity of the database.

There may be instances where it becomes necessary to remove certain permissions from users, such as when their role within an organization changes or their access level needs to be reduced for security reasons. In such cases, knowing how to remove permissions from MySQL or MariaDB users is crucial.

This step-by-step guide will demonstrate how to remove permissions from MySQL or MariaDB users using simple SQL commands. By following the steps below, you'll be able to easily revoke privileges from a user, ensuring that your database remains secure and well-managed.

1. Log in to your MySQL or MariaDB server using a user with administrative privileges.

   mysql -u root -p

2. Show users and permissions in the system.
3. Revoke specific permissions from the user by specifying the privilege and the user's username.

   REVOKE SELECT, INSERT ON database_name.* FROM 'username'@'localhost';

4. In case you want to remove all privileges from the user, use the following command.

   REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

5. If the user has been granted the ability to grant privileges to other users, revoke that as well.

   REVOKE GRANT OPTION ON *.* FROM 'username'@'localhost';

6. To ensure the changes take effect, flush the privileges using the following command.

   FLUSH PRIVILEGES;

7. Verify that the permissions have been revoked by checking the user's privileges.

   SHOW GRANTS FOR 'username'@'localhost';

8. After confirming that the desired permissions have been removed, exit the MySQL or MariaDB server.

   EXIT;