【MYSQL】查看mysql的所有用户和权限

最新推荐文章于 2024-11-12 12:30:00 发布

hustxiaoxian

最新推荐文章于 2024-11-12 12:30:00 发布

阅读量1k

点赞数

分类专栏： MYSQL 文章标签： mysql php

MYSQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何在MySQL中查看所有用户及特定用户的权限，并展示了如何查询user表的结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、查看mysql数据库中的所有用户：

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;       
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'root'@'127.0.0.1';             |
| User: 'root'@'::1';                   |
| User: 'debian-sys-maint'@'localhost'; |
| User: 'root'@'localhost';             |
| User: 'root'@'ubuntu';                |
+---------------------------------------+
5 rows in set (0.01 sec)

2、查看数据库中具体某个用户的权限。

mysql> show grants for 'root'@'localhost';

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*717A8D268FB37A6FFE2A4E037D9B3895EA558E50' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

另外的方式：

mysql> select * from mysql.user where user='root' \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *717A8D268FB37A6FFE2A4E037D9B3895EA558E50
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y

3、查看user表的表结构。需要具体的项可以根据表的结构来查询。

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                     |       |