主题：补充课件下载——SQL Server 2000的安全性管理

1 简述
SQL Server 2000的安全性管理建立在认证（authentication）和访问许可（permission ）两者机制上的

1.1认证
来确定登录SQL Server的用户的登录账号和密码是否正确，以此来验证其是否具有连接SQL Server的权限
但是通过认证阶段并不代表能够访问SQL Server中的数据，用户只有在获取访问数据库的权限之后才能够对服务器上的数据库进行权限许可下的各种操作，主要是针对数据库对象，如表、视图、存储过程等

1.2 访问许可
这种用户访问数据库权限的设置是通过用户账号来实现的，同时在SQL Server中角色作为用户组的代替物大大地简化了安全性管理

2 SQL Server的登录认证
SQL Server能在两种安全模式下运行：仅WINDOWS认证模式、混合模式（SQL Server认证和WINDOWS认证）
可以在“企业管理器”中对数据服务器的“安全性”属性进行设置，调整何种安全认证方式（默认为仅WINDOWS认证，此时无法进行SQL Server认证）

2.1 WINDOWS认证模式
SQL Server数据库系统通常运行在NT服务器平台或基于NT构架的WINDOWS2000上，而NT作为网络操作系统本身就具备管理登录，验证用户合法性的能力
所以WINDOWS认证模式正是利用这一用户安全性和账号管理的机制，允许SQL Server也可以使用NT的用户名和口令

WINDOWS认证模式比起SQL Server认证模式来有许多优点
WNDOWS认证模式集成了NT或WINDOWS 2000的安全系统，并且NT安全管理具有众多特征，如安全合法性口令，加密，对密码最小长度进行限制等。所以当用户试图登录到SQL Server时，它从NT或WNDOWS 2000的网络安全属性中获取登录用户的账号与密码，并使用NT或WINDWOS 2000验证账号和密码的机制来检验登录的合法性，从而提高了SQL Server的安全性
可以使用Window的组来方便用户的管理

如果用户登录SQL Server没有给出用户登录名，自动使用NT集成认证方式，如果SQL Server为NT集成认证方式，SQL Server忽略给出的登录名

2.2 SQL Server认证
在该认证模式下用户在连接SQL Server时必须提供登录名和登录密码，这些登录信息存储在系统表syslogins中，与NT的登录账号无关，SQL Server自己执行认证处理，如果输入的登录信息与系统表syslogins中的某条记录相匹配，则表明登录成功

实验：
利用企业管理器，对其中的服务器进行认证模式的设置（只有混合方式才可以两者皆行）
利用设置ODBC数据源的方式尝试连接
利用VB的ADO或者VFP的远程视图尝试不同认证方式下的连接

2.3 混合认证模式
在混合认证模式下WINDOWS认证和SQL Server认证这两种认证模式都是可用的，NT的用户既可以使用NT认证，也可以使用SQL Server认证

3 管理SQL Server的登录
使用企业管理器来操作
使用Transact_SQL来操作

3.1 使用企业管理器来操作
试验：
新建NT用户
在企业管理器的“安全性”——“登录”中可以添加新的登录名，设置它为NT认证方式，所关联的用户即为上述新建用户，尝试设置它的数据库访问权限（如db_accessadmin和db_owner），再以此用户登录观察数据库的访问情况（注意先把服务器设置为NT访问方式）
也可以新建一个Sql Server认证的登录名（无密码），并把服务器设置为混合访问方式，再用新建SQL Server注册的方式观察数据库的访问情况（此时，两种方式都可以登录）
也可以设置一个集成认证的拒绝访问（他无法使用SQL Server服务器）

3.2 使用Transact_SQL来操作
在SQL Server中一些存储过程提供了管理SQL Server登录功能，主要包括
sp_granlogin
sp_revokelogin
sp_denylogin
sp_addlogin
sp_droplogin
sp_helplogins

3.2.1 sp_grantlogin
设定一WINDOWS NT用户或用户组为SQL Server登录者
exec sp_grantlogin 'STUDENT101\XX'

3.2.2 sp_revokelogin
用来删除NT用户或用户组在SQL Server上的登录

3.2.3 sp_denylogin
拒绝某一NT用户或用户组连接到SQL Server
注意：使用sp_revokelogin并不能完全拒绝某一用户登录到SQL Server，比如对于NT用户XJ，来说其归属于组GROUP1，则只要该NT用户组具有登录SQL Server的权限，即使执行了上例中的语句，用户XJ仍可以登录到SQL Server上，同理，只要用户XJ的登录信息没有被删除，即使GROUP1的登录信息被删除，则该用户仍能登录到SQL Server上

3.2.4 sp_addlogin
创建新的使用SQL Server认证模式的登录账号
exec sp_addlogin 'JSJ','12345678','pubs','us_english'

3.2.5 sp_droplogin
SQL Server中删除该登录账号，禁止其访问SQL Server
exec sp_droplogin 'JSJ'

3.2.6 sp_helplogins
用来显示SQL Server所有登录者的信息，包括每一个数据库里与该登录者相对应的用户名称
sp_helplogins @LoginNamePattern = 'sa'

4 数据库用户
数据库用户用来指出哪一个人可以访问哪一个数据库
在一个数据库中，用户ID惟一标识一个用户、用户对数据的访问权限以及对数据库对象的所有关系都是通过用户账号来控制的
用户账号总是基于数据库的，即两个不同数据库中可以有两个相同的用户账号

在数据库中，用户账号与登录账号是两个不同的概念。一个合法的登录账号只表明该账号通过了NT认证或SQL Server认证，但不能表明其可以对数据库数据和数据对象进行某种或某些操作，所以一个登录账号总是与一个或多个数据库用户账号（这些账号必须分别存在相异的数据库中）相对应，这样才可以访问数据库

例如，登录账号sa自动与每一个数据库用户dbo相关联，通常而言，数据库用户账号总是与某一登录账号相关联，但有一个例外那就是guest用户（不可以删除），在安装系统时guest用户被加入到master、pubs、tempdb和Northwind数据中，具体的原因在于guest用户让那些没有属于自己的用户账号的SQL Server登录者把其作为缺省的用户从而使该登录者能够访问具有guest用户的数据库

实验：
利用管理员将Northwind等数据库的guest用户删除，重新建立新的SQL Server注册时以其他用户登录，会发现无法打开删除guest用户的数据库

4.1 利用企业管理器管理数据库用户
在创建一个SQL Server 登录账号时就可以先为该登录账号定出其在不同数据库中所使用的用户名称，这实际上也完成了创建新的数据库用户这一任务

4.2 利用存储过程管理数据库用户
有以下一些方法：
sp_granddbaccess
sp_revokedbaccess
sp_helpuser
注意：老版本的sp_adduser和sp_dropuser不要再使用

4.2.1 sp_grantdbaccess
用来为SQL Server登录者或NT用户或用户组建立一个相匹配的数据用户账号
use Northwind
exec sp_grantlogin 'STUDENT101\XX‘
exec sp_grantdbaccess 'STUDENT101\XX','XJ'

4.2.2 sp_revokedbaccess
将数据库用户从当前数据库中删除，其相匹配的登录者就无法使用该数据库
正如我们不能删除有数据库用户与之相匹配的登录账号一样，如果被删除的数据库用户在当前数据库中拥有任一对象（如表、视图、存储过程）将无法用该系统过程把它从数据库中删除，只有在删除其所拥有和所有的对象后才可以将数据库用户删除

例子1：
use Northwind
exec sp_revokedbaccess 'XJ‘
例子2：
use Northwind
exec sp_addlogin 'JSJ','12345678','pubs','us_english'
exec sp_grantdbaccess 'JSJ','XJ'

4.2.3 sp_helpuser
用来显示当前数据库的指定用户信息
use pubs
exec sp_helpuser 'ssss'

5 权限管理
用户在登录到SQL Server之后，其用户账号所归属的NT组或角色所被授予的权限决定了该用户能够对哪些数据库对象执行哪种操作，以及能够访问修改哪些数据

5.1 权限的类型
在SQL Server中包括两种类型的权限
对象权限
语句权限

5.1.1 对象权限
对象权限总是针对表、视图、存储过程而言，它决定了能对表、视图、存储过程执行哪些操作，如UPDATE，DELETE，INSERT，EXECUTE
如果用户想要对某一对象进行操作，其必须具有相应的操作的权限，例如，当用户要成功修改表中数据时，则前提条件是他已经被授予表的UPDATE权限

不同类型的对象支持不同的针对它的操作，如不能对表对象执行EXECUTE操作

5.1.2 语句权限
主要指用户是否具有权限来执行某一语句，这些语句通常是一些具有管理性的操作，如创建数据库、表、存储过程等。这种语句虽然仍包含有操作（如CREATE）的对象，但这些对象在执行该语句之前并不存在于数据库中，如创建一个表在CREATE TABLE语句未成功执行前，数据库中没有该表，所以将其归为语句权限范畴

5.2 利用企业管理器管理权限
展开指定的数据库，然后单击Users图标，“属性”——“权限”按钮
当空白方格内为对号时，表示执行了GRANT语句，即用户能够对该对象执行相应的操作
当空白方格内为错号时，表示执行了DENY语句，即禁止对该对象的相应操作
也可以通过面向数据库对象的权限设置来进行
在表的属性中就有权限按钮即可察看

注意：
如果在Database role membership选项栏中选择任何一个数据库角色（在缺省条件下任何数据库用户都至少是public）角色实际上就完成了数据库用户语句权限的设置，因为对于这些数据库固定角色，SQL Server已定义了其具有哪些语句权限
例如db_owner具有执行CREATE DATABASE的语句权限

5.3 利用存储过程管理权限
使用三种命令来管理权限
GRANT
REVOKE
DENY

5.3.1 GRANT
用来把权限授予某一用户以允许该用户执行针对该对象的操作
如UPDATE、SELECT、DELETE、EXECUTE或允许其运行某些语句，如CREATE TABLE、CRETAE DATABASE

5.3.2 REVOKE
取消用户对某一对象或语句的权限，这些权限是经过GRANT语句授予的，不许该用户执行针对数据库对象的某些操作
如UPDATE、SELECT、DELETE、EXECUTE或不允许其运行某些语句如CREATE TABLE、CREATE DATABASE

5.3.3 DENY
用来禁止用户对某一对象或语句的权限明确禁止其对某一用户对象执行某些操作
如UPDATE、SELECT、DELETE、EXECUTE或运行某些语句，如CREATE TABLE、CREATE DATABASE
如：
use pubs
deny select,insert,update,delete
on authors
to ssss

6 角色管理
自SQL Server 7版本开始引入了新的概念：角色，替代以前版本中组的概念
和组一样，SQL Server管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可实现对所有用户权限的设置，大大减少了管理员的工作量

6.1 角色的类型
在SQL Server中，主要有两种角色类型：
服务器角色
数据库角色

6.1.1 服务器角色
指根据SQL Server的管理任务以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分成不同的用户组，每一组所具有管理SQL Server的权限已被预定义
服务器角色适用在服务器范围内，并且其权限不能被修改

例如，具有sysadmin角色的用户在SQL Server中可以执行任何管理性的工作，任何企图对其权限进行修改的操作都将会失败。这一点与数据库角色不同

6.1.2 数据库角色
常常会发现要将一套数据库专有权限授予给多个用户，但这些用户并不属于同一个NT用户组，或者虽然这些用户可以被NT管理者划为同一NT用户组，但遗憾的是却没有管理NT账号的权限，这时就可以在数据库中添加新数据库角色，或使用已经存在的数据库角色，并让这些有着相同数据库权限的用户归属于同一角色

由此可见，数据库角色能为某一用户或一组用户授予不同级别的管理或访问数据库或数据库对象的权限
这些权限是数据库专有的，而且还可以使一个用户具有属于同一数据库的多个角色

SQL Server提供了两种数据库角色类型
预定义的数据库角色
用户自定义的数据库角色

6.1.2.1 预定义的数据库角色
指这些角色所有具有的管理访问数据库权限已被SQL Server定义，并且SQL Server管理者不能对其所具有的权限进行任何修改
SQL Server中的每一个数据库中都有一组预定义的数据库角色，在数据库中使用预定义的数据库角色可以将不同级别的数据库管理工作分给不同的角色，很容易实现工作权限的传递
如让某一用户临时或长期具有创建和删除数据库对象、表、视图、存储过程的权限，那么只要把他设置为db_ddladmin数据库角色即可

6.1.2.2 用户自定义的数据库角色
当为某些数据库用户设置相同的权限，但是这些权限不等同于预定义的数据库角色所具有的权限时，就可以定义新的数据库角色来满足这一要求，从而使这些用户能够在数据库中实现某一特定功能

用户自定义的数据库角色具有以下几个优点：
SQL Server数据库角色可以包含NT用户组或用户
在同一数据库中用户可以具有多个不同的自定义角色，这种角色的组合是自由的，而不仅仅是public与其它一种角色的结合
角色可以嵌套，在数据库实现不同级别的安全性

用户定义的数据库角色有两种类型：
标准角色
应用角色

6.1.2.2.1 标准角色
类似于SQL Server 7版本以前的用户组
它通过对用户权限等级的认定而将用户划分为不同的用户组使用户总是相对于一个或多个角色，从而实现管理的安全性
所有的预定义的数据库角色或SQL Server管理者自定义的某一角色（该角色具有管理数据库对象或数据库的某些权限）都是标准角色

6.1.2.2.2 应用角色
是一种比较特殊的角色类型，只能让某些用户通过特定的应用程序间接地存取数据库中的数据，如通过SQL Server Query Analyzer或Microsoft Excel而不是直接存取数据库数据
限制用户的这种访问方式将禁止用户使用应用程序（如 SQL查询分析器）连接到 SQL Server实例并执行编写质量差的查询，以免对整个服务器的性能造成负面影响

6.2 角色的管理
使用企业管理器管理服务器角色
使用存储过程来管理服务器角色
使用企业管理器管理数据库角色
使用存储过程来管理数据库角色

6.2.1 使用企业管理器管理服务器角色
安全性”——“服务器角色”
其中的System Administrator有成员关联

6.2.2 使用存储过程来管理服务器角色
sp_addsrvrolemember
sp_dropsrvrolemember

6.2.2.1 sp_addsrvrolemember
将某一登录加入到服务器角色内使其成为该角色的成员
exec sp_addsrvrolemember 'eric','sysadmin'

6.2.2.2 sp_dropsrvrolemember
将某一登录者从某一服务器角色中删除，当该成员从服务器角色中被删除后，便不再具有该服务器角色所设置的权限
exec sp_dropsrvrolemember 'eric','sysadmin'

6.2.3 使用企业管理器管理数据库角色
在打开的数据库中新建角色，并可以将数据库用户加入进来

6.2.4 使用存储过程来管理数据库角色
sp_addrole
sp_addapprole
sp_droprole
sp_dropapprole
sp_helprole
sp_helprolemember
sp_addrolemember
sp_droprolemember

6.2.4.1 sp_addrole
用来创建新数据库角色
exec sp_addrole 'lsq'

6.2.4.2 sp_addapprole
建立应用角色

6.2.4.3 sp_droprole
用来删除数据库中某一自定义的数据库角色

6.2.4.4 sp_dropapprole
删除应用角色

6.2.4.5 sp_helprole
用来显示当前数据库所有的数据库角色的全部信息

6.2.4.6 sp_helprolemember
用来显示某一数据库角色的所有成员
use northwind
exec sp_addrolemember 'lsq','eric'
exec sp_helprolemember 'lsq'

6.2.4.7 sp_addrolemember
用来向数据库某一角色中添加数据库用户，这些角色可是用户自定义的标准角色，也可以是预定义的数据库角色，但不能是应用角色
exec sp_addrolemember 'lsq','eric'（注意：lsq为角色名，eric为用户名）

6.2.4.8 sp_droprolemember
用来删除某一角色的成员
exec sp_droprolemember 'lsq','eric'