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操作

运行截图:
在这里插入图片描述
测试成功!

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值