mysqlcommand-linemysql5

How can I get a list of user accounts using the command line in MySQL?


I'm using the MySQL command-line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.


Solution

  • Use this query:

    SELECT User FROM mysql.user;
    

    Which will output a table like this:

    +-------+
    | User  |
    +-------+
    | root  |
    +-------+
    | user2 |
    +-------+
    

    An advanced and efficient answer is the use of a stored procedure to retrieve MySQL users because it simplifies this repeated query, improves readability, and makes it easier to manage access in a secure, reusable way. You type it once, then you only need to type: CALL mysql.users(); to retrieve your mysql users.

    USE mysql;
    DELIMITER $$
    CREATE PROCEDURE users()
    BEGIN
      SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) AS Users
      FROM mysql.user
      ORDER BY user, host;
    END $$
    DELIMITER ;
    CALL mysql.users();
    

    enter image description here