MySQL安全机制 DCL 权限管理相关语句 ======================================
DDL DML DCL
认 证 || 授 权
|
用户名 && 主机 密码
zhang3 @ localhost
zhang3 @ 192.168.10.1
mysql.user 表
 |
概括一句话：
控制： 什么用户使用什么密码 ，从 什么主机 连接服务器， 对 数据库/表/视图/数据行，进行 何种操作？
示例：
grant select on class.students to "zhang3"@"192.168.2.2" identifiled by "123123";
授权，zhang3 用户可以使用 密码 123123 从 192.168.2.2 登录， 能够对 class数据库中的 students 表做 select 操作。
一、MySQL权限管理的相关表 ==========================
mysql.user 主要 Global level 用户字段、权限字段、安全字段、资源控制字段
mysql.db Database level 用户字段、权限字段
mysql.tables_priv Table level
mysql.columns_priv Column level
二、MySQL用户管理===============================
#### 创建mysql用户： -------------------------------------------------------
方法一：CREATE USER 语句创建 -------------------
CREATE USER user1@'localhost' IDENTIFIED BY '123456'; ## 只添加认证。
方法二： GRANT语句 ，授权的用户如果不存在，则自动 创建 -------------------------
示例 GRANT ALL ON *.* TO 'user3'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
# 除了root 管理员之外，的最大授权。
# ALL 表示除了 grant权限 以外的所有权限。
#### 删除mysql用户： --------------------------------------------------------------
方法一： DROP USER 语句删除
DROP USER 'user1'@'localhost';
### user 字段和 host 字段为 联合主键， 必须要两个字段同时声明，才能表示 唯一值。
'cat2'@'192.168.2.2'
方法二：DELETE 语句删除
DELETE FROM mysql.user WHERE user='user2' AND host='localhost';
FLUSH PRIVILEGES; ##让新的权限表立刻生效。
从用户认证的角度：
默认安装后，用户的安全建议 尤其是 5.6 之前的版本 ：==================
有几个空密码的用户， 还有 test 的一个 空数据库，但是该库的权限非常开放 。
 |
1、删除test数据库
2、删除所有密码为空的用户
3、删除所有用户名为空的用户
MariaDB [stu_sys]> select user,host,password from mysql.user where password=""; ##多行删除前，一定要对 where 条件 做验证！
MariaDB [stu_sys]> delete from mysql.user where password='';
MariaDB [(none)]> select user,host,password from mysql.user; ###验证。
drop database test;
#### 修改msyql本机root账户的密码： --------------------------------------------------------------
方法一 :
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; ##成功后修改新密码。
mysql> flush privileges;
方法二:
SET PASSWORD = PASSWORD('你的新密码'); ## 新版本的root 密码不再接受 弱密码，需要一定的复杂度。
否则会提示错误：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
方法三:
update mysql.user set authentication_string=password("123123@qq.com");
flush privileges;
## 5.7 引入密码的有效期， update不会引起密码修改时间的变化。
password 1 过期
update password2 也是过期。
#### 破解 root用户密码 / 跳过用户 密码认证 ： --------------------------------------------------------------
1、 修改： vim /etc/my.cnf
[mysqld]
新增
skip-grant-tables
2、 重启：service mysqld restart
3、root用户无密码登录： mysql -uroot
### 该次登录，无法使用 Grant 命令，只能用 update 的方式进行密码的修改。
4、 修改自己的密码：
mysql> UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='root' AND host='localhost';
5、mysql> FLUSH PRIVILEGES;
6、 ##退出后，立刻注释掉 skip-grant-tables 并重启。
三、MySQL 权限管理 =============================================
权限应用的顺序：
user (Y|N) ==> db ==> tables_priv ==> columns_priv
|
zhang3 select N ----------------------------->class select---->
语法格式：
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];
==权限列表 all 所有权限（不包括 grant 授权权限）
select,update
==数据库.表名 *.* 所有库下的所有表 Global level
web.* web库下的所有表 Database level
web.stu_info web库下的stu_info表 Table level
== 客户端主机 % 所有主机
192.168.2.% 192.168.2.0网段的所有主机
192.168.2.168 指定主机
zhang3 localhost # 指定主机名称
with_option参数：--------------------------------
GRANT OPTION： 授权选项
----------------------------------------------------------------
MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数
mysql> grant select on *.* to 'cu_blog'@'localhost' identified by '123456' with max_queries_per_hour 5;
Grant示例：
GRANT ALL ON *.* TO admin1@'%' IDENTIFIED BY '(TianYunYang584131420)';
GRANT ALL ON *.* TO admin2@'%' IDENTIFIED BY '(TianYunYang584131420)' WITH GRANT OPTION;
GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY '(TianYunYang584131420)';
GRANT ALL ON bbs.user TO admin4@'%' IDENTIFIED BY '(TianYunYang584131420)';
查看权限: ======================================
SHOW GRANTS\G ##查看自己的权限
SHOW GRANTS FOR admin1@'%' \G ##查看某个用户的权限
回收权限 REVOKE ============================
回收权限REVOKE
语法：
REVOKE 权限列表 ON 数据库名 FROM 用户名@'客户端主机'
示例：
REVOKE DELETE ON *.* FROM admin1@'%'; //回收部分权限
REVOKE ALL PRIVILEGES ON *.* FROM admin2@'%'; //回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION ON *.* FROM 'admin2'@'%'; ##回收所有权限以及，附加权限。
update 语句进行修改 。（可视化操作界面推荐）
### 视图？ 虚拟的表， 多个select 语句结果的一个 集合，每一次查询视图时，临时执行相关的 select 语句，即时生成。
新建视图 view1 （虚拟表），但是可以对视图进行独立授权，达到隐藏 真实表的目的。
grant select on myclass.view1 to ‘zhang3’@'localhost'
由此，达到 对某些用户 隐藏 表格中 某些 敏感字段的 目的。
授权 练习： ============================================
一、授权数据库开发人员，对 testdb数据库，具有创建表、索引、视图、存储过程、函数。。。等权限。
复杂授权建议 使用 Navicat 在 mysql.table_priv 表中操作。
建库：create database testdb;
建用户：CREATE USER developer@'192.168.0.%' IDENTIFIED BY '123456';
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create,alter,drop on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';
二、普通 DBA 管理某个 MySQL 数据库的权限。
grant all privileges on testdb to dba@'localhost'
其中，关键字 “privileges” 可以省略。
三、 高级 DBA 管理 MySQL 中所有数据库的权限。
grant all on *.* to dba@'localhost'
用户密码过期策略 5.6.6 ： ====================================
MySQL版本5.6.6版本起，添加了 password_expired 功能，它允许设置用户的过期时间。
这个特性已经添加到 mysql.user 数据表，但是它的默认值是”N”。
一旦某个用户的这个选项设置为”Y”，那么这个用户还是可以登陆到 MySQL服务器，
但是在用户未设置新密码之前不能运行任何查询语句，而且会得到如下错误消息提示：
ERROR 1820 (HY000): You must SET PASSWORD before executing this statementKeep in
mind that this does not affect any current connections the account has open.
MySQL 5.7.4 版开始，用户的密码过期时间这个特性得以改进，可以通过
全局变量 default_password_lifetime 来设置密码过期的策略，
此全局变量可以设置一个全局的自动密码过期策略
[mysqld]
default_password_lifetime=90 #单位：天
如果要设置密码永不过期的全局策略，可以设置 为 0 （注意这是默认值，配置文件中可以不声明）
还可以使用ALTER USER命令为每个具体的用户账户单独设置特定的值，它会自动覆盖密码过期的全局策略。
要注意ALTER USER语句的INTERVAL的单位是 “天”。
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY; ## 用户密码testuser 30天后过期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER; ## 禁用密码过期：
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT; ##让用户使用默认的密码过期全局策略：
查看当前 Mysql 的连接数量，限制 连接数量和错误频率 ================================
命令：
show processlist;
如果是root帐号，你能看到所有用户的当前连接。
如果是其它普通帐号，只能看到自己占用的连接。
show processlist; 只列出前 100条，如果想全列出请使用
show full processlist;
mysql> show processlist;
mysql> show full processlist;
 |
设置服务器最大并发连接数量： =====================================
max_connections=3200 ## 可以写入 /etc/my.cnf 默认不需要改。
实际MySQL服务器允许的最大连接数 16384；
设置服务器对同一个主机允许最大的连接错误数量（网络连接中出现的错误，密码错误不算）：
max_connect_errors = 1000
## 对本机 localhost 127.0.0.1 ::1 的信息均不缓存。
mysql> select * from performance_schema.host_cache; ##记录主机连接出错的详细信息。
Empty set (0.00 sec)
mysql> show variables like "%host_cache%";
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| host_cache_size | 279 |
+-----------------+------------+
1 row in set (0.00 sec)
mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)
####################################################################
错误提示ERROR 1129 (HY000): Host '10.24.236.231' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'
####################################################################
================== 附录：grant 授权的选项(官网) =========================
 |
 |
命令： show status;
命令：show status like '%下面变量%';
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过 long_query_time 时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。