MySQL: Show Users, Privileges and Passwords

In this article i will show how to list MySQL users, their passwords and granted privileges from the command-line prompt.

MySQL account consists of two components: user and host.

This allows the same user to use different MySQL accounts with different privileges, depending on which host they are connecting from.

In the Host field, besides of the common IP addresses and host names, you can see the % sign, that is a wildcard character that means “any” host.

Important Notice: The % character does not include the localhost, as the localhost means a connection over a UNIX socket instead of a standard TCP/IP.

Show Users In MySQL

Show all MySQL users:

mysql> SELECT user FROM mysql.user;

List only unique user names:

mysql> SELECT DISTINCT user FROM mysql.user;

Show MySQL users and hosts they are allowed to connect from:

mysql> SELECT user,host FROM mysql.user;

Show MySQL users, their passwords and hosts:

mysql> SELECT user,host,password FROM mysql.user;

in MySQL 5.7 and higher:

mysql> SELECT host,user,authentication_string FROM mysql.user;

Cool Tip: Need to change MySQL user password? This can be easily done from the command-line prompt! Read more →

Show User Privileges In MySQL

In MySQL, you can use the SHOW GRANTS command to show privileges granted to a user.

Without any additional parameters, the SHOW GRANTS command lists the privileges granted to the current user account with which you have connected to the server.

The SHOW GRANTS requires the SELECT privilege for the mysql database, except to see the privileges for the current user, so if you have such privilege you can also list the privileges granted to the other MySQL users.

Cool Tip: Create a MySQL database and GRANT ALL PRIVILEGES on it to a user! Simple and clear MySQL tutorial with good examples! Read more →

Show privileges granted to the current MySQL user:


Show privileges granted to the MySQL user (if you don’t specify a host for the user name, MySQL assumes % as the host):

mysql> SHOW GRANTS FOR 'user_name';

Show privileges granted to a particular MySQL user account from a given host:

mysql> SHOW GRANTS FOR 'user_name'@'host';

– e.g. –

mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> SHOW GRANTS FOR 'root'@'%';
mysql> SHOW GRANTS FOR 'admin'@'';
Was it useful? Share this post with the world!

10 Replies to “MySQL: Show Users, Privileges and Passwords”

  1. Or you could just in install percona toolkit and then run pt-show-grants.
    This is a solved problem.

    1. Yeap, u’re right. pt-show-grants shows MySQL users, passwords and grants nicely. But as you have mentioned it requires installation of percona-toolkit while the above commands work out of the box.

  2. When I run the given Query in MySQL version of 8.0
    —> select user,host,password from user;
    The error is generated
    —> ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
    How to solve this.

    1. List out what columns are available to select from with the following query:
      SHOW columns FROM mysql.user;
      Ensure your requested columns shown in the list.

  3. MySQL versions 5.7 or higher doesnt have a column named password in mysql.user table. MySQL versions 5.7 or higher use the column authentication_string instead.

    So you query should be:

    mysql> SELECT host, user, authentication_string FROM mysql.user;

    Source Raymond Nijland:

    1. Thanks. Updating the article.

  4. How Create User in MySQL?

    1. this is pretty late but could help someone else:

      CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
  5. Andrew Androsow says: Reply

    SELECT host,user,authentication_string FROM mysql.user;
    There is a hash , a password can`t save as a raw string – because save a raw password is not safe at all.

  6. how to convert hexadecimal code to decimal

Leave a Reply