Learn to use the MySQL SHOW USERS command to list users with various use cases like SHOW ALL USERS, SHOW CURRENT USER and more:
In MySQL, in order to SHOW USERS (unlike in other databases ex-Oracle, MSSQL), you can use mysql.user table to list down all the configured users for the MySQL server instance.
We will learn them with different examples of how you can get details of the currently logged-in users, how we can get the grants and privileges for different users etc.
=> Check ALL MySQL Tutorials Here
Table of Contents:
MySQL SHOW USERS
Syntax
SELECT {column-list} from mysql.user
Here
- mysql.user is a special System table that holds the details of all the users present in the given MySQL Server instance.
- {column-list} – column names to be retrieved from mysql.user schema.
To retrieve the schema or column list in mysql.user table, we can run DESCRIBE
DESCRIBE mysql.user;
You will see the output as shown below for the above command:
There are about 51 columns in this table that represent different properties with regards to the users in MySQL.
In a nutshell, these properties define:
- What level of access does the given user have?
- Which databases can the user access?
- When was the password changed?
- Has the password expired?
- What’s the authentication string?
- What’s the password encryption algorithm etc?
Examples
In this section, let’s see the various examples to list down the users.
SHOW ALL USERS
To display all the users for the current MySQL service instance, you can run the below query.
SELECT user, host FROM mysql.user;
Output looks like below
Please note that the users displayed in the above list are created by default for every new MySQL server instance.
Let’s try adding a new user and run the above command again.
To CREATE a new user named ‘test’ with Password as – ‘TestPassword’, you can run the below command
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'TestPassword'
Once this query is executed, a new user gets created for the MySQL Server instance.
Let’s try to execute the user listing query again.
SELECT user, host FROM mysql.user;
In the above output, you can see that the newly created user ‘test’ is now displayed in the user list.
SHOW CURRENT USER
In order to see the current user i.e. the user who is executing the query, we can make use of the user() function along with the SELECT query.
The command that gives the user details is
SELECT user();
From the above output, we can infer that the currently logged on user / or the user who ran this command was – ‘root’ user.
SHOW ALL LOGGED-IN USERS
At times, for Database administrators, it’s useful to see what all users are currently logged in for security or administration or monitoring purposes.
To do this, you can run the below command and fetch the list of users who are currently connected to the MySQL server instance.
SELECT * FROM information_schema.processlist;
If there is just one active session from a user, then you would see just one entry in the output.
Let’s try creating a new session, by connecting with the same user through the terminal or command prompt (For more details about using MySQL from the command prompt, you can refer to our tutorial here).
You can try running the same command again.
SELECT * FROM information_schema.processlist;
As you can see in the above output, we are now getting more logged-in sessions as we connected the same user through a different session on the terminal.
It’s important to note that there can be multiple sessions from a single user and there can be multiple user sessions simultaneously as well.
MySQL SHOW GRANTS FOR ALL USERS
As we discussed in the previous sections, mysql.user table has around 51 columns representing a lot of information for the given user.
GRANTS are nothing but the privileges that the user has been granted based on which they can perform different actions within the database. To display GRANTS for a user, we can use the SHOW GRANTS command.
Please note, that the SHOW GRANTS command requires the SELECT privilege for the MySQL Schema.
Let’s see an example to list down the grants for the current user.
SHOW GRANTS FOR CURRENT_USER();
Let’s create a new user and provide explicit grants for a test table.
We will create a user named emp_read_only
CREATE USER IF NOT EXISTS 'emp_read_only'@'localhost' IDENTIFIED BY 'TestPassword'
Let’s now create a schema named employee and a table named employee_names which contains just one column containing the names of the employees.
CREATE SCHEMA employee; CREATE TABLE employee.employee_names(emp_name VARCHAR(150));
Now, we will GRANT ALL privileges to the user emp_read_only for the newly created employee_names table.
GRANT ALL ON employee.employee_names TO 'emp_read_only'@'localhost';
Let’s now list the GRANTS for the newly created user.
SHOW GRANTS FOR 'emp_read_only'@'localhost';
In the above output, you can see that the GRANTS available to the emp_read_only user are limited to the ones that were explicitly assigned for the employee.employee_names table.
Frequently Asked Questions
Q #1) How do I see all the users in MySQL?
Answer: MySQL provides a system table named Mysql.user which stores information about all the users that have been registered with the server instance.
In order to query the user list, the current user must have the privilege to execute SELECT on the MySQL.user table.
The command used to query all the users is as shown below.
SELECT * FROM mysql.user;
Q #2) How do I show user permissions in MySQL?
Answer: In the Database world, permissions are stored as GRANTS for each user. GRANTS can be thought of as access rights to perform a particular operation. There can be different grants to one or multiple tables and different operations like SELECT, INSERT, DELETE, etc.
In order to query GRANTS for a given user, we can use a command with a fully qualified user name as given below:
SHOW GRANTS FOR 'username'@'localhost';
Conclusion
In this tutorial, we discussed how we can list down the USERS belonging to a given MySQL Server instance.
Depending on the need, we can query all the existing users within the system, the current user, and the list of users who are currently logged in (or have an active session) with the system.
These commands are typically executed by the Database administrators for monitoring and troubleshooting purposes.
=> Explore The Beginner’s MySQL Guide Here