How to remove permissions from MySQL or MariaDB users

MySQL and MariaDB are widely used relational database systems that provide data management solutions for a variety of applications. Both use the privilege system to grant and revoke user permissions. Properly managing these permissions ensures the safety and integrity of your database content.

Over time, you may need to revoke certain permissions from a user, whether for security reasons, a change in job roles, or simply tidying up unnecessary access. While MySQL and MariaDB are similar in many ways, including their privilege system, it's always good to verify the specific syntax for your database version.

Removing permissions from a user requires understanding the REVOKE command, which allows administrators to take back specific privileges from a user or revoke all privileges entirely. After updating permissions, it's also crucial to flush privileges to ensure changes take immediate effect.

1. Login to the MySQL or MariaDB database using the command line.

   $ mysql -u root -p
     - Enter password:

2. Identify the privileges currently held by the user.

   SHOW GRANTS FOR 'username'@'host';

3. Use the REVOKE command to remove a specific permission from the user.

   REVOKE SELECT ON database_name.* FROM 'username'@'host';

   This revokes the SELECT permission from the specified user for the specified database.

4. If you want to revoke all privileges from the user, use the following command.

   REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

   Be careful when using the ALL PRIVILEGES clause, as it will completely restrict the user's access to the specified database.

5. Update the privilege tables to ensure that the changes are applied.

   FLUSH PRIVILEGES;

   It's essential to execute this command to ensure the changes are recognized by MySQL or MariaDB immediately.

6. Verify the changes by checking the privileges of the user again.

   SHOW GRANTS FOR 'username'@'host';

7. Exit the database system.

   exit;

8. Optionally, test the user's access to validate that the permissions have indeed been revoked.

Remember, while revoking access is an important aspect of database administration, it's equally crucial to regularly audit user permissions to ensure that only necessary rights are granted. Proper privilege management reduces the risk of unauthorized data access and potential breaches.