Remove Permissions for a MySQL User on Linux via Command Line

A fundamental part of managing users in MySQL is removing permissions no longer required for a user. Administrators should ensure that terminated users or those whose roles have changed within the company have had permissions removed from their user profiles. This action secures the system against unlawful access to information.

This guide walks you through how to remove MySQL user permissions on Linux via the command line.

Prerequisites

• A server running CentOS or AlmaLinux.
• Root access and log in as the root user.
• Access to the terminal.
• Basic command line knowledge.

How to Remove MySQL User Permissions on Linux via Command Line

Step 1: Access the MySQL Server

Use the following command to access the MySQL server from the command line. This command specifies the root user with the -u flag. The -p flag makes MySQL prompt for a password. Enter your current password to complete the login.

mysql -u root -p

The system presents the MySQL prompt. Here you can create users in MySQL or change a user password for the root or any other user in the database.

mysql>

Step 2: View Permissions for MySQL Users

Before changing the permissions, it is helpful to know the permissions assigned to each user. You will receive an error if the specified user does not have the permission you are trying to remove. The following command checks the permissions for the user testuser.

SHOW GRANTS FOR 'testuser'@'localhost';

Step 3: Revoke Permissions for a MySQL User

The following is the basic syntax for revoking a user’s permissions.

REVOKE permission ON database.table FROM 'user'@'localhost';

Here is a short list of commonly used permissions:

• ALL - Allow complete access to a specific database. If a database is not specified, then allow full access to the entirety of MySQL.
• CREATE - Allow a user to create databases and tables.
• DELETE - Allow a user to delete rows from a table.
• DROP - Allow a user to drop databases and tables.
• EXECUTE - Allow a user to execute stored routines.
• GRANT OPTION - Allow a user to grant or remove another user's privileges.
• INSERT - Allow a user to insert rows from a table.
• SELECT - Allow a user to select data from a database.
• SHOW DATABASES - Allow a user to view a list of all databases.
• UPDATE - Allow a user to update rows in a table.

Using an asterisk (*) in the place of the database or table is an option that implies all databases or tables. Use the following command to revoke CREATE permissions for all databases and tables from the user testuser.

REVOKE CREATE ON *.* FROM 'testuser'@'localhost';

Use the  DROP permission to revoke the ability to drop tables in the database tutorial_database.

REVOKE DROP ON tutorial_database.* FROM 'testuser'@'localhost';

After making your permission changes, it is good practice to refresh all the privileges with the flush command.

FLUSH PRIVILEGES;

Wrapping Up

Remove MySQL permissions with ease using this tutorial. It offers an easy way of accomplishing this task. Removing permissions for a MySQL user on Linux via command line is one of many security measures administrators must take to secure data systems.

Fully managed Liquid Web customers on VPS Hosting, Cloud Dedicated Servers, and Dedicated Servers get 24/7/365 support for these and other functions. Contact our sales team today to ensure you get the managed experience.