回顾

各位朋友们好,经常过上章的学习,大家应该可以独立完成Mysql的安装,并且应该已经掌握到各个版本间安装的不同区别。

安装虽然简单,但是也建议大家多练习几遍,安装流程一定要滚瓜烂熟,多在自己在机器上玩玩(对于安装比较熟悉的同学可以简单过一下即可),这样有助于我们去学习下面的权限认证部分。

对于权限控制,是DBA必须要做的事情,我们需要给予开发人员什么权限,给予领导什么权限,给予自己的超管用户什么权限(一般非特殊情况下不建议使用root直接进行数据库的管理),这些都是要进行细化的,不能每个账号的权限都是一样的,最终如果出问题(开发人员误操作/账号被黑导致删库),还是因为你的权限没有分配好,下面让我们来学习一下mysql对于权限这块是怎么处理的,首先我们来了解一下Mysql的连接方式

Mysql连接方式

Mysql连接方式分为两种：1.网络IP:port连接 2.本地socket文件连接

一般在进行重大维护管理或无远程连接环境时才使用socket方式进行Mysql连接，大部分我们在进行日常维护、报表查询、SQL编写、开发人员连接时均使用网络连接方式，以下为两种连接方式的使用介绍：

  1. tcp/ip（网络连接方式）
    此种方式要指定-h（--host）填写Mysql服务端IP参数,远程连接,为了保障安全一般root会禁止进行网络连接，下面我们讲权限分配时会带到如何将root禁止远程登录，以及当时遇到的一个小案例讲解其重要性
连接命令：/usr/local/mysql/bin/mysql -h192.168.9.11 -P3306 -uroot -p123456

2.socket（本地连接方式）
此种方式不指定-h参数,本地连接,一般仅做为root用户登陆使用,socket也是一种通讯方式,只不过是以文件方式承现,开发人员应该对此很熟悉不过了

连接命令：/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/tmp/mysql.sock

3.查看登陆信息（Mysql服务器端）

查看当前数据库内全部的连接会话信息：以下三种方式均可以查看，每个字段的意义后续在优化时我们会详细讲解

1）show full processlist;
2）mysqladmin –uroot –ppassword pr
3）select * from information_schema.processlist;

Mysql用户密码管理

对Mysql连接方式有个基本了解后,我们开始进行用户、密码、权限方面的讲解,并将老师在生产中因权限配置不当遇到的故障问题介绍给大家,首先我们来看下用户的创建

1.连接Mysql,我们此处是使用维护的本地socket方式进行管理

/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/tmp/mysql.sock

2.创建普通用户,此处创建用户需要有对mysql数据库的操作权限,因为创建命令其实就是在mysql数据库中的权限表进行添加用户与权限的对应记录,如user表

create user username@'localhost' identified by 'password' 注意：此处三个地方 1.username：登陆使用的用户名 2.password：登陆使用的密码 localhost：此处是Mysql连接时验证的一个额外机制,代表限制远端使用该用户进行连接时,客户端的IP地址范围,此值可以有如下三个选择： 1)%：代表可以允许客户端以任意IP访问 2)192.168.18.%：代表可以允许客户端以192.168.18网段的IP地址进行访问,一般生产中使用此方式,仅仅配置某个应用服务器网段的IP地址允许访问 3)localhost：代表只能通过Mysql服务器端进行连接,一般我们创建维护账号时才选择此方式,如root

3.用户密码的管理,在工作维护期间,一定会是将开发人员的账号进行定期更新的,这时我们可以使用以下方式进行用户密码的修改
此处注意：因为Mysql密码修改其实也就是对mysql库中user表的修改,所以在Mysql是只读模式或没有对user表修改权限时会修改失败

1）mysqladmin -hlocalhost -uroot -p原密码 password：输入新密码即可,此种方式不需要进行flush privileges

2）update mysql.user set password/authentication_string=PASSWORD('new-password') where user='root' and host='localhost'：此种方式使用Mysql中的密码函数PASSWORD进行密码配置,可以看到authentication_string都是进行加密后的字符串,我们也可以先用此函数查询出对应的字符串值,直接再使用update更新成该值,更新完需要将权限表进行刷新 flush privileges 3） 修改当前用户密码：set PASSWORD=PASSWORD('new-password') 修改其他用户密码：set password for root@localhost = password('new-password'); 将用户的密码时间配置为永不过期：alter user root@’localhost’ password expire never;

4.删除某个用户,Mysql中删除用户没有像Oracle那样有用户归属的对象,删除用户只是将该用户删除,对有此存储过程,触发器的定义或会受到影响,后续讲解时我们会带到存储过程的安全定义级别

drop user username@'localhost'

5.为某个用户进行权限赋值与回收,权限赋值可以根据多个级别,可以对整个服务端的管理权限,对全部数据库的操作权限,对单个库,多表,单表的操作权限,我们来看下如何进行配置,如我们配置给某个库内全部的读与更新权限

赋值：grant select,update on test.* to username@'IP段'; 回收：revoke select,update on test.* from username@'IP段'; with grant option：可以将权限赋于其他用户,该选项一般在生产中不做使用,权限将由DBA进行管控,不存在开发人员互相开通权限情况

6.查看登陆用户及权限信息

查看当前登陆用户：select user();
查看某个用户权限：show grants for root@'localhost'
   查询一张表所有的字段名称： select COLUMN_NAME, column_comment from INFORMATION_SCHEMA.Columns where table_name='你的表名'

Mysql权限管理

了解完基本的用户、密码、权限管理后,我们来看一下,Mysql中有哪里我们经常使用的到权限配置,并且这些权限的作用范围和作用是什么,

表级别：
SELECT：SELECT命令的使用
INSERT：INSERT命令的使用
DELETE：DELETE命令的使用 UPDATE：UPDATE命令的使用 CREATE：CREATE TABLE命令的使用 ALTER：ALTER TABLE命令的使用 DROP：DROP TABLE命令的使用 INDEX：CREATE INDEX和DROP INDEX命令的使用 LOCK TABLES：LOCK TABLES命令的使用 临时表： CREATE TEMPORARY TABLES：CREATE TEMPORARY TABLE命令的使用 存储过程： select on mysql.proc：查看存储过程 CREATE ROUTINE：创建存储过程 ALTER ROUTINE + SUPER：更改存储过程 EXECUTE：运行存储过程(此权限只是针对运行存储过程这个对象,是否执行成功还要取决于满足存储过程里面SQL的执行权限,比如有create TEMPORARY tables,需要额外将权限赋值） 触发器： TRIGGER：执行触发器（从MySQL5.1.6开始） 视图： CREATE VIEW：CREATE VIEW命令的使用 SHOW VIEW：SHOW CREATE VIEW命令的使用 备份： FILE：SELECT INTO OUTFILE和LOAD DATA INFILE的使用 （一般日常备份中我们不会使用此方式,在进行业务文本类数据导出导入时才会使用） 复制： REPLICATION SLAVE：从服务器所需的权限 REPLICATION CLIENT：用户查询从服务器和主服务器位置（一般不用） 定时任务： EVENT：执行事件（从MySQL5.1.6开始） 用户及权限： ALL PRIVILEGES：除WITH GRANT OPTION之外的所有权限 CREATE USER：创建、弃用、重命名和撤销用户权限 GRANT OPTION：用户委派权限 系统状态： PROCESS：SHOW PROCESSLIST命令的使用 RELOAD：FLUSH命令集的使用(比如刷新日志内容时) SHOW DATABASES：SHOW DATABASES命令的使用 SHUTDOWN：SHUTDOWN命令的使用 SUPER：管理员级命令的使用，如CHANGE、MASTER、KILL thread、mysqladmin debug、PURGE MASTER LOGS和SET GLOBAL USAGE：允许只连接Mysql(默认用户只有此权限)

以上权限信息,像表/临时表/存储过程/触发器/视图一般都是可以针对库及表进行权限分配的,像定时任务,用户及权限,系统状态均是对于全局来定义的,一般我们只对线上应用连接用户赋予特定数据库的select,update,insert,delete权限足够,如果存在使用临时的情况或存储过程的情况,需要额外进行添加,每个公司不同,有此公司是不用临时表的,有些公司是不使用存储的,大家只要根据自己的环境去赋权即可

为什么有的公司不使用临时表和存储过程呢？
答：
1.在GTID复制模型中,临时表的create语句是不能使用的,所以有些选择GTID复制模型时,就需要进行取舍
2.Mysql不像Oracle,Oracle跑个存储过程可能速度很快,因为Oracle已经完善的足够好了,Mysql只是做为存储数据库,如果一些计算业务放到Mysql的存储过程上处理速度会差很多,甚至有些存储直接会导致业务卡死,所以一般能不用存储过程就不用

全局：grant PROCESS on *.* to username@'IP网段'
库级：grant SELECT, INSERT, UPDATE, DELETE on test.* to username@'IP网段' 存储过程：grant EXECUTE, ALTER ROUTINE ON PROCEDURE test.* TO username@'IP网段' 表级：grant SELECT, INSERT, UPDATE, DELETE on test.test to username@'IP网段' flush privileges;

Mysql权限表

上面我们说过Mysql的权限其实就是存储在mysql数据库中一些权限表中的数据信息,所以在此处我们有必要了解一下Mysql中权限表信息,权限的寻找顺序及作用域

1.user表：权限生效于全部数据库
1)验证信息：user host  password/authentication_string
2)权限信息：
1.库表权限：数据库操作类的权限,select,update.....
2.全局权限：数据库管理类的权限,grant shutdown....
3.系统权限：查询次、更新次、连接次、并发连接次

2.db表：权限生效于某个数据库 1)验证信息：user,host,db 2)权限信息：create/update/alter 3.tables_priv表：权限生效于某个表 1)验证信息：user,host,db,table_name 2)权限信息：select insert 4.columns_priv表：权限生产于某个列 5.proces_priv表：权限可以生效于全部数据库/某个数据库

权限查找顺序：
mysql会先查找user表的全局权限,若无权限再查看db/host表的数据库权限,若无权限再查看tables_priv表权限,若无权限再查看column_priv字段权限,若无权限返回错误信息,一般我们将用户的权限细化至表已经足够了,我们可以看下图对顺序请求过程有个了解

生产案例

业务场景：
就在年前不久,18年12月份的时候,在老师公司所维护的数据库中有一套OA工作流的库,因为是2018下半年的项目,当时已经初始测试完成,刚上生产,后来在有天测试人员测试后,业务部门通报说OA的数据好像有问题,我们的数据全不见了

问题描述：业务部门给予我们的信息其实就是数据库数据被删了,并且我们的数据有脏数据

问题排查：首先我们考虑是不是有内部人员进行了线上库的批量删除操作,由于部分业务是由特定的开发人员进行维护,所以他们在需要时会向我们这边走流程进行权限申请,后来查看了一下,确实是有删除操作,但是是分波操作的,并且发现执行的用户却是线上应用的用户,并不是开发人员,难道我们是被黑了？并不是！

问题原因：既然是应用连接的用户,于是根据审计信息我们找到了执行这个操作的用户连接信息,发现是测试服务器的应用IP,而这个用户当时是做测试使用,是直接将用户导入的,所以问题到这就很明显了,是测试使用的测试应用连接的正式的数据库

问题解决：这就是用户的host没有配置规范导致,虽然开发人员在进行测试时数据库的连接配置有问题,但这最终的锅还是双方都要背的,而我们在最初将用户的host控制好,只允许生产网段的IP进行连接就不会出现此类问题,当然也需要开发人员进行规范配置

小结

在本章中我们学习了Mysql的两种连接方式,简单的用户创建、删除、查看、用户权限定义回收、用户权限表信息、权限表及查找顺序,权限类的知识点并不是很多,只要是在初始将用户全部规范好,后期基本不需要进行维护,而用户权限配置也是很重要的一环,谁也不能保证自己100%经历过的SQL全是自己想要的结果,否则就不会出现顺丰被删数据这样的情况,除了进行此类权限控制,我们也可以在架构上搭建SQL的审核平台（推荐inception）,去避免这种人员误操作情况。

有兴趣的朋友可以去了解一下,安装配置比较简易,在下章中我们就开始进入SQL部1分了,SQL语句其实就是那些东西,东西都是很简单的,难的地方在于你在你的业务中怎么去运用这些SQL,如何写出执行效率比较高的SQL,这些我们在优化中会拿出真实的SQL进行分析,SQL结束之后我们就正式进入初级部分Mysql的体系结构了,此部分为重中之重,哪怕你前面 这些不是很熟悉也没事,这些只是基础多练几遍就行可以,体系这块一定要打起12分的精神学习,如果一遍没看懂就多看几遍,如果还有不了解的部分可以问老师,因为体系就是原理,体系通了,万物就通了,让我们下章再会！