MySQL List User

Introduction to MySQL List User

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

Most of the cases some databases used SHOW USERS command to get the list of the users. But MySQL database doesn’t use any such command instead it uses the query which we have mention in the Syntax session.

• 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 login to the MySQL with an admin account and query the user table. Users can be created in MySQL. Let us see in brief about the creation of the user along with the example.

Let us see an example for the same: –

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

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

Syntax:

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

How does MySQL List User work?

In order to get the details of the user, 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 columns names are listed as below: –

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

Code:

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

Output:

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:

Let us just 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:

If we want to get the information of the current user, 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:

Users can be created in MySQL. In order to create the below syntax is used: –

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 for 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 that we planning to create.

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

Recommended Articles

This is a guide to MySQL List User. Here we discuss an introduction, how to use MySQL List User in detail for better understanding. You can also go through our other related articles to learn more –

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