MySQL权限与安全管理
11.1 权限表
11.1.1 user表
User表是MySQL中最重要的一个权限表，记录允许连接到服务器的帐号信息，里面的权限是全局级的。
11.1.2 db表和host表
db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致地控制。
11.1.3 tables_priv表和columns_priv表
tables_priv表用来对表设置操作权限。
columns_priv表用来对表的某一列设置权限。
11.1.4 procs_priv表
procs_priv表可以对存储过程和存储函数设置操作权限。
11.2 账户管理
11.2.1 登录和退出MySQL服务器
mysql命令的常用参数以及登录和退出mysql服务器的方法。
退出服务器的方法就是exit;(基本上许多应用只要有命令行的基本都是这样退出的)
实例
这里执行一条登录mysql的语句，登录完之后会让你输入密码，输入完之后会执行后面的语句，执行完就退出数据库
mysql -h localhost -u root -p xxx -e"desc person;"
11.2.2 新建普通用户
使用CREATE USER或GRANT语句。
直接操作MySQL授权表。
实例
使用create user创建一个用户，用户名是jeffrey，密码是mypass，主机是localhost
这种是用户登录时不用密码
create user 'user_name'@'loaclhost'
创建指定密码的用户，需要使用identified by指定明文密码值
create user 'user_name'@'loaclhost' identified by 'mypassword'
11.2.3 删除普通用户
1．使用DROP USER语句删除用户
实例
drop user 'user_name'@'localhost';
2．使用DELETE语句删除用户
delete from mysql.user where host='localhost' and user='user_name';
11.2.4 root用户修改自己的密码
1．使用mysqladmin命令在命令行指定新密码
2．修改mysql数据库的user表
3．使用SET语句修改root用户的密码
11.2.5 root用户修改普通用户密码
1．使用SET语句修改普通用户的密码
set password for 'user'@'localhost'='new_passwd'
2．使用UPDATE语句修改普通用户的密码
update mysql.user set authentication_string=md5('new_passwd') where user='user_name' and host='localhost';
3．使用GRANT语句修改普通用户密码
11.2.6 普通用户修改密码
普通用户登录MySQL服务器后，通过SET语句设置自己的密码。
SET PASSWORD =
PASSWORD(‘newpassword’);
11.2.7 root用户密码丢失的解决办法
1．使用--skip-grant-tables选项启动MySQL服务
2．使用root用户登录和重新设置密码
3．加载权限表
11.3 权限管理
11.3.1 MySQL的各种权限
账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表内容读入内存。
11.3.2 授权
授权就是为某个用户授于权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授于权限。
11.3.3 收回权限
收回权限就是取消已经赋于用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。
11.3.4 查看权限
SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANT语句查看账户信息。
SHOW GRANTS FOR ‘user’@’host’ ;
11.4 访问控制
11.4.1 连接核实阶段
当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码验证身份来接受或拒绝连接。
user表
3个字段（Host，User和Password）
11.4.2 请求核实阶段
建立了连接之后，对在此连接上进来的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。
user、db、host、tables_priv或columns_priv表
11.5 提升安全性
11.5.1 AES 256加密
MySQL 8.0支持多种AES 256加密模式，通过更大的密钥长度和不同的块模式增强了高级加密标准。这里主要通过加密函数AES_ENCODE()和解密函数AES_DECODE()来提高安全强度。
11.5.2 密码到期更换策略
MySQL 8.0允许数据库管理员手动设置账户密码过期时间。任何密码超期的账户想要连接服务端时都必须更改密码。通过设置default_password_lifetime参数可以设置账户过期时间。
11.5.3 安全模式安装
MySQL 新增了“安全模式”的安装形式，从而可以避免用户的数据被泄漏。
用户通过以下方式来提升MySQL安装的安全性：
(1) 为root账户设置密码。
(2) 移除能从本地主机以外的地址访问数据库的root账户。
(3) 移除匿名账户。
(4) 移除test数据库，该数据库默认可被任意用户甚至匿名账户访问。
11.6 MySQL 8.0的新特性——管理角色
在MySQL 8.0数据库中，角色可以看成是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无需为每个用户单独授权。
下面通过案例来学习如何管理角色。
创建角色，执行语句如下：
mysql> CREATE ROLE role_tt; # 创建角色
mysql> GRANT SELECT ON db.* to 'role_tt'; # 给角色role_tt授予查询权限
mysql> CREATE USER ' myuser1'@'%' identified by '123456';
mysql> GRANT 'role_tt' TO 'myuser1'@'%';
mysql> GRANT INSERT ON db.* to 'role_tt';
mysql> REVOKE INSERT ON db.* FROM 'role_tt';
mysql> SELECT * FROM mysql.default_roles;
mysql> SELECT * FROM mysql.role_edges;
mysql> DROP ROLE role_tt;
11.7 综合案例——综合管理用户权限
本章详细介绍了MySQL如何管理用户对服务器的访问控制和root用户如何对每一个账户授予权限。这些被授予的权限分为不同的层级，可以是全局层级、数据库层级、表层级或者是列层级等，读者可以灵活地将混合权限授予各个需要的用户。通过本章的内容，读者将学会如何创建账户，如何对账户授权、如何收回权限以及如何删除账户。下面的综合案例将帮助读者建立执行这些操作的能力。
案例目的：掌握创建用户和授权的方法
打开MySQL客户端工具，输入登录命令，登录MySQL
登录命令要在MySQL的安装目录下的bin文件夹下安装即可
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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中)
可以查看到MySQL数据库中有33张表，都是和数据库创建，权限有关的
mysql> use mysql;
Database changed
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 |
| 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 |
+---------------------------+
33 rows in set (0.00 sec)
使用GRANT 语句创建新账户，创建过程如下：
mysql> create user 'newAdmin'@'localhost'identified by 'pw123';
Query OK, 0 rows affected (0.06 sec)
然后分别从user表中查看新账户的账户信息
用户账号创建完成之后，账户信息已经保存在user表中，查询user表中名称为newAdmin的账户信息，执行过程如下
mysql> select host,user,select_priv,update_priv from user where user='newAdmin';
+-----------+----------+-------------+-------------+
| host | user | select_priv | update_priv |
+-----------+----------+-------------+-------------+
| localhost | newAdmin | N | N |
+-----------+----------+-------------+-------------+
1 row in set (0.00 sec)
使用show grants语句查看newAdmin账户的权限信息
mysql> show grants for 'newAdmin' @'localhost';
+----------------------------------------------+
| Grants for newAdmin@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `newAdmin`@`localhost` |
+----------------------------------------------+
1 row in set (0.00 sec)
退出当前账户，使用exit
mysql> exit
Bye
使用newAdmin账户登录MySQL
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u newAdmin -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
此时创建的newAdmin账户对数据库的操作权限只有查询
如
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| COLUMNS |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEY_COLUMN_USAGE |
| KEYWORDS |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| STATISTICS |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLESPACES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+---------------------------------------+
67 rows in set (0.00 sec)
mysql> select * from tables;
这里太长就不列出来了，
下面尝试着创建数据库
mysql> create database test_db;
ERROR 1044 (42000): Access denied for user 'newAdmin'@'localhost' to database 'test_db'
这样看来新创建的账户只有查询的权限，
如何赋予新创建的账户创建数据库的权限
还是使用grant语句mysql授予用户新建数据库的权限
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";
将全部的权限赋予所有数据库/表：
mysql> GRANT ALL ON *.* TO 'myuser'@'localhost';
###你也可以将用户现有的权限删除。使用以下命令废除"myuser"帐号的现有权限：
mysql> REVOKE <privileges> ON <database>.<table> FROM 'myuser'@'localhost';
然后退出newAdmin账户，登录root账户
删除newAdmin的账户信息
drop user 'newAdmin'@'localhost';
