资源简介

(共40张PPT)
维护学生信息管理数据库的安全性
任务9-1 SQL Server身份验证模式
目录
Contents
任务9-2 SQL Server权限管理
任务9-3 用户和角色管理
任务9-4 数据备份和恢复
任务9-5 实训：实现数据库安全性
任务9-3 用户和角色管理
PART 3
（一）数据库用户
（二）管理角色
（三）管理服务器角色
（四）管理数据库角色
任务
assignments
用户和角色管理
任务9-3
任务1-1 数据处理
【任务分析】
理解SQL Server 2016数据库用户和角色的管理。
【课堂任务】
SQL Server 2016数据库用户的管理
SQL Server 2016数据库角色的管理
SQL Server 2016服务器角色的管理
用户和角色管理
任务9-3
任务1-1 数据处理
（一）数据库用户
数据库用户是数据库级的主体，是登录名在数据库中的映射，是在数据库中执行操作和活动的行动者。在SQL Server 2016系统中，数据库用户不能直接拥有表、视图等数据库对象，而是通过架构拥有这些对象。
1．数据库用户
使用数据库用户账户限制访问数据库的范围，默认的数据库用户有dbo、guest和sys等。
（1）dbo用户
数据库所有者或dbo是一个特殊的数据库用户，并且被授予特殊的权限。一般来说，创建数据库的用户是数据库的所有者。dbo被隐式授予对数据库的所有权限，并且能将这些权限授予其他用户。因为sysadmin服务器角色的成员被自动映射为特殊用户dbo，以sysadmin角色登录能执行dbo能执行的任何任务。
用户和角色管理
任务9-3
任务1-1 数据处理
（2）guest用户
guest用户是一个加入数据库并且允许登录任何数据库的特殊用户。以guest账户访问数据库的用户账户被认为是guest用户的身份并且继承guest账户的所有权限和许可。
默认情况下，guest用户存在于model数据库中，并且被授予guest的权限。由于model是创建所有数据库的模板，所以新的数据库将包括guest账户，并且该账户将授予guest权限。
用户和角色管理
任务9-3
任务1-1 数据处理
在使用guest账户之前，应该注意以下几点关于guest账户的信息。
（1）guest用户是公共服务器角色的一个成员，并且继承这个角色的权限。
（2）在任何用户以guest访问数据库以前，guest必须存在于数据库中。
（3）guest用户仅用于用户账户具有访问SQL Server的权限，但是不能通过这个用户账户访问数据库。
用户和角色管理
任务9-3
任务1-1 数据处理
2．创建数据库用户
创建数据库用户可分为两步，首先，创建数据库用户使用的SQL Server 2016登录名，如果使用内置的用户账户，则可省略这一步，然后为数据库指定到创建的用户账户。
（1）使用SSMS来创建数据库用户账户
使用SSMS创建数据库用户账户，并给用户授予访问数据库grademanager的权限，具体步骤如下。
① 打开SSMS窗口，展开【服务器】|【数据库】|grademanager节点。
② 再展开【安全性】|【用户】节点并用鼠标右键单击，从弹出的快捷菜单中选择【新建用户】命令，打开【数据库用户-新建】窗口。
③ 在【用户名】文本框中输入xuser_dbu指定要创建的数据库用户名称。
用户和角色管理
任务9-3
任务1-1 数据处理
④ 单击【登录名】文本框旁边的【选项】按钮，打开【选择登录名】窗口，然后单击【浏览】按钮，打开【查找对象】窗口。
⑤ 选中xuser旁边的复选框，单击【确定】按钮，返回【选择登录名】窗口，单击【确定】按钮，返回【数据库用户-新建】窗口。
⑥ 用同样的方式，选择【默认架构】为dbo，如图9.13所示。
⑦ 单击【确定】按钮，完成带登录名“xuser”的SQL用户“xuser_dbu”的创建。
⑧ 为了验证是否创建成功，可以刷新【用户】节点，此时在【用户】节点下可以看到刚才创建的xuser_dbu用户账户。
用户和角色管理
任务9-3
任务1-1 数据处理
⑨ 其他选项卡
【数据库用户-新建】窗口还提供了4个选项：【拥有的架构】、【成员身份】、【安全对象】和【扩展属性】。
【拥有的架构】选择列出了可由新的数据库用户拥有的所有可能的架构。若要向数据库用户添加架构或者从数据库用户中删除架构，则在【此用户拥有的架构】下选中或取消选中架构旁边的复选框。
图9.14 【数据库用户-新建】窗口
【成员身份】选项列出了可由新的数据库用户拥有的所有可能的数据库成员身份角色。若要向数据库用户添加角色或者从数据库用户中删除角色，则在【数据库角色成员身份】下选中或取消选中角色旁边的复选框。
用户和角色管理
任务9-3
任务1-1 数据处理
【安全对象】选项将列出所有可能的安全对象以及可授予登录名的针对这些安全对象的权限。
【扩展属性】选项允许向数据库用户添加自定义属性。此选项还提供以下选项：
数据库：显示所选数据库的名称。此字段为只读。
排序规则：显示用于所选数据库的排序规则。此字段为只读。
属性：查看或指定对象的扩展属性。每个扩展属性都由与该对象关联的元数据的名称/值对组成。
省略号（...）：单击【值】后面的省略号 (…)按钮可以打开【扩展属性的值】对话框。在这一较大的空间中键入或查看扩展属性的值。有关详细信息请参阅【扩展属性的值】对话框。
删除：删除所选扩展属性。
用户和角色管理
任务9-3
任务1-1 数据处理
（2）使用系统存储过程sp_grantdbaccess添加数据库用户账户
使用系统存储过程sp_grantdbaccess添加数据库用户账户的语法格式如下。
sp_grantdbaccess [@loginname=]'login'
[,[@name_in_db=]'name_in_db']
① @loginname：映射到新数据库用户的Windows 组、Windows登录名或SQL Server登录名。
② @name_in_db：新数据库用户的名称。name_in_db是OUTPUT变量，其数据类型为sysname，默认值为NULL。如果不指定，则使用login。如果指定值为NULL的OUTPUT的变量，则@name_in_db将设置为login。name_in_db不存在于当前数据库中。
用户和角色管理
任务9-3
任务1-1 数据处理
【例9.6】 使用sp_addlogin系统存储过程创建一个到grademanager数据库的登录名xuser_ exam，并使用sp_grantdbaccess系统存储过程将该登录名作为数据库用户进行添加。
EXEC sp_addlogin 'xuser_exam','123456','grademanager'
GO
USE grademanager
GO
EXEC sp_grantdbaccessxuser_exam
用户和角色管理
任务9-3
任务1-1 数据处理
（二）管理角色
角色是SQL Server 2016用来集中管理数据库或者服务器权限的方式。数据库管理员将操作数据库的权限赋予角色，数据库管理员再将角色赋予数据库用户或者登录账户，从而使数据库用户或者登录账户拥有相应的权限。
1．服务器角色
服务器级角色也称为固定服务器角色，因为不能创建新的服务器级角色。服务器级角色的权限作用域为服务器范围，可以向服务器级角色添加SQL Server登录名、Windows账户和Windows组。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
下面按照从低级别到高级别的角色顺序，介绍图9.15中的角色。
（1）bulkadmin
bulkadmin服务器角色的成员可以运行BULK INSERT语句。该语句允许从文本文件中将数据导入到SQL Server 2016数据库中，为需要执行大容量插入数据库的域账户设计。
（2）dbcreator
dbcreator服务器角色的成员可以创建、更改、删除和还原任何数据库。这不仅是适合助理DBA的角色，也是适合开发人员的角色。
（3）diskadmin
diskadmin服务器角色用于管理磁盘文件，如镜像数据库和添加备份设备。它适合于助理DBA。
用户和角色管理
任务9-3
任务1-1 数据处理
（4）processadmin
SQL Server 2016能够多任务化，也就是说，可以执行多个进程或多个事件。例如，SQL Server 2016可以生成一个进程用于向高速缓存写数据，同时生成另一个进程用于从高速缓存中读取数据。这个角色的成员可以删除进程。
（5）securityadmin
securityadmin服务器角色的成员将管理登录名及其属性。它们可以授权、拒绝和撤销服务器级权限和数据库级权限。另外，它们可以重置SQL Server 2016登录名的密码。
（6）serveradmin
serveradmin服务器角色的成员可以更改服务器范围的配置选项和关闭服务器，如SQL Server 2016可以使用多大内存、监视通过网络发送多少信息，或者关闭服务器， 这个角色可以减轻管理员的管理负担。
用户和角色管理
任务9-3
任务1-1 数据处理
（7）setupadmin
该服务器角色为需要管理链接服务器和控制启动的存储过程的用户而设计。 Setupadmin服务器角色的成员能添加到setupadmin，能增加、删除和配置链接服务器，并能控制启动过程。
（8）sysadmin
sysadmin服务器角色的成员有权在SQL Server 2016中执行任何任务。因为不熟悉SQL Server 2016的用户可能会意外地造成严重问题，所以给这个角色指派用户时应该特别小心。通常情况下，这个角色仅适合于数据库管理员。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
2．管理数据库角色
SQL Server 2016提供了固定数据库角色，这些角色拥有内置的且不能被更改的权限。可以使用固定角色为用户指派数据库管理权限，用户可以从属于多个角色，也就是可以同时享有多重权限，常用的预定义数据库角色如下。
（1）db_owner：在数据库中拥有全部权限。
（2）db_accessadmin：可以添加或删除用户账号，以指定谁可以访问数据库。
（3）db_securityadmin：可以管理全部权限、对象所有权、角色和角色成员资格。
（4）db_ddladmin：可以发出ALL DDL，但不能发出GRANT（授权）、REVOKE和DENY语句。
用户和角色管理
任务9-3
任务1-1 数据处理
（5）db_backupoperator：可以备份该数据库。
（6）db_datareader：可以选择数据库任何用户表中的所有数据。
（7）db_datawriter：可以更改数据库任何用户表中的所有数据。
（8）db_denydatareader：不能选择数据库任何用户表中的任何数据，但可以修改架构。
（9）db_denydatawriter：不能更改数据库任何用户表中的任何数据。
（10）public：在SQL Server 2016中，每个数据库用户都属于public数据库角色。当尚未对某个用户授予或拒绝安全对象的特定权限时，该用户将继承授予该安全对象的public角色的权限。这个数据库角色不能删除。
用户和角色管理
任务9-3
任务1-1 数据处理
（三）管理服务器角色
1．使用SSMS将登录名指派到服务器角色
为前面创建的登录名指派或者更改服务器角色，步骤如下。
（1）打开SSMS窗口，选择某种身份验证模式，建立与SQL Server 2016服务器的连接。
（2）在【对象资源管理器】窗格中展开【服务器】|【安全性】|【登录名】节点。
（3）用鼠标右键单击登录名xuser，选择【属性】命令，弹出【登录属性-xuser】窗口。
（4）选择【服务器角色】选项，如图9.15所示。
（5）【服务器角色】列表中，选中复选框来授予xuser不同的服务器角色，例如sysadmin。
（6）设置完成后，单击【确定】按钮返回。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
2．使用系统存储过程sp_addsrvrolemember增加登录到服务器角色
使用系统存储过程sp_addsrvrolemember增加登录到服务器角色的语法格式如下。
SP_addsrvrolemember [@loginame=] 'login',[@rolename=] 'role'
【例9.7】 将Windows登录名xuser添加到sysadmin服务器角色中。
EXEC sp_addsrvrolemember 'xuser','sysadmin'
3．使用系统存储过程sp_dropsrvrolemember删除登录到服务器角色
使用系统存储过程sp_dropsrvrolemember删除登录到服务器角色的语法格式如下。
sp_dropsrvrolemember [@loginame=] 'login',[@rolename=] 'role'
【例9.8】 从sysadmin服务器角色中删除登录xuser。
EXEC sp_dropsrvrolemember 'xuser','sysadmin'
用户和角色管理
任务9-3
任务1-1 数据处理
（四）管理数据库角色
数据库角色是具有相同访问权限的数据库用户账号或组的集合。可以使用 SQL Server 中的角色高效地管理权限。将权限分配给角色，然后在角色中添加和删除用户以及登录名。使用角色，不必单独维护各个用户权限。通过数据库用户和角色来控制数据库的访问和管理。数据库角色应用于单个数据库。
存在两种类型的数据库角色：数据库中预定义的“固定数据库角色”和可以创建的“用户定义的数据库角色”。可以向数据库角色中添加任何数据库用户账户和其他SQL Server 角色。
用户和角色管理
任务9-3
任务1-1 数据处理
1．将登录指派到角色
将登录名添加到数据库角色中来限定该登录对数据库拥有的权限，步骤如下。
（1）打开SSMS窗口，在【对象资源管理器】窗格中，展开【数据库】|grademanager节点。
（2）展开【安全性】/【数据库角色】节点，用鼠标右键单击db_denydatawriter节点，选择【属性】命令，打开【数据库角色属性】窗口。
（3）单击【添加】按钮，打开【选择用户数据库或角色】窗口，然后单击【浏览】按钮，打开【查找对象】对话框，如图9.17所示。
（4）在【匹配的对象】列表中选中【名称】列前的复选框，然后单击【确定】按钮，返回【选择用户数据库或角色】窗口。全部启用指派多个登录到同一个数据库角色。
用户和角色管理
任务9-3
任务1-1 数据处理
（5）单击【确定】按钮，返回【数据库角色属性】窗口，如图9.18所示。 （6）添加完成后，单击【确定】按钮关闭【数据库角色属性】窗口。
（7）使用SQL Server身份验证模式建立连接，在【用户名】文本框中输入前面指定的数据库用户xuser，在【密码】文本框中输入前面设定的密码，单击【连接】按钮打开新的查询窗口。
（8）单击【新建查询】按钮，打开新的SQL Server查询窗口，在查询窗口中输入以下测试角色修改是否生效的语句。
USE grademanager
INSERT INTO student(sno,sname,ssex)
VALUES('2007020212','江南','男')
用户和角色管理
任务9-3
任务1-1 数据处理
2．管理数据库角色
SQL Server 2016提供了固定数据库角色，这些角色拥有内置的且不能被更改的权限。可以使用固定角色为用户指派数据库管理权限，用户可以从属于多个角色，也就是可以同时享有多重权限，常用的预定义数据库角色如下。
（1）db_owner：在数据库中拥有全部权限。
（2）db_accessadmin：可以添加或删除用户账号，以指定谁可以访问数据库。
（3）db_securityadmin：可以管理全部权限、对象所有权、角色和角色成员资格。
（4）db_ddladmin：可以发出ALL DDL，但不能发出GRANT（授权）、REVOKE和DENY语句。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
2．新建数据库角色
固定的数据库角色不能更改权限，有时可能不能满足需要。这时，可以为特定数据库创建用户定义的数据库角色设置权限。
例如，假设有一个数据库有3种类型的用户：需要查看数据的普通用户、需要能够修改数据的管理员和需要能修改数据库对象的开发员。在这种情况下，可以创建3个角色来处理这些用户类型，然后仅管理这些角色，而不用管理许多不同的用户账户。
在创建数据库角色时，先给该角色指派权限，然后将用户指派给该角色，这样用户将继承给这个角色指派的任何权限。这不同于固定数据库角色，因为在固定角色中不需要指派权限，只需要添加用户。创建自定义数据库角色的步骤如下。
（1）打开SSMS窗口，在【对象资源管理器】窗格中，展开【数据库】|grademanager节点。
用户和角色管理
任务9-3
任务1-1 数据处理
（2）展开【安全性】|【角色】节点，用鼠标右键单击【数据库角色】节点，选择【新建数据库角色】命令，打开【数据库角色属性】窗口。在【角色名称】文本框输入school_role，设置【所有者】为dbo。
（3）单击【添加】按钮，将数据库用户guest和xuer_exam添加到【此角色成员】列表中，如图9.19所示。
（4）选择【安全对象】选项，单击【搜索】按钮，添加student表为【安全对象】，启用【选择】后面【授予】列的复选框，如图9.20所示。
（5）单击【确定】按钮，创建这个数据库角色，并返回SSMS窗口。
（6）关闭SSMS窗口，然后再次打开，使用xuser登录连接SQL Server 2016服务器。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
（7）新建一个【查询】窗口，输入下列测试语句。
USE grademanager
GO
SELECT * FROM student
（8）这条语句将会成功执行，因为xuser是新建的school_role角色的成员，而该角色具有执行SELECT的权限。
（9）执行下列语句将会失败，如图9.21所示，因为xuser作为角色school_role的成员只能对student表进行SELECT操作。
USE grademanager
GO
INSERT INTO student(sno,sname,ssex)
VALUES('2007010255','张小朋','男')
用户和角色管理
任务9-3
任务1-1 数据处理
3．应用程序角色
应用程序角色是一个数据库主体，它使应用程序能够用其自身的、类似用户的特权来运行。使用应用程序角色，只允许通过特定应用程序连接的用户访问特定数据。与数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且是非活动的。应用程序角色使用两种身份验证模式，如果不启动应用程序角色，就不能够访问任何应用程序角色专有的数据。可以使用sp_setapprole来激活，并且需要密码。因为应用程序角色是数据库级别的主体，所以它们只能通过其他数据库中授予 guest用户账户的权限来访问这些数据库。因此，任何已禁用的guest用户账户将无法访问其他数据库中的应用程序角色。
用户和角色管理
任务9-3
任务1-1 数据处理
一旦激活了应用程序角色，SQL Server 2016就不再将用户作为他们本身来看待，而是将用户作为应用程序来看待，并给他们指派应用程序角色权限。创建并测试一个应用程序角色的具体步骤如下。
（1）打开SSMS窗口，在【对象资源管理器】窗格中，展开【数据库】|grademanager节点。
（2）展开【安全性】|【角色】节点，用鼠标右键单击【应用程序角色】节点，选择【新建应用程序角色】命令，打开【应用程序角色-新建】窗口。在【角色名称】文本框输入approle_xuser，设置【默认架构】为dbo，设置密码为abc，如图9.23所示。
（3）选择【安全对象】选项，单击【搜索】按钮，从打开的【添加对象】对话框中选中【特定对象】单选按钮，如图9.23所示。
用户和角色管理
任务9-3
任务1-1 数据处理
用户和角色管理
任务9-3
任务1-1 数据处理
（4）单击【确定】按钮，弹出【选择对象】对话框，单击【对象类型】按钮，选择【表】选项，单击【确定】按钮返回。
（5）单击【浏览】按钮，从打开的【查找对象】对话框中，选中student表旁边的复选框，单击【确定】按钮返回，如图9.24所示。
（6）单击【确定】按钮，返回【安全对象】窗口，选中【选择】后面【授予】列的复选框，如图9.25所示。
（7）单击【确定】按钮，完成应用程序角色的创建。
用户和角色管理
任务9-3
任务1-1 数据处理
（8）单击【新建查询】按钮，打开一个新的SQL Server查询窗口，并使用【SQL Server身份验证】模式以xuser登录。
（9）在查询窗口执行如下所示语句。
USE grademanager
GO
SELECT * FROM student
执行结果将显示拒绝了对对象student的select权限。
（10）激活应用程序角色，具体语句如下。
SP_SETAPPROLE @ROLENAME='AppRole_xuser',@PASSWORD='abc'
（11）重新执行第（9）步中的语句，可以看到这次查询是成功的。

展开更多......

收起↑