How to Show Users in MySQL

Technical writer

MySQL

13.02.2025

Reading time: 7 min

MySQL stands as a widely recognized relational database system, extensively utilized for handling structured data. It provides administrators with robust utilities to oversee users' accounts effectively. Whether the goal is to retrieve a full user list, check access rights, or filter specific accounts, MySQL offers multiple ways to accomplish these tasks. Understanding these methods can help ensure better control over database security and access management.

In this write-up, we’ll explore all possible approaches to show users in MySQL.

Prerequisites

Before proceeding, ensure you meet these prerequisites:

MySQL is set up on your machine.
Superuser permissions for MySQL (root or an account with sufficient rights).
A MySQL interface (such as MySQL Shell, Workbench, or a command terminal with the MySQL CLI tool).

Understanding MySQL Users

In MySQL, accounts represent users who can interact with the database by executing tasks like reading, updating, or deleting data. Each account consists of a username and a host, which specifies the allowed connection source. For instance, 'user'@'localhost' restricts access to the same computer where MySQL is running, whereas 'user'@'%' allows access from any IP address(location).

We can handle user accounts with simple commands, as illustrated below:

Task	Command
Create User	`CREATE USER 'anees'@'localhost' IDENTIFIED BY 'password';`
Grant Permissions	`GRANT ALL PRIVILEGES ON mydb.* TO 'anees'@'localhost';`
See Existing Users	`SELECT user, host FROM mysql.user;`
Delete User	`DROP USER 'anees'@'localhost';`

MySQL controls user access using privileges, ensuring that each account can execute only permitted actions, thereby maintaining database security.

Different Methods to Show MySQL Users

MySQL offers multiple ways to view user accounts, each designed for a particular need. Whether you require a basic user list, detailed account insights, or filtered results, MySQL offers flexible options to fetch user details. Common approaches include querying the mysql.user table, executing SHOW GRANTS to review privileges, and using SELECT statements with filtering conditions. These methods help database administrators effectively manage accounts, maintain secure access, and enforce proper control within the database.

1. Querying the mysql.user Table

The mysql.user is MySQL's built-in system table that stores all user account details, including usernames, host specifications, authentication methods, and assigned privileges. Administrators can retrieve data from this table to gain a comprehensive view of all accounts and their respective access rights. It plays a crucial role in auditing permissions, checking authentication plugins, and filtering users based on defined parameters. However, accessing this table requires administrative rights, and the privilege data may appear intricate for those unfamiliar with its structure. Despite this, it remains one of the most detailed and effective tools for handling user accounts in MySQL.

Here’s how to fetch all users using this table:

SELECT User, Host FROM mysql.user;

The User column depicts account names, while the Host column indicates the originating host that allows user connections:

2. Retrieving Unique Users with DISTINCT

In MySQL, the DISTINCT clause can be applied within a SELECT statement on the mysql.user table to retrieve distinct user accounts. Since MySQL associates each account with both a username and a host, the same username may appear multiple times with varying host values. Utilizing DISTINCT removes redundant usernames, yielding a streamlined list of unique accounts. This approach is particularly beneficial for obtaining a summarized view of all database users, especially in environments where multiple hosts are involved:

SELECT DISTINCT User FROM mysql.user;

This technique is ideal when you require only the usernames without including host-related details.

3. Extracting User Privileges with SHOW GRANTS

The SHOW GRANTS statement in MySQL helps us extract the permissions granted to a particular user. SHOW GRANTS presents access rights in a structured format that mirrors the GRANT commands used to grant those privileges. This feature makes it an essential tool for reviewing user privileges and ensuring security compliance.

Employ this command to fetch a particular user alongside its access rights:

SHOW GRANTS FOR 'user'@'host';

Substitute the username and host with the relevant user information, as depicted in the snippet below:

4. Checking Users via MySQL Workbench

This method offers a graphical interface (GUI) for handling MySQL user accounts, making it suitable for those who prefer a visual approach over command line operations (CLI). MySQL Workbench enables users to view existing accounts, assigned roles, and granted privileges via its Users and Privileges section. This structured view reduces the risk of errors compared to manually running SQL commands, making it beginner-friendly.

To view user accounts in MySQL Workbench, launch the application and establish a connection to your MySQL server:

Next, in the Navigator panel, choose Administration, expand the Management section, and click the "Users and Privileges" to access the user management window:

Within the Users tab, a list of existing MySQL user accounts alongside their corresponding hosts will be displayed:

We can choose a user to review or modify its privileges, authentication method, and other settings.

Fetching Logged-In User Details in MySQL

The SELECT USER(); query in MySQL fetches the current session’s authenticated user, including the originating host. It’s a fast and efficient way to examine the active user without needing additional tools or administrative rights. This method works on all MySQL installations, including remote connections, and has minimal system overhead. However, it only exhibits the authenticated user and lacks information regarding roles or permissions:

SELECT USER();

The resultant outcome demonstrates that root@localhost is currently logged in user on our system:

Alternatively, we can employ the below-listed query to fetch the current user details:

SELECT CURRENT_USER();

How Can We Check the Existence of a Particular User in MySQL?

To confirm if a particular MySQL account is available, we can query the mysql.user table with the EXISTS clause. The succeeding command checks for a user’s presence:

SELECT EXISTS(SELECT 1 FROM mysql.user 
WHERE user = 'username' AND host = 'host');

It returns one if the account is present and zero if not:

If you are uncertain about the host, you may exclude the host condition or execute the command below:

SELECT User, Host 
FROM mysql.user 
WHERE User = 'anees';

If the desired account is found, it depicts the username and hostname; otherwise, it shows "Empty Set":

Effective Ways to Display MySQL Accounts

Here are key guidelines to follow for efficient handling and presenting MySQL accounts to maintain security and streamline user administration:

To efficiently handle MySQL accounts, ensure you have administrative privileges before running commands like retrieving data from the mysql.user table or utilizing SHOW GRANTS.
Avoid revealing confidential details such as passwords by only selecting the necessary columns, like User and Host, rather than employing "SELECT *".
When searching for certain user accounts, apply WHERE to refine the results and avoid fetching irrelevant data.
To obtain a refined list of distinct accounts, apply DISTINCT, as MySQL records users with both their usernames and hosts.
To depict a user's permissions in a clear format, execute SHOW GRANTS FOR 'username'@'hostname';.
If you're not comfortable with the command line interface, utilize MySQL Workbench, which provides a user-friendly interface to handle accounts.
Ensure limited access to the mysql.user table to protect sensitive data, and periodically audit user accounts and privileges to maintain security.

Conclusion

Effectively handling MySQL users/accounts is crucial for enhancing security and proper access control within your database. Whether you're using SQL queries like SELECT and SHOW GRANTS, or MySQL Workbench for a more intuitive experience, multiple approaches are available for listing and managing accounts. It's important to consider optimal practices, such as using appropriate privileges, limiting data exposure, and regularly auditing user accounts, to ensure your MySQL setup remains secure. In this write-up, we explained how to confidently manage users, monitor permissions, and uphold the reliability of your database system.

MySQL

13.02.2025

Reading time: 7 min

Similar

MySQL

How to Show Users in MySQL

Prerequisites

Understanding MySQL Users

Different Methods to Show MySQL Users

1. Querying the mysql.user Table

2. Retrieving Unique Users with DISTINCT

3. Extracting User Privileges with SHOW GRANTS

4. Checking Users via MySQL Workbench

Fetching Logged-In User Details in MySQL

How Can We Check the Existence of a Particular User in MySQL?

Effective Ways to Display MySQL Accounts

Conclusion

Similar

How to Create a MySQL Database Dump

How to Import and Export Databases in MySQL or MariaDB

How to Secure MySQL Server

Do you have questions, comments, or concerns?

Do you have questions,
comments, or concerns?