How to Show Users in MySQL? (MySQL Show Users)

Databases are designed to store data in a structured format, allowing for the retrieval and management of information. When working with a MySQL database, you might need to access a list of all user accounts in the system. While it’s common to assume there might be a command like in MySQL SHOW USERS—similar to SHOW DATABASES or SHOW TABLES—to display the users, MySQL doesn’t provide a direct SHOW USERS command. However, there are alternative methods in MySQL to show users.

This guide outlines the steps to list users effectively in a MySQL environment. Discover how to show MySQL users with this straightforward guide. Follow these step-by-step instructions to easily display all users in your MySQL database. 

Why Create MySQL Users on MySQL Server?

When MySQL is installed, the first account created is the root user, which acts as the system's administrator. This account has unrestricted access to all MySQL databases and their resources.

While having full access can be convenient, it poses significant security risks. Sharing the root account among multiple users increases the likelihood of vulnerabilities. Cyber attackers often target the root account to gain control of the system, potentially leading to the theft or destruction of all data on the MySQL server.

To mitigate these risks, database administrators create additional user accounts with specific permissions tailored to certain databases. This ensures that if an account’s credentials are compromised, the potential damage remains limited and manageable.

How to Show Users in MySQL Database? (MySQL Show Users)

In MySQL, commands like SHOW DATABASES or SHOW TABLES allow you to quickly view databases or tables. However, MySQL does not provide a SHOW USERS command to list all users directly. Instead, you can use specific queries to retrieve a list of users in the database server. Below, you will see how to show users in MySQL database.

Step 1: Log in as the Root User

Start by accessing your server via SSH and logging in as the root user. Once connected, use the following command to open the MySQL command line:

$ sudo mysql -u root -p

After entering the command, you’ll be prompted to input the MySQL root password. Once authenticated, you will have access to the MySQL console as the root user.

Step 2: Run a Query MySQL Show Users (MySQL List Users)

To retrieve a list of users, execute the following query in the MySQL console:

SELECT user FROM mysql.user;

This query will display a list of all users created in the MySQL database. 

Keep in mind that duplicate entries may appear because MySQL can restrict access based on the user’s source IP address.

Step 3: MySQL Show Users and Host Details (Optional)

If you want to see which hosts or IP addresses are authorized for each user, modify the query as follows:

SELECT user, host FROM mysql.user;

This will display a list of MySQL users along with their associated hosts or IP addresses.

Step 4: View Additional User Information (Optional)

For more detailed information about the users, you can query all columns from the user table:

SELECT * FROM mysql.user;

This query will show all available information about MySQL users. However, the output may be overwhelming and difficult to interpret. To simplify the results, consider narrowing your search with more specific queries tailored to the information you need.

MySQL List Users (Using phpMyAdmin)

You can use phpMyAdmin to view a list of MySQL users by following a simple process. If you're using CyberPanel, phpMyAdmin is installed automatically, providing a user-friendly interface for managing MySQL databases.

Step 1: Access phpMyAdmin

Log into CyberPanel and select phpMyAdmin from the left-hand menu. You will be automatically logged into phpMyAdmin.

Step 2: Open the SQL Query Tool

Click on the SQL tab in the top menu to open the query editor. In the query box that appears, you can execute commands to interact with the MySQL database.

Step 3: Display the User Table Structure

To view the structure of the mysql.user table, run the following command:

DESC mysql.user;

Step 4: MySQL List Users (MySQL List all Users)

To display all users and their associated hosts, execute this query:

SELECT user, host FROM mysql.user;

Step 5: View the Current User

If you want to find out which user is currently logged in, use this query:

SELECT current_user();

Step 6: Check Logged-In Users and Their Status

For more details, such as the states of active users or identifying idle connections, use the following query:

SELECT user, host, command FROM information_schema.processlist;

This query can help you identify inactive users consuming server resources.

Display Current User Login Details (MySQL Show Users)

MySQL provides a built-in function, USER(), to retrieve the username and host currently being used to access the MySQL command line. You can use it with the following query:

SELECT user();

The output will display the username and host of the logged-in user.

If you want additional details, such as the status of currently logged-in users, you can use a more comprehensive query. This is particularly useful for identifying idle users who may be consuming unnecessary resources. Execute the following command to get this information:

SELECT user, host, command FROM information_schema.processlist;

This query provides insights into user activity, helping you manage system resources effectively.

Display Unique Usernames in MySQL (MySQL View Users)

In MySQL, it’s common to encounter duplicate usernames in the mysql.user table, especially if a user is assigned to multiple hosts or connects through various IP addresses or domains. This can make the user list appear cluttered and harder to interpret.

To simplify the view and display only unique usernames, you can use the SELECT DISTINCT command. Here’s how:

SELECT DISTINCT User FROM mysql.user;

This query filters out duplicates, ensuring that each username appears only once, regardless of the number of associated hosts. It provides a cleaner and more concise view of the user accounts on your MySQL server, making it easier to manage and analyze user data.

Viewing User Table Details

If you want more information about the structure of the mysql.user table and its fields, you can use the following command:

DESC mysql.user;

This will display all the fields in the table, providing insights into the available user properties.

Retrieving Specific User Details

To view particular details, you can query specific fields from the mysql.user table. For example, if you want to see a list of users along with their update privileges, run this query:

SELECT User, Update_priv FROM mysql.user;

You can replace Update_priv with any other field to retrieve specific information about user accounts. This approach helps you tailor the query to your needs, providing only the relevant details for effective user management.

How to Check Locked Accounts or Expired Passwords in MySQL?

To identify locked accounts or expired passwords in MySQL, run the following query:

SELECT User, Host, Account_locked, password_expired FROM mysql.user;

In the above command, all parameters are used for the purpose:

• User: Displays the username.
• Host: Shows the associated host for the account.
• Account_locked: Indicates if the account is locked (Y for locked, N for unlocked).
• password_expired: Shows if the password has expired (Y for expired, N for valid).

This information helps you identify accounts needing a password update or those locked out for security.

Managing Locked Accounts and Expired Passwords:

To unlock an account, you can use the following command:

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

To reset an expired password:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

How to Check and Manage User Privileges in MySQL?

To see the permissions assigned to a specific user, use:

SHOW GRANTS FOR 'username';

For example:

SHOW GRANTS FOR 'samreena';

How to Change User Privileges in MySQL Database?

As a database administrator, you have the ability to modify user privileges according to your needs. This includes:

Granting privileges: You can provide specific users with access to certain databases, tables, or commands using commands like:

GRANT SELECT, INSERT ON database_name.* TO 'samreena';

This grants the user Maria the ability to select and insert data into all tables within the specified database.

Revoking privileges: If you need to remove a user's access to specific actions, you can use the REVOKE command:

REVOKE INSERT ON database_name.* FROM 'samreena';

This command removes the INSERT privilege from Maria.

Global, database, or table-level privileges: Privileges can be granted or revoked at different levels. Global privileges apply to the entire database server, while database-level or table-level privileges are specific to certain areas, allowing for more granular control over what each user can access or modify.

Conclusion

The MySQL database server is a popular choice among developers and users exploring structured query languages due to its reliability, security, and efficiency.

By utilizing the commands like MySQL Show users, you can easily manage and retrieve user information from the mysql.user table. Beyond simply listing users, you can refine search queries to access detailed, user-specific information.

Having a comprehensive and flexible understanding of user management helps you make informed decisions to enhance the security and functionality of your MySQL server.

Experience the unmatched power of limitless hosting with 10GBVPS! At 10GBVPS.com, we deliver lightning-fast speeds to supercharge your website's performance. Say goodbye to bandwidth limits and overage fees, and enjoy peace of mind with our reliable, high-performance hosting solutions. 

With multiple global locations, we ensure optimal speed and dependability, no matter where your audience is. Don’t wait—unlock the full potential of your website today with 10GBVPS!