MySQL每个用户只对自己的记录有select权限
原题再现
**今有以下两个关系模式 :
职工(职工号,姓名,年龄,职务,工资,部门号)
部门(部门号,名称,经理名,地址,电话号)
请用SQL的 GRANT和 REVOKE语句完成以下授权定义或存取控制功能 : **
每个职工只对自己的记录有 SELECT权限。
这个题目是我最近的一次数据库系统上机实验碰到的题目,来源于课本习题,课程要求在MySQL里实现这道题的操作,作为一个还在学习过程中的菜鸟,说实话,我也不太会这个题,于是我在网上搜索了一下看看大佬们怎么说,我看到的普遍说法是mysql里无法直接直接在 表 上精准实现权限授予,但是可以借助视图来实现。
- 麻烦一点的方法是给每个用户单独创建一个视图然后分别授权,这个方法存在的问题是用户数量很多时,emm…,这样视图就要创建很多个视图,过于繁琐!
- 较好的方法是创建一个视图,创建时用 user() 函数获取当前登录的用户名,然后从 表 中查找该用户名,把查找到的该用户的信息放入视图中;给每个用户授予对视图的select权限。
习题来源 :
《数据库系统概论》王珊,萨师煊 编著(第五版)
第 155 页习题第 7 题的第 (3) 小题。
1.运行环境
-
操作系统:
- Windows 10 家庭中文版(1809) MySQL版本:
- mysql 5.7.27 打包发行版(须手动写配置文件并手动创建mysql服务)
我已经在配置文件内选择好了字符集编码,以确保使用中文不会乱码
2.创建习题中涉及到的用户
原题中共涉及到6个用户,为了便于测试,密码统一设置为123456;
mysql创建用户的格式为:
create user ‘用户名’@‘主机’ identified by ‘密码’;
flush privileges; -- 刷新权限(刷新一下比较稳妥)
create user '王明'@'localhost' identified by '123456';
create user '李勇'@'localhost' identified by '123456';
create user '刘星'@'localhost' identified by '123456';
create user '张新'@'localhost' identified by '123456';
create user '周平'@'localhost' identified by '123456';
create user '杨兰'@'localhost' identified by '123456';
flush privileges; -- 刷新权限
运行截图:
3.创建测试所需的数据库和表
为了写出的实验报告比较直观,以便于给我的任课老师批改,所以以下代码含有中文,如果会出现乱码,替换为英文单词即可。
-- 这是我的第2次上机实验,所以数据库名随手命名为了 test2
create database test2; -- 创建数据库
use test2; -- 使用该数据库
create table 职工( -- 创建职工表
职工号 char(10) primary key, -- 主码
姓名 varchar(18),
年龄 tinyint unsigned, -- 无符号tinyint,适合于与年龄相关的数据
职务 varchar(24),
工资 int,
部门号 char(10)
);
运行截图:
4.向职工表中插入测试数据
insert into 职工 values('1485673838','王明',35,'总经理',8500,'362567');
insert into 职工 values('7366462592','李勇',29,'架构师',6200,'453302');
insert into 职工 values('8663553524','刘星',30,'程序员',5450,'674865');
insert into 职工 values('8663550057','张新',32,'程序员',5850,'674865');
insert into 职工 values('4726576355','周平',30,'管理员',6100,'506830');
insert into 职工 values('2097578002','杨兰',24,'设计师',4900,'805311');
运行截图:
5.创建视图(这里划重点!)
这里是实现题目要求的重点代码
create view 职工视图 as select * from 职工 where concat(姓名,'@localhost') = user();
concat() 是字符串连接函数,user()函数获取当前用户
使用 select user(); 命令可以查看当前用户,获取到的格式为 ‘用户名’@‘localhost’,所以用 concat() 函数对职工表中存储的姓名进行处理,确保 ‘=’ 条件能够成立,否则视图为空,不符合题目要求。
运行截图:
6.将视图的select权限授予给每个用户
grant select on test2.职工视图 to
'王明'@'localhost','李勇'@'localhost','刘星'@'localhost',
'张新'@'localhost','周平'@'localhost','杨兰'@'localhost';
运行截图:
7.权限测试
随便使用一个用户账号登录MySQL,进行select操作
以用户刘星为例,登录数据库,对视图进行select操作
运行截图:
测试成功!