中南大学 数据库系统安全课程设计报告

1、CENTRAL SOUTH UNIVERSITY 数据库系统安全课程设计报告 题 目 大学学生成绩管理系统 学生姓名 专业班级 学 号 学 院 信息科学与工程学院 指导教师 李祖德 实验时间 2015年01月 目录第一章 问题描述及设计目标2第二章 系统需求分析3 2.1 需求描述3 2.2 系统功能结构3 2.3 数据流图4第三章 数据库设计5 3.1 全局E-R图5 3.2 数据字典5 3.3 数据库内关系表定义 7第四章 数据库实现 8 4.1 数据库创建SQL代码8 4.2 关系表（及相关视图）创建SQL代码8 4.3 存储过程、触发器等的创建SQL代码14第五章 RBAC应用场境描述

2、18 5.1 场境1描述18 5.2 场境2描述20 5.3 场境3描述22 5.4 场境4描述23第六章 心得体验26第七章 总结26第一章 问题描述及设计目标1.1 问题描述随着学校规模的不断扩大，专业、班级、学生的数量急剧增加，有关学生选课的各种信息量也成倍增长，而目前许多高校的学生成绩管理仍停留在复杂的人工操作上，重复工作较多，工作量大，效率低。除此之外，虽然大量数据库实现了基于角色的访问控制功能，却没有对其特征集达成一致。缺乏广为接受的模型，导致了对基于角色的访问控制作用和含义理解的不规范性和不确定性。基于以上情况，为了解决学生成绩查询和管理上遇到的实际困难，设计了一套基于RBAC模

3、型的大学学生成绩管理系统，使学生成绩的录入和管理更加信息化、智能化，使成绩的查询更加高效，系统价值有了更高的体现。1.2 设计总目标（1）掌握如何在真实数据库系统中实现基于角色访问控制（RBAC）模型。（1）掌握如何在特定软件系统中基于RBAC模型访问和操控数据库。1.3 设计总要求· 在真实数据库系统中实现RBAC模型· 开发应用演示界面，演示如何基于RBAC访问某数据库o 演示界面最好是可实际操作的可视化界面，要能演示。o 如果未可视化，必须演示代码运行过程及结果。· 数据库依据应用系统的需求而定，应用系统从如下列表中选择。1.4 RBAC简介 基于角色访问控

4、制（RBAC）是当前主流的数据库访问控制技术。RBAC的一般模型如下：第二章 系统需求分析2.1 需求描述 2.1.1 学生成绩管理 负责对学生成绩的录入、删除、更改、查询以及其他功能的管理。 (1)管理员负责学生表、教师表、课程表等基本表的基本数据的录入、删除和更改，并且可修改成绩表的选课情况。 (2)教师、学生分别可以查询及更改教师表、学生表中属于自己的相应数据。 (3)教师可以查询及更改成绩表中所教学生的成绩，学生只能查询成绩表中属于自己的成绩。 2.1.2 用户和权限管理 实现对系统用户以及不同角色的权限的管理 （1）管理员可以创建、删除系统用户 （2）用户可以在登录系统后修改自己的相

5、应资料  （3）每个用户在创建时须赋于相应角色 （4）每个角色在被创建时可以赋于相应权限，其所具有的权限可以修改 （5）角色创建后可以修改、删除 （6）默认的系统角色：管理员 教师 班长 学生 系统结构图：界面显示模块主要面向用户完成相应操作，并收集操作相关信息，向上提交给逻辑处理模块。逻辑处理模块收集界面显示模块返还的信息，并判断数据的真实性和合法性，将合法数据封装好后提交给传输模块。本地管理模块通过与数据库连接池获取数据库连接后，将相关信息传输到数据库进行操作。数据库2.2 系统功能结构数据库连接池本地管理模块逻辑处理模块界面显示模块大学学生成绩管理系统成绩管理权限管理

6、用户管理角色更改管理角色查询管理成绩录入管理登录管理权限分配管理操作管理角色管理角色分配管理数据库系统结构图2.3 数据流图逻辑处理模块界面显示模块用户操作 用户操作信息 合法数据信息本地管理模块 传输数据数据库数据库连接池 查询结果信息 数据操作信息第三章 数据库设计3.1 全局E-R图全局E-R图如下：专业年龄性别班级姓名入学时间学号学生选课课程号年龄课程名课程教师性别学分教师编号教师姓名3.2 数据字典 3.2.1 表单信息数据库表名说明users用户表roles角色表user_role用户角色映射表Permissions权限表role_permission角色权限映射表Student学

7、生信息表Teacher教师信息表Course课程信息表SCT成绩表LoginInfo登录用户信息表 3.2.1 各表信息表名:usersuserid用户IDusername用户姓名userpassword用户密码表名:rolesrolerid角色IDrolename角色名称表名:user_roleuserid用户IDroleid角色ID表名:PermissionsPid权限IDOperation执行动作objtable/view对象表/视图objcolumn对象列名表名:role_permissionroleid角色IDPid权限ID表名:StudentSno学号Sname姓名Ssex性别Sa

8、ge年龄Sdept专业Sentertime入学时间Sclass班级表名:TeacherTno教师编号Tname姓名Tsex性别Tage年龄表名:CourseCno课程编号Cname课程名Ccredit学分表名:SCTSno学号Sname姓名Cno课程编号Tno教师编号Grade成绩表名:LoginInfoCURRENTUSER当前登录用户ID3.3 数据库内关系表定义数据库内关系图如下：第四章 数据库实现4.1 数据库创建SQL代码USE masterGOCREATE DATABASE StudentGrade ON PRIMARY ( NAME = N'StudentGrade

9、9;, FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAStudentGrade.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'StudentGrade_log', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASt

10、udentGrade_log.LDF' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE StudentGrade SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')beginEXEC StudentGrade.dbo.sp_fulltext_database action = 'enable'endGO4.2 关系表（及相关视图）创建SQL代码S

11、tudent表的建立：CREATE TABLE dbo.Student(Sno varchar(20) NOT NULL,Sname varchar(20) NULL,Ssex char(2) NULL,Sage smallint NULL,Sdept varchar(20) NULL,Sentertime date NULL,Sclass varchar(50) NULL,PRIMARY KEY CLUSTERED (Sno ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW

12、_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOTeacher表的建立：CREATE TABLE dbo.Teacher(Tno varchar(9) NOT NULL,Tname varchar(20) NULL,Tsex char(2) NULL,Tage smallint NULL,PRIMARY KEY CLUSTERED (Tno ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW

13、_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOCourse表的建立：CREATE TABLE dbo.Course(Cno varchar(4) NOT NULL,Cname varchar(40) NULL,Ccredit smallint NULL,PRIMARY KEY CLUSTERED (Cno ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PA

14、GE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSCT表(成绩表)的建立：CREATE TABLE dbo.SCT(Sno varchar(20) NOT NULL,Sname varchar(20) NULL,Cno varchar(4) NOT NULL,Tno varchar(9) NOT NULL,Grade smallint NULL, CONSTRAINT PK_SCT_8AC4525145F365D3 PRIMARY KEY CLUSTERED (Sno ASC,Cno ASC,Tno ASC)WITH (PAD_INDEX = OFF, STATI

15、STICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.SCT WITH CHECK ADD CONSTRAINT FK_SCT_Cno_48CFD27E FOREIGN KEY(Cno)REFERENCES dbo.Course (Cno)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.SCT CHECK CONSTRAINT FK

16、_SCT_Cno_48CFD27EGOALTER TABLE dbo.SCT WITH CHECK ADD CONSTRAINT FK_SCT_Sno_47DBAE45 FOREIGN KEY(Sno)REFERENCES dbo.Student (Sno)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.SCT CHECK CONSTRAINT FK_SCT_Sno_47DBAE45GOALTER TABLE dbo.SCT WITH CHECK ADD CONSTRAINT FK_SCT_Tno_49C3F6B7 FOREIGN KEY

17、(Tno)REFERENCES dbo.Teacher (Tno)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.SCT CHECK CONSTRAINT FK_SCT_Tno_49C3F6B7GOusers表的建立：CREATE TABLE dbo.users(userid varchar(50) NOT NULL,username varchar(100) NOT NULL,userpassword varchar(100) NULL,userremark varchar(100) NULL, CONSTRAINT PK_users_

18、CBA1B257145C0A3F PRIMARY KEY CLUSTERED (userid ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOroles表的建立：CREATE TABLE dbo.roles(roleid int NOT NULL,rolename varchar(100) NULL,roleremark varchar(100) N

19、ULL, CONSTRAINT PK_roles_CD994BF21B0907CE PRIMARY KEY CLUSTERED (roleid ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOuser_role表的建立：CREATE TABLE dbo.user_role(connectionid int IDENTITY(1,1) NOT NULL

20、,userid varchar(50) NOT NULL,roleid int NOT NULL,Premark varchar(100) NULL, CONSTRAINT PK_user_rol_A042C108267ABA7A PRIMARY KEY CLUSTERED (connectionid ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO

21、ALTER TABLE dbo.user_role WITH CHECK ADD CONSTRAINT FK_user_role_roles FOREIGN KEY(roleid)REFERENCES dbo.roles (roleid)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.user_role CHECK CONSTRAINT FK_user_role_rolesGOALTER TABLE dbo.user_role WITH CHECK ADD CONSTRAINT FK_user_role_users FOREIGN KEY

22、(userid)REFERENCES dbo.users (userid)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.user_role CHECK CONSTRAINT FK_user_role_usersGOPermissions表的建立：CREATE TABLE dbo.Permissions(Pid int NOT NULL,Operation varchar(50) NULL,objtable/view varchar(50) NULL,objcolumn varchar(50) NULL, CONSTRAINT PK_Pe

23、rmissi_C57059386383C8BA PRIMARY KEY CLUSTERED (Pid ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOrole_permission表的建立：CREATE TABLE dbo.role_permission(roleid int NOT NULL,Pid int NOT NULL,PRIMARY KEY

24、 CLUSTERED (Pid ASC,roleid ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.role_permission WITH CHECK ADD FOREIGN KEY(roleid)REFERENCES dbo.roles (roleid)ON UPDATE CASCADEON DELETE CAS

25、CADEGOALTER TABLE dbo.role_permission WITH CHECK ADD CONSTRAINT FK_role_permis_Pid_01142BA1 FOREIGN KEY(Pid)REFERENCES dbo.Permissions (Pid)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dbo.role_permission CHECK CONSTRAINT FK_role_permis_Pid_01142BA1GOLoginInfo表的建立：CREATE TABLE dbo.LoginInfo(CURRE

26、NTUSER varchar(20) NOT NULL,PRIMARY KEY CLUSTERED (CURRENTUSER ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO视图Student1（学生信息视图，角色为学生可以查看）的建立：CREATE VIEW dbo.Student1ASSELECT *FROM dbo.StudentWHERE (

27、Sno = CURRENT_USER OR Sno = (SELECT CURRENTUSER FROM LoginInfo)GO视图Student2（同班学生信息视图，角色为班长可以查看）的建立：CREATE VIEW dbo.Student2ASSELECT *FROM dbo.StudentWHERE (Sclass = (SELECT Sclass FROM Student WHERE Sno = CURRENT_USER OR Sno = (SELECT CURRENTUSER FROM LoginInfo)GO视图Student1（教师信息视图，角色为教师可以查看）的建立：CREA

28、TE VIEW dbo.Teacher1ASSELECT *FROM dbo.TeacherWHERE (Tno = CURRENT_USER OR Tno = (SELECT CURRENTUSER FROM LoginInfo)GO视图SCT1（成绩信息视图，角色为学生或教师可以查看）的建立：CREATE VIEW dbo.SCT1ASSELECT *FROM dbo.SCTWHERE (Sno = CURRENT_USER OR Sno = (SELECT CURRENTUSER FROM LoginInfo) OR (Tno = CURRENT_USER OR Tno = (SELEC

29、T CURRENTUSER FROM LoginInfo)GO4.3 存储过程、触发器等的创建SQL代码存储过程LOGIN：CREATE PROCEDURE dbo.LOGIN userid varchar(50),userpassword varchar(100)ASIF (SELECT userpassword FROM users WHERE userid = userid) = userpasswordBEGINprint '登录成功'UPDATE dbo.LoginInfo SET CURRENTUSER = useridENDELSEBEGINprint '

30、用户名或用户密码错误，请重新输入'ENDGO存储过程PermissionJudge：CREATE procedure dbo.PermissionJudge ope varchar(20),tname varchar(20),obj varchar(20)asif (SELECT * FROM LoginInfo)=ANY(select userid from user_role where roleid in (select roleid from role_permission where Pid in (select Pid from Permissions where (obj

31、table/view=tname or objtable/view = 'ALL') and (objcolumn = obj or objcolumn = 'ALL' ) and (Operation = ope or Operation = 'ALL' )/*从permissions表中找到输入操作的权限ID,逐一匹配，找到拥有此权限的用户ID，对输入的操作进行匹配*/beginreturn 1 -如果匹配成功，返回endelsebeginreturn 0 -否则，返回EndGO存储过程AddGrade：CREATE procedure db

32、o.AddGradesno varchar(10),sname varchar(20),cno char(10),tno varchar(20),grade int-参数依次为，学号，学生姓名，课程编号，教工号，课程名asdeclare a intexec a = PermissionJudge 'INSERT','SCT1','ALL'if (a=1)beginprint'你拥有此权限，可以进行相关操作'insert into SCT1 values(sno,sname,cno,tno,grade)select sno,snam

33、e,cno,tno,grade from SCT1 where sno=sno and cno=cnoendif(a=0)beginprint '你无权进行此操作，如有疑问请与管理员联系'endGO存储过程DoDELETE：CREATE procedure dbo.DoDELETE table varchar(20),no1 varchar(20),str1 varchar(20),no2 varchar(20),str2 varchar(20)-参数依次为删除的表名，标签列名1（便于筛选），标签列名的数据，标签列名2（便于筛选），标签列名的数据as declare a int

34、declare sql nvarchar(max)exec a = PermissionJudge 'DELETE',table,'ALL'if (a=1)beginprint'你拥有此权限，可以进行相关操作'set sql='delete from '+table+' where '+no1+ '=' +str1+' and '+no2+'='+str2exec(sql)endif(a=0)beginprint'你无权进行此操作,如有疑问请与管理员联系

35、9;endGO存储过程DoUPDATE：CREATE procedure dbo.DoUPDATEtable varchar(20),obj varchar(20),no varchar(20),num varchar(20),oldstr varchar(20),str varchar(20)-输入的参数依次为，修改的表名，修改的列名，标签列名（便于筛选），标签列名的数据，原来的数据，新的数据-标签列的作用在于：在选择更改的列时，某些列的数据是一样的，所以可能会造成误操作，为此增加标签列，防止误操作asdeclare a int -获取PermissionJudge的返回值declare s

36、ql nvarchar(max)exec a = PermissionJudge 'UPDATE',table,obj -调用PermissionJudge存储过程if (a=1) beginprint'你拥有此权限，可以进行相关操作'set sql='update '+table+' set '+obj+ '=' +str+' where '+no+'='+num+' and '+obj+'='+oldstr-采用动态语句，使表名可以作为参数引用ex

37、ec(sql)endif(a=0)beginprint '你无权进行此操作，如有疑问请与管理员联系'endGO存储过程DoSELECT：CREATE procedure dbo.DoSELECTtable varchar(20)-输入的参数为，查询的表名asdeclare a int -获取PermissionJudge的返回值declare sql nvarchar(max)exec a = PermissionJudge 'SELECT',table,'ALL' -调用PermissionJudge存储过程if a=1beginprint&#

38、39;你拥有此权限，可以进行相关操作'set sql='SELECT * FROM '+table-采用动态语句，使表名可以作为参数引用exec(sql)endif(a=0)beginprint '你无权进行此操作，如有疑问请与管理员联系'endGO存储过程LOGOUT：CREATE PROCEDURE dbo.LOGOUTASBEGINUPDATE dbo.LoginInfo SET CURRENTUSER = '0000'print '注销成功'ENDGO触发器UPDATE_SAL：CREATE TRIGGER dbo

39、.UPDATE_SAL ON dbo.Student AFTER UPDATEAS BEGINSET NOCOUNT ON; UPDATE SCT SET Sname = (SELECT Sname FROM Student WHERE Student.Sno = SCT.Sno) - 当学生名字更改时，同步更改成绩表中相应学号的学生名字ENDGO第五章 RBAC应用场境描述5.1 场境1：班长登录系统进行相关的查询和更改操作 打开SQL Server 2008，登录方式选择SQL Server身份验证，输入登录名:“0909120101”，密码：“120101”，连接。（用户09091201

40、01的角色为学生和班长）新建查询，输入“SELECT * FROM Student1”，结果如下：新建查询，输入“SELECT * FROM Student2”，结果如下：新建查询，输入“SELECT * FROM SCT1”，结果如下：新建查询，输入“SELECT * FROM Teacher1”，结果如下：新建查询，输入“UPDATE Student1 SET Sname='刘三' WHERE Sno='0909120101'”，结果如下：然后输入“SELECT * FROM Student1”，可以看到名字已改好。新建查询，输入“UPDATE Studen

41、t1 SET Sno='10000001' WHERE Sno='0909120101'”，结果如下：5.2 场境2描述:教师登录系统进行相关的查询和更改操作 打开SQL Server 2008，登录方式选择SQL Server身份验证，输入登录名:“09091001”，密码：“1001”，连接。（用户09091001的角色为老师）新建查询，输入“SELECT * FROM Student1”，结果如下：新建查询，输入“SELECT * FROM SCT1”，结果如下：新建查询，输入“SELECT * FROM Teacher1”，结果如下：新建查询，输入“UP

42、DATE SCT1 SET Grade='99' WHERE Sno='0909120101' AND Cno='1'”，结果如下：然后输入“SELECT * FROM SCT1”，可以看到成绩已改好。新建查询，输入“UPDATE SCT1 SET Sname='刘刘' WHERE Sno='0909120101'”，结果如下：5.3 场境3描述：普通学生登录系统进行相关的查询和更改操作 打开SQL Server 2008，登录方式选择SQL Server身份验证，输入登录名:“0909120202”，密码：“12

43、0202”，连接。（用户0909120202的角色为学生）新建查询，输入“SELECT * FROM Student1”，结果如下：新建查询，输入“SELECT * FROM Student2”，结果如下：此查询说明普通学生不具备查看同班同学信息的权限。新建查询，输入“SELECT * FROM SCT1”，结果如下：其他查询结果与5.1类似，这里不再赘述。5.4 场境4描述：管理员登录系统进行相关的查询和更改操作 打开SQL Server 2008，登录方式选择SQL Server身份验证，输入登录名:“10000001”，密码：“0001”，连接。（用户10000001的角色为管理员）如下所示，管理员具有对所有基本表、视图及存储过程的所有操作权限，对整个系统的建立、维护起着非常大的作用。这里演示管理员对几个基本表的查询操作以查看系