Home » Learn – MySQL Show Users

Learn – MySQL Show Users

Learn – MySQL Show Users

You may need to administer a database in MySQL on occasion. In such instance, we’ll need access to a database that has a list of all user accounts. Most of the time, we presume that a command like SHOW DATABASES, SHOW TABLES, etc. exists to display a list of all users available on the database server. Unfortunately, there is no SHOW USERS command in the MySQL database to display a list of all users on the MySQL server. To obtain a list of all users in the database server, use the following query.

mysql > Select user from mysql.user;

Following the successful execution of the above line, we will retrieve user data from the MySQL database server’s user table.
Let’s take a look at how we can use this query. To begin, we must use the mysql client tool to connect to the MySQL server and log in as an administrator to the server database. Run the following command:

mysql -u root -p
Enter password:
*********

mysql > use mysql;
Database changed

mysql > SELECT user FROM user;

Let’s take a look at how we can use this query. To begin, we must use the mysql client tool to connect to the MySQL server and log in as an administrator to the server database. Run the following command:
We’ll see the five users in our local database in the following output:
Execute the command below to see more information on the user table.

mysql > DESC user;

It will give the following output that lists all the available columns of the mysql.user database:

To get the selected information like as hostname, password expiration status, and account locking, execute the query as below

mysql > SELECT user, host, account_locked, password_expired FROM user;

After the successful execution, it will give the following output:
Show Current UserWe can get information of the current user by using the user() or current_user() function, as shown below

mysql > Select user();
or,
mysql > Select current_user();

For SQL Server related user creation and lockout visit https://sqlserver-dba.co.uk site