MySQL-用户与权限管理

Etaon

已于 2022-02-19 20:59:40 修改

阅读量930

点赞数 1

文章标签： mysql 数据库

于 2022-02-17 14:23:32 首次发布

本文链接：https://blog.csdn.net/weixin_43394724/article/details/122982491

版权

本文档为学习【宋红康】MySQL数据库（mysql安装/基础/高级/优化）笔记

用户管理

MySQL用户可以分为普通用户和root用户。
root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；
普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
MySQL数据库的安全性需要通过账户管理来保证。

登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

mysql －h hostname | hostIP －P port －u username －p DatabaseName －e ＂SQLB＂

参数：

h参数后面接主机名或者主机IP， hostname为主机， hostIP为主机IP。
P参数后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306
p参数是使用密码，后面可以直接跟密码，也可以不跟，系统会询问
e参数是使用SQL命令

创建用户

在MySQL数据库中，官方推荐使用CREATE USER 语句创建新用户。
MySQL 8版本移除了PASSWORD加密方法，因此不再推荐使用INSERT语句直接操作MySQL中的user表来增加用户。
使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。
每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限。
如果添加的账户已经存在，CREATE USER语句就会返回一个错误。

CREATE USER语句的基本语法形式如下：

CREATE USER 用户名［IDENTIFIED BY＇密码＇］［，用户名［IDENTIFIED BY＇密码＇］］；

用户名参数表示新建用户的账户，由用户（User）和主机名（Host）构成；
“［］”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。
如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
CREATE USER语句可以同时创建多个用户。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zyi              |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

使用root用户登录，查看mysql数据库的user表

mysql> create user 'apple' identified by 'VMware1!';
Query OK, 0 rows affected (0.13 sec)

mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | apple            |
| %         | root             |
| %         | zyi              |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
6 rows in set (0.00 sec)

创建apple用户，host一栏是%，表示所有host

mysql> create user 'apple' identified by 'VMware1!';
ERROR 1396 (HY000): Operation CREATE USER failed for 'apple'@'%'
mysql> create user 'apple'@'localhost' identified by 'VMware1!';
Query OK, 0 rows affected (0.04 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | apple            |
| %         | root             |
| %         | zyi              |
| localhost | apple            |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
7 rows in set (0.00 sec)

再次创建apple用户失败，提示：failed for ‘apple’@’%’；
这里的用户是和作用host相关联，即host和user是联合主键；

再次创建apple用户并指定localhost主机，成功

建议创建用户的时候把host范围写明

使用apple用户登录

[root@Alma ~]# mysql -uapple -pVMware1!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.03 sec)

mysql> show grants;
+-------------------------------------------+
| Grants for apple@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `apple`@`localhost` |
+-------------------------------------------+
1 row in set (0.03 sec)

可以看到是没权限的

修改用户

修改用户名：很少使用，修改user表的字段

UPDATE mysql.user SET USER='1i4' WHERE USER='wang5' ;
FLUSH PRIVILEGES;

mysql>_update-mysql.user set user='li4' where user='wang5';
Query OK. 1 row affected (0.27 sec)
Rows matched:1 Changed:1 Warnings:0

删除用户

在MySQL数据库中，可以使用DROP USER 语句来删除普通用户，也可以直接在mysql.user表中删除用户。

方式1：使用DROP方式删除（推荐）
使用DROP USER语句来删除用户时，必须用于DROP USER权限。
DROP USER语句的基本语法形式如下：

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zyi              |
| localhost | apple            |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> drop user 'apple';
ERROR 1396 (HY000): Operation DROP USER failed for 'apple'@'%'
mysql> drop user 'apple'@'localhost';
Query OK, **0 rows affected** (0.04 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zyi              |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

用户默认是’user_name’@’%’
返回OK，0 rows affected
不用执行FLUSH PRIVILEGES;
方式2：操作user数据库
可以使用DELETE语句直接将用户的信息从mysql.user表中删除，但必须拥有对mysql.user表的DELETE权限；
DELETE语句的基本语法形式如下：

DELETE FROM mysql.user WHERE Host=＇hostname＇ AND User=＇username＇；
#Host字段和User字段是user表的联合主键，因此两个字段的值才能唯一确定一条记录。
#执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES:

举例：

DELETE FROM mysql.user WHERE Host=＇localhost＇ AND User=＇Emily＇；
FLUSH PRIVILEGES:

注意：不推荐通过*DELETE FROM USER u WHERE USER＝＇1i4＇*进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

设置当前用户密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。
root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，

使用***ALTER USER***修改用户密码是MySQL官方推荐的方式。此外，也可以通过***SET语句***修改密码。

由于MySQL8中已移除了PASSWORD（）函数，因此不再使用UPDATE语句直接操作用户表修改密码。

方式1：使用ALTER USER命令来修改当前用户密码（推荐）
用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。
基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

练习：下面使用ALTER命令来修改root用户的密码，将密码改为“Hello_1234”。

命令如下：

ALTER USER USER() IDENTIFIED BY 'He11o_1233';

#root修改自己的password
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter user user() identified by 'VMware123!';
Query OK, 0 rows affected (0.03 sec)

方式2：使用SET语句来修改当前用户密码
具体SQL语句如下：

SET PASSWORD='new_password';
#该语句会自动将密码加密后再赋给当前用户。

练习：下面使用SET语句来修改root用户的密码，将密码改为“Hello_1234”。
SET语句具体如下：

SET PASSWORD='He11o_1234';

#root修改自己的password
mysql> set password='VMware1!VMware1!';
Query OK, 0 rows affected (0.01 sec)

修改其它用户密码

方法1：使用Alter命令
基本语法如下：

ALTER USER 'user_name'@'host' IDENTIFIED BY 'new_password';

修改以后不影响用户的当前登录

user表中的authentication_string字段记录密码：

mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | boy              | $A$005$]Sn"dZ@,,PY) <2
                                                       uWgyC.BpkYaRwBzx4q7JOfwO9U/XdxseFc8v9hsOkm6 |
| %         | root             | *16907081F70B954E79743DA4F785043069E9D33F                              |
| %         | zyi              | $A$005$
                                        @Otz\(~!ipN.iODobuhjloqmoFeY0wqsGS1xTPgJK7qZnpAyt09r31POWB |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

方法2：使用set命令
使用root用户登录MySQL后，可以使用SET语句来修改其他用户密码
具体SQL语句如下：

SET PASSWORD for 'username'%'host'='new_password';
#该语句会自动将密码加密后再赋给用户。

mysql> set password for 'boy'@'%'='VMware123!';
Query OK, 0 rows affected (0.03 sec)

方法3：使用UPDATE语句修改普通用户的密码**（不推荐）**
使用root用户登录MySQL服务器后，可以使用UPDATE语句修改MySQL数据库的user表的password字段，从而修改普通用户的密码。
使用UPDATA语句修改用户密码的语法如下：

UPDATE MySQL.user SET authentication_string=PASSWORD（＂123456＂）
WHERE User=＂username＂ AND Host=＂hostname＂；
FLUSH PRIVILEGES:

需要使用FLUSH PRIVILEGES:

MySQL8密码管理（了解）

MySQL中记录使用过的历史密码，目前包含如下密码管理功能：
（1）密码过期：要求定期修改密码。
（2）密码重用限制：不允许使用旧密码。
（3）密码强度评估：要求使用高强度的密码。

提示

MySQL密码管理功能只针对使用基于MySQL授权插件的账号，这些插件有mysql_native_password、 sha256_password和caching_sha2_password。

密码过期策略

在MySQL中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略。
过期策略可以是全局的，也可以为每个账号设置单独的过期策略。
手动设置立马过期
手动设置账号密码过期，可使用如下语句：

ALTER USER user PASSWORD EXPIRE；

练习：将用户kangshifu账号的密码设置为过期，SQL语句如下

ALTER USER 'zyi'@'localhost' PASSWORD EXPIRE；

该语句将用户kangshifu的密码设置为过期，kangshifu用户仍然可以登录进入数据库，但无法查询，只有重新设置了新密码，才能正常使用。

mysql>show databases；
ERROR 1820（HY000）:You must reset your password using ALTER USER statement before executing this statement.手动设置指定时间过期方式1:全局

如果密码使用的时间大于允许的时间，服务器会自动设置为过期，不需要手动设置。
MySQL使用default_password_lifetime系统变量建立全局密码过期策略。

手动设置指定时间过期方式：全局
如果密码使用的时间大于允许的时间，服务器会自动设置为过期，不需要手动设置。
MySQL使用default_password_lifetime系统变量建立全局密码过期策略。（配置文件）
- 它的默认值是0，表示禁用自动密码过期。
- 它允许的值是正整数N，表示允许的密码生存期。即密码必须每隔N天进行修改。
两种实现方式分别如图所示：
方式①：使用SQL语句更改该变量的值并持久化
```
SET PERSIST default_password_lifetime ＝180；
＃建立全局策略，设置密码每隔180天过期
```
```
mysql> SET PERSIST default_password_lifetime = 180；
Query OK， O rows affected （0.00 sec）
```
方式②：配置文件my.cnf中进行维护
```
［mysqld］
default_password_lifetime＝180 ＃建立全局策略，设置密码每隔180天过期
```
手动设置指定时间过期：单独设置
每个账号既可延用全局密码过期策略，也可单独设置策略。
在CREATE USER和ALTER USER 语句上加入PASSWORD EXPIRE选项可实现单独设置策略。下面是一些语句示例。

＃设置zyi账号密码每90天过期：
CREATE USER 'zyi'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'zyi'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
＃设置密码永不过期：
CREATE USER 'zyi'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'zyi'@'localhost'PASSWORD EXPIRE NEVER;
＃延用全局密码过期策略：
CREATE USER 'zyi'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'zyi'@'localhost' PASSWORD EXPIRE DEFAULT;

密码重用策略

MySQL限制使用已用过的密码。重用限制策略基于密码更改的数量和使用的时间。
重用策略可以是全局的，也可以为每个账号设置单独的策略。

账号的历史密码包含过去该账号所使用的密码。
MySQL基于以下规则来限制密码重用：

如果账号的密码限制基于密码更改的数量，那么新密码不能从最近限制的密码数量中选择。例如，如果密码更改的最小值为3，那么新密码不能与最近3个密码中任何一个相同。
如果账号密码限制基于时间，那么新密码不能从规定时间内选择。
例如，如果密码重用周期为60天，那么新密码不能从最近60天内使用的密码中选择。
MySQL使用password_history和password_reuse_interval系统变量设置密码重用策略
- opassword_history：规定密码重用的数量
- opassword_reuse_interval：规定密码重用的周期
- 这两个值可在服务器的配置文件中进行维护，也可在运行期间使用SQL语句更改该变量的值并持久化。
手动设置密码重用方式：全局
方式①：使用SQL

SET PERSIST password_history=6;
＃设置不能选择最近使用过的6个密码

SET PERSIST password_reuse_interval=365;
＃设置不能选择最近一年内的密码

mysql> SET PERSIST password_history=6;
Query OK, O rows affected (0.00 sec)
mysql> SET PERSIST password_reuse_interval = 365;
Query OK, O rows affected (0.00 sec)

方式②：my.cnf配置文件

[mylsqld]
password_istory=6
password_reuse_interval=365

手动设置密码重用方式：单独设置
每个账号可以延用全局密码重用策略，也可单独设置策略。
这两个选项可以单独使用，也可以结合在一起使用。
下面是一些语句示例。

＃不能使用最近5个密码：
CREATE USER 'zyi'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'zyi'@'localhost' PASSWORD HISTORY 5;

＃不能使用最近365天内的密码：
CREATE USER 'zyi'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

＃既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'zyi'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

ALTER USER 'zyi'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

＃延用全局策略
CREATE USER 'zyi'@'localhost'
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT:

ALTER USER 'zyi'@'localhost'
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT:

权限管理

关于MySQL的权限简单的理解就是MySQL允许你做你权力以内的事情，不可以越界。

比如只允许你执行SELECT操作，那么你就不能执行UPDATE操作。
只允许你从某台机器上连接MySQL，那么你就不能从除那台机器以外的其他机器连接MySQL。

权限列表

mysql> show privileges;
+-----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                   | Context                               | Comment                                               |
+-----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                       | Tables                                | To alter the table                                    |
| Alter routine               | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                      | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine              | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                 | Server Admin                          | To create new roles                                   |
| Create temporary tables     | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                 | Tables                                | To create new views                                   |
| Create user                 | Server Admin                          | To create new users                                   |
| Delete                      | Tables                                | To delete existing rows                               |
| Drop                        | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                   | Server Admin                          | To drop roles                                         |
| Event                       | Server Admin                          | To create, alter, drop and execute events             |
| Execute                     | Functions,Procedures                  | To execute stored routines                            |
| File                        | File access on server                 | To read and write files on the server                 |
| Grant option                | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                       | Tables                                | To create or drop indexes                             |
| Insert                      | Tables                                | To insert data into tables                            |
| Lock tables                 | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                     | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                       | Server Admin                          | To make proxy user possible                           |
| References                  | Databases,Tables                      | To have references on tables                          |
| Reload                      | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client          | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave           | Server Admin                          | To read binary log events from the master             |
| Select                      | Tables                                | To retrieve rows from table                           |
| Show databases              | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                   | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                    | Server Admin                          | To shut down the server                               |
| Super                       | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                     | Tables                                | To use triggers                                       |
| Create tablespace           | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                      | Tables                                | To update existing rows                               |
| Usage                       | Server Admin                          | No privileges - allow connect only                    |
| SHOW_ROUTINE                | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER         | Server Admin                          |                                                       |
| REPLICATION_APPLIER         | Server Admin                          |                                                       |
| PASSWORDLESS_USER_ADMIN     | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ENABLE      | Server Admin                          |                                                       |
| XA_RECOVER_ADMIN            | Server Admin                          |                                                       |
| GROUP_REPLICATION_STREAM    | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN     | Server Admin                          |                                                       |
| FLUSH_USER_RESOURCES        | Server Admin                          |                                                       |
| FLUSH_TABLES                | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN  | Server Admin                          |                                                       |
| ROLE_ADMIN                  | Server Admin                          |                                                       |
| BACKUP_ADMIN                | Server Admin                          |                                                       |
| CONNECTION_ADMIN            | Server Admin                          |                                                       |
| SET_USER_ID                 | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN     | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN        | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE     | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN     | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN     | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN      | Server Admin                          |                                                       |
| SYSTEM_USER                 | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN  | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN      | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN    | Server Admin                          |                                                       |
| AUDIT_ADMIN                 | Server Admin                          |                                                       |
| AUTHENTICATION_POLICY_ADMIN | Server Admin                          |                                                       |
| BINLOG_ADMIN                | Server Admin                          |                                                       |
| ENCRYPTION_KEY_ADMIN        | Server Admin                          |                                                       |
| CLONE_ADMIN                 | Server Admin                          |                                                       |
| FLUSH_OPTIMIZER_COSTS       | Server Admin                          |                                                       |
| FLUSH_STATUS                | Server Admin                          |                                                       |
+-----------------------------+---------------------------------------+-------------------------------------------------------+
65 rows in set (0.02 sec)

（1）CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
（2） SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。
（3）SELECT权限只有在它们真正从一个表中检索行时才被用到。
（4）INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
（5）ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。
（6）CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
（7）GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
（8）FILE权限使用户可以使用LOAD DATA INFILE和SELECT…INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

MySQL的权限如何分布

权限分布	可能的设置的权限
表权限	＇Select＇，＇Insert＇，＇Update＇，＇Delete＇，＇Create＇，＇Drop＇，＇Grant＇，＇References＇，＇Index＇，＇Alter＇
列权限	＇Select＇，＇Insert＇，＇Update＇，＇References＇
过程权限	＇Execute＇，＇Alter Routine＇，＇Grant＇

授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

只授予能满足需要的最小权限，防止用户干坏事。
比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段
为每个用户设置满足密码复杂度的密码。
定期清理不需要的用户，回收权限或者删除用户。

授予权限

给用户授权的方式有2种，分别是通过角色赋予用户给用户授权和直接给用户授权。

授权命令：

GRANT 权限1，权限2，...权限n ON 数据库名称.表名称 TO 用户名@用户host
[IDENTIFIED BY '密码口令'];

如果该权限如果发现没有该用户，则会直接新建一个用户。
```
GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO zyi@localhost;
```

示例：

mysql> grant select on *.* to 'boy'@'%';# root用户操作
Query OK, 0 rows affected (0.03 sec)

#以下为boy操作
mysql> show grants;
+---------------------------------+
| Grants for boy@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `boy`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------+
| Grants for boy@%                 |
+----------------------------------+
| GRANT SELECT ON *.* TO `boy`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

参数解释：

GRANT ALL PRIVILEGES ON *.* TO joi@'%' IDENTIFIED BY 'Cisco123!@#';

ALL PRIVILEGES是表示所有权限，也可以使用SELECT、UPDATE等具体权限。

ON用来指定权限针对哪些库和表。
TO表示将权限赋予某个用户。
joi@＇localhost＇表示joi用户，＠后面接限制的主机，可以是IP、IP段、域名以及％，％表示任何地方。注意：这里％有的版本不包括本地，以前碰到过给某个用户设置了％允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
IDENTIFIED BY指定用户的登录密码。
授予通过网络方式登录的joi用户，对所有库所有表的全部权限，密码设为Cisco123!@#。
注意这里唯独不包括grant的权限
如果需要赋予包括GRANT的权限，添加参数“WITH GRANT OPTION”，表示该用户可以将自己拥的权限授权给别人。

mysql> grant all privileges on *.* to 'zyi'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个SELECT权限，然后又给用户添加一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。

所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是删除。

查看权限

查看当前用户权限

SHOW GRANTS;
＃或
SHOW GRANTS FOR CURRENT_USER;
＃或
SHOW GRANTS FOR CURRENT_USER();

查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机host';I

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。

MySQL中使用***REVOKE语句***取消用户的某些权限。
使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。

删除用户前，应该回收其权限

REVOKE 权限1,权限2... ON 数据库名.表名 FROM 用户@主机host;

mysql> revoke update on *.* from 'boy'@'%';
Query OK, 0 rows affected (0.03 sec)

#boy登录
mysql> show grants;
+--------------------------------------------------+
| Grants for boy@%                                 |
+--------------------------------------------------+
| GRANT SELECT, SHOW DATABASES ON *.* TO `boy`@`%` |
+--------------------------------------------------+
1 row in set (0.00 sec)

权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。
MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、column_priv表和proc_priv表等。
在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.03 sec)

User表：用户和其权限的操作控制

mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | boy              | $A$005$u
(gMP.Z{t?t.5obNyrzMtF8hxXUGB95QvqLrf2Z9nsJrFdhnkrzsD7eWU. |
| %         | root             | *16907081F70B954E79743DA4F785043069E9D33F                              |
| %         | zyi              | $A$005$
                                        @Otz\(~!ipN.iODobuhjloqmoFeY0wqsGS1xTPgJK7qZnpAyt09r31POWB |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+------------------------------------------------------------------------+
6 rows in set (0.02 sec)

可以看到在user表中存储了用户的名字，主机登录范围和加密password等。

mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| 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                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.03 sec)

user表结构，可以看到，大多数地段默认都是No
host和user是联合组件

| Host                          | NO   | PRI |                       |       |
| User                          | NO   | PRI |

db表：数据库有关的权限

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(255)     | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| 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       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

联合主键：user+host+db

**tables_priv：**表相关权限

mysql> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                                         |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                                                                                                         | NO   | PRI |                   |                                               |
| Db          | char(64)                                                                                                                          | NO   | PRI |                   |                                               |
| User        | char(32)                                                                                                                          | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                                                                                                          | NO   | PRI |                   |                                               |
| Grantor     | varchar(288)                                                                                                                      | NO   | MUL |                   |                                               |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                                               |
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   |     |                   |                                               |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.03 sec)

联合主键：user+host+db+table_name

**columns_priv：**表中的列权限

mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                                         |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host        | char(255)                                    | NO   | PRI |                   |                                               |
| Db          | char(64)                                     | NO   | PRI |                   |                                               |
| User        | char(32)                                     | NO   | PRI |                   |                                               |
| Table_name  | char(64)                                     | NO   | PRI |                   |                                               |
| Column_name | char(64)                                     | NO   | PRI |                   |                                               |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                                               |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.03 sec)

联合主键：user+host+db+table_name+column_name

访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作，当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程称为MySQL中的访问控制过程。
MySQL的访问控制分为两个阶段：连接核实阶段和请求核实阶段。

阶段1：连接核实阶段

当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码。

MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。

服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

阶段2：请求核实阶段

一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。

对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。
这些权限可以来自user、db、table_priv和column_priv表。

确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表。

db表是下一安全层级，其中的权限限定于数据库层级。
比如：在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；

如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及 columns_priv表。

如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。请求核实的过程如图所示。

在这里插入图片描述

角色管理

角色的理解-8.0新特性

引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

在这里插入图片描述

创建角色
给角色赋权
绑定用户和角色（赋予和激活）

创建角色

#语法如下：
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略，不可为空。
练习：我们现在需要创建一个经理-manager的角色，就可以用下面的代码：

mysql> create role 'manager'@'localhost';
Query OK, 0 rows affected (0.01 sec)

5.3 给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述语句中privileges代表权限的名称，多个权限以逗号隔开。

可使用SHOW语句查询权限名称

mysql> show privileges\G;
*************************** 1. row ***************************
Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
  Context: Databases,Tables,Indexes
  Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
  Context: Databases
  Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create role
  Context: Server Admin
  Comment: To create new roles
*************************** 6. row ***************************
Privilege: Create temporary tables
  Context: Databases
  Comment: To use CREATE TEMPORARY TABLE
*************************** 7. row ***************************
Privilege: Create view
  Context: Tables
  Comment: To create new views
*************************** 8. row ***************************
Privilege: Create user
  Context: Server Admin
  Comment: To create new users
*************************** 9. row ***************************
Privilege: Delete
  Context: Tables
  Comment: To delete existing rows
*************************** 10. row ***************************
Privilege: Drop
  Context: Databases,Tables
  Comment: To drop databases, tables, and views
*************************** 11. row ***************************
Privilege: Drop role
  Context: Server Admin
  Comment: To drop roles
*************************** 12. row ***************************
Privilege: Event
  Context: Server Admin
  Comment: To create, alter, drop and execute events
*************************** 13. row ***************************
Privilege: Execute
  Context: Functions,Procedures
  Comment: To execute stored routines
*************************** 14. row ***************************
Privilege: File
  Context: File access on server
  Comment: To read and write files on the server
*************************** 15. row ***************************
Privilege: Grant option
  Context: Databases,Tables,Functions,Procedures
  Comment: To give to other users those privileges you possess
*************************** 16. row ***************************
Privilege: Index
  Context: Tables
  Comment: To create or drop indexes
*************************** 17. row ***************************
Privilege: Insert
  Context: Tables
  Comment: To insert data into tables
*************************** 18. row ***************************
Privilege: Lock tables
  Context: Databases
  Comment: To use LOCK TABLES (together with SELECT privilege)
*************************** 19. row ***************************
Privilege: Process
  Context: Server Admin
  Comment: To view the plain text of currently executing queries
*************************** 20. row ***************************
Privilege: Proxy
  Context: Server Admin
  Comment: To make proxy user possible
*************************** 21. row ***************************
Privilege: References
  Context: Databases,Tables
  Comment: To have references on tables
*************************** 22. row ***************************
Privilege: Reload
  Context: Server Admin
  Comment: To reload or refresh tables, logs and privileges
*************************** 23. row ***************************
Privilege: Replication client
  Context: Server Admin
  Comment: To ask where the slave or master servers are
*************************** 24. row ***************************
Privilege: Replication slave
  Context: Server Admin
  Comment: To read binary log events from the master
*************************** 25. row ***************************
Privilege: Select
  Context: Tables
  Comment: To retrieve rows from table
*************************** 26. row ***************************
Privilege: Show databases
  Context: Server Admin
  Comment: To see all databases with SHOW DATABASES
*************************** 27. row ***************************
Privilege: Show view
  Context: Tables
  Comment: To see views with SHOW CREATE VIEW
*************************** 28. row ***************************
Privilege: Shutdown
  Context: Server Admin
  Comment: To shut down the server
*************************** 29. row ***************************
Privilege: Super
  Context: Server Admin
  Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
*************************** 30. row ***************************
Privilege: Trigger
  Context: Tables
  Comment: To use triggers
*************************** 31. row ***************************
Privilege: Create tablespace
  Context: Server Admin
  Comment: To create/alter/drop tablespaces
*************************** 32. row ***************************
Privilege: Update
  Context: Tables
  Comment: To update existing rows
*************************** 33. row ***************************
Privilege: Usage
  Context: Server Admin
  Comment: No privileges - allow connect only
*************************** 34. row ***************************
Privilege: SHOW_ROUTINE
  Context: Server Admin
  Comment: 
*************************** 35. row ***************************
Privilege: RESOURCE_GROUP_USER
  Context: Server Admin
  Comment: 
*************************** 36. row ***************************
Privilege: REPLICATION_APPLIER
  Context: Server Admin
  Comment: 
*************************** 37. row ***************************
Privilege: PASSWORDLESS_USER_ADMIN
  Context: Server Admin
  Comment: 
*************************** 38. row ***************************
Privilege: INNODB_REDO_LOG_ENABLE
  Context: Server Admin
  Comment: 
*************************** 39. row ***************************
Privilege: XA_RECOVER_ADMIN
  Context: Server Admin
  Comment: 
*************************** 40. row ***************************
Privilege: GROUP_REPLICATION_STREAM
  Context: Server Admin
  Comment: 
*************************** 41. row ***************************
Privilege: GROUP_REPLICATION_ADMIN
  Context: Server Admin
  Comment: 
*************************** 42. row ***************************
Privilege: FLUSH_USER_RESOURCES
  Context: Server Admin
  Comment: 
*************************** 43. row ***************************
Privilege: FLUSH_TABLES
  Context: Server Admin
  Comment: 
*************************** 44. row ***************************
Privilege: PERSIST_RO_VARIABLES_ADMIN
  Context: Server Admin
  Comment: 
*************************** 45. row ***************************
Privilege: ROLE_ADMIN
  Context: Server Admin
  Comment: 
*************************** 46. row ***************************
Privilege: BACKUP_ADMIN
  Context: Server Admin
  Comment: 
*************************** 47. row ***************************
Privilege: CONNECTION_ADMIN
  Context: Server Admin
  Comment: 
*************************** 48. row ***************************
Privilege: SET_USER_ID
  Context: Server Admin
  Comment: 
*************************** 49. row ***************************
Privilege: SESSION_VARIABLES_ADMIN
  Context: Server Admin
  Comment: 
*************************** 50. row ***************************
Privilege: RESOURCE_GROUP_ADMIN
  Context: Server Admin
  Comment: 
*************************** 51. row ***************************
Privilege: INNODB_REDO_LOG_ARCHIVE
  Context: Server Admin
  Comment: 
*************************** 52. row ***************************
Privilege: BINLOG_ENCRYPTION_ADMIN
  Context: Server Admin
  Comment: 
*************************** 53. row ***************************
Privilege: REPLICATION_SLAVE_ADMIN
  Context: Server Admin
  Comment: 
*************************** 54. row ***************************
Privilege: SYSTEM_VARIABLES_ADMIN
  Context: Server Admin
  Comment: 
*************************** 55. row ***************************
Privilege: SYSTEM_USER
  Context: Server Admin
  Comment: 
*************************** 56. row ***************************
Privilege: APPLICATION_PASSWORD_ADMIN
  Context: Server Admin
  Comment: 
*************************** 57. row ***************************
Privilege: TABLE_ENCRYPTION_ADMIN
  Context: Server Admin
  Comment: 
*************************** 58. row ***************************
Privilege: SERVICE_CONNECTION_ADMIN
  Context: Server Admin
  Comment: 
*************************** 59. row ***************************
Privilege: AUDIT_ADMIN
  Context: Server Admin
  Comment: 
*************************** 60. row ***************************
Privilege: AUTHENTICATION_POLICY_ADMIN
  Context: Server Admin
  Comment: 
*************************** 61. row ***************************
Privilege: BINLOG_ADMIN
  Context: Server Admin
  Comment: 
*************************** 62. row ***************************
Privilege: ENCRYPTION_KEY_ADMIN
  Context: Server Admin
  Comment: 
*************************** 63. row ***************************
Privilege: CLONE_ADMIN
  Context: Server Admin
  Comment: 
*************************** 64. row ***************************
Privilege: FLUSH_OPTIMIZER_COSTS
  Context: Server Admin
  Comment: 
*************************** 65. row ***************************
Privilege: FLUSH_STATUS
  Context: Server Admin
  Comment: 
65 rows in set (0.00 sec)

ERROR: 
No query specified

练习1：我们现在想给经理角色授予在数据库test中的所有表select,update权限：

mysql> grant select,update on test.* to 'manager';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

#此处的manager默认host为%，所以，如果是loacalhost的role，需要写全

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

查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS 语句

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

回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

REVOKE privileges ON tablename FROM 'rolename';

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

mysql> revoke update on test.* from 'manager'@'localhost';
Query OK, 0 rows affected (0.03 sec)

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

删除角色

DROP ROLE role [,role2]...

mysql> drop role boss;
Query OK, 0 rows affected (0.06 sec)

绑定用户和角色

给用户赋予角色

mysql> create user 'apple' identified by 'VMware1!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant  'admin_00'@'%' to 'apple'@'%';
Query OK, 0 rows affected (0.03 sec)

此时，apple并没有拿到授权：

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.03 sec)

激活角色
- 方法1：set命令

SET DEFAULT ROLE ALL TO 'user'@'host';

mysql> set default role all  to 'apple'@'%';
Query OK, 0 rows affected (0.03 sec)

再看apple用户：需要重新登录

mysql> exit
Bye
[root@Alma ~]# mysql -uapple -pVMware1!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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 current_role();
+----------------+
| current_role() |
+----------------+
| `admin_00`@`%` |
+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| books              |
| girls              |
| information_schema |
| myemployees        |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
8 rows in set (0.01 sec)
mysql> show grants;
+-------------------------------------+
| Grants for apple@%                  |
+-------------------------------------+
| GRANT SELECT ON *.* TO `apple`@`%`  |
| GRANT `admin_00`@`%` TO `apple`@`%` |
+-------------------------------------+
2 rows in set (0.00 sec)

方法2：配置activate_all_roles_on_login=on

默认是off

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.03 sec)

mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

再绑定用户和role，就不需要激活了

mysql> create user 'cisco'@'localhost' identified by 'VMware1!';
Query OK, 0 rows affected (0.03 sec)

mysql> grant 'manager'@'localhost' to 'cisco'@'localhost';
Query OK, 0 rows affected (0.02 sec)

cisco登录

[root@Alma ~]# mysql -ucisco -pVMware1!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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  current_role();
+-----------------------+
| current_role()        |
+-----------------------+
| `manager`@`localhost` |
+-----------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.03 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

撤销用户的角色

REVOKE role FROM user;

mysql> revoke  'admin_00'@'%' from 'apple'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'apple';
+-----------------------------------+
| Grants for apple@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `apple`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

但是，用户需要重新登录后生效

设置强制角色(mandatory role)

每个创建的账户配置默认角色，不能remoke或drop

方式1：conf文件

[mysqld]
mandatory_roles='role1,role2@localhost'

方式2：运行时手动配置

SET PERSIST mandatory_roles = 'role1,role2@localhost'; #系统重启后仍然
有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost'; #系统重启后失效

Etaon

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL-用户与权限管理

本文档为学习【宋红康】MySQL数据库（mysql安装/基础/高级/优化）笔记用户管理MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性需要通过账户管理来保证。登录MySQL服务器启动MySQL服务后，可以通过
复制链接

扫一扫