1.背景
当今社会是一个信息爆炸的社会，因为信息科技人们生活发生了巨大的变化，以计算机技术为代表的应用及普及到经济和社会生活的各个方面。这是属于一个大数据的时代，数据库系统几乎在每个领域都发挥着重大作用，用于加强对大量信息的管理，方便了人们的生活。
在大学里学生成绩管理如果靠人力将十分麻烦，有必要设计一个成绩管理系统，为了加强系统的安全性，引入了基于角色的访问控制模型（RBAC），应用该模型可以使不同用户登陆后得到不同的访问权限，不具有某个权限的用户将不能操作该权限，从数据库层进行权限控制有利于提高系统易用性，可扩展性，健壮性以及安全性。本次课程设计就是要设计一个基于RBAC的成绩管理系统，要从底层对用户权限进行控制，而不是在应用层。
2.系统需求分析
2.1需求描述
该成绩管理系统基于RBAC技术，能够对大学里各学生的成绩进行管理。系统有三个角色，分别是学生，教师以及管理员，管理员的权限最大。
学生通过该系统可以查询到自己某个课程的成绩，以及选择某个老师的某个课程。
教师可以录入所教课程学生的成绩，但不能修改，如果要修改只能联系管理员修改，还有查询有哪些学生选了他的课。
管理员拥有所有权限，包括删除用户，给用户重置登录密码，对各表的记录进行删除，增加，修改等。
该系统在微软的SQL SERVER 2008上进行开发，操作系统环境为Windows 7，通过写好的存储过程在数据库层完成各种操作，从而实现成绩管理。
2.2系统功能结构
2.3数据流图
3.数据库设计
3.1全局E-R图
ER图包括两个部分，一个有关用户权限分配，一个有关学生的成绩管理。
3.2数据字典
3.3数据库内关系表定义
用户信息（用户ID,用户名,用户密码）
用户-角色（用户ID,角色ID）
角色（角色ID，角色名）
角色—权限（角色ID，权限ID）
权限（权限ID，动作，对象，表名）
学生成绩（学号，课程号，姓名，成绩）
选课（课程号，课程名，学号，教师号）
教师（教师号，姓名，课程号）
加下划线的表示为主键。
4.数据库实现
4.1关系表（及相关视图）创建SQL代码
建立系统注册成员的信息表，包括用户唯一的账号，密码，姓名:
|
1 2 3 4 5 6 |
create table userinfo( userid int, username varchar(20), userpwd varchar(20), primary key(userid) ) |
建立角色信息表:
|
1 2 3 4 5 |
create table role( roleid int, rolename varchar(20) primary key(roleid) ) |
建立用户角色联系的表：
|
1 2 3 4 5 6 7 |
create table user_role( userid int, roleid int, foreign key(userid) references userinfo(userid) on delete cascade, foreign key(roleid) references role(roleid) on delete cascade, primary key(userid,roleid) ) |
建立角色权限联系的表：
|
1 2 3 4 5 6 7 |
create table role_permit( roleid int, permitid int, primary key(roleid,permitid), foreign key(roleid) references role(roleid) on delete cascade, foreign key(permitid) references permit(permitid) on delete cascade ) |
建立权限信息表：
|
1 2 3 4 5 6 7 |
create table permit ( permitid int, primary key(permitid), action varchar(20), object varchar(20), tablename varchar(20) ) |
建立学生成绩表，包括学号，姓名，课程号，成绩：
|
1 2 3 4 5 6 7 8 |
create table student( sno int, sname varchar(20), cno int, grade int, primary key(sno,cno), foreign key(cno) references course(cno) , ) |
建立教师表，包括教师号，姓名，课程名：
|
1 2 3 4 5 6 |
create table teacher( tno int, tname varchar(20), cno int, primary key(tno), ) |
建立选课表：
|
1 2 3 4 5 6 7 8 |
create table course( cno int, course varchar(20), sno int, tno int, primary key(cno), foreign key(tno) references teacher(tno) ) |
建立临时表，用于存储某个用户的权限：
|
1 2 3 4 5 6 7 |
create table temp( userid int, username varchar(20), action varchar(20), object varchar(20), tablename varchar(20), ) |
4.2存储过程、触发器等的创建SQL代码
用户注册：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create procedure zuce @userid int,@username varchar(20),@userpwd varchar(20) as if @userid in(select userid from userinfo) begin print '该账号已被注册,注册失败！！！请更换账号重新注册' end else begin insert into userinfo(userid,username,userpwd) values(@userid,@username,@userpwd) print '恭喜'+@username+'注册成功,欢迎使用本成绩管理系统' select userid as 用户ID,username as 用户姓名,userpwd as 密码 from userinfo where userid=@userid End |
修改密码:
|
1 2 3 4 5 6 7 8 9 10 |
create procedure xiugaipwd @uid int,@pwd int,@newpwd int as if @pwd=(select userpwd from userinfo where @uid=userid) begin update userinfo set userpwd=@newpwd where @uid=userid print'密码修改成功' end else print'输入密码错误，请重新输入' |
用户角色分配:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
create procedure rolefenpei @userid int,@roleid int as if exists(select * from user_role where userid = @userid) begin print '该用户原先已分配过其他角色，我们将重新分配角色' delete from user_role where userid = @userid insert into user_role values (@userid,@roleid); select username,rolename from role,userinfo,user_role where userinfo.userid = user_role.userid and role.roleid = user_role.roleid print '角色分配成功' end else begin insert into user_role values (@userid,@roleid); select username,rolename from role,userinfo,user_role where userinfo.userid = user_role.userid and role.roleid = user_role.roleid print '角色分配成功' end |
角色权限分配:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create procedure permitfenpei @roleid int,@permitid int as if exists (select * from role_permit where permitid = @permitid and roleid=@roleid) begin print '给角色已经分配了这个权限，无需再次分配' end else begin insert into role_permit values (@roleid,@permitid) print '权限分配成功' select rolename,action,object,tablename from role,permit,role_permit where role.roleid = role_permit.roleid and permit.permitid=role_permit.permitid; End |
删除角色某个权限:
|
1 2 3 4 5 6 |
create procedure deletepermit @roleid int ,@permitid int as delete from role_permit where roleid=@roleid and permitid=@permitid print '该角色此权限删除成功,其他权限为:' select rolename,action,object from role,permit,role_permit where role.roleid = role_permit.roleid and permit.permitid=role_permit.permitid; |
将某用户数据放入前面创建的一张临时表temp中,调用该存储过程可知用户有哪些权限：
|
1 2 3 4 5 6 7 8 |
create procedure user_permit @userid int as delete from temp insert into temp select @userid,username,action,object,tablename from permit,userinfo,role_permit,user_role where userinfo.userid=@userid and userinfo.userid = user_role.userid and user_role.roleid = role_permit.roleid and permit.permitid =role_permit.permitid |
从临时表得到数据，判断用户是否有此权限:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create procedure permitif @uid int ,@act varchar(20),@tna varchar(20),@obj varchar(20) as exec user_permit @uid if exists(select @uid,@act,@tna,@obj from temp where @uid=userid and @act=action and @tna=tablename and @obj=object) begin print'你拥有此权限，可以进行相关操作' end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
安排教师教授某个课程,只有管理员有这权限
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
create procedure jiaoke @uid int,@tno int,@tname varchar(20),@cno int as exec user_permit @uid if exists(select userid,action,tablename,object from temp where @uid=userid and action='增加' and tablename='course' and object='行') begin print'你拥有此权限，可以进行相关操作' insert into teacher values(@tno,@tname,@cno) select * from teacher end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
学生选课:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
create procedure xuanke @uid int,@cno int,@course varchar(20),@sno int,@tno int as exec user_permit @uid if exists(select userid,action,tablename,object from temp where @uid=userid and action='增加' and tablename='course' and object='行') begin print'你拥有此权限，可以进行相关操作' insert into course values(@cno,@course,@sno,@tno) select * from course end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
往student表添加一行数据，即录入成绩，由前面的权限分配可知只有教师管理员有这权限:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
create procedure saddhan @uid int,@sno int,@sname varchar(20), @cno int,@grade int,@course varchar(20) as exec user_permit @uid if exists(select @uid,action,tablename,object from temp where @uid=userid and action='增加' and tablename='student' and object='行') begin print'你拥有此权限，可以进行相关操作' insert into student values(@sno,@sname,@cno,@grade) select sno,sname,cno,@course as course,grade from student where sno=@sno and cno=@cno end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
往student表删除某行数据:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create procedure sdelhan @uid int,@act varchar(20),@tna varchar(20),@obj varchar(20),@sno int,@course varchar(20) as exec user_permit @uid if exists(select @uid,@act,@tna,@obj from temp where @uid=userid and @act=action and @tna=tablename and @res=object) begin print'你拥有此权限，可以进行相关操作' delete from course where sno=@sno and cno=@cno end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
删除某个用户:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create procedure deluser @uid int,@uid1 int as exec user_permit @uid if exists(select @uid,action,object from temp where @uid=userid and action='删除' and object='用户') begin print'你拥有此权限，可以进行相关操作' delete from userinfo where userid=@uid1 end else begin print'你无此操作权限，但你可以进行如下操作' select action,tablename,object from temp end |
5.应用场景描述（SQL SERVER 2008模拟
在场景描述前我们先为该系统注册了三个用户，分别是张三（ID:1,学生），李四（ID:2,教师），王五（ID：3，管理员），角色信息表按如下初始化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
insert into role(roleid,rolename) values(1,'学生') insert into role(roleid,rolename) values(2,'教师') insert into role(roleid,rolename) values(3,'管理员') 权限信息表按如下初始化： insert into permit values(1,'增加','行','student'); insert into permit values(2,'增加','列','student'); insert into permit values(3,'删除','行','student'); insert into permit values(4,'删除','列','student'); insert into permit values(5,'修改','行','student'); insert into permit values(7,'查找','表','student'); insert into permit values(8,'增加','行','course'); insert into permit values(9,'删除','行','course'); insert into permit values(10,'修改','行','course'); insert into permit values(11,'查找','行','teacher'); insert into permit values(12,'查找','表','teacher'); insert into permit values(13,'删除','行','teacher'); insert into permit values(14,'修改','行','teacher'); insert into permit values(15,'删除','用户','userinfo'); insert into permit values(16,'增加','行','teacher'); |
根据上述权限信息，我们给学生，教师，管理员分配了各种权限，示例场景如下：
1）给老师安排某个课程：
假设李四的教师号为2，我们要给李四安排教学1号课程，用到的存储过程为procedure jiaoke。
首先让学生张三安排，执行exec jiaoke 1,2,'李四','1'，得到结果：
消息提示该用户无此权限，同时左侧结果提示拥有的权限，避免盲目操作。
同理2号用户作为老师的李四执行也是相同结果，接着安排管理员，3号用户王五执行：exec jiaoke 3,2,'李四','1'，得到结果：
2)学生选课:
我们要给张三安排课程，假设安排的是课程号为1，课程名为计算机，李四老师上的课，对于选课，只有学生，管理员有这权限。
首先张三选课，假设张三学号为111，执行：
exec xuanke 1,1,'计算机',111,2,得到结果：
教师李四执行：exec xuanke 2,1,'计算机',111,2，结果为：
管理员执行时得到与学生相同的结果。
3)录入成绩
学生选完课后，就可以录入成绩了，此时只有老师管理员有此权限，首先让学生张三自己录入成绩，执行exec saddhan 1,111,'张三',1,95,’计算机’，系统提示无此操作权限。
2号教师执行：exec saddhan 1,111,'张三',1,95,’计算机’，得到结果：
Comments