SQL Server操作之数据库安全性和完整性

 一道题学会SQL Server安全性和完整性控制，包括创建数据库，创建数据库用户，权限赋予及收回等

题目如下：

1、在SQL SERVER 2008上附加teaching数据库,其中三张表的含义解释如下：

学生表dbo.student有属性sno、sname、spec、birthday、email、sex、scholarship,分别代表学号、姓名、专业、生日、电子邮箱、性别、奖学金；

课程表dbo.course有属性cno、cname、credit、teacher，分别代表课号、课程名、学分、任课教师；

选课表dbo.student_course有属性sno、cno、grade，分别代表学号、课号、成绩。

具体操作过程博主该系列上一文章。

 

2、SQL Server系统的安全性练习

1）视图技术可以使无权使用数据的用户不能接触他感兴趣的数据，请设计一个只能查看每个学生平均成绩的视图student_avg_grade（sno,sname,avg_grade）；

 

2）登录到SQL Server Management Studio后，先展开数据库服务器®安全性®登录名，通过右键单击登录名，在新建登录名的对话框中创建登录名S1；然后展开数据库teaching®安全性®用户，通过右键单击用户，在新建用户的对话框中创建用户名U1，并使数据库用户名U1与数据库服务器登录名S1相关联。

3）用grant和revoke语句进行授权和收权操作，具体如下：

（1）以S1登录数据库服务器，查看用户U1是否有权力查询teaching数据库中的Student表？为什么？

（2）以Administration身份登录数据库服务器，为U1用户授予查询student表的权力，同时允许U1将该权利授予其他用户；

（3）以S1登录数据库服务器，查看用户U1是否有权力查询teaching数据库中的Student表？

 

（4）收回用户U1查询student表的权力；

 

（5）再次以S1登录数据库服务器，查看用户U1的权限是否收回？

回收权限执行结果：

 

3、SQL Server系统的完整性练习。以teaching数据库为例，在SSMS中创建数据库S_C，在查询分析器中创建S、SC和C三张表时分别设置primary key、foreign key、not null、unique、check完整性约束，然后用实验数据证实当操作违反了完整性约束条件时，系统是如何处理的。

1）举例说明如何在S、C表中设置primary key、not null、unique完整性约束；

SQL Sever实现

--2、SQL Server系统的安全性练习--1）视图技术可以使无权使用数据的用户不能接触他感兴趣的数据，--请设计一个只能查看每个学生平均成绩的视图student_avg_grade（sno,sname,avg_grade）；use teachinggocreate view student_avg_grade(sno,sname,avg_grade)as select s.sno,s.sname,avg(sc.grade) from student s,student_course sc group by s.sno,s.sname go  --创建登录名 use teaching create login S1 with password='mimas1',default_database=teaching go --激活登录名 use teaching alter login S1 enable go  --创建用户 use teaching create user U1 for login S1 with default_schema=guest go --删除登录名 use teaching drop login S1 go --删除用户 use teaching drop user U1 go--验证是否能查询use teachingselect * from studentgo--赋予权限use teachinggrant select on student to U1 with grant optiongo--收回权限use teachingrevoke select on student from U1 cascadego--更新use teachingupdate studentset spec='计算机'where sno='0012301' and sname='杨海涛'go--3--SQL Server系统的完整性练习。以teaching数据库为例，在SSMS中创建数据库S_C，--在查询分析器中创建S、SC和C三张表时分别设置primary key、foreign key、--not null、unique、check完整性约束，然后用实验数据证实当操作违反了完整性约--束条件时，系统是如何处理的。USE master--使用系统GOCREATE DATABASE S_C --创建数据库ON PRIMARY--主文件(NAME='S_C_data', --文件名FILENAME='D:\SQLProject\S_C_data.mdf',--路径SIZE=5MB,--初始大小MAXSIZE=100MB,--最大容量FILEGROWTH=10%--增长速度)LOG ON--日志文件(NAME='S_C_log',FILENAME='D:\SQLProject\S_C_log.ldf',SIZE=5MB,FILEGROWTH=0)GO--3.1--举例说明如何在S、C表中设置primary key、not null、unique完整性约束；use S_Ccreate table S( sno char(11) not null primary key, creadit_ID char(16) not null unique, age int not null check(age<=0), sname varchar(20) not null)gocreate table C( cno char(5) not null primary key, cname varchar(20) not null)go--3.2--举例说明如何在SC表中设置foreign key、check完整性约束create table SC( sno char(11) not null foreign key(sno) references S(sno), cno char(5) not null foreign key(cno) references C(cno), grade numeric(5,0) not null check(grade<0), constraint SC_pk primary key(sno,cno))gouse S_Cdrop table S,Cgo--非法记录,check ageuse S_Cinsert into S(sno,creadit_ID,age,sname)values('14401010410','3301271996021617',18,'Jhon')go--合法记录use S_Cinsert into S(sno,creadit_ID,age,sname)values('14401010411','3311271996021617',0,'June')go--合法记录use S_Cinsert into S(sno,creadit_ID,age,sname)values('14401010412','3311271996021618',-1,'July')go--合法记录use S_Cinsert into Cvalues('12345','数据库')go--非法记录use S_Cinsert into Cvalues('123456','语文')go--合法记录use S_Cinsert into SC(sno,cno,grade)values('14401010411','12345',-2)go--非法记录,check gradeuse S_Cinsert into SCvalues('14401010412','12345',8)go

结果截图：