MySQL Show User Privileges

October 1, 2021

Introduction

By default, MySQL installs and works under the root user with all privileges. However, not everyone who accesses the database should have all rights over the data for security reasons.

MySQL provides methods to create new user accounts and grant privileges over the database. A simple command helps provide valuable information about what privileges the users currently have.

This tutorial shows how to check the user privileges on a MySQL server.

MySQL Show User Privileges

Prerequisites

  • Access to the command line/terminal.
  • MySQL installed and configured.
  • Access to the MySQL root user account.

How to Show Privileges for a User in MySQL?

To show privileges for a user in MySQL:

1. Open the terminal (CTRL+ALT+T) and log into the MySQL server as root:

mysql -u root -p

Provide the root password when prompted, and press Enter to start the MySQL monitor.

Note: Logging in as root is not necessary. However, the root user has the SELECT permission, which is needed to overview the grants for all other users.

To provide the special SELECT permission to another user, run the following command as a user with SELECT permissions (or root):

GRANT SELECT ON *.* TO <username>;

2. If you know the exact username and host for which you'd like to check the privileges, skip this step. Otherwise, show all users and hosts:

SELECT user,host FROM mysql.user;
Output of the select user and host query in MySQL

Locate the exact username and host for the next step.

3. Use the following statement to check the privileges for a specific user:

SHOW GRANTS FOR <username>@<host>;

For example, to check the permissions for test_user:

SHOW GRANTS FOR test_user;
Output of the show grants for user query permissions list

Without a hostname, the command checks for the default host '%'.

Alternatively, check the permissions for the currently logged in user with:

SHOW GRANTS;
Output of the show grants query for current user permissions list

The output prints a table with all the access privileges. The first grant was auto-generated when the user was created, and the administrator assigned all the following rights later.

Note: For the best MySQL data management, deploy a Bare Metal Cloud server instance to separate your database from other applications and services. BMC servers are an efficient way to handle high volume applications with ease.

Conclusion

After this tutorial, you now know how to check the permissions for a specific user in a database. The command is simple to use and works together with other MySQL commands to monitor privilege access.

For more database security topics, check out our list of 10 Database Security Best practices.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is an aspiring technical writer at phoenixNAP and a data nerd. Her background in Electrical Engineering and Computing and her teaching experience give her a unique set of skills - being able to easily explain complex technical concepts through her content.
Next you should read
How to List All Users in a MySQL Database
November 18, 2019

This simple tutorial analyses the commands used to list all user accounts in MySQL. Learn about additional options that help you focus your search.
Read more
Best Database Management Software - Top 25 Software
July 21, 2021

A DBMS facilitates data manipulation and provides an additional security layer for your database. This...
Read more
How To Remove or Delete a MySQL User Account
December 1, 2019

This article covers the basics of using the DROP USER statement used to delete MySQL user account. Follow the step-by-step...
Read more
How To Create New MySQL User and Grant Privileges
September 18, 2019

MySQL is a database application for Linux. It’s part of the LAMP (Linux, Apache, MySQL, PHP) stack that powers many features on...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.