Grant Permissions to 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
After an administrator creates a MySQL user via the command line on Linux, the next step is to grant permissions to that user. The goal is to ensure that the user is able to log in and access the MySQL server to perform tasks. This article shows you how to grant permissions to a MySQL user 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 Grant Permissions to a MySQL User on Linux via Command Line
Step 1: Access the MySQL Server
Open a terminal to access the MySQL server from the command line using the following command. It 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. From here, you can change a password for MySQL via the command line for the root or any other user in the database here.
mysql>
Step 2: Grant Permissions to MySQL User
Below is the basic syntax for granting user permissions.
GRANT permission ON database.table TO 'user'@'localhost';
Here is a short list of commonly used permissions:
- ALL - Allows complete access to a specific database. If a database is not specified, it allows complete 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 database or table is a completely valid option, as it implies all databases or all tables. To grant CREATE permissions for all databases and tables to the user, testuser, use the following command.
GRANT CREATE ON *.* TO 'testuser'@'localhost';
To grant testuser the ability to drop tables in the database called tutorial_database, use the DROP permission.
GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';
When you finish making your permission changes, itβs best practice to reload all the privileges with the flush command.
FLUSH PRIVILEGES;
Step 3: Confirm Grantted Permissions for the MySQL User
After granting permissions to a MySQL user, confirm them with the following command. For this tutorial, the command checks the permissions for testuser.
SHOW GRANTS FOR 'testuser'@'localhost';
Wrapping Up
Knowing how to grant permissions to a MySQL user on Linux via command line helps administrators complete this task right from the terminal. Those that prefer the terminal over another interface do well to add this tool to their arsenal.
If you are not getting the support you need for these and other tasks, consider contacting Liquid Web. Our Dedicated Servers, Cloud Dedicated Servers, or VPS Hosting include support for this and much more 24 hours a day, 7 days a week, 365 days a year. Reach out to our sales team today and get started.
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
- Remove Permissions for 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 Apache Tomcat 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