Introduction to MySQL Show Users
MySQL does not have any command like SHOW USERS to display the list of the users as for tables and databases in SHOW TABLES and SHOW DATABASES command. 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. Other than this there are functions available in MySQL to get the current user like user() or current_user() functions. We can even check the list of the users that are 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 previously, there is a database named MySQL that 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 your current chosen database being used is MySQL or you can use:
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 command-line through terminal or by entering the credentials in case if you are using any IDE or editor such as sqlyog of workbench.
An alternative to using the above select query, you can firstly fire the command to select the MySQL database and then simply fire the following select 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;
4.5 (2,670 ratings)
View Course
Output:
Explanation: Similarly, other columns can also 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 certain 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 get the current user of the database.
Step 1: Ths following query can be used for that purpose
Code:
SELECT user();
Output:
Explanation: The above function returns the name of the current user then the host that in our case is % and then the database server’s address.
Step 2: There is also one more method available to retrieve the current user that is current_user() function. This can be used in the following manner:
Code:
SELECT CURRENT_USER();
Output:
Explanation: The above-returned result of the CURRENT_USER() function is in the format of the name of the user and then @ symbol that is further followed by the host in our case it is (%). Both the functions USER() and CURRENT_USER() can be used to retrieve and fetch the current user of the database.
3. Checking all the currently logged in users
There might be the case that there are multiple users of MySQL database server and many users can access the system at the same time. In that case, if we want to fetch the list of the users that are 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 that are logged in and data related to each of the operations being performed by users on that database at that particular instant when the query was fired.
Step 1: We will first retrieve the whole process list that is currently being executed by MySQL.This can be done in the following manner:
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, If you want to retrieve the list of the users that are currently logged in to the system, we can write the query using DISTINCT keyword in the following manner:
Code:
SELECT
DISTINCT(USER)
FROM
information_schema.processlist;
Output:
Explanation: The above output shows that there is only one user that is logged into my system that is AcmeInfi user.
Conclusion
The information related to the user such as password related fields, hosts, and other privileges assigned to the user is all stored in the table named user located in MySQL database. We can retrieve the user information by querying it. To retrieve the current user, there are two functions provided by MySQL namely user() and current_user() that nearly function in the same way retrieving the user name and the host. User() function retrieves the database address along with the host and user name. If you want to see the list of all the users that are currently logged into the database, you can query the processlist table of the information_schema database.
Recommended Articles
This is a guide to MySQL Show Users. Here we discuss an introduction to MySQL Show Users, usage of user-related data retrieval with query example. You can also go through our other related articles to learn more –
