EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Show Users

MySQL Show Users

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 26, 2023

MySQL Show Users

Introduction to MySQL Show Users

MySQL does not have any command like SHOW USERS to show the list of users for tables and databases in SHOW TABLES and SHOW DATABASES commands. The information about all the users is stored inside the table named user. The user table contains all the information related to the users of your MySQL database server. This user table is stored in the MySQL-named database of your database server. Besides, MySQL has functions to get the current user, like user() or current_user() functions. We can even check the list of users currently logged in to your database server. We will learn about the syntaxes and usage of all the above user-related data retrieval in this article.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Usage of User-Related Data Retrieval

Let us learn about syntaxes and usage:

1. User-related information in the user table

As discussed, a MySQL database stores the user-related information in the table named user. This table contains a total of 45 columns to store user information. Some of them are user, host, ssl_type, ssl_cipher, max_questions, max_connections, authentication_string, password_expired, account_locked, password_lifetime, password_last_changed, and other columns related to privileges assigned to the user for different database operations.

Step 1: To view the complete list of the columns of the user table, you can describe the user table using the following query:

Code:

DESC user;

OR

If you are currently using MySQL as your chosen database, you can utilize the following method:

Code:

DESC mysql.user;

Output:

complete list of the columns

Step 2: We will first retrieve the names of all the users from the user table using the following SELECT query:

Code:

SELECT USER FROM mysql.user;

Output:

mysql show users2

Explanation: We can see that four users are present for my database server.

Step 3: To retrieve the data from the user table of the MySQL database, you first need to login to MySQL using MySQL -u root pg command if you are using MySQL from the command line through the terminal or if you are using an IDE, enter your credentials or editor such as sqlyog of workbench.

An alternative to using the above select query, you can first fire the command to select the MySQL database and then fire the following choose query that will produce the same output as above:

Code:

SELECT user from user;

To retrieve more information about users, we can add the columns we want to retrieve in the select query as follows:

Code:

SELECT
USER,
HOST,
account_locked,
password_last_changed,
password_lifetime,
max_connections
FROM
USER;

Output:

mysql show users3

Step 4: Suppose you want to retrieve assigned privilege-related information of the users. Then, in that case, you can choose privilege-related columns as done in the following SELECT query:

Code:

SELECT
USER,
Insert_priv,
Select_priv,
Update_priv,
Delete_priv,
Create_priv,
Drop_priv,
File_priv,
Grant_priv,
Index_priv,
Alter_priv
FROM
USER;

Output:

mysql show users4

Explanation: Other columns can be retrieved, whichever you want, using the select query. Most of the columns of the user table that store the values like boolean, i.e., either true or false, have the datatype of an enum having values ‘Y’ that stands for true and ‘N’ that specifies false, i.e., enum(‘N’,’ Y’) datatype that informs whether the particular privilege is assigned to the user or not.

2. Viewing current user information

MySQL provides us with the function user() that can be used to retrieve the database’s current user.

Step 1: You can use the following query to accomplish that:

Code:

SELECT user();

Output:

Viewing current user information

Explanation: The above function returns the current user’s name, then the host that, in our case, is %, and then the database server’s address.

Step 2: One more method is available to retrieve the current user: the current_user() function. You can achieve this by taking the following steps:

Code:

SELECT CURRENT_USER();

Output:

mysql show users6

Explanation: The above-returned result of the CURRENT_USER() function is in the format of the user’s name and then @ symbol that the host further follows. In our case, it is (%). The functions USER() and CURRENT_USER() can retrieve and fetch the current database user.

3. Checking all the currently logged-in users

There is a possibility that there are multiple users of the MySQL database server, and many users can access the system simultaneously. In that case, if we want to fetch the list of the users currently logged in to your MySQL server can be done by querying on the processlist table of information_schema. The processlist schema stores the list of all the users logged in and data related to each of the operations performed by users on that database at that particular instant when the query was fired.

Step 1: We will first retrieve the process list that MySQL is executing. You can achieve this by following the steps mentioned below.

Code:

SELECT
*
FROM
information_schema.processlist;

Output:

retrieve

Explanation: This table contains process-related information such as process id, user, host, DB, Command, Time, State, and INFO.

Step 2: Now, To retrieve the list of currently logged-in users from the system, you can write a query using the DISTINCT keyword in the following manner:

Code:

SELECT
DISTINCT(USER)
FROM
information_schema.processlist;

Output:

DISTINCT keyword

Explanation: The above output indicates that your system has only one logged-in user, specifically an AcmeInfi user.

Conclusion – MySQL Show Users

The table named “user” in the MySQL database stores information related to users, including fields such as passwords, hosts, and other privileges assigned to the user. We can retrieve the user information by querying it. You can use the user() and current_user() functions in MySQL to retrieve the current user. These functions work similarly, allowing you to obtain the user name and host information. The user () function retrieves the database address, host, and user name. If you want to see the list of all the users currently logged into the database, you can query the processlist table of the information_schema database.

Recommended Articles

We hope that this EDUCBA information on “MySQL Show Users” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Index
  2. ROLLUP in MySQL
  3. MySQL Root
  4. MySQL Administration
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more