Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Show Users
3.
Show Current User
4.
Show Current logged Users
5.
FAQs
6.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Show Users

Author Apoorv Dixit
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

While working with databases, sometimes it becomes necessary to show the list of users to manage the Database well. So how will you do that on the MySQL server? You might be wondering if a command similar to SHOW DATABASES, SHOW TABLES that show us all databases on MySQL server and tables in the selected database. Unfortunately, with MySQL, the case is a bit different. There is no ‘SHOW USERS’ command in MySQL.

 
                                            Source: tenor.com

Therefore to list all users, we have to use queries. 

Now let's start with the implementation: 

Show Users

To list all users in a MySQL database server, we can use the following query:

SELECT user FROM mysql.user;

In the above statement, we query the user data from the user table of the MySQL database.

After successfully executing the above statement, we will get the user data from the user table.

Now let’s see how we will use this query: 

To use the above query, one must log in to the MySQL database server as an administrator.

>mysql -u root -p
Enter password: *******
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;

We will get the following output to see the list of users.

+-----------+

| user      |

+-----------+

| mysql.sys |

| mysqlxsys |

| root      |

+-----------+

3 rows in set (0.00 sec)

To get more information about the user table, we can preview its columns. To this, use the following command.

DESC user;

The above command will give the lists of all the available columns of the mysql.user database as an output.

For information like hostname, password expiration status, and account locking, execute the following query:

mysql> SELECT user, host, account_locked, password_expired FROM user;  

The output of the following query will be similar to the output given below.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Show Current User

To show the information about the current user, we can use user() or current_user() function.

The code given below shows the use of user() function and sample output.

SELECT user();

mysql> SELECT user();

+-----------------+

| user()          |

+-----------------+

| local@localhost |

+-----------------+

1 row in set (0.00 sec)

The code given below shows use of the current_user() function and a sample output.

SELECT current_user();

mysql> SELECT current_user();

+----------------+

| current_user() |

+----------------+

| local@localhost |

+----------------+

1 row in set (0.00 sec)

Here, the current user is local@localhost.

Show Current logged Users

To show the list of users currently logged in the MySQL database server, we can use the following command:

SELECT 
    user, 
    host, 
    db, 
    command 
FROM 
    information_schema.processlist;

The above command will output similar the following:

+-------+-----------------+---------------+---------+

| user  | host            | db            | command |

+-------+-----------------+---------------+---------+

| local | localhost:50557 | brandedcars | Sleep   |

| root  | localhost:50590 | NULL            | Query  |

+-------+-----------------+---------------+---------+

2 rows in set (0.00 sec)

 In the above sample output, you can clearly see that currently, two logged users are there, one is in execution and the other is sleep.

FAQs

  1. What is the SHOW DATABASES command?
    SHOW DATABASES command lists all databases on a MySQL server host. SHOW SCHEMAS command can also be used for the same purpose; it's a synonym of the SHOW DATABASES command.
     
  2. What is the SHOW command in MySQL?
    The SHOW command is a query in MySQL that is used to view the information schema of any records stored on the databases.
     
  3. What is the MySQL user table?
    MySQL user table is a table that contains information about users that have permission to access the MariaDB server and their global privileges.
     
  4. What is the MySQL database name?
    There is no default database name. A fresh MySQL server install will have zero databases. The install script will run after the server runs to create a MySQL database.
     
  5. How can we see MySQL users, passwords, and hosts?
    To see the MySQL users and passwords run the following query:  
mysql> select user, password, host from mysql.user;

Key Takeaways

After completing the article, you should have learned how to show the list of all users by querying the data user table in the MySQL database. 

Recommended Readings:

You can also check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Refer to the Guided Path to learn more about DBMS. You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation.

Live masterclass