mysql如何查看授权账号

摘要

查看 MySQL 授权账户涉及以下步骤：

1. 使用具有适当权限的 MySQL 用户登录数据库。
2. 执行 SHOW GRANTS 语句以显示授予特定用户的权限。
3. 可选：执行 SELECT * FROM mysql.user 表以查看所有用户及其权限。

详细说明

使用 SHOW GRANTS

SHOW GRANTS 语句用于显示授予特定用户的权限。其语法如下：

SHOW GRANTS FOR [user_name]

其中 [user_name] 是您要查看其权限的用户名。

例如，要查看名为 "admin" 的用户的权限，您可以执行以下查询：

SHOW GRANTS FOR "admin";

这将输出类似以下内容的结果：

Grants for admin@localhost
GRANT USAGE ON *.* TO "admin"@"localhost"
GRANT SELECT ON `my_database`.* TO "admin"@"localhost"
GRANT INSERT ON `my_database`.`my_table` TO "admin"@"localhost"
...

使用 SELECT 语句

您还可以使用 SELECT 语句从 mysql.user 表中获取所有用户及其权限。该表的结构如下：

mysql> DESCRIBE mysql.user;
+-------------+-----------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| Host         | char(64)         | NO   | PRI |         |       |
| User         | char(16)         | NO   | PRI |         |       |
| Password     | char(41)         | NO   |     |         |       |
| Select_priv  | enum("N","Y")    | YES  |     | N       |       |
| Insert_priv  | enum("N","Y")    | YES  |     | N       |       |
| Update_priv  | enum("N","Y")    | YES  |     | N       |       |
| Delete_priv  | enum("N","Y")    | YES  |     | N       |       |
| Create_priv  | enum("N","Y")    | YES  |     | N       |       |
| Drop_priv    | enum("N","Y")    | YES  |     | N       |       |
| Grant_priv   | enum("N","Y")    | YES  |     | N       |       |
| References_priv | enum("N","Y")    | YES  |     | N       |       |
| Index_priv   | enum("N","Y")    | YES  |     | N       |       |
| Alter_priv   | enum("N","Y")    | YES  |     | N       |       |
| Show_db_priv | enum("N","Y")    | YES  |     | N       |       |
| Create_tmp_table_priv | enum("N","Y")    | YES  |     | N       |       |
| Lock_tables_priv | enum("N","Y")    | YES  |     | N       |       |
| Execute_priv | enum("N","Y")    | YES  |     | N       |       |
| Reload_priv  | enum("N","Y")    | YES  |     | N       |       |
| Process_priv | enum("N","Y")    | YES  |     | N       |       |
| Show_view_priv | enum("N","Y")    | YES  |     | N       |       |
| Create_routine_priv | enum("N","Y")    | YES  |     | N       |       |
...

要查看所有用户及其权限，您可以执行以下查询：

SELECT * FROM mysql.user;

这将输出类似以下内容的结果：

+-------+------+----------------------------------------------+
| Host   | User | Password                                      |
+-------+------+----------------------------------------------+
| %      | root  | *411AA8317590E147E82B3C1FD12663E2C511B591 |
| localhost | mysql | *3F0D6932F86126AE4D380A800C8AC8AD96E6A80F |
+-------+------+----------------------------------------------+

您还可以使用 WHERE 子句过滤结果，例如：

SELECT * FROM mysql.user WHERE Host = "localhost";

这将仅显示 localhost 上的用户及其权限。

以上就是mysql如何查看授权账号的详细内容，更多请关注编程网其它相关文章！