当前位置 : 首页 » 互动问答 » 正文

How to get a list of MySQL user accounts

分类 : 互动问答 | 发布时间 : 2009-07-16 11:23:53 | 评论 : 14 | 浏览 : 1370952 | 喜欢 : 1255

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.

回答(14)

  • 1楼
  • Use this query:

    SELECT User FROM mysql.user;
    

    Which will output a table like this:

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

    As Matthew Scharley points out in the comments on this answer, you can group by the User column if you'd only like to see unique usernames.

  • 2楼
  • I find this format the most useful as it includes the host field which is important in MySQL to distinguish between user records.

    select User,Host from mysql.user;
    
  • 3楼
  • A user account comprises the username and the host level access.

    Therefore, this is the query that gives all user accounts

    SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
    
  • 4楼
  • to avoid repetitions of users when they connect from different origin:

    select distinct User from mysql.user;
    
  • 5楼
  • If you are referring to the actual MySQL users, try:

    select User from mysql.user;
    
  • 6楼
  • MySQL stores the user information in its own database. The name of the database is MySQL. Inside that database, the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

    SELECT User, Host FROM mysql.user;
    
    +------------------+-----------+
    | User             | Host      |
    +------------------+-----------+
    | root             | localhost |
    | root             | demohost  |
    | root             | 127.0.0.1 |
    | debian-sys-maint | localhost |
    |                  | %         |
    +------------------+-----------+
    
  • 7楼
  • SELECT * FROM mysql.user;
    

    It's a big table so you might want to be more selective on what fields you choose.

  • 8楼
  • Login to mysql as root and type following query

    select User from mysql.user;
    
    +------+
    | User |
    +------+
    | amon |
    | root |
    | root |
    +------+
    
  • 9楼
  • The mysql.db table is possibly more important in determining user rights. I think an entry in it is created if you mention a table in the GRANT command. In my case the mysql.users table showed no permissions for a user when it obviously was able to connect and select, etc.

    mysql> select * from mysql.db;
    mysql> select * from db;
    +---------------+-----------------+--------+-------------+-------------+-------------+--------
    | Host          | Db              | User   | Select_priv | Insert_priv | Update_priv | Del...
    
  • 10楼
  • Peter and Jesse are correct but just make sure you first select the mysql DB.

    use mysql;

    select User from mysql.user;

    that should do your trick

  • 11楼
  • I use this to sort the users, so the permitted hosts are more easy to spot:

    mysql> SELECT User,Host FROM mysql.user ORDER BY User,Host;
    
  • 12楼
  • This displays list of unique users:

    SELECT DISTINCT User FROM mysql.user;
    
  • 13楼
  • $>  mysql -u root -p -e 'Select user from mysql.user' > allUsersOnDatabase.txt
    

    Executing this command on linux prompt will first ask for the password of mysql root user, on providing correct password it will print all the database users to the text file.

  • 14楼
  • I found his one more useful as it provides additional information about DML and DDL privileges

    SELECT user, Select_priv, Insert_priv , Update_priv, Delete_priv, 
           Create_priv, Drop_priv, Shutdown_priv, Create_user_priv 
    FROM mysql.user;
    

相关阅读:

MySQL date format DD/MM/YYYY select query?

Multiple select statements in Single query

How to access remote server with local phpMyAdmin client?

Grant **all** privileges on database

How to get a list of MySQL user accounts

How to get a list of MySQL user accounts

How can I prevent SQL injection in PHP?

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

'IF' in 'SELECT' statement - choose output value based on column values

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)