[大数据]sqlserver数据库练习5

前言

实验中所有的TeachingData数据库均为TeachingData2

讲解问题

--密码是区分大小写的。

--只有创建SQL Server登录时，才支持对密码预先进行哈希运算。

--如果指定MUST_CHANGE，则CHECK_EXPIRATION和 CHECK_POLICY必须设置为 ON。 否则，该语句将失败。

--不支持CHECK_POLICY=OFF和 CHECK_EXPIRATION=ON的组合。

--如果CHECK_POLICY设置为OFF，将对lockout_time进行重置，并将CHECK_EXPIRATION设置为OFF。

--只有在Windows Server 2003及更高版本上才会强制执行CHECK_EXPIRATION 和 CHECK_POLICY。 

--语法：
--create login login_name 
--with 
--password={ 'password' | hashed_password hashed }
--[must_change][,]
--[sid=0x14585E90117152449347750164BA00A7][,]
--[default_database=database_name][,]
--[default_language=language][,]
--[check_expiration={ on | off }][,]
--[check_policy={ on | off }][,]
--[credential=credential_name]

题目

1. 利用对象资源管理器创建Tadmin登录帐号，密码为myteacher，默认数据库为teachingData。提示：选择与数据库文件夹并列的安全性文件夹下的登录名进行创建即可

2. 利用对象资源管理器在“TeachingData”数据库中，创建 “T_User1”用户，登录号为Tadmin， 默认架构为dbo。注意：1-8题在当前账号下操作，无需切换登录账号，直接选择TeachingData下的安全性、用户来创建用户T_User1。

3. 利用T-SQL命令创建Sadmin登录帐号，密码为mystudent，默认数据库为teachingData

use teachingData2;

go

--3. 利用T-SQL命令创建Sadmin登录帐号，密码为mystudent，默认数据库为teachingData。
--语法提示：Create login 登录名  With password=‘密码值’  Default_database=默认数据库
create login Sadmin
with password = 'mystudent',
default_database = teachingData2;

--创建用户登录语法
--create login login_name
--with
--password={ 'password' | hashed_password hashed }
--[must_change][,]
--[sid=0x14585E90117152449347750164BA00A7][,]
--[default_database=database_name][,]
--[default_language=language][,]
--[check_expiration={ on | off }][,]
--[check_policy={ on | off }][,]
--[credential=credential_name]

4 利用T-SQL命令创建一个Sadmin登录帐号下的“S_User1”用户。

--4 利用T-SQL命令创建一个Sadmin登录帐号下的“S_User1”用户。
--语法提示：CREATE USER 用户名  FOR LOGIN 登录账号
create user S_User1 for login Sadmin;

5. 利用T-SQL命令禁用Tadmin登录帐号。

--5. 利用T-SQL命令禁用Tadmin登录帐号。
--语法提示：Alter login 登录账号 disable
alter login Tadmin disable;

6. 启用Tadmin登录帐号，将该帐号的登录名更改为tch_login。

--6. 启用Tadmin登录帐号，将该帐号的登录名更改为tch_login。
--语法提示： 启用账号：ALTER  LOGIN  登录账号 ENABLE;
--	       更改登录名： ALTER  LOGIN  登录账号 WITH  NAME = 新登录名;

alter login Tadmin enable;
alter login Tadmin with name = tch_login;

7. 利用对象资源管理器将表stuInfo的查询权限和更新权限授予用户T_User1，并允许T_User1将该权限再授予其他用户

8. 利用T-SQL命令将对表tchInfo中的列tname,title,dept的查询权限授予用户T_User1。

--8. 利用T-SQL命令将对表tchInfo中的列tname,title,dept的查询权限授予用户T_User1。

--Grant  <权限>  on  表名[(列名)]  to  用户 With  grant  option

--或 GRANT <权限> ON <数据对象> FROM <数据库用户>  

grant select on TchInfo(Tname,Title,Dept) to T_User1;

9. 关闭SQL-Server，以tch_login登录帐号重新登录（密码为myteacher）。登录后，请将服务器名称截图。

10. 在tch_login账号下，分别使用T-SQL命令查询StuInfo表、 CourseInfo表。看看执行结果。说明为什么

--10. 在tch_login账号下，分别使用T-SQL命令查询StuInfo表、 CourseInfo表。

use teachingData2;

select * from StuInfo;
select * from CourseInfo;

--报错原因：
--我们在对tch_login账号进行创建的时候只对该账号赋予了teachingData2下的StchInfo表的权限
--没有CourseInfo表的权限所以无法对CourseInfo进行任何的操作

11. 在tch_login账号下，使用SQL命令将stuInfo的查询权限授予用户S_User1。

--11. 在tch_login账号下，使用SQL命令将stuInfo的查询权限授予用户S_User1。
grant select on StuInfo to S_User1;

12．切换到Sadmin账户登录，写一个查询语句查询stuInfo表的所有信息。然后，尝试插入一条记录到stuInfo表中看看什么情况，说明原因。

--12．切换到Sadmin账户登录，写一个查询语句查询stuInfo表的所有信息。然后，尝试插入一条记录到stuInfo表中看看什么情况，说明原因。
--INSERT INTO stuInfo VALUES ('01000888','张小小','女','2000-10-01','计算机系','计算机科学','计科1班','19级')
use teachingData2;

select * from StuInfo;

insert into StuInfo values('01000888','张小小','女','2000-10-01','计算机系','计算机科学','计科1班','19级');

--报错原因：
--我们对Sadmin这个账户进行创建的时候仅仅只创建了该账户的用户S_User1
--没有授予任何表的权限给该用户，所以该用户不拥有任何权限
--但是在tch_login账号下，我们把Stuinfo的查询权限给了S_User1
--故S_User1有了查询权限但是没有插入的权限所以报错拒绝INSERT权限

13. 切换到windows账号登录，使用T-SQL语句在TeachingData数据库中创建新的数据库角色，角色名为Teacher。

--13. 切换到windows账号登录，使用T-SQL语句在TeachingData数据库中创建新的数据库角色，角色名为Teacher。
--语法：Sp_addrole 角色名

use teachingData2;

--sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ]
--sp_addrole 'Teacher'

create role Teacher;

14. 利用对象资源管理器为用户定义角色Teacher授权，使其拥有对TchInfo表的增删改查的权限，同时具有授予权限。（步骤同题7）

说明操作步骤并截图

15.利用对象资源管理器为用户自定义角色Teacher添加成员T_User1和S_User1。然后，切换到Sadmin账户登录，执行如下语句插入一条记录到TchInfo表中，看看什么情况，说明原因

所选行不会从数据库中删除。 发生这种情况的可能原因有很多，例如，如果您删除了涉及外键关系的行。 错误消息框上的信息提供了关于究竟发生了什么的最佳指导。 第一句告诉您无法删除的行号。 如果您要删除多行，它将显示无法删除的第一行的编号。 错误来源：后跟检测到问题的组件的名称。 错误消息：后面是无法从数据库中删除行的详细信息。 要更正此错误，请阅读消息框中的信息（尤其是错误消息文本）以尝试确定无法删除行的原因。 如果您仍想删除行，请使用此信息来了解您可以在表定义中更改哪些内容以允许您删除行。 例如，如果要删除的主行存在明细行，请先删除明细行，然后再次尝试删除主行。

https://docs.microsoft.com/zh-cn/previous-versions/yh0b775c(v=vs.140)?f1url=%3FappId%3DDev15IDEF1%26l%3DZH-CN%26k%3Dk(vdt.querydesigner.resultpane.rowdeletefailure)%26rd%3Dtrue

--15. 利用对象资源管理器为用户自定义角色Teacher添加成员T_User1和S_User1。
--然后，切换到Sadmin账户登录，执行如下语句插入一条记录到TchInfo表中，看看什么情况，说明原因。
--insert into TchInfo values('99999999','许洁','女','1956-01-01','教授','基础部')
use teachingData2;

--insert into TchInfo values('99999999','许洁','女','1956-01-01','教授','基础部');

insert into TchInfo values('99999889','许洁','女','1956-01-01','教授','基础部');

--报错原因：
--违反了 PRIMARY KEY 约束“PK_TchInfo”。不能在对象“dbo.TchInfo”中插入重复键。重复键值为 (99999999)。
--但是我感觉不是这么简单的问题
--所以我删除了这条数据再进行插入
--这里要注意我们得先把整个关联都删除再回去操作
--但是发现无法删除，原因其实很简单，就是关联性问题
--但是解决起来很麻烦
--所以我直接将插入的信息进行修改即可

--后面添加成功了
--证明仅仅是因为表中的主键约束问题

数据库完整性

1. 用T-SQL语句将StuInfo表中的属性SID设置为PRIMARY KEY。

--数据库完整性
use teachingData2;
--1.	用T-SQL语句将StuInfo表中的属性SID设置为PRIMARY KEY。
--ALTER TABLE table_name
--ADD [CONSTRAINT constraint_name] PRIMARY KEY(column_name1,column_name2,...);
alter table StuInfo add primary key(SID);

2. 用T-SQL语句将成绩表ScoreInfo中的SID和CID字段设置复合主键，并将该约束命名为PK_SC。

--2.	用T-SQL语句将成绩表ScoreInfo中的SID和CID字段设置复合主键，并将该约束命名为PK_SC。
alter table ScoreInfo add constraint PK_SC primary key(SID,CID);

3. 使用T-SQL语句在数据库TeachingData中建立学生信息表StuInfo和成绩表ScoreInfo之间的联系，设置ScoreInfo表中的SID为外键，参照StuInfo表中的SID属性值。

--3.	使用T-SQL语句在数据库TeachingData中建立学生信息表StuInfo和成绩表ScoreInfo之间的联系，
--设置ScoreInfo表中的SID为外键，参照StuInfo表中的SID属性值。
alter table ScoreInfo add foreign key(SID) references StuInfo(SID);

4. 在TeachingData数据库中，使用T-SQL命令为课程信息表（CourseInfo）中的课程名CName属性建立UNIQUE约束。

--4.	在TeachingData数据库中，使用T-SQL命令为课程信息表（CourseInfo）中的课程名CName属性建立UNIQUE约束。
alter table CourseInfo add unique(CName)

5. 使用T-SQL命令为TeachingData数据库的成绩表ScoreInfo设置约束：要求成绩（Score）属性的取值在0～100之间。

--5.	使用T-SQL命令为TeachingData数据库的成绩表ScoreInfo设置约束：要求成绩（Score）属性的取值在0～100之间。
alter table ScoreInfo add check(Score>=0 and Score<=100);

6.在TeachingData数据库中，用T-SQL语句为现有教师信息表TchInfo教师职称Title添加约束CK_Title，要求Title的取值只能为“助教”、“讲师”、“副教授”、“教授”。

--6.	在TeachingData数据库中，用T-SQL语句为现有教师信息表TchInfo教师职称Title添加约束CK_Title，
--要求Title的取值只能为“助教”、“讲师”、“副教授”、“教授”。
alter table TchInfo add constraint CK_Title check(Title in('助教','讲师','副教授','教授'));

7. 在TeachingData数据库中，用T-SQL语句为教师信息表TchInfo中教师职称Title添加一约束DF_Title，要求将Title的默认值设置为“讲师”。

--7.	在TeachingData数据库中，用T-SQL语句为教师信息表TchInfo中教师职称Title添加一约束DF_Title，要求将Title的默认值设置为“讲师”。
alter table TchInfo add constraint DF_Title default '讲师' for Title

安全性简答

1． 什么是数据库安全性？其核心问题是什么？简述原因。

数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。数据库系统的核心是数据库管理系统。用于建立、使用和维护数据库，简称DBMS。
它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过DBMS进行数据库的维护工作。它可以支持多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。

2． 什么是数据库的完整性？简述关系模型的三个完整性规则，分别举例说明。

指关系数据库的参照完整性，是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。
1、实体完整性：指关系的主键约束primary key，主键不能取空值，唯一；比如学生表的主键通常是取学号为主键
2、参照完整性：指参照关系中每个元素的外码要么为空，要么等于被参照关系中某个元素的主码
3、用户定义的完整性：指对关系中每个属性的取值作一个约束的具体定义。比如 性别属性只能取男或女“

3． 什么是数据库角色？其作用是什么？

在数据库中，为便于对用户及权限进行管理，可以将一组具有相同权限的用户组织在一起，这一组具有相同权限的用户就称为角色。可以将用户集中到一个单元中，然后对该单元应用权限。对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限

4． 什么是数据库的恢复?其基本原理是什么？其常用技术包括哪些？

指当数据库在受到安全性问题，如被非法修改，误操作时，或对当前版本不满意时，使得当前的数据库恢复到未出错或未被修改，即正常的版本的操作。
数据恢复有3个步骤
（1）反向扫描文件日志，查找该事务的更新操作。
（2）对事务的更新操作执行逆操作。
（3）继续反向扫描日志文件，查找该事务的其他更新操作，并做同样的处理，直到事务的开始标志。
要使数据库在发生故障后能够恢复，必须建立冗余数据，在故障发生后利用这些冗余数据实施数据库恢复。建立冗余数据常用的技术是数据转储和建立日志文件。在一个数据库系统中，这两种方法一般是同时被采用的。
常用技术：

1. 数据转储
2. 登记日志文件
3. 检查点恢复技术
4. 数据库镜像
5. 远程备份系统