Remove Permissions for a MySQL User on Linux via Command Line
- Using MySQL Command Line to Create a User
- Grant Permissions to a MySQL User on Linux via Command Line
- Remove Permissions for a MySQL User on Linux via Command Line
MySQL via Command Line 101: Basic Database Interaction
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.
Related Articles:
- Using MySQL Command Line to Create a User
- How to Use Disk Quotas in Dedicated Linux Servers With cPanel
- How to Use Disk Quotas in Dedicated Linux Servers with Plesk
- Remove a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line
- Delete a MySQL or MariaDB Database via Command Line

About the Author: Ronald Caldwell
Ron is a Technical Writer at Liquid Web working with the Marketing team. He has 9+ years of experience in Technology. He obtained an Associate of Science in Computer Science from Prairie State College in 2015. He is happily married to his high school sweetheart and lives in Michigan with her and their children.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
Latest Articles
How to Install WordPress on Linux (AlmaLinux)
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleRedis as Cache: How It Works and Why You Should Use It
Read ArticleRefer-a-Friend Program for Website Hosting: Get $100 for Each Friend!
Read Article