目录：

• • • MySQL——用户及权限管理
    • • 1、创建用户
      • 2、删除用户
      • 3、查询用户
      • 4、用户授权
      • 5、查看权限
      • 6、用户登录
      • 7、查系统表

MySQL——用户及权限管理

Mysql权限一览表：

mysql里的用户以USER_NAME@HOST唯一确定一个用户，所以在创建用户和赋权时需要指定host，如果省略，则默认为%（无访问地址限制）

注意：host实现同pg_hba.conf的addres功能

1、创建用户

mysql>  create user test@'localhost' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)

2、删除用户

drop user test@'localhost';

3、查询用户

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

4、用户授权

语法：

GRANT priv_type  ON [object_type] priv_level TO user [WITH GRANT OPTION]

回收权限使用revoke from 关键字其他同授予权限

mysql> grant select on mysql.user to test@'localhost';
Query OK, 0 rows affected (0.00 sec)

#可以使用user.*来授权
mysql> grant select on mysql.* to test@'localhost';
Query OK, 0 rows affected (0.00 sec)

5、查看权限

查看某一个用户权限：

show grants for USER_NAME@HOST

mysql> show grants for test@'localhost';
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'             |
| GRANT SELECT ON `mysql`.* TO 'test'@'localhost'      |
| GRANT SELECT ON `mysql`.`user` TO 'test'@'localhost' |
+------------------------------------------------------+
3 rows in set (0.00 sec)

查看当前session登录用户的权限：

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

也可以从以下系统表查看：

• 查看mysql.user表 ---- 查看全局所有库的权限
• 查看mysql.db表 ---- 查看指定库的权限
• 查看mysql.table_priv表 ---- 查看指定表的权限
• 查看mysql.column_priv表 ---- 查看指定列的权限

更多用户及权限信息请参阅：官方文档

生产环境一般如下赋权：

6、用户登录

# mysql -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.38 MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)mysql> 

7、查系统表

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)