第八章 SQL Server 的安全

学习目标 了解 SQL Server 2000 安全管理机制； 掌握登录帐户和服务器角色的设置； 掌握数据库用户和数据库角色的设置； 掌握权限管理的方法。

学习重点 登录帐户； 数据库用户； 服务器角色与数据库角色； SQL Server 2000 的权限管理。

8.1 SQL Server 2000 的安全机制 SQL Server 2000 的安全机制可以划分为 4 个

等级

8.1 SQL Server 2000 的安全机制 第一级：客户端操作系统的安全性 第二级： SQL Server 2000 登录安全性 若要连接到 Microsoft SQL Server 2000 实例，

需要给 SQL Server 2000 客户端应用程序提供下面中的信息：

．运行 SQL Server 2000 实例的计算机所在的网络名称

．实例名（可选，只有在连接到命名实例时才需要）

．登录标识符 (ID)

8.1 SQL Server 2000 的安全机制 SQL Server 2000 提供的身份验证模式有两种，

即： 1.Windows 身份验证模式 2. Windows 和 SQL Server 验证模式

8.1 SQL Server 2000 的安全机制 第三级：数据库使用的安全性 第四级：数据库对象使用的安全性

8.2 登录帐户和服务器角色 可以使用企业管理器来设置 SQL Server 的身

份验证模式 使用企业管理器添加标准 SQL Server 登录帐

户

8.2.2 添加标准 SQL Server 身份登录帐户

使用 T-SQL 添加标准 SQL Server 登录帐户的语法格式为：

sp_addlogin [ @loginame = ] 'login'    [ ,[ @passwd = ] 'password' ] [ ,[ @defdb = ] 'database' ]   [ , [ @deflanguage = ] 'language' ]

8.2.2 添加标准 SQL Server 身份登录帐户

例 8.1 创建了一个名叫“ zhl” ，密码是“ 88908123” ，默认数据库为“ students” 的标准 SQL Server 登录帐户

EXEC sp_addlogin 'zhl', '88908123', 'students'

GO

8.2.2 添加标准 SQL Server 身份登录帐户

使用系统存储过程 sp_defaultdb 修改默认数据库。

语法格式： sp_defaultdb [ @loginame = ] 'login' ,[ @defd

b = ] 'database'

8.2.2 添加标准 SQL Server 身份登录帐户

使用系统存储过程 sp_defaultlanguage 修改登录帐户的默认语言。

语法格式： sp_defaultlanguage [ @loginame = ] 'login' [ ,

[ @language = ] 'language' ]

8.2.2 添加标准 SQL Server 身份登录帐户

例 8.2 将 pubs 设置为用户 zhl 的默认数据库。

EXEC sp_defaultdb 'zhl', 'pubs‘ 例 8.3 将 zhl 帐户连接的默认英语设置为 Eng

lish 。 EXEC sp_defaultlanguage, 'zhl', 'English'

8.2.2 添加标准 SQL Server 身份登录帐户

使用系统存储过程 sp_password 修改登录帐户的密码。

语法格式： sp_password[[@old=]'old_password'][,[@new

=]'new_password']

8.2.2 添加标准 SQL Server 身份登录帐户

使用系统存储过程 sp_droplogin 删除 SQL Server 标准登录帐户。

语法格式： sp_droplogin [ @loginame = ] 'login'

8.2.2 添加标准 SQL Server 身份登录帐户

在 SQL Server 中删除帐户信息时，有如下限制：

．已经映射到数据库用户的帐户不允许被删除； ．系统帐户 sa 不能被删除，正在使用的帐户

不能被删除； ．拥有数据库的帐户不能被删除。

8.2.2 添加标准 SQL Server 身份登录帐户

例 8.4 将 zhl 帐户连接的密码由 88908123 设置为 88999988

EXEC sp_password '88908123', '88999988', 'zhl’

例 8.5 删除 zhl 标准帐户。 EXEC sp_droplogin, 'zhl'

8.2.3 添加 Windows 身份验证登录帐户

添加 Windows 身份验证登录帐户 所谓与 Windows 集成的登录帐户，实际上是

将 Windows 的用户和工作组映射为 SQL Server 的登录帐户。

使用 T-SQL 添加 Windows 登录帐户 语法格式： sp_grantlogin [@loginame =] 'login'

8.2.3 添加 Windows 身份验证登录帐户

例 8.6 WINDOWS 操作系统中已存在域 cdny,用户 js ，现将其添加为 SQL Server 登录。

sp_grantlogin [cdny\js] 或 sp_grantlogin ‘cdny\js’

8.2.3 添加 Windows 身份验证登录帐户

使用系统存储过程 sp_revokelogin 可以从 SQL Server 中删除用 sp_grantlogin 创建的 Windows 用户或组的登录项。

语法格式： sp_revokelogin [ @loginame = ] 'login'

8.2.3 添加 Windows 身份验证登录帐户

拒绝 Windows 帐户登录 使用系统存储过程 sp_denylogin 可以设置不

允许 Windows 的工作组或用户连接 SQL Server 服务器。

语法格式： sp_denylogin [ @loginame = ] 'login'

8.2.3 添加 Windows 身份验证登录帐户

例 8.7 删除’ cdny\student’ 帐户或工作组的登录。

EXEC sp_revokelogin ‘cdny\student’ 例 8.8 拒绝 cdny/student 帐户登录 SQL Serv

er 。 EXEC sp_denylogin [cdny\student]

8.2.4 特殊登录帐户 sa

特殊登录帐户 sa sa 帐户拥有服务器和所有的数据库，即 sa 拥

有最高的管理权限，可以执行服务器范围内的所有操作。

sa 帐户无法删除。

8.2.5 服务器角色 固定服务器角色用于对登录帐户授予服务器范围内的安

全权限。 System administrators 可执行 SQL Server 安装中的

任何操作 Security administrators 可管理服务器的登录 Server administratorsq 可配置服务器范围的设置 Setup administrators 可以管理扩展的存储过程 Process administrators 可以管理运行在 SQL Server

中的进程 Disk administrators 可以管理磁盘文件 Database creators 可以创建和更改数据库 Bulk inset administrators 可以执行大容量插入操作

8.2.5 服务器角色 使用系统存储过程浏览服务器角色： Sp_helpsrvrole[[@srvrolename=]’role’] 其中 [@srvrolename=]’role’ 是指固定服务器

角色的名称 例 8.9 使用 sp_helpsrvrole 存储过程浏览本地

服务器角色。 EXEC sp_helpsrvrole

8.2.5 服务器角色 将一个帐户加入一个服务器角色，也可使用系

统存储过程 sp_addsrvrolemember 。 语法格式： sp_addsrvrolemember [ @loginame = ] 'logi

n'[@rolename =] 'role'

8.2.5 服务器角色 例 8.10 将固定服务器角色 sysadmin 分配给

了登录帐户 cdny\student1 ，使 cdny\student1拥有了角色 sysadmin 所拥有的所有权限。

EXEC sp_addsrvrolemember cdny\student1 ‘sysadmin’

8.2.5 服务器角色 使用系统存储过程浏览固定服务器角色的权限

语法格式： sp_srvrolepermission[[@srvrolename=]’role’] 例 8.11 EXEC sp_srvrolepermission ‘sysadmi

n’ -- 显示 sysadmin 角色的权限。 EXEC sp_srvrolepermission -- 显示所

有固定服务器角色的权限。

8.3.1 数据库用户 对于每个要求访问数据库的登录，应将其帐户

添加到数据库，成为数据库用户。如果登录帐户不是数据库用户，则登录账户还是无法访问数据库。

1 ．使用企业管理器添加数据库用户 ２．使用 T-SQL 添加数据库用户

8.3.1 数据库用户 ２．使用 T-SQL 添加数据库用户 语法格式： sp_grantdbaccess [@loginame=]'login'[,[@na

me_in_db=] 'name_in_db' [OUTPUT]]

8.3.1 数据库用户 例 8.12 在 pubs 数据库中为 Windows NT 用

户 cdny\leijin 添加帐户，并取名为 leijin 。 Use pubs GO EXEC sp_grantdbaccess ' cdny\leijin ', 'leijin' GO

8.3.2 删除数据库用户 １．使用企业管理器删除数据库用户 2 ．使用系统存储过程 sp_revokedbaccess 删

除数据库用户 语法格式： sp_revokedbaccess [ @name_in_db = ] 'nam

e'

8.3.2 删除数据库用户 例 8.13 删除数据库用户 leijin 。 USE pubs GO EXEC sp_revokedbaccess ‘leijin’ GO

8.3.2 删除数据库用户 注意： revokedbaccess 存储过程不能删除以下内容：

． dbo 、 guest 内置用户帐户； ．数据库中的固定角色。 ． Windows NT 组中的 Windows NT 用户。 在用户定义事务内部不能执行 sp_revokedba

ccess

8.3.3 内置用户帐户 １． dbo 用户 （ 1)dbo 是具有在数据库范围内执行一切操

作的内置用户。它是数据库的所有者。 （ 2 ）固定服务器角色 sysadmin 的任何成员

都映射到每个数据库内称为 dbo 的一个特殊用户上。另外，由固定服务器角色 sysadmin的任何成员创建的任何对象都自动属于 dbo 。

（ 3) 无法删除 dbo 用户，且此用户始终出现在每个数据库中。

8.3.3 内置用户帐户 （ 4) 只有由 sysadmin 固定服务器角色成员

（或 dbo 用户）创建的对象才属于 dbo 。由任何其他也不是 syadmin 固定服务器角色成员的用户（包括 db_owner 固定数据库角色成员）创建的对象：

．属于创建该对象的用户，而不是 dbo 。 ．用创建该对象的用户名限定。 （ 5 ）它的用户 ID （ UID ）总是 1

8.3.3 内置用户帐户 ２． guest 用户 （ 1 ） guest 用户帐户允许没有用户帐户的

登录访问数据库。 （ 2 ）可以在除 master 和 tempdb 外（在

这两个数据库中它必须始终存在）的所有数据库中添加或删除 guest 用户。

8.3.3 内置用户帐户 （ 3 ） guest 用户的存在意味着所有登录到 S

QL Server 服务器的用户都可以访问数据库，即使它还没有成为本数据库的用户。

（ 4 ）默认情况下，新建的数据库中没有 guest 用户帐户。

8.3.3 内置用户帐户 例 8.14 将 guest 用户帐户添加到名为 stude

nts 的数据库中。 USE students GO EXECUTE sp_grantdbaccess guest GO

8.3.4 数据库角色 固定数据库角色是在每个数据库中都存在的预

定义组。 数据库管理员可以将一个用户加入到一个或多

个数据库角色中。 固定数据库角色不能被添加、修改或删除。 SQL Server 在数据库级别上设置了固定数据

库角色来提供基本的数据库权限管理。

8.3.4 数据库角色 固定数据库角色 描述 db_owner 数据库所有者 db_accessadmin 数据库访问管理员 db_securityadmin 数据库安全管理员 db_ddladmin 数据库 DDL 管理员 db_backupoperator 数据库备份操作员 db_datareader 数据库数据读取者 db_datawriter 数据库数据写入者 db_denydatareader 数据库拒绝数据读取者 db_denydatawriter 数据库拒绝数据写入者

8.3.5 管理数据库角色 系统存储过程 sp_addrole 在当前数据库中创

建新的数据库角色 语法格式： sp_addrole [ @rolename = ] 'role'[ , [ @owner

name = ] 'owner' ]

8.3.5 管理数据库角色 例 8.15 将名为 teacher 的新角色添加到 stud

nets 数据库中。 EXEC sp_addrole 'teacher'

8.3.5 管理数据库角色 系统存储过程 sp_addrolemember 将某个数据

库用户加入到某个数据库角色中 语法格式： sp_addrolemember [ @rolename = ] 'role' ,[

@membername = ] 'security_account'

8.3.5 管理数据库角色 例 8.16　将数据库用户加入到 students 数据库 teacher

角色中 A. 添加 Windows NT 用户 将 Windows NT 登录帐户 cdny\zhl 添加到 students

数据库，使其成为数据库用户 jszhl 。然后，再将 jszhl 添加到 students 数据库的 teacher 角色中。

USE students GO EXEC sp_grantdbaccess 'cdny\zhl', ‘jszhl’ GO EXEC sp_addrolemember 'teacher', 'jszhl'

8.3.5 管理数据库角色 B. 添加 SQL Server 用户 将 SQL Server 用户 leijin 添加到 studetns 数

据库中的 teacher 角色。 EXEC sp_addrolemember 'teacher', 'leijin'

8.3.5 管理数据库角色 系统存储过程 sp_droprolemember 从当前数

据库中的数据库角色中删除安全帐户 语法格式： sp_droprolemember [ @rolename = ] 'role' , [

@membername = ] 'security_account'

8.3.5 管理数据库角色 例 8.17 从角色 teacher 中删除用户 leijin 。 EXEC sp_droprolemember 'teacher', 'leijin'

8.3.5 管理数据库角色 角色应用 （ 1 ） public 角色 public 角色是一个特殊的数据库角色，每个数据库用

户都属于它。 public 角色捕获数据库中用户的所有默认权限。 无法将用户、组或角色指派给 public 角色，因为默

认情况下它们即属于该角色。 public 角色含在每个数据库中，包括 master 、 msd

b 、 tempdb 、 model 和所有用户数据库。 无法除去 public 角色。

8.3.5 管理数据库角色 （ 2 ）应用程序角色 应用程序角色是一种特殊的数据库角色。 应用程序角色不包含任何成员，用户不允许添

加到该角色中。

8. 4 权限管理 SQL Server 2000 中的权限分为三种：对象

权限、语句权限和隐含权限 1. 对象权限 是指数据库用户对数据库中的表、存储过程、视图等对象的操作权限。

2.隐含权限 是指系统预定义的服务器角色或数据库角色，

或数据库所有者和数据库对象所有者拥有的权限。隐含权限不能明确地赋予和撤销。

8. 4 权限管理 3. 语句权限 指是否可以执行某些 T-SQL 命令的权限。 具体包括： CREATE DATABASE ：创建一个新数据库及存储该数据库的文件 BACKUP DATABASE ：备份整个数据库 BACKUP LOG ：备份事务日志 CREATE DEFAULT ：创建称为默认值的对象 CREATE FUNCTION ：创建用户定义函数 CREATE PROCEDURE ：创建存储过程 CREATE RULE ：创建规则 CREATE TABLE ：创建表 CREATE VIEW ：创建视图

8. 4 权限管理 权限管理包含以下三方面的内容： ．授予权限 ．拒绝权限 ．取消权限 管理权限的方法有两种：一是使用 T-SQL 语句，二是通过企业管理器。

8. 4 权限管理 （ 1 ）授予权限 ①授予对象权限的语法格式如下： GRANT {ALL [PRIVILEGES] | permission[,...n]} { [(column [,...n])] ON {table | view} | ON {table | view }[(column[,...n])] | ON {stored_procedure } } TO security_account [,...n] [WITH GRANT OPTION]

8. 4 权限管理②授予命令权限 语法格式： GRANT ｛ ALL ｜ statement [,...n]｝ TO security_account [,...n]

8. 4 权限管理 例 8.18 将“ students” 数据库的“ stu”表中查询数据的权限授予了 public 角色，将在“ stu”表中执行 INSERT 操作的权限授予了 yq 。

USE students GO GRANT SELECT ON stu TO public GO GRANT INSERT ON stuTo yq

8. 4 权限管理 （ 2 ）撤销权限　①撤销语句权限。 语法格式 : REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ]

8. 4 权限管理 ②撤销对象权限。 语法格式：  REVOKE {ALL|permission[,...n]} { | ON {table | view }[(column[,...n])] | ON {stored_procedure } } {TO | FROM} security_account [,...n]

8. 4 权限管理 例 8.19 将从登录帐户 yq 和 cdny\js 组撤消创

建表和视图的权利。 USE students GO REVOKE CREATE VIEW, CREATE TABLE FROM 'yq', [cdny\js] GO

8. 4 权限管理 （ 3 ）拒绝访问 ①拒绝对象权限。 语法格式： DENY {ALL|permission[,...n]} { | ON {table | view }[(column[,...n])] | ON {stored_procedure } } TO security_account [,...n]

8. 4 权限管理②拒绝命令权限。 语法格式： DENY ｛ ALL ｜ statement [,...n]｝ TO security_account [,...n]

8. 4 权限管理 例 8.20 首先将在“ students” 数据库“ stu”表中执

行 SELECT 操作的权限授予 public 角色，这样所有数据库用户都拥有了该项权限。然后，又拒绝 yq 拥有该项权限。

USE students GO GRANT SELECT ON stu TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON s

tu TO yq GO

总结 登录帐户通过映射成为数据库用户，从而实现对数据

库的访问。 在服务器级别上可使用固定服务器角色来实现登录帐

户权限的管理。 在数据库级别上可使用固定服务器角色实现数据库用

户权限的管理。 权限用来控制数据库用户如何访问数据库对象。权限

分为对象权限、语句权限和隐含权限。 一个用户或角色的权限可以有三种存在形式：授权、

拒绝、剥夺。