数据库的安全性与完整性.docx

数据库的安全性和完整性
一、实验目的和要求
1、理解数据库安全性和完整性的概念。
2、掌握SQL Server2000中有关用户、角色及操作权限管理等安全性技术。
3、掌握SQL Server2000中有关约束、规则、默认值的使用等完整性技术。

二、实验内容和步骤
㈠数据库的安全性
1、SQL Server的安全模式
认证是指来确定登陆SQL SERVER的用户的登陆账号和密码是否正确，以此来验证其是否具有连接SQL SERVER的权限，但是通过认证阶段并不代表能够访问数据，用户只有在获取访问数据库的权限之后才能对服务器上的数据库进行权限许可下的各种操作。

⑴设置SQL Server的安全认证模式：使用企业管理器来设置，步骤如下：
Step1: 展开服务器组，右击需要设置的SQL服务器，在弹出菜单中选择“属性”。
Step2: 在弹出的SQL服务器属性对话框中，选择“安全性”选项卡。
Step3: 选择仅Windows选项（NT/2000验证模式）
或SQL Server和Windows选项（混合模式）。
注：设置改变后，用户必须停止并重新启动SQL Server服务，设置才生效。
如果设置成NT认证模式，则用户在登录时输入一个具体的登陆名时，SQL SERVER将忽略该登录名。

⑵添加SQL Server账号：若用户没有Windows NT/2000账号，则只能为他建立SQL Server账号。
①利用企业管理器
Step1: 展开服务器，选择安全性/登录。
Step2: 右击登录文件夹，出现弹出式菜单。
Step3: 在弹出式菜单中选择“新建登录”选项后，就会出现一个登录属性对话框。

step4: 在名称框中输入一个不带反斜杠的用户名，选中SQL Server身份验证单选按钮，并在密码框中输入口令（如下图所示）。

Step5: 单击“确定”按钮，出现确认密码窗口，再输入密码，单击“确定”按钮，创建完成。

②利用T_SQL添加SQL Server账号：执行系统存储过程sp_addlogin：

Sp_addlogin login_id[,passwd[,defdb[,deflanguage]]]
其中：Login_id为注册标识或SQL Server用户名；Passwd为口令，默认口令是NULL（即不需要口令）；Defdb指定用户在注册时连接到的默认数据库，如果没有指定默认数据库，则默认数据库是master；Deflanguage说明用户注册到SQL Server时使用的默认语言代码。
例如：

⑶修改登录账号的属性
① 利用企业管理器：用鼠标双击要修改属性的登录账号，在属性对话框中进行修改。
②利用T_SQL：涉及以下几个系统存储过程：sp_password修改账号口令，sp_defaultdb修改账号默认数据库，sp_defaultlanguage修改账号默认语言。其中，系统存储过程sp_password的格式是：
sp_password old_passwd,new_passwd[,login_id]
例如： ⑷删除登录账号
①利用企业管理器：右击要删除的账号，在弹出的快捷菜单中选择“删除”命令，在确认对话框中单击“是”按钮，这个登录账号就永久被删除了。
②利用T_SQL：使用系统存储过程sp_droplogin来删除SQL Server登录账号。如：
例如：

2、管理数据库用户：
安装SQL Server之初只有两个用户，一个是sa（系统管理员），另一个是guest（系统安装时创建的一个可以对样板数据库作最基本查询的用户）。登陆账号sa自动与每一个数据库用户dbo相关联。
⑴添加数据库的用户
①利用企业管理器
Step1: 在企业管理器中，展开服务器中的数据库文件夹，再展开要添加用户的数据库，右击用户目录，在弹出的快捷菜单中选择“新建数据库用户…”命令。

Step2: 打开新建数据库用户对话框。
Step3: 从登录名下拉列表框中选择一个登录账号。
Step4: 在用户名框中输入用户名，默认情况下它被设置为登录账号名。

Step5: 若需要，在数据库角色成员列表中选中除public外的其他角色。

②利用T_SQL：执行系统存储过程sp_adduser，格式是：
sp_adduser [ @loginame = ] 'login'
    [ , [ @name_in_db = ] 'user' ]
    [ , [ @grpname = ] 'group' ]
[@loginame =] 'login' 用户的登录名称。
[@name_in_db =] 'user' 新用户的名称。如果没有指定 user，则用户的名称默认为 login 名称。指定 user 即为新用户在数据库中给予一个不同于 SQL Server 上的登录 ID 的名称。
[@grpname =] 'group' 角色，新用户自动地成为其成员，默认值为 NULL。
例如：

⑵删除数据库用户：相当于删除一个登录账号在这个数据库中的映射。
①利用企业管理器：右击要删除的用户，从弹出的快捷菜单中选择“删除”命令，在提示的对话框中确认，该用户就被删除了。

② 利用T_SQL：使用系统存储过程sp_dropuser。
例如：

3、管理数据库角色
⑴创建自定义数据库角色
①使用企业管理器
Step1: 在企业管理器中，展开服务器中的数据库文件夹，再展开要添加角色的数据库，如pubs数据库，右击角色目录，在弹出的快捷菜单中选择“新建数据库角色…”命令。

Step2: 在打开的新建数据库角色对话框中，在名称框中输入数据库新角色的名称，如NewRole1,并选中标准角色单选按钮。
Step3：可以为角色添加用户或其他角色，若不添加，则创建一个暂无成员的角色。
若单击对话框中的添加按钮，出现添加角色成员对话框， 选择要添加的用户，单击确定按钮，完成角色创建。

②使用T_SQL ：使用系统存储过程sp_addrole可以创建数据库新角色，同时使用系统存储过程sp_addrolemember和 sp_droprolemember分别用于向角色中增加或从角色中删除成员。
如在pubs数据库中创建NewRole2新角色，并且将用户newlg添加到该角色中：

用户名与角色名必须同时存在，且位于同一个数据库中。

⑵删除用户自定义角色：不能删除一个有成员的角色，在删除这样的角色之前，应先删除其成员。只能删除自定义的角色，系统的固定角色不能被删除。
①利用企业管理器：右击要删除的用户自定义角色，从弹出的快捷菜单中选择“删除”命令，在提示的对话框中确认，该用户自定义角色就被删除了。
②利用T_SQL：使用系统存储过程sp_droprole，
如：

4、权限管理：
分为语句权限和对象权限管理两类。前者是对用户执行语句或命令的权限的管理；后者是对数据库及其对象的操作权限的控制。
在SQL SERVER中，我们使用GRANT、REVOKE、DENY三种命令来管理权限。
⑴利用企业管理器管理权限
①管理语句权限
Step1: 在企业管理器中，展开服务器中的数据库文件夹，右击要修改权限的数据库，如pubs 数据库，在弹出的快捷菜单中选择“属性”命令，打开数据库属性对话框。
Step2: 单击权限标签，打开权限选项卡。
Step3: 在权限选项卡中列出数据库中所有的用户和角色，以及所有的语句权限。
Step4：设置完毕后，单击确定按钮，使设置生效。
②管理对象权限
Step1: 在企业管理器中展开服务器中的数据库文件夹，再展开要进行角色对象权限的数据库，如pubs数据库，选中角色目录，在右边窗口的角色列表中双击NewRole2角色，打开角色属性对话框。
Step2: 在该对话框中单击权限按钮，打开权限对话框，其中列出了数据库中的所有对象，和这些对象可以执行的操作，给角色NewRole2授予对表authors执行SELECT的权限，则应在SELECT与authors的交叉处的方框中打钩。

Step3: 单击图中的“列”按钮，可以指定对列操作的权限。
Step4: 设置完毕后，单击确定按钮，使设置生效。

⑵利用T_SQL管理权限
GRANT {ALL|statement_list} TO {PUBLIC|name_list}
其中：
ALL即全部语句，只有系统管理员可以使用此选项，因为只有系统管理员可以授予或收回CREATE DATABASE的权限；
statement_list给出授权的语句列表，这些语句可以是CREATE DATABASE（如果执行这个语句的用户是系统管理员）、CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW等；

PUBLIC说明这些语句的执行权限将授予所有的用户；
name_list是数据库用户名，说明这些语句的执行权限授予哪些用户。

例：将pubs数据库authors表的zip列和city列的修改权限授予“我的登录”这名用户。

收回语句授权：
REVOKE{ALL|statement_list}FROM{PUBLIC|name_list}
例：将pubs数据库authors表的zip列的修改权限从用户“我的登录”收回。

㈡数据库的完整性
完整性是指数据的正确性和有效性，是为防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
约束：约束是强制数据完整性的第一选择。约束有：检查约束、缺省约束、唯一约束、非空约束、主键约束和外键约束等6种。
⑴使用企业管理器实施约束
例：为PUBS数据库的authors表的“city”字段创建一个缺省约束，缺省值为“UNKNOWN”

在企业管理器中，展开服务器中的数据库文件夹，再展开要操作的数据库，如PUBS数据库，单击“表”，在右边窗口中显示所有在PUBS数据库中的表。右击要设置缺省的表AUTHORS，在弹出的快捷菜单中选择“设计表”，单击设置缺省值的字段，将弹出下图所示窗口，在下边属性设置栏的默认值输入框中输入相应的默认值即可。
如上面的DF默认值约束语句执行完毕后，可以看到如下显示：

继续下面试验前，先执行以下语句：
create database "学生选课"
go
use "学生选课"
create table Student
(Sno char(9) primary key,
Sname char(20),
Ssex char(2),
Sage smallint,
Sdept char(20))
create table Course
(Cno char(4) primary key,
Cname char(20),
Cpno char(4),
Ccredit smallint)
create table SC
(Sno char(9),
Cno char(4),
Grade smallint,
primary key (Sno,Cno))
例：将SC表的Sno字段设置为外键，该外键参考表Student中的主键Sno。
在企业管理器中，展开服务器中的数据库文件夹，再展开"选课"数据库，单击“表”。右击要设置外键的表SC，在弹出的快捷菜单中选择“设计表”，在打开的表结构对话框中，点击工具栏上的击”管理关系”按钮 ，系统将弹出下图所示窗口，单击”新建”按钮，系统给出默认的关系名，选择要参考主键的表名Student及表中的主键Sno，要设置外键的表名SC及外键列名Sno，单击”关闭”按钮，外键约束创建完成。

⑵使用T_SQL语句实施约束
例：为 “Sdept”字段创建一个检查约束，使得所在系必须是”计算机”、”数学”或”信息”之一。

例：为Student表的“Sname”字段增加一个唯一性约束

例：为SC表建立外键,依赖于Course表的fk外码约束。

例：禁止启用Student表的“Sdept”的CHECK约束ck。

2、默认值对象：是数据库对象之一，它指定在向数据库中的表插入数据时，如果用户没有明确给出某列的值，SQL Server将自动指定该列使用默认值。
⑴使用企业管理器管理默认值
在PUBS数据库下执行create table newtable(pk char,odate datetime,newdate datetime)
①创建一个默认的日期值，默认值为2008-8-8：
在企业管理器中，展开服务器中的数据库文件夹，再展开pubs数据库，右击”默认”，在弹出的快捷菜单中选择”新建默认”，系统将弹出 “默认属性”窗体。在”名称”对应的输入框中输入默认值的名称”OLYMPIC”，在值对应的输入框中输入CONVERT(DATETIME,'2008/8/8')；单击”确定”按钮即完成默认值的创建。

②将默认值绑定到newtable表的 odate字段，然后解除绑定。
在“默认属性” 窗体中，点击”绑定”，选择待绑定的默认值，再点击”绑定列”，选择待绑定的表和字段，最后点击”确认”，可将默认值绑定到指定表的列
③删除默认值：删除前必须解除该默认值的所有绑定。在企业管理器中，展开服务器中的数据库文件夹，再展开PUBS数据库，单击”默认”，再右击右边窗口中的默认值OLYMPIC，在弹出的快捷菜单中选择”删除”按钮即可。
⑵使用T_SQL语句进行默认值操作
①设定默认值：创建一个名为BirthDate,值为GETDATE的默认值。

②将默认值绑定到列

③解除默认绑定

④删除默认值
3、规则对象：规则是数据库对象之一。它指定向表中某列插入或更新数据时，限制输入新值的取值范围。规则可以是值的清单、值的范围或值必须满足的条件。
⑴利用企业管理器进行规则操作
①创建规则：为Student表中的Sno字段创建一个规则Rule1，使sno的值大于00000且小于99999。
在企业管理器中，展开服务器中的数据库文件夹，再展开"学生选课"数据库，右击”规则”，在弹出的快捷菜单中单击选择”新建规则”，系统将弹出 “规则属性”窗口；在”名称”输入框中输入规则名称Rule1，在”文本”输入框中输入规则的文本：@x>00000 and @x<99999，单击”确定”按钮，即可完成规则的创建。
②将规则绑定到指定的字段上：点击规则属性对话框上的”绑定列”按钮，在”表”中选择对应的表student，然后在左边的窗格中选择欲被捆绑规定的列Sno，点击”添加”按钮，最后选择”确定”，完成规则的绑定。
③删除规则：在企业管理器中，展开服务器中的数据库文件夹，再展开"学生选课"数据库，单击”规则”，右击右边窗格里的规则Rule1，单击”删除”按钮即可。

⑵使用T_SQL语句实施规则
①创建规则：为Student表中的Sno字段创建一个规则Rule1，使Rule1的值大于1111且小于9999。

②将规则rule2绑定到表Student的Sno字段

③解除规则rule1到教工号的绑定

④将规则rule1删除
三、实验练习题
练习1：创建一个数据库用户或角色，使之拥有对数据库pubs表authors的全部操作权限，练习对其他用户的授权及权限收回。
练习2：创建一个教工表Teacher，将其教工号设为主键，在查询分析器中输入以下语句。同时为性别字段创建DEFAULT约束，默认值为‘男’。
练习3：根据上面已经创建好的Teacher表，完成下面的任务：
1、用T_SQL创建默认的对象phone：
2、这个默认对象phone绑定到教工表的电话字段Telpnhoe上
3、取消默认对象phone的绑定并删除默认对象。
4、利用T_SQL创建规则YGH，使得教工号Tno必须在0到10000之间。
5、把规则YGH绑定到教工表的教工号字段Tno上。
6、取消规则YGH的绑定并删除规则，并用企业管理器完成上述规则。