Updated May 26, 2023
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.
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:
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:
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:
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:
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:
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:
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:
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:
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.
