18 | 权限管理：如何控制数据库访问，消除安全隐患？

你好，我是朱晓峰，下面我就来公布一下上节课思考题的答案： 上节课，我们学习了触发器。下面是思考题的答案： DELIMITER // CREATE TRIGGER demo.upd_importdetails AFTER UPDATE ON demo.importdetails FOR EACH ROW BEGIN UPDATE demo.importhead SET quantity = IFNULL(quantity,0) - OLD.quantity + NEW.quantity, importvalue = IFNULL(importvalue,0) - OLD.importvalue + NEW.importvalue WHERE listnumber = NEW.listnumber; END // DELIMITER ;

有一点要注意，在使用 Windows + SQL Server 技术栈时，不要把操作系统的用户等同于数据库的用户。

工作中负责的一个项目，对数据库的访问控制非常严格，屡受其害。 MySQL 的权限控制感觉非常灵活，下个版本安排上。

既然老师指出了这个「坑」，那我必须要记录下来了。 权限这块儿的知识点，之前只是了解，实际开发中没太把权限的控制放在数据库用户上面去做。今天这节，补足了我这块之前模糊的理解。 MySQL 8.0开始支持创建权限集合的角色，用来集中管理拥有同一批权限的用户了。

给大家总结下： 1.操作权限 创建角色 CREATE ROLE 角色名; CREATE ROLE 'manager'@'localhost'; 不写主机名 默认是通配符% 可以从任何主机登录 角色授权 GRANT 权限 ON 表名 TO 角色名; GRANT SELECT,INSERT,DELETE,UPDATE ON demo.user TO 'manager'; 查看角色权限 SHOW GRANTS FOR 'manager'; 删除角色 DROP ROLE 角色名称; 2.操作用户 创建用户 CREATE USER 用户名 [IDENTIFIED BY 密码]; create user bb identify by 123; 给用户授权 直接授权 GRANT 角色名称 TO 用户名称; 通过角色授权 GRANT 权限 ON 表名 TO 用户名; 查看用户权限 SHOW GRANTS FOR 用户名; 删除用户 DROP USER 用户名; 3.注意点 mysql创建角色后 默认是没有激活的 需要激活 SET global activate_all_roles_on_login=ON; mysql8开始才支持角色

请教老师，某个特定角色是否被激活的状态如何查询呢？

-- 创建财务角色 CREATE ROLE 'accountant’; -- 授予财务角色对商品信息表、盘点表有只读的权限 GRANT SELECT ON demo.goodsmaster TO 'accountant'; GRANT SELECT ON demo.invcount TO 'accountant’; -- 授予财务角色对应付账款表有增删改查的权限 GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'accountant'; -- 创建lisi用户 CREATE USER 'lisi' IDENTIFIED BY 'mysql'; -- 授予lisi的财务角色 GRANT 'accountant' TO 'lisi’; -- 激活所有角色登录权限 SET global activate_all_roles_on_login=ON;

补充： 撤销角色某个权限: REVOKE 权限 ON 表名 FROM 角色名; 撤销某个用户角色: REVOKE 角色名 FROM 用户名; 修改密码相关后执行: FLUSH PRIVILEGES;­【刷新MySQL的系统权限相关表】 -- 创建角色 CREATE ROLE ’test_role‘; -- 删除角色 DROP ROLE 'test_role'; -- 给角色赋予权限: GRANT SELECT,UPDATE ON demo.test_table TO 'test_role'; -- 撤销角色某个权限 REVOKE INSERT ON demo.test_table FROM 'test_role'; -- 创建用户 CREATE USER 'test_user'; -- 删除用户 DROP USER 'test_user'; -- 给用户授权角色 GRANT 'test_role' TO 'test_user'; -- 撤销用户的角色 REVOKE 'test_role' FROM 'test_user';

SET global activate_all_roles_on_login=ON; 显示【SQL 錯誤 (1193)：Unknown system variable 'activate_all_roles_on_login' */】

可不可以理解角色就是为了快速给用户赋权？