EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL List User
 

MySQL List User

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated June 5, 2023

Introduction to MySQL List User

To manage the database in MySQL, sometimes, we need to list all the user accounts in the database. To receive a user list, you need administrative privileges. We can also group users, record them, and get the appropriate information in the user list. The only field you can’t see is the one for a password. MySQL ciphers the password of the user.

 

 

In most cases, some databases used the SHOW USERS command to get the list of users. But MySQL database doesn’t use any such command; instead, it uses the query we mentioned in the Syntax session.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • To get the user list from the database in MySQL, we query the user data from the user table and the MySQL database, which provides us the details regarding the Host, User, Select_priv, Insert_priv, Shutdown_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Create_routine_priv, Create_tablespace_priv, ssl_type,ssl_cipher, x509_issuer, x509_subject, max_updates, password_lifetime, account_locked.
  • To get the User list details, we log in to MySQL with an admin account and query the user table. MySQL allows users to create other users. Let us briefly discuss the user’s creation along with the example.

Let us see an example of the same: –

create user new_user_to_create@localhost identified by 'new_userpassword';
/ * - - -  to create the new user - - - * /

In this session, let us learn how to query the ‘MySQL’ database and get the user list and other details.

Syntax:

select * from mysql.user;
/ * - - used to get the details regarding the user list - - * /

How does MySQL List User work?

To get the user’s details, we execute the below query and get the details.

select * from mysql.user;
/ * - - used to get the details regarding the user list - - * /

Here if we see in the above statement, “MySQL” is the database, and “user” is the table from the database. We have nearly 37 columns that return from the user table. The column’s names are listed below: –

To get all the above columns, we execute the below query:

Code:

desc mysql.user;
/ * - - - Gives description of the query - - - * /

Output:

description

MySQL List User - 2

Code:

Let us get the list of the user: –

select user, host, password_expired, account_locked from mysql.user;
/ * - - -  to get the user, host, pawword_expired, account_locked list - - - * /

Output:

local host

Let us extract only the user and host of the database as below:

Code:

select user, host from mysql.user;
/ * - - -  to get the user, host name list - - - * /

Output:

MySQL List User - 4

If we want to get the current user’s information, we use the inbuilt function user (). Below is the query for the same:

Code:

select user();
/ * - - - gets the information of the current user - - - * /

or

select current_user();
/ * - - - gets the information of the current user - - - * /

Output:

MySQL List User - 5

To create users in MySQL, you use the following syntax:

CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'password';
/ * - - -  to create the new user - - - * /

Here account_name indicates the user name along with the hostname. Let us see an example of the same: –

create user new_user@localhost identified by 'new_userpassword';
/ * - - -  to create the new user - - - * /

Here new_user: – is the name of the new user we plan to create.

Localhost is the hostname. ‘new_userpassword’ is the password given to the user.

Recommended Articles

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

  1. MySQL INSTR()
  2. EXPLAIN in SQL
  3. MySQL Root
  4. MySQL Database Repair

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW