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.
- 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:
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 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 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:
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.
