五、MySQL安全管理

2020-03-11  本文已影响0人  Ly3911

1、安全管理是每一个接触数据库的人都应该考虑的问题，尤其是DBA（数据库管理员）

2、数据库安全的维度有很多

一、外键约束

1、外键

概念

外键：foreign key，表中指向外部表主键的字段定义成外键

步骤

1、确定表中字段与另外一张表存在关联关系

2、使用外键明确关联外表

3、外键约束成功

示例

1、创建专业表和学生表，学生表中的专业id指向专业表id

create table t_47(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_48(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment '指向t_46表中的id主键',
    constraint `c_id` foreign key(c_id) references t_47(id)
)charset utf8;

2、外键可以不指定名字，系统会自动生成

create table t_49(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_47(id)
)charset utf8;

小结

1、外键是需要保证字段与外部连接的主键字段一致的

2、一张表可以有多个外键，但是一个字段只能产生一个外键

2、外键约束

概念

外键约束：当表建立外键关系后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果

步骤

1、确定表的外键关联关系

2、确定主表的约束控制

3、明确使用相应的约束控制

4、系统自动约束

示例

1、子表不能插入主表不存在的数据

insert into t_48 values(null,'Tony',2); # 错误

insert into t_47 values(null,'English');
insert into t_48 values(null,'Peny',1);

2、默认的外键产生后，主键不能更新被关联的主键字段或者删除被关联的主键记录

# 错误
update t_47 set id = 2;
delete from t_47 where id = 1;

3、限制外键约束，一般使用更新级联，删除置空

create table t_50(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_51(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int, # 如果要允许置空，就不能not null
    foreign key(c_id) references t_50(id) on update cascade on delete set null
)charset utf8;

insert into t_50 values(null,'Chinese'),(null,'Computer');
insert into t_51 values(null,'Tony',1),(null,'Petter',2);

# 错误
insert into t_51 values(null,'Lilei',3);

insert into t_51 values(null,'Lilei',NULL); # OK                               

update t_50 set id = 3 where id = 1;

delete from t_50 where id = 3;

小结

1、外键约束对子表和父表都有约束

2、外键约束增强了数据的安全性和可靠性，但是会增加程序对于数据的不可控性，所以是实际开发中一般会通过程序逻辑控制来保证数据的完整性和安全性，外间使用较少

3、外键管理

概念

外键管理：在表创建后期维护外键

alter table 表名 add [constraint `外建名`] foreign key(外键字段) references 表名(主键) [on 外键约束]

alter table 表名 drop foreign key 外键名;

示例

1、删除外键

alter table t_51 drop foreign key t_51_ibfk_1;  # 系统生成的外键

2、追加外键

alter table t_51 add constraint `t_51_50` foreign key(c_id) references t_50(id);

小结

1、外键的使用最好的创建表结构的时候就维护好，后期的维护对子表数据有要求

二、事务安全

1、事务

概念

事务：要做的某个事情

示例

银行转账：从A账户转账到B账户

创建数据表

create table t_52(
    id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_52 values(null,'Tom',10000),(null,'Lucy',100);

转账：Tom向Lucy转账，一定是分为两步

# Tom扣钱
update t_52 set account = account - 1000 where id = 1;

# Lucy收钱
update t_52 set account = account + 1000 where id = 2;

事务安全原理

事务安全是在操作前告知系统，接下来所有的操作都暂不同步到数据表，而是记录到事务日志，指导后续所有操作都成功，再进行同步；否则取消所有操作

以上述转账为例

graph TB
A(转账开始)-->B[开启事务]
B-->C{事务1:Tom转出1000}
C-->|成功|D[记录到事务日志]
C-->|失败|G
D-->E{事务2:Lucy转入1000}
D-->|失败|G
E-->|成功|F[记录到事务日志]
F-->G[关闭事务<br>成功:提交事务 同步到数据表\清除事务日志<br>失败:回滚事务 清除事务日志]
G-->H((结束))

小结

1、事务的目的就是为了保障连续操作的一致性，保证结果的完整性

2、事务的原理是通过将操作结果暂时保存在事务日志中，等所有操作的结果都是成功的，然后一并同步到数据表

2、事务处理

概念

事务处理：利用自动或者手动方式实现事务管理

步骤

1、确定操作需要使用到事务操作

2、开启事务

3、执行事务

4、结束事务

示例

1、手动事务：启用事务转账，成功提交事务

# 开启事务
start transaction;

# Tom扣钱
update t_52 set account = account - 1000 where id  = 1;

# Lucy收钱
update t_52 set account = account + 1000 where id  = 2;

# 提交事务
commit;

2、手动事务：启用事务转账，成功提交事务（回滚点）

# 开启事务
start transaction;

# Tom扣钱
update t_52 set account = account - 1000 where id= 1;

# 设置回滚点
savepoint sp1;

# Lucy收钱
update t_52 set account = account + 10000 where id= 2;

# 操作失败回到回滚点
rollback to sp1;

# Lucy收钱
update t_52 set account = account + 1000 where id= 2;

# 提交事务
commit;

3、自动事务

show variables like 'autocommit';

set autocommit = 0; 

insert into t_52 values(null,'Liu',1000);
commit;

小结

1、事务处理要应用到多次写操作组成的大事务中，如金融安全等

2、事务处理通常都会使用手动控制事务，没必要去修改原本的自动提交的机制，开启所有事务

3、扩展：事务处理的支持是有条件的

3、事务特点

概念

事务特点：事务处理具有ACID四大特性

小结

1、事务特点需要在对应事务操作时，结合多个用户来看才能看的完整和亲切

2、扩展

三、预处理

1、预处理

目标：了解预处理的概念，掌握预处理的基本处理方式和应用场景

概念

预处理：prepare statement，一种预先编译SQL指令的方式（然后命令执行）

步骤

1、要执行的SQL指令想使用预处理

2、发送预处理指令

3、执行预处理

示例

1、查询学生的SQL指令需要重复执行很多次

# 普通操作
select * from t_42;

# 预处理操作：发送预处理
prepare p1 from 'select * from t_42';

# 预处理操作：执行预处理
execute p1;

# 删除预处理
deallocate  prepare p1;

预处理原理

普通处理和预处理对比

graph TB
A(普通处理)-->B[接收SQL指令]
B-->C[编译SQL]
C-->D[执行SQL]
D-->E((返回结果))

A1(预处理)-->B1[接收预处理指令]
B1-->C1[编译预处理指令]
C1-->D1{是否执行}
D1-->|execute<br>以后都不需要执行接收SQL和编译SQL|E1[执行SQL<br>可重复执行]
D1-->|不执行|F1
E1-->F1((返回结果))

小结

1、预处理就是把要执行的结构（SQL指令）提前发送给服务器端，服务器进行编译但不执行，等待执行指令后才执行

2、预处理的作用

2、预处理传参

概念

预处理传参：在执行预处理的时候传入预处理需要的可变数据

步骤

1、同样的SQL指令要执行N次，但是条件不一致

2、使用预处理占位符发送预处理指令

3、设定变量保存要传入的数据

4、执行预处理，携带变量参数

示例

向t_40表中插入数据

# 准备预处理：涉及参数
prepare t_40_insert from 'insert into t_40 values(null,?,?,?,?)';

# 设置变量并传入参数
set @name = '药师兜';
set @gender = '男';
set @age = 23;
set @class_name = '木叶1班';

# 执行预处理
execute t_40_insert using @name,@gender,@age,@class_name;

小结

1、预处理传参是实际应用预处理时最常见的方式

2、预处理指令可以适用于增删改查各种指令

3、如果预处理的指令不是在一次连接中重复使用，那么预处理反而会降低效率。所以预处理的执行如果不是考虑到安全因素，那么一定是SQL需要重复执行

四、视图

1、视图

概念

视图：view，一种由select指令组成的虚拟表

# 创建视图
create view 视图名字 as select指令;

# 访问视图：一般都是查询
select */字段名 from 视图名字;

步骤

1、确定需要使用视图提供数据

2、使用视图

示例

1、需要对外提供一个学生详情的数据，经常使用：可以利用视图实现

# 对外提供数据，要保护数据本身的安全
# 需要长期使用

# 创建视图
create view v_student_info as select * from t_45 left join t_46 using(c_id);
# 使用视图：像表一样使用
select * from v_student_info;

2、有些复杂的SQL又是经常用到的，如多张表的连表操作：可以利用视图实现

# 院系表
create table t_53(
    id int primary key auto_increment,
    name varchar(50) not null
)charset utf8;
insert into t_53 values(null,'语言系'),(null,'考古系');

# 专业表
create table t_54(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '学院id'
)charset utf8;
insert into t_54 values(null,'English',1),(null,'Chinese',1);

# 学生表
create table t_55(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '专业Id'
)charset utf8;
insert into t_55 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1);

# 获取所有学生的明细信息
select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

# 以视图保存这类复杂指令，后续可以直接访问视图
create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

select * from v_student_detail;

小结

1、视图是用来提供数据支持的，是由select指令组成的结构

2、视图的目的

2、视图管理

概念

视图管理：对视图结构的管理

show tables;    # 查看全部视图
show create table/view 视图名字;    # 查看视图创建指令
desc 视图名字;   # 查看视图结构

# 更改视图
alter view 视图名 as 新的查询指令;
create or replace view 视图名 as 新的查询指令;   # 创建新的或者替换新的

drop view 视图名;

示例

1、查看全部视图和视图明细

show tables;    # 查看全部表，包括视图
desc v_student_detail;  # 查看视图结构
show create view v_student_detail;  # 查看视图创建明细

2、修改视图：重置视图数据逻辑

alter view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);
create or replace view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);

3、删除视图

drop view v_student_info;

小结

1、视图操作与表操作类似，通常情况下不会经常的去修改维护，而是会在一开始就维护好

2、视图管理可以与表一样对结构进行管理

3、视图数据操作

概念

视图数据操作：直接对视图进行写操作（增删改）然后实现基表数据的变化

步骤

1、根据需求确定需要使用视图

2、确定允许视图进行数据操作（通常用户权限设定，且是单基表视图）

3、确定视图数据的操作是否需要操作检查（有where条件筛选，且只对新增和更新有影响）

4、使用视图进行数据操作（最终数据写落点是基表）

示例

1、增加一个单表视图和多表视图

create view v_student_1 as select s_id,s_name from t_45;
create view v_student_2 as select s.*,c.c_name from t_45 s left join t_46 c using(c_id);
create or replace view v_student_3 as select * from t_45 where c_id is not null with check option;

2、新增数据

insert into v_student_1 values(null,'student7');    # 正确：视图包含所有必有字段
insert into v_student_2 values(null,'student8',null,null); # 错误：不可插入
insert into v_student_3 values(null,'student8',null);   # 错误：check option，因为第三个字段c_id为NULL，不符合视图筛选条件，查不出来
insert into v_student_3 values(null,'Student9',1);  # 正确

3、更新数据

update v_student_1 set s_name = 'boy' where s_id = 8;
update v_student_2 set s_name = 'boy' where s_id = 7; # 错误：不可修改
update v_student_3 set c_id = null where s_id = 1;    # 错误：check option，修改后c_id为null，变得不符合视图筛选条件了
update v_student_3 set s_name = 'boy' where s_id = 1; # 正确

4、删除数据

delete from v_student_1 where s_id = 2;
delete from v_student_2 where s_id = 3; # 错误：不可删除
delete from v_student_3 where s_id = 1; # 可以删除，说明with check option不影响删除操作

小结

1、视图数据操作一般情况下是不允许的，通常之所以对外提供视图就提供数据的只读操作

2、视图数据操作与视图的基表数量和字段有关

3、with check option是针对有where条件的视图组成有效，需要手动选择是否增加该选项

4、视图算法

概念

视图算法：指视图在执行过程中对于内部的select指令的处理方式

create ALGORITHM = 算法 view 视图名字 as select指令;

步骤

1、确定使用视图

2、确定视图算法：考虑视图内部SQL指令中的子句使用情况

3、创建视图并使用视图

示例

1、创建三种不同算法视图

create algorithm = undefined view v_student_4 as select * from t_42 order by age desc;
create algorithm = merge view v_student_5 as select * from t_42 order by age desc;
create algorithm = temptable view v_student_6 as select * from t_42 order by age desc;

2、使用视图：为了体现算法效果，给视图增加分组效果

select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_4 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_5 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_6 group by class_name;

3、临时表算法的视图不能进行数据插入操作

insert into v_student_6 values(null,'冥加','男',100,'神妖1班'); # 错误：不可插入

小结

1、视图算法是用来结合外部外的查询指令的优化思路，主要的优化方式有两种

2、一般在设计视图的时候要考虑到视图算法的可行性，通常视图中如果出现了order by排序的话，就要考虑使用temptable算法

五、数据备份与还原

概念

备份：backup，将数据或者结构按照一定的格式存储到另外一个文件中，以保障阶段数据的完整性和安全性

还原：restore，在当前数据出问题的情况下，将之前备份的数据替换掉当前数据，保证系统的持续、正确的运行

小结

1、数据的备份与还原是作为一个正常运行的数据库必须做的事情

2、数据库的备份与还原是作为一个DBA最基本的技术要求（开发者也要会）

1、表数据备份

概念

表数据备份：单独针对表里的数据部分进行备份（数据导出）

select 字段列表|*  into outfile 外部文件路径 
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
from 数据表;

步骤

1、确定需要对表数据进行导出处理（备份），而且不需要考虑字段名字

2、确定导出的数据的处理

3、执行表数据导出

示例

1、将t_40表的数据按照默认的方式导出到文件

select * into outfile  'D:/t_40.csv' from t_40;

select name,gender,age,class_name into outfile 'D:/t_40_self.csv'
    fields terminated by '-' enclosed by '"'
    lines starting by 'GO:'
from t_40;

3、多表数据导出：t_45连接t_46表

select * into outfile 'D:/t_45_46.csv' from t_45 left join t_46 using(c_id);

小结

1、表数据备份是一种将表中的数据按照一定的格式导出到外部文件进行保存

2、表数据备份通常是为了进行数据加工后存入回表中，或者到其他表

3、目前比较少用这种方式进行数据备份

2、表数据还原

目标：了解表数据还原的概念，了解表数据还原所能解决的问题
概念

表数据还原：将符合数据表结构的数据导入到数据表中（数据导入）

load data infile '数据文件所在路径' into table 表名
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
    [(字段列表)];   # 如果是部分表字段，那么必须将字段列表放到最后

步骤

1、数据文件里的数据满足数据表的字段要求

2、数据文件里的数据可以通过字段加工、行加工处理满足表字段要求

3、使用数据导入

示例

1、将t_40.csv数据导入到db_3数据库中的一个与t_40表结构一致的表中

create table t_40 like db_2.t_40;

load data infile 'D:/t_40.csv' into table t_40; # 有可能因为字符集出现问题           
load data infile 'D:/t_40.csv' into table t_40 charset utf8;

注意：数据加载的时候需要注意外部数据的字符集，在加载的时候需要指定字符集为外部文件数据格式，在表后增加字符集charset 外部文件数据字符集
2、将t_40_self文件里的数据导入到db_3.t_40表中

load data infile 'D:/t_40_self.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'GO:' (name,gender,age,class_name) ;

小结

1、表数据还原其实是将外部符合条件的数据，按照一定的格式要求导入到数据表中

2、数据导入可以解决不同格式数据或者不同数据库产品间的数据互相导入到对应数据库产品的问题

3、目前较少使用这种方式进行数据导入：数据的生成应该是业务产生，而不是人工参与（破坏数据的客观有效性，使得数据不真实）

3、文件备份

概念

文件备份：直接对数据表进行文件保留，属于物理备份

步骤

1、设定备份时间节点

2、设定备份文件存储位置

3、确定备份表的存储引擎

4、根据节点进行文件备份：将文件转移（复制）到其他存储位置

示例

1、MyIsam表的文件备份：找到三个文件，复制迁移

小结

1、文件备份是一种简单粗暴的数据备份方式，是直接将数据文件打包管理的方式

2、文件备份方式非常占用磁盘空间

4、文件还原

概念

文件还原：利用备份的文件，替换出现问题的文件，还原到备份前的良好状态

步骤

1、找到出问题的数据文件

2、将备份数据放到相应删除的文件位置

示例

1、MyIsam数据备份表的数据迁移：单表迁移到不同数据库

2、InnoDB数据备份完成整个数据库的迁移（包括数据库用户信息）

小结

1、文件备份的还原通常使用较少

2、文件备份与还原通常可以在数据迁移的情况下使用

5、SQL备份

概念

SQL备份：将数据库的数据以SQL指令的形式保存到文件当中，属于逻辑备份

步骤

1、确定备份的时间：通常是有规则的时间备份

2、确定备份的目标级别：全库、单库、数据表

3、使用mysqldump实现备份

示例

1、全库备份（借助于Windows下的cmd访问mysqldump.exe，当前用户使用root账号）

mysqldump.exe -uroot -proot --all-databases > D:/mysql.sql
2、单库备份

mysqldump -uroot -proot --databases db_2 > D:/db_2.sql
3、单表备份（没有创建数据库的指令）

mysqldump -uroot -proot db_2 t_40 t_42 > D:/t_40_42.sql

小结

1、SQL备份是一般更新频次不高的数据库的常用备份方式

2、SQL备份是将数据表（库）以SQL指令形式进行备份

3、SQL备份能够完成的备份结构和数据，而结构和数据又是独立的，所以比较方便用作备份和还原

6、SQL还原

概念

SQL还原：在需要用到SQL备份数据时，想办法让SQL执行，从而实现备份数据的还原

mysql.exe -h -P -u -p [数据库名字] < SQL文件路径

source SQL文件路径;

步骤

1、确定数据库（表）需要进行数据还原

2、找到对应节点的SQL备份文件

3、SQL还原

示例

1、使用mysql客户端对db_2的数据文件进行单库还原（通常针对数据库）

mysql.exe -uroot -p < D:/db_2.sql

mysql.exe -uroot -p db_2 < D:/t_40_42.sql

2、在进入数据库之后，使用source指令还原SQL备份（通常针对表）

source D:/t_40_42.sql;

小结

1、SQL还原是利用SQL备份文件，触发SQL指令执行，从而恢复到指定时间点的结构和数据

2、SQL还原不能百分百保证数据库的数据不受影响

7、总结

1、数据的备份与还原是作为数据库管理者和使用者都应该掌握的一项技能

2、数据库备份与还原的方式很多，每一种都有自己的特点和适用点，需要我们熟练区分和选择

3、数据库的备份与还原是一门学问，所以不同的企业、业务都会选择不同的备份策略，也有可能使用交叉策略备份来确保数据的安全，而且一般会将备份文件与运行环境分离开来以确保数据真正的隔离和安全。

六、用户管理

学习目标：了解用户管理的目标，掌握用户管理在实际开发过程中的应用

1、账号管理

概念

账号管理：根据项目的需求设置和管理账号

步骤

1、根据项目要求创建用户

2、根据项目要求删除用户

示例

1、根据项目情况，跟不同的项目组创建不同的账号

# A团队只允许在公司访问服务器，公司IP为163.177.151.110
create user `admin`@`163.177.151.110` identified by 'admin123';

# B团队不限定负责数据库管理，不限定工作地点
create user `admin` identified by 'admin321';

2、开发任务结束，A团队的任务已经完成，不需要进行数据库操作

drop user `admin`@`163.177.151.110`;

小结

1、账号管理是用户管理的基础，但是账号管理也只是用户管理的一部分

2、权限管理

概念

权限管理：对账号进行权限的支持与回收

Privilege Grant Table Column Context
ALL [PRIVILEGES] Synonym for “all privileges” Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROLE Create_role_priv Server administration
CREATE ROUTINE Create_routine_priv Stored routines
CREATE TABLESPACE Create_tablespace_priv Server administration
CREATE TEMPORARY TABLES Create_tmp_table_priv Tables
CREATE USER Create_user_priv Server administration
CREATE VIEW Create_view_priv Views
DELETE Delete_priv Tables
DROP Drop_priv Databases, tables, or views
DROP ROLE Drop_role_priv Server administration
EVENT Event_priv Databases
EXECUTE Execute_priv Stored routines
FILE File_priv File access on server host
GRANT OPTION Grant_priv Databases, tables, or stored routines
INDEX Index_priv Tables
INSERT Insert_priv Tables or columns
LOCK TABLES Lock_tables_priv Databases
PROCESS Process_priv Server administration
PROXY See proxies_priv table Server administration
REFERENCES References_priv Databases or tables
RELOAD Reload_priv Server administration
REPLICATION CLIENT Repl_client_priv Server administration
REPLICATION SLAVE Repl_slave_priv Server administration
SELECT Select_priv Tables or columns
SHOW DATABASES Show_db_priv Server administration
SHOW VIEW Show_view_priv Views
SHUTDOWN Shutdown_priv Server administration
SUPER Super_priv Server administration
TRIGGER Trigger_priv Tables
UPDATE Update_priv Tables or columns
USAGE Synonym for “no privileges” Server administration

步骤

1、创建新的用户账号

2、根据需求赋予/回收指定数据库（一般整库）或者指定数据表的操作权限

3、刷新权限

示例

1、给用户admin@localhost分配权限：db_2下所有表的所有权限

create user `admin`@`localhost` identified by '123456';
grant all privileges on db_2.* to `admin`@`localhost`;

2、给用户admin分配权限：db_2下的查看视图权限

grant select on db_2.v_student_1 to `admin1`;
grant select on db_2.v_student_2 to `admin1`;
grant select on db_2.v_student_3 to `admin1`;
grant select on db_2.v_student_4 to `admin1`;
grant select on db_2.v_student_5 to `admin1`;
grant select on db_2.v_student_6 to `admin1`;

3、回收权限

# 如果用户不要了，可以直接删除用户，保留用户不给权限，就回收全部权限
revoke all on db_2.* from `admin`@`localhost`;

# 针对单独授权表的权限回收：只能针对表进行操作
revoke select on db_2.v_student_1 from `admin1`;

小结

1、权限管理是整个用户管理的核心：账号只能让用户能够连接服务器，而权限管理才能给用户提供各类操作

2、权限的操作是根据使用账号的用户需要出发的

3、扩展：可以直接使用赋权创建新用户（MySQL7以上不允许这么操作）

grant select on db_2.* to `user`@`localhost` with grant option;

3、角色管理

概念

角色管理：role，即根据角色来分配权限，然后用户只需要关联角色即可（分配角色）：Mysql8以后才有的

步骤

关联角色

1、创建角色

2、确定角色的权限：给角色分配权限

3、将角色分配给用户（和第2步可以没有先后关系）

取关角色

1、权限过大：回收角色权限

2、放弃角色：删除角色

示例

1、创建用户角色，分配给具有同样权限的用户

# 创建角色（角色与用户名很相似）
create role developer,app_read,app_write;

# 给角色分配权限
grant all on db_2.* to developer;
grant select on db_2.* to app_read;
grant insert,update,delete on db_2.* to app_write;

# 创建用户，并分配角色给用户
create user 'admin1'@'%' identified by '1234';
create user 'admin2'@'%' identified by '1234';
create user 'admin3'@'%' identified by '1234';

grant developer to 'admin1'@'%';
grant app_read to 'admin2'@'%','admin1'@'%'; # 允许批量给用户分配角色
grant app_write to 'admin3'@'%';

注意：虽然权限已经最终关联到用户，但是用户并不能真正使用权限，还需要权限分配者每次登陆服务器时激活角色：set default role all to 用户名@主机地址（一次只能激活一个角色）

2、回收角色权限或者角色

# 回收角色权限
revoke insert,delete on db_2.* from app_write;

# 回收角色
revoke app_read from 'admin2'@'%';

# 删除角色
drop role developer;

小结

1、角色管理是利用角色与权限关联，实现角色批量关联用户

2、角色的使用需要角色创建者（有权限的就行）激活角色才能使用（关联角色的用户需要重新登录才会生效）

上一篇下一篇

猜你喜欢

热点阅读