SQL-Server实用数据库技术课件第10章

SQLServer应用系统开发第十章SQLServer安全管理授课教师：张新成课程回顾存储过程和触发器存储过程的创建与使用触发器的创建与使用本章综述

数据库是电子商务、金融以及ERP系统的基础。数据库中保存着交易记录、工程数据、产品资料等重要的商业数据和客户信息，这些数据是企业的重要资源和宝贵财富。如果数据库中的数据丢失或者破坏，将会对企业带来巨大的损失。因此安全性是评价数据库系统的重要指标。SQLServer2008提供了完善的管理机制和操作手段，有效实现对数据访问的控制，从而保证了数据库的安全性

。

本章结构3.2创建数据库3.4分离与附加数据库

身份验证模式简介设置身份验证模式使用SSMS管理登录使用T-SQL语句管理登录架构概述使用SSMS管理架构使用T-SQL语句管理架构服务器角色数据库角色应用程序角色用户简介使用SSMS管理用户使用T-SQL语句管理用户10.3登录管理10.1SQLServer2008安全机制10.2SQLServer身份验证模式10.4用户管理10.5架构管理10.6角色管理权限的类型使用SSMS管理权限使用T-SQL语句管理权限10.7权限管理技能展示了解SQLServer2008的安全机制理解SQLServer2008的身份验证模式

理解用户、架构、角色、权限等相关概念掌握登录管理、用户管理、架构管理和角色管理和权限管理10.1 SQLServer

2008安全机制图10-1SQLServer2008的安全等级

123

服务器安全性3数据库安全性

数据库对象安全性4操作系统安全性10.2 SQLServer

2008身份验证模式

1.Windows验证模式

SQLServer运行在NT服务器平台或基于NT构架的Windows2008/XP/Vista上。NT作为网络操作系统，本身就具备管理登录验证用户合法性的能力。因此，该验证模式使用Windows操作系统的安全机制验证用户身份，只要用户能够通过Windows的认证就可以连接到SQLServer。Windows验证模式是SQLServer默认的身份验证模式2.混合验证模式

混合验证模式允许用户使用Windows验证或SQLServer验证进行连接。在SQLServer模式下，用户在连接SQLServer时必须提供登录名和密码。SQLServer将输入的登录信息与存储在系统表syslogins中的登录信息进行核对。如果能找到相匹配的记录，则表明登录成功。

混合验证模式适用于Windows9x/Me等windows老版本，或者UnixLinux等非Windows用户

10.2.2设置身份验证模式 在对象资源管理器中右击服务器，选择【属性】，打开服务器属性窗口。选择【安全性】页，在【服务器身份验证】下选择身份验证模式，如图10-2所示

图10-210.3 登录管理

使用SSMS管理登陆创建Windows登录账户

创建SQLServer登录账户使用T–SQL语句管理登陆

10.3.1使用SSMS管理登陆

【例10-1】创建Windows登录账户

选择【开始】

【设置】

【控制面板】

【管理工具】

【计算机管理】打开计算机管理窗口，如图10-3所示。

图10-3

展开【本地用户和组】节点，右击用户，选择【新用户】，输入用户名和密码点击【创建】按钮完成Windows用户的创建，如图10-4所示

图10-4

打开SSMS，在对象资源管理器中展开【安全性】，右击【登录名】，选择【新建登录名】，打开如图10-5所示窗口

图10-5

点击【搜索】按钮，在【选择用户或组】窗口中点击【对象类型】按钮，然后选中【用户】前面的方框，如图10-6所示

图10-6

点击【高级】中的【立即查找】按钮，选中用户test，即将Windows系统的用户test作为SQLServer的登录账户，如图10-7所示

图10-7

选择【Windows身份验证】选项，并在默认数据库中选择build。如图10-8所示

图10-8

选择【用户映射】选项，在映射到此登录名的用户列表框中选中【build】并在数据库角色成员身份列表框中选中db_owner复选框。单击【确定】完成Windows登录账户的创建，如图10-9所示

图10-910.3.1使用SSMS管理登陆

【例10-2】创建SQLServer登录账户

选在对象资源管理器中展开【安全性】节点，然后右击【登录名】，选择新建【登录名】，打开【登录名-新建窗口】。输入登录名和密码，选中【SQLServer身份验证】单选钮，在默认数据库下拉列表中选择【build】，如图10-10所示

。

图10-10

选择【用户映射】选项，选择【用户映射】选项，在映射到此登录名的用户列表框中选中【build】复选框，并在数据库角色成员身份列表框中选中db_owner

单击【确定】按钮完成SQLServer登录账户的创建。使用SQLServer账户登录如图10-11所示

图10-1110.3.2 使用T-SQL语句管理登录

创建登录账户

createlogin

修改登录账户

alterlogin

删除登录账户

droplogin

查看登录账户

sp_helplogins创建登录账户

Createloginlogin_name|with<option_list1>|from<source><option_list1>::Password={‘password’|hashed_passwordhashed}[must_change]<option_list2>::|default_database=database|default_languang=language|check_expiration={on|off}|check_policy={on|off}<source>::windows[with<windows_options>[,…]]<windows_options>::=default_database=database|default_language=language

查看登录账户

sp_helplogins参数说明Login_name：指定要创建的登录名。既可以是Windows登录名，也可以是SQLServer登录名。如果从Windows域账户映射login_name，则必须用方括号括起来。Password：仅适用于SQLServer登录名，指定正在创建的登录名对应的密码Must_change：仅适用于SQLServer登录名，如果包括此选项，则SQLServer将在首次使用新登录名时提示用户输入新密码。Default_database：指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。check_expiration：仅适用于SQLServer登录名，指定是否对此登录账户强制实施密码过期策略。【例10-3】创建一个Windows登录名，其默认数据库为build

【例10-4】创建一个名为peter的SQLServer登录，其默认数据库为build

Createlogin[KFDX\Bobbie]Fromwindows,Withdefault_database=buildCreateloginpeterWithpassword=‘123456’,default_database=build;KFDX指定域名，Bobbie是Windows中已存在的登录名修改登录账户

alterloginlogin_name{<status_option>|with<set_option>}<status_option>::=enable|disable<set_option>::=Password={‘password’|hashed_passwordhashed[old_password=‘oldpassword’|<password_option>[<password_option>]]|default_database=database|default_languang=language|name=login_name|check_expiration={on|off}|check_policy={on|off}<password_option>::=must_change|unlock删除登录账户

Droploginlogin_name【例10-5】将登录账户peter的密码进行修改

【例10-6】删除登录账户peteralterloginpeterWithpassword=‘1234’droploginpeter10.4 用户管理

用户简介使用SSMS管理用户使用T–SQL语句管理用户

10.4.1 用户概述

在SQLServer中，账号有两种：登录服务器的登录账号和使用数据库的用户账号。登录账号只能让用户登录到服务器，但不能访问服务器中的数据库。要访问特定的数据库，必须具有用户名。注

登录账户基于服务器，用户账号基于数据库用户名必须关联一个登录名一个登录名可以在不同的数据库中映射为不同的数据库用户不同的数据库可以有相同的用户账号；同时，一个登录名可以在不同的数据库中映射为不同的数据库用户10.4.2使用SSMS管理用户

【例10-7】为数据库build创建名为xq的用户

。打开SSMS，展开【数据库】|【build】|【安全性】节点，右击用户节点，选择【新建用户】，打开数据库用户--新建窗口。

单击登录名后面的…按钮,打开选择登录名对话框。单击浏览按钮，在查找对象对话框中选择PC-201109292119\test，将新用户映射到此登录账户

,如图10-12所示

图10-12

单击确定，返回到选择登录名对话框，在输入要选择的对象名称中将显示已选择的登录账户，如图10-13所示。

图10-13

单击确定，返回到数据库用户-新建窗口，完成其他设置：设置用户名为xq,在数据库角色成员身份列表框中选择db_owner复选框，如图10-14所示

图10-1410.4.3使用T-SQL语句管理用户

创建数据库用户createuser

修改数据库用户alteruser

删除数据库用户dropuser查看数据库用户

sp_helpuser

创建数据库用户

，语法格式如下Createuseruser_nameFromlogin_name

查看数据库用户

，语法格式如下

sp_helpuser‘用户账号名称’

由于用户是登录名在数据库中的映射，因此在创建用户时需要指定登录名。一个登录账号可以关联多个用户账号

修改数据库用户

，语法格式如下alteruseruser_namewith<set_item><set_item>::=Name=new_use_name|default_schema=schema_name|login=login_name

删除数据库用户

，语法格式如下Dropuseruser_name

【例10-8】创建一个名为peter的SQLServer登录账户，然后基于该账户创建数据库用户rq【例10-9】将数据库用户rq重命名为AndyCREATELOGINpeterWITHPASSWORD='1234';gocreateuserrqfromloginpeteralteruserrqwithname=Andy10.5 架构管理

从SQLServer2008开始，在数据库中增加了架构的角色。数据库对象不再归用户直接所有，而是由架构拥有，用户只能通过架构使用对象。每个架构可以归角色所有，允许多个用户管理数据库对象。10.5.1 架构概述

架构是数据库对象的容器。在SQLServer的早期版本中，数据库对象由用户所有，当用户被删除时，数据库对象将会成为没有所有者的“孤儿”。架构的引入有效解决了“孤儿”对象问题。这种机制实现了用户和数据库对象的分离，简化了数据库的管理。创建架构修改架构删除架构架构管理10.5.2使用SSMS管理架构

【例10-10】创建一个名称为Admin的架构。在【对象资源管理器】中展开【数据库】|【build】|【安全性】节点，右击【架构】节点，选择【新建架构】命令，显示如图10-15所示的【架构-新建】窗口

图10-15

在【常规】页面，可以指定架构的名称以及设置架构的所有者。单击【搜索】按钮打开【搜索角色和用户】对话框，如图10-16所示。

图10-16

单击【浏览】按钮，打开【查找对象】对话框。在【查找对象】对话框中选择架构的所有者，可以选择当前系统的所有用户或者角色，如图10-17所示

单击【确定】按钮完成架构的创建

图10-17

在【架构属性】窗口中，选择【权限】打开如图10-18所示窗口。

在【权限】页面单击搜索，添加用户。然后在【用户或角色】列表中选择用户，并在下面的权限列表中，启用相应的复选框的选项，即可完成用户的权限的配置。

图10-1810.5.3使用T-SQL语句管理架构

创建架构createschema

修改架构

alterschema

删除架构dropschema创建架构Createschema{schema_name|authorizationowner_name[<schema_element>[…n][<schema_element>::={Table_definition|view_definition|grant_statementRevoke_statement|deny_statement}参数说明schema_name指定当前数据库中的架构名称。AUTHORIZATIONowner_name指定将拥有架构的数据库级主体的名称。此主体还可以拥有其他架构，并且可以不使用当前架构作为其默认架构。table_definition指定在架构内创建表的CREATETABLE语句。执行此语句的主体必须对当前数据库具有CREATETABLE权限。view_definition指定在架构内创建视图的CREATEVIEW语句。执行此语句的主体必须对当前数据库具有CREATEVIEW权限。grant_statement指定可对除新架构外的任何安全对象授予权限的GRANT语句revoke_statement指定可对除新架构外的任何安全对象撤消权限的REVOKE语句。deny_statement指定可对除新架构外的任何安全对象拒绝授予权限的DENY语句。【例10-11】创建一个名称为web的架构，其拥有者为用户Andy

usebuildcreateschemawebauthorizationAndy修改架构Alterschemaschema_nametransfersecurable_name删除架构Dropschemaschema_name要删除的架构不能包括任何对象。如果包含对象，则删除架构前必须首先删除架构中包含的对象。并且要求具有control权限或者对数据库具有alteranyschema权限

【例10-12】在“build”数据库中创建一个名为Aricle的表，并将该表由默认架构dbo移到Admin架构中【例10-13】删除架构Admin。createtableArticle(IDint,Titlevarchar(50)Contentvarchar(max));GoalterschemaAdmintransferdbo.ArticleDroptableAdmin.Article;DropschemaAdmin;10.6 角色管理

为了方便权限管理，将一些用户集中到一个单元并对该单元设定权限,这样的单元称为角色。在SQLServer中，通过角色可以将用户分为不同的组，属于同一组的用户即具有相同角色的成员能够统一进行管理，并被授予相同的操作权限。

在SQLServer2008中，数据库的权限分配通过角色实现。10.6.1 服务器角色管理10.6.1 服务器角色

服务器角色服务器角色指根据SQLServer的管理任务，以及这些任务相对的重要性等级，把具有SQLServer管理职能的用户划分成不同的用户组。

SQLServer安装时会自动创建9个固定服务器角色用户不能添加、修改和删除服务器角色，但可以把其他登录名作为成员添加到服务器角色中。固定服务器角色描

述bulkadmin块数据操作管理员，具有执行块操作的权限，例如执行BULKINSERT操作dbcreator数据库创建者，具有创建、修改和删除数据库的权限diskadmin磁盘管理员，具有管理磁盘文件的权限processadmin进程管理员，具有管理服务器连接和状态的权限securityadmin安全管理员，具有执行修改登录名的权限serveradmin服务器管理员，具有设置和关闭服务器的权限setupadmin安装程序管理员，具有添加和删除链接服务器并执行某些系统存储过程的权限sysadmin系统管理员，具有执行所有操作的权限public公共角色，没有预先设置的权限，用户可以向该角色授权表10-1固定服务器角色的功能描述

1、使用SSMS管理服务器角色

【例10-14】为登录账户xiaoqi指派sysadmin角色，使其具备系统管理员的权限。

打开SSMS，在对象资源管理器中展开【安全性】|【服务器角色】节点。双击sysadmin角色选项，打开【服务器角色属性】窗口，然后单击【添加】按钮，打开【选择登录名】窗口单击【浏览】按钮，在【查找对象】对话框中选中xiaoqi复选框，如图10-19所示

图10-19

单击【确定】按钮返回到【选择登录名】对话框，如图10-20所示。单击【确定】按钮返回【服务器角色属性】窗口，在角色成员列表中可以看到服务器角色sysadmin的所有成员，其中包括刚刚添加的xiaoqi，如图10-21所示

图10-20

图10-21

2、使用系统存储过程管理服务器角色添加服务器角色成员

sp_addsrvrolemember

查看服务器角色成员

sp_helpsrvrolemember

删除服务器角色成员

sp_dropsrvrolemember

添加服务器角色成员

，语法格式如下sp_addsrvrolemember[@loginame=]‘login’,[@rolename=]’role’

查看服务器角色成员

，语法格式如下

sp_helpsrvrolemember[@srvrolename=]’role’删除服务器角色成员

，语法格式如下

sp_dropsrvrolemember[@loginame=]‘login’,[@rolename=]’role’服务器角色role必须为sysadmin、securityadmin、serveradmin、setupadmin、processadmin、dbcreator、bulkadmin之一

【例10-15】将登录账户xiaoqi添加到sysadmin固定服务器角色，并查看sysadmin角色的成员

。usebuildGosp_addsrvrolemember‘xiaoqi’,’sysadmin’Gosp_helpsrvrolemember‘sysadmin’10.6.2数据库角色

数据库角色能为一组用户授予不同级别的管理或访问数据库或数据库对象的权限。分类固定数据库角色。自定义数据库角色1、固定数据库角色具有执行特定数据库级管理活动的权限在创建数据库时，系统默认创建10个固定的数据库角色用户不能添加、修改和固定数据库角色数据库角色描

述db_accessadmin访问权限管理员，具有添加、删除数据库使用者、角色和组的权限db_backupoperator数据库备份操作员，具有执行数据库备份的权限db_datareader数据检索操作员，可以检索用户表中的所有数据db_datawriter数据维护操作员，可以对用户表执行插入、更新、删除等操作db_ddladmin数据库对象管理员，具有添加、修改或删除数据库中对象的权限db_denydatareader拒绝执行检索操作员，不能读取数据库中任何表的内容db_denydatawriter拒绝执行数据维护操作员，不能对任何表执行删除、插入、更新等操作db_owner数据库所有者，具有在数据库中执行所有操作的权限db_securityadmin安全管理员，具有执行权限管理和角色成员管理等操作的权限public最基本的数据库角色。每个用户都是public角色的成员表10-2固定数据库角色及其权限

【例10-16】将用户Andy添加到固定数据库角色db_owner中

打开SSMS，在对象资源管理器中依次展开【数据库】|【build】|【安全性】|【角色】|【数据库角色】节点。右击db_owner角色，选择属性，打开【数据库角色属性】窗口单击【添加】按钮，打开【选择数据库用户或角色】对话框，单击【浏览】按钮打开【查找对象】对话框，选择数据库用户Andy，如图10-22所示

图10-22

单击【确定】按钮返回【选择数据库用户或角色】对话框，如图10-23所示

单击【确定】按钮，即可将用户Andy添加到db_owner数据库角色中，如图所示，在该窗口中可看到当前角色拥有的架构以及该角色所有的成员，如图10-24所示

。

图10-23

图10-242.自定义数据库角色【例10-17】在数据库build中创建一个名为TestRole的数据库角色并授予权限。

在对象资源管理器窗口依次展开【数据库】|【build】|【安全性】|【角色】节点右击【数据库角色】节点，选择【新建数据库角色】命令，设置角色名称为TestRole，所有者选择dbo，单击【添加】按钮，选择数据库用户，如图10-25所示

图10-25选中【安全对象】选项，单击【搜索】按钮，添加【T_Facility】表为安全对象选中【选择】后面【授予】列的复选框，如图10-26所示单击【列权限】按钮，为该数据角色配置具体权限，如图10-27所示

图10-26

图10-2710.6.3应用程序角色应用程序角色是一种特殊的角色类型，用于满足个别应用程序的特殊需要。该角色只允许通过特定应用程序的用户访问特定数据。这种访问方式能够禁止操作者自行登录SQLServer并执行低质量的查询，以免对整个服务器性能造成负面影响。

应用程序角色特点应用程序角色不包含任何成员，不能将用户添加到应用程序角色

应用程序在默认情况下是非活动的，必须用密码激活后才能发挥作用应用程序角色被激活后，会失去原有的权限，拥有与应用程序角色相关的权限

【例10-18】创建应用程序角色app_user

。

打开SSMS，依次展开依次展开【数据库】|【build】|【安全性】|【角色】结点右击应用程序角色，选择新建应用程序角色，打开【应用程序角色-新建】窗口在【应用程序角色-新建】中填写角色名app_user，默认架构为dbo，并输入密码1234。如图10-28所示

图10-28应用程序角色创建后，只有被激活后，才能发挥作用。通常使用sp_setapprole存储过程激活应用程序角色

10.7 权限管理

权限是执行操作、访问数据的通行证。只有拥有了针对某种安全对象的指定权限，才能对该对象执行相应的操作。隐含权限对象权限语句权限权限类型隐含权限不需要通过授权即可拥有的权限。固定服务器角色和固定数据库角色拥有的权限就是隐含权限。例如

sysadmin拥有在服务器中执行任何活动的权限；db_datareader拥有对数据库中任何表或视图执行select语句的权限；

对象权限针对SQLServer中的对象，决定能对其执行哪些操作

。

分类针对SQLServer2008中所有对象，如control、alter、create、impersonate、viewdefinition

只针对某些对象，如execute只针对存储过程，不针对表或视图

对

象常

用

权

限数据库BACKUPDATABASE、BACKUPLOG、CREATEDATABASE、CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATERULE、CREATETABLE、CREATEVIEW表SELECT、DELETE、INSERT、UPDATE、REFERENCES表值函数SELECT、DELETE、INSERT、UPDATE、REFERENCES视图SELECT、DELETE、INSERT、UPDATE、REFERENCES存储过程EXECUTE、SYNONYM标量函数EXECUTE、REFERENCES表11-3SQLServer2008中的常用对象权限

语句权限用户是否具有执行某一语句的权限。

例如创建表时需要使用createtable语句，用户如果需要创建表，必须具有使用createtable语句的权限

10.7.2使用SSMS管理权限

【例10-21】为用户Andy授予数据库build的createtable权限

。打开SSMS，在对象资源管理器中展开数据库节点。右击数据库build，选择属性，打开数据库属性对话框。在数据库属性对话框中选择权限，打开权限页，如图10-29所示。选择用户Andy，然后设置权限。

图10-2910.7.3使用T-SQL语句管理权限授予权限Grant撤销权限revoke拒绝权限Deny权限管理授予权限Grant|All[privileges]||permission[(column[,…n])][,…n][on[class::]securable]toprincipal[,…n][withgrantoption][asprincipal]参数说明ALL：表示授予对象所有权限。privileges：包含此参数是为了符合ISO标准。Permission：指定权限的名称。Column：指定表中将授予其权限的列的名称。Class：指定将授予其权限的对象的类。Securable：指定将授予其权限的安全对象。Principal：指定为其对象授予权限的主体的名称。WITHGRANTOPTION：允许用户将权限授予其他用户

【例10-22】授予用户xiaoqi对数据库build中T_Facility表的delete权限

usebuildgo

GrantdeleteonT_Facilitytoxiaoqi;撤销权限Revoke[grantoptionfor]|[all[privileges]]|permission[(column[,…n])][,…n]}[on[class::]securable]{to|from}principal[,…n][cascade][asprincipal]参数说明grantoptionfor：表示撤销授予指定权限的能力。如果主体具有不带grant选项的指定权限，则将撤销该权限。cascade：撤销当前主体权限的同时，还将撤销当前主体为其他主体授予的权限。如果使用cascade参数时，则必须同时指定grantoptionfor

。如果对授予withgrantoption权限的权限执行级联撤消,将同时撤销该权限的grant和deny权限。【例10-23】撤销数据库build的用户xiaoqi对T_Facility表的delete的权限

usebuildgoRevokedeleteonT_Facilityfromxiaoqi;拒绝权限Revoke[grantoptionfor]|[all[privileges]]|permission[(column[,…n])][,…n]}[on[class::]securable]{to|from}principal[,…n][cascade][asprincipal]【例8-24】拒绝用户xiaoqi以及xiqoqi已授予delete权限的所有主体对数据库build的delete权限

usebuildgo

Denydeletetoxiaoqicascade;cascade撤销当前主体权限的同时，还将撤销当前主体为其他主体授予的权限。小结SQLServer2008的安全机制SQLServer2008的身份验证模式用户、架构、角色、权限等相关概念登录管理、用户管理、架构管理和角色管理和权限管理习题--填空题1．混合验证模式允许用户使用______或______身份验证进行连接。2．使用_________语句可以创建数据库用户。3．通过系统存储过程sp_helpsrvrole可以查看固定服务器角色列表，通过系统存储过程_________可以查看固定数据库角色列表。4．在SQLServer2008中，数据库用户通过_________拥有表、视图等数据库对象。5．允许用户在数据库中创建视图的权限是_________。6．撤销授予权限使用REVOKE语句，拒绝授予权限使用______语句。