This tutorial shows you how to list all users in a MySQL database server. In MySQL, users are stored in the user<\/code> table in a special database called mysql<\/code>.<\/p>\n\n\n\n
MySQL<\/a> has special commands that allow you to list databases and tables. Users’ information on the other hand is handled just like any other data stored in the database<\/a>.<\/p>\n\n\n\n
List all Users in MySQL<\/h2>\n\n\n\nWith the above information, we can see that all we need is a SELECT<\/code> query as shown here:<\/p>\n\n\n\n
SELECT \n user \nFROM \n mysql.user;<\/code><\/pre>\n\n\n\nThe output will look something similar to this.<\/p>\n\n\n\n
+----------------+\n| user |\n+----------------+\n| nate |\n| mysql.sesssion |\n| mysql.sys |\n| root |\n+----------------+\n4 rows in set (0.00 sec) <\/code><\/pre>\n\n\n\nFrom the response, we can see that there are four users in this MySQL database server.<\/p>\n\n\n\n
In most cases, you might want to look at more information other than just the users’ names. For that you will need to get the columns list using the following command:<\/p>\n\n\n\n
mysql> DESC mysql.user;<\/code><\/pre>\n\n\n\nWith the following as the result.<\/p>\n\n\n\n
+------------------------+-----------------------------------+------+-----+-----------------------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+------------------------+-----------------------------------+------+-----+-----------------------+-------+\n| Host | char(60) | NO | PRI | | |\n| User | char(32) | NO | PRI | | |\n| Select_priv | enum('N','Y') | NO | | N | |\n| Insert_priv | enum('N','Y') | NO | | N | |\n| Update_priv | enum('N','Y') | NO | | N | |\n| Delete_priv | enum('N','Y') | NO | | N | |\n| Create_priv | enum('N','Y') | NO | | N | |\n| Drop_priv | enum('N','Y') | NO | | N | |\n| Reload_priv | enum('N','Y') | NO | | N | |\n| Shutdown_priv | enum('N','Y') | NO | | N | |\n| Process_priv | enum('N','Y') | NO | | N | |\n| File_priv | enum('N','Y') | NO | | N | |\n| Grant_priv | enum('N','Y') | NO | | N | |\n| References_priv | enum('N','Y') | NO | | N | |\n| Index_priv | enum('N','Y') | NO | | N | |\n| Alter_priv | enum('N','Y') | NO | | N | |\n| Show_db_priv | enum('N','Y') | NO | | N | |\n| Super_priv | enum('N','Y') | NO | | N | |\n| Create_tmp_table_priv | enum('N','Y') | NO | | N | |\n| Lock_tables_priv | enum('N','Y') | NO | | N | |\n| Execute_priv | enum('N','Y') | NO | | N | |\n| Repl_slave_priv | enum('N','Y') | NO | | N | |\n| Repl_client_priv | enum('N','Y') | NO | | N | |\n| Create_view_priv | enum('N','Y') | NO | | N | |\n| Show_view_priv | enum('N','Y') | NO | | N | |\n| Create_routine_priv | enum('N','Y') | NO | | N | |\n| Alter_routine_priv | enum('N','Y') | NO | | N | |\n| Create_user_priv | enum('N','Y') | NO | | N | |\n| Event_priv | enum('N','Y') | NO | | N | |\n| Trigger_priv | enum('N','Y') | NO | | N | |\n| Create_tablespace_priv | enum('N','Y') | NO | | N | |\n| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |\n| ssl_cipher | blob | NO | | NULL | |\n| x509_issuer | blob | NO | | NULL | |\n| x509_subject | blob | NO | | NULL | |\n| max_questions | int(11) unsigned | NO | | 0 | |\n| max_updates | int(11) unsigned | NO | | 0 | |\n| max_connections | int(11) unsigned | NO | | 0 | |\n| max_user_connections | int(11) unsigned | NO | | 0 | |\n| plugin | char(64) | NO | | mysql_native_password | |\n| authentication_string | text | YES | | NULL | |\n| password_expired | enum('N','Y') | NO | | N | |\n| password_last_changed | timestamp | YES | | NULL | |\n| password_lifetime | smallint(5) unsigned | YES | | NULL | |\n| account_locked | enum('N','Y') | NO | | N | |\n+------------------------+-----------------------------------+------+-----+-----------------------+-------+\n45 rows in set (0.03 sec)<\/code><\/pre>\n\n\n\nShow the Current User<\/h2>\n\n\n\nTo get the details about the current user, you can use the user()<\/code> function as shown here:<\/p>\n\n\n\n
mysql> SELECT user();\n+-----------------+\n| user() |\n+-----------------+\n| nate@localhost |\n+-----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\nThe same results can be achieved using the current_user()<\/code> function.<\/p>\n\n\n\n
mysql> SELECT current_user();\n+-----------------+\n| current_user() |\n+-----------------+\n| nate@localhost |\n+-----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\nShow Current Logged Users<\/h2>\n\n\n\nYou can list all users that are currently logged in to the MySQL database server with the following statement:<\/p>\n\n\n\n
SELECT \n user, \n host, \n db, \n command \nFROM \n information_schema.processlist;<\/code><\/pre>\n\n\n\nThe result will be something similar to this.<\/p>\n\n\n\n
+------+-----------------+-------+---------+\n| user | host | db | command |\n+------+-----------------+-------+---------+\n| root | localhost:58679 | NULL | Query |\n| nate | localhost:65338 | dbpos | Sleep |\n+------+-----------------+-------+---------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\nYou can see the users root<\/code> and nate<\/code> are signed into the MySQL database server. One of the users is using dbpos<\/code> database but not executing any query (sleep) while root<\/code> user is not using any specific database but is running a query.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\nHere, you have learned how to list all users in a MySQL database server by querying data from the user table in the mysql <\/code>database. You can find out more about MySQL from the official vendor site here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"