MySQL FAQ: How do I show/list MySQL users, i.e., the user accounts in a MySQL or MariaDB database?
Solution
To show the users in a MySQL database, first log into your MySQL server as an administrative user using the mysql command line client, then run this MySQL query:
mysql>
select * from mysql.user;
However, note that this query shows all of the columns from the
mysql.user table. This makes for a lot of output, so as a practical matter you may want to trim down some of the fields to display, something like this:
mysql>
select host, user, password from mysql.user;
The next section provides more details and information about this second query.
How to reduce the amount of ‘user’ information shown
You can get a listing of the fields in the mysql.user table by running this MySQL query:
mysql>
desc mysql.user;
On my current server this shows the following 37 columns of
MySQL user information, as shown here:
mysql>
desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.10 sec)
So for most cases where you want to show MySQL user accounts you'll probably want to limit your MySQL users' query to a few important columns, something like this:
select host, user, password from mysql.user;
In summary, if you need to list the users in a MySQL database, I hope this has been helpful.
How do I find MySQL username and password?
So for example, to show MySQL users' username, password and host, we'll modify the sql query to accordingly as such: mysql> select user, password, host from mysql. user; The above sql query will present you with a list of users and their respective user name, password and database host.
How do I find my MySQL username?
Try the CURRENT_USER() function. This returns the username that MySQL used to authenticate your client connection. It is this username that determines your privileges.
How can I see all user privileges in MySQL?
MySQL Show User Privileges.
Access to the command line/terminal. MySQL installed and configured. ... .
Locate the exact username and host for the next step. ... .
Without a hostname, the command checks for the default host '%' . ... .
The output prints a table with all the access privileges..