第 11章 安全管理
11.1 安全认证模式
11.2 登录管理
11.3 用户管理
11.4 角色管理
11.5 权限管理
11.1 安全认证模式在 SQL Server系统中,安全性采用的是两级权限管理机制 。 第一级是服务器级的,连接权,,第二级是数据库的,访问权,。 用户使用 SQL Server时,需要通过两级权限的验证：
（ 1） 规定哪些用户能登录系统,即登录时的身份验证 。
用户登录时,系统将首先检验用户是否有连接 SQL Server
实例的权力,如用户通过了身份验证,仅表示该用户可以连接
SQL Server实例,而不是表示其对 SQL Server实例中的数据库有进行操作的权力 。
（ 2） 这些用户对相应数据库中的对象有哪些访问权限,即权限论证 。
通过了身份验证后,还必须在要访问的数据库中存在一个用户帐户,并将访问相应数据库的权限授予该登录用户 。 只有得到了访问数据库的权限,才能允许登录用户访问相关数据库中的数据 。 因此,权限论证可以控制用户对数据库中数据的操作 。
第
11
章安全管理
< >
11.1.1 身份验证
1,身份验证模式
SQL Server提供了两种身份验证模式,Windows身份验证模式和混合模式 。
（ 1） Windows身份验证模式
Windows身份验证模式使用 Windows操作系统本身提供的安全机制验证用户的身份 。 只要用户能够通过 Windows NT或
Windows 2000的用户帐户验证,就可连接到 SQL Server。
（ 2） 混合模式 （ Windows身份验证和 SQL Server身份验证 ）
混合模式下,用户的身份验证由 Windows和 SQL Server共同承担,系统将确认连接用户在 Windows操作系统下是否可信,
对于可信的连接用户,系统直接采用 Windows身份验证方式,
当用户使用指定的登录账号和密码进行非信任连接时,SQL
Server将检测登录账号的存在性和密码的匹配性,自行进行验证 。
第
11
章安全管理
< >
11.1.1 身份验证
2,设置身份验证模式
SQL Server的身份验证模式在安装时由管理员指定 。
1） 启动企业管理器,展开相应的服务器组 。
2） 右击需更改身份验证模式的服务器节点,在弹出的快捷菜单中执行,属性,命令,出现如图 11-1所示的,SQL Server
属性 （ 配置 ）,对话框 。
3） 单击,安全性,选项卡,进入,安全性,选项页 。 在
,安全性,组框中提供了两个选项：
l“身份验证,单选按钮组中设置身份验证模式 。
l“审核级别,单选按钮组中设置审核级别 。
4） 选择一种身份验证模式和审核级别 。
5） 单击,确定,按钮,关闭对话框 。
6） 重新启动 SQL Server,以使修改生效 。
第
11
章安全管理
< >
11.1.2 权限认证当登录用户通过了身份验证并允许其登录到 SQL Server实例之后,在用户必须访问的每个数据库中都要求有对应的用户帐户 。 在每个数据库中都要求用户帐户可防止用户访问服务器上的所有数据库,同时对于没有用户账号的数据库将无法访问 。
默认情况下,数据库的所有者 (dbo) 可以实现对该数据库的完全控制,还可以根据需要向其他用户授予或解除访问该数据库或对象的权限,以便让这些用户也拥有针对该数据库的访问权利或解除权力 。
用户可向 SQL Server发送各种 T-SQL语 句命令,用于完成相应的任务 。 但当 SQL Server 实例接收到 T-SQL语句时,将首先检查用户是否有在数据库中执行该语句的权限,如果用户没有执行语句的权限,或者没有访问该语句所使用对象的权限,
则 SQL Server将返回权限错误,命令执行将以失败告终 。 因此对于每个要求访问数据库的 SQL Server登录用户,应将其 SQL
Server用户帐户添加到相应数据库,如果用户未在数据库中创建,则登录用户就无法访问数据库 。
第
11
章安全管理
< >
11.2 登录管理
11.2.1 SQL Server的安全性
1,登录账号为登录和访问 SQL Server,用户必须提供正确的登录账号 。 这些登录账号既可以是 Windows中的登录账号,也可以是
SQL Server登录账号 。 登录账号是系统信息,存储在 master数据库的 sysxlogins系统表中 。
SQL Server安装完成后,系统中已存在两个登录帐户：
系统管理员 sa和临时账号 guest。
2,数据库用户每个用户必须通过登录帐户建立与 SQL Server实例的连接 （ 身份验证 ）,以获得对 SQL Server实例的访问权限 。 然后,
该登录还必须将登录账号映射到需访问数据库中的一个 SQL
Server 用户帐户,通过该用户帐户取得对数据库的执行权限
（ 权限验证 ） 。
用户账号信息存储在需要访问数据库的系统表 sysusers中,
其中每个记录表示一个 Windows用户账号,组账号,SQL
Server登录账号或 SQL Server角色 。
第
11
章安全管理
< >
11.2 登录管理
3,角色
SQL Server的角色相当于 Windows中的组,可以把某些用户集中到一种角色中,然后对该角色应用权限 。 对一个角色授予,拒绝或废除权限将适用于该角色中的任何成员,这样,只要对角色进行权限管理就可以实现对属于该角色中的所有用户的管理,如此可以大大减轻数据库管理员的工作量 。
第
11
章安全管理
< >
11.2.2 创建登录
1,创建 Windows用户或组创 建 Windows用户或组,就是将 Windows中的用户和组映射为 SQL Server的登录帐户 。
(1) 使用 T-SQL语句创建 Windows用户或组使用系统存储过程 sp_grantlogin将使 Windows的用户或组账号映射成 SQL Server的登录帐户 。 sp_grantlogin系统存储过程的基本语法如下：
sp_grantlogin [@loginame =] 'login'
其中 login
参数用于指定要添加的 Windows NT 用户或组的名称 。
Windows NT 组和用户必须用 Windows NT 域名限定,格式为
,域 \用户,,例如 JinlqServer\Jinlq。
【 例 11-1】 将 Windows Server 2000中本地组 users映射成
SQL Server的登录帐户 。
sp_grantlogin [BUILTIN\users]
第
11
章安全管理
< >
11.2.2 创建登录
(2) 使用企业管理器创建 Windows用户或组
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 单击并展开,安全性,节点,右击,登录,项,在弹出的快捷菜单中执行,新建登录,命令,出现如图 11-2所示的
,新建登录,对话框 。
3） 在,名称,输入框中输入 Windows中已存在的用户账号或工作组名称,这里输入,users”工作组 。
4） 在,身份验证,单选按钮组中选中,Windows身份验证,
单选项,表示该登录使用 Windows身份验证 。
5） 在,域,输入框中输入或选择一个使用的域名 。 这里输入,BUILTIN”,表示内置本地组 。
6） 在,默认设置,栏的,数据库,列表框中选择一个默认的数据库 。
7） 单击,确定,按钮,即可将 Windows工作组 users映射成
SQL Server的登录帐户 。
第
11
章安全管理
< >
2．创建 SQL Server 登录
(1) 使用 T-SQL语句创建 登录可 以 使 用 系 统 存 储 过 程 sp_addlogin 创建 SQL
Server登录账号 。 sp_addlogin的语法格式如下：
sp_addlogin 'loginID' [,'password' ] [,
'database' ]
其中各参数的含义如下：
l loginID：指定将要创建的登录账号名称；
l password：指定登录账号所对应的登录密码；
l database：指定登录后所连接到的默认数据库 。
【 例 11-2】 创建 SQL Server的登录帐户 Useredu，
并设置密码和默认数据库 。
sp_addlogin 'Useredu','edu','Educational'
第
11
章安全管理
< >
2．创建 SQL Server 登录
(2) 使用企业管理器创建 登录
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 单击并展开,安全性,节点,右击,登录,项,在弹出的快捷菜单中执行,新建登录,命令,出现如图 11-2所示的
,新建登录,对话框 。
3） 在,名称,输入框中输入需创建的 SQL Server登录名,
这里输入,eduTest”。
4） 在,身份验证,单选按钮组中选中,SQL Server身份验证,单选项,表示该登录使用 SQL Server身份验证 。
5） 在,密码,输入框中为该登录账号设置一个密码 。
6） 在,默认设置,栏的,数据库,列表框中选择一个默认的数据库 。
7） 单击,确定,按钮 。
8） 系统检查两次密码输入的正确性,若正确,则完成 SQL
Server登录帐户的创建工作；否则要求重输,直至正确 。
第
11
章安全管理
< >
11.2.3 管理登录
1,查看登录信息使用系统存储过程 sp_helplogins可以提供每个数据库中的登录及相关用户的信息 。 sp_helpuser的语法格式如下：
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]
2,修改登录信息数据库管理员或数据库所有者可以方便地用企业管理器来修改登录账号的属性,如默认数据库,默认语言,授权访问某一数据库等 。 如果是 SQL Server账号,则还可以修改口令；
若是 Windows NT/2000账号,则应使用 Windows NT/2000的用户管理器修改其口令 。
修改方法是：在企业管理器中按 11.2.2节中,使用企业管理器创建登录,相似,只是在第 2)步稍有不同,展开,安全性,
节点后,单击,登录,项,在右边的,登录项目,列表框中列出了系统中已创建的所有登录账号,右击需修改的登录账号,
在弹出的快捷菜单中选择,属性,命令,在出现的,登录属性,
对话框中进行必要的修改即可 。
第
11
章安全管理
< >
11.2.3 管理登录
3,删除登录
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开,安全性,节点,单击,登录,项,在右边的,登录项目,列表框中列出了系统中已创建的所有登录账号 。
3） 右击需删除的登录账号,在弹出的快捷菜单中执行,删除,命令,出现如图 11-3所示的,删除登录,
确认框 。
4） 单击,是,按钮,删除选定的登录账号 。 登录账号删除后,该登录账号所对应的数据库用户也一起被删除 。
第
11
章安全管理
< >
11.3 用户管理一个登录必须与每个数据库中的一个用户账号相关联后,
用这个登录账号登录的用户才能访问数据库中的对象 。
11.3.1 查看用户使用系统存储过程 sp_helpuser可以查看数据库中的有效帐户信息 。 sp_helpuser的语法格式如下：
sp_helpuser [[ @name_in_db = ] 'security_account' ]
其中 security_account参数指定当前数据库中 SQL Server
用户,Windows NT 用 户 或 数 据 库 角 色 的 名 称 。
security_account必须存在于当前的数据库中 。
第
11
章安全管理
< >
11.3.2 创建用户安全账号通过登录账号登录 SQL Server的用户,如果在数据库中没有授予该登录访问数据库的权限,则该用户还是无法访问数据库的 。 因此,必须将每一个需要访问数据库的登录账号添加到相关数据库中 。
1,使用 T-SQL语句创建用户账号使用系统存储过程 sp_grantdbaccess可以在当前数据库中添加一个用户安全帐户,并 使 其 能 够 被 授 予 在 数 据 库 中 执 行 活 动 的 权 限 。
sp_grantdbaccess的语法格式如下：
sp_grantdbaccess 'login' [,'name_in_db' [OUTPUT]]
其中各参数的含义如下：
l login：指定当前数据库中新安全帐户的登录名称 。 Windows NT组和用户必须用 Windows NT域名限定,格式为,域 \用户,。
l name_in_db：指定数据库中用户帐户的名称 。
【 例 11-3】 在 Educational数据库中为 Useredu登录账号创建一个名为
U_edu的用户账号 。
USE Educational
EXEC sp_grantdbaccess 'Useredu','U_edu'
第
11
章安全管理
< >
11.3.2 创建用户安全账号
2,使用企业管理器创建用户账号
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要创建用户账号的数据库节点,
这里为 Educational数据库 。
3） 单击,用户,项,在右边的,用户项目,列表框中列出了系统中已创建的所有用户账号,如图 11-4所示 。
4） 右击右边列表框的任意位置,在弹出的快捷菜单中执行
,新建数据库用户,命令,出现,新建用户,对话框 。
5） 在,登录名,组合框中选择一个关联的登录账号 。 选择后系统在下面的,用户名,输入框中自动填入与登录名相同的用户账号,可以根据需要修改用户账号名,但同一数据库中的用户账号必须惟一 。
6） 选择,登录名,后,在图 11-5下部的,数据库角色成员,
列表框中系统自动将,public”数据库角色应用到该用户,使该登录账号有访问 Educational数据库的权限 。
7） 单击,确定,按钮,完成创建用户账号及初步设置访问权限的工作 。
第
11
章安全管理
< >
11.3.3 删除数据库中的用户账号当需要撤消某一登录用户对指定数据库的访问权限时,最简单的办法就是在该数据库中删除该登录用户在指定数据库中的用户账号 。
1,使用 T-SQL语句删除数据库中的用户账号
sp_revokedbaccess [ @name_in_db = ] 'name'
其中 name参数指定数据库中需删除用户帐户的名称 。
【 例 11-4】 在 Educational数据库中删除 U_edu用户账号 。
USE Educational
EXEC sp_revokedbaccess 'U_edu'
2,使用企业管理器删除数据库中的用户账号
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要删除用户账号的数据库节点 。
3） 单击,用户,项,在右边的,用户项目,列表框中列出了数据库中已存在的所有用户账号,如图 11-4所示 。
4） 右击要删除的用户账号,在弹出的快捷菜单中执行,删除,命令,出现,确定删除用户,确认框,单击,是,按钮即可 。
第
11
章安全管理
< >
11.4 角色管理
SQL Server中提供了两种角色：固定服务器角色和固定数据库角色 。
固定服务器角色是服务器级权限,通过固定服务器角色可以授予用户在服务器上进行相应管理操作的权限 。 固定服务器角色信息存储在 master数据库的 sysxlogins系统表中,具体信息如表 11-1所示 。
固定数据库角色是数据库级权限,存在于每个数据库中,
通过固定数据库角色可以授予用户在数据库上进行相应管理操作的权限 。 固定数据库角色信息存储在 需访问数据库的系统表
sysusers中,具体信息如表 11-2所示 。
在 SQL Server中,一个用户可以属于多个角色,用户权限是多个角色权限的总和 。 权限在用户成为角色成员时自动生效 。
第
11
章安全管理
< >
表 11-1 固定服务器角色可以执行 BULK INSERT 语句bulkadmin
可以管理磁盘文件diskadmin
可以创建、更改和删除数据库dbcreator
可以管理在 SQL Server中运行的进程processadmin
可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码
securityadmin
可以管理链接服务器和启动过程setupadmin
可以设置服务器范围的配置选项，关闭服务器serveradmin
可以在 SQL Server中执行任何操作sysadmin
说 明固定服务器角色表 2 固定数据库角色不能更改数据库内任何用户表中的任何数据db_denydatawriter
不能选择数据库内任何用户表中的任何数据db_denydatareader
可以更改数据库内任何用户表中的所有数据db_datawriter
可以选择数据库内任何用户表中的所有数据db_datareader
可以发出 DBCC,CHECKPOINT 和 BACKUP 语句db_backupoperator
可以发出 ALL DDL，但不能发出 GRANT,REVOKE
或 DENY 语句
db_ddladmin
可以管理全部权限、对象所有权、角色和角色成员资格db_securityadmin
可以添加或删除用户帐户db_accessad in
在数据库中有全部权限db_owner
说 明固定数据库角色
11.4.1 创建用户定义数据库角色为了方便管理,有时需要自定义数据库角色 。 通过自定义数据库角色将一组相同的权限授予不同的用户 。 可以使用系统存储过程 sp_addrole在当前数据库中创建自定义数据库角色,sp_addrole的语法格式如下：
sp_addrole 'role' [,'owner' ]
其中各参数的含义如下：
l role：要创建的新角色的名称 。
l owner：新角色的所有者 。 必须是当前数据库中的某个用户或角色 。
【 例 11-5】 在 Educational数据库中创建一个名为
eduManagers的数据库角色 。
USE Educational
EXEC sp_addrole 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.2 添加角色成员在 SQL Server中,将需授予一定权限的用户添加到相应的角色中,就可实现权限的授予,而不必将权限直接应用到用户帐户上 。
可以将有效的用户添加到固定服务器角色,固定数据库角色和用户自定义数据库角色中 。
1,将用户添加到固定服务器角色使用系统存储过程 sp_addsrvrolemember可以将用户添加到固定服务器角色中,使其成为固定服务器角色的成员 。
sp_addsrvrolemember的语法格式如下：
sp_addsrvrolemember 'login' [,'role' ]
其中各参数的含义如下：
l login：是添加到固定服务器角色的登录名称 。
l role：要将登录添加到的固定服务器角色的名称 。
【 例 11-6】 将登录用户 Useredu添加到 sysadmin固定数据库角色中 。
EXEC sp_addsrvrolemember 'Useredu','sysadmin'
第
11
章安全管理
< >
11.4.2 添加角色成员
2,将用户添加到数据库角色使用系统存储过程 sp_addrolemember可以将用户添加到数据库角色中,使其成为数据库角色的成员 。 sp_addrolemember
的语法格式如下：
sp_addrolemember 'role','security_account'
其中各参数的含义如下：
l role：指定当前数据库中数据库角色的名称 。
l security_account：是添加到数据库角色的用户账号 。
【 例 11-7】 将 Educational数据库中的用户 U_edu添加到
eduManagers数据库角色中 。
USE Educational
EXEC sp_addrolemember 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.3 查看角色信息
1,查看 SQLServer固定服务器角色列表使用系统存储过程 sp_helpsrvrole可以查看 SQL Server固定服务器角色列表 。 sp_helpsrvrole的语法格式如下：
sp_helpsrvrole [ [ @srvrolename = ] 'role' ]
其中 role参数是要查看的固定服务器角色的名称,如省略,
表示查看所有 固定服务器角色 。
2,查看 当前数据库中角色的信息使用系统存储过程 sp_helprole可以查看当前数据库中数据库角色的信息 。 sp_helprole的语法格式如下：
sp_helprole [ [ @rolename = ] 'role' ]
其中 role参数是当前数据库中某个角色的名称,如省略,表示查看所有数据库角色 。
第
11
章安全管理
< >
11.4.3 查看角色信息
3,查看 固定服务器角色成员的信息使用系统存储过程 sp_helpsrvrolemember可以查看固定服务器角色成员的信息 。 sp_helpsrvrolemember的语法格式如下：
sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]
其中 role参数是要查看的固定服务器角色的名称,如省略,
表示查看所有 固定服务器角色 。
4,查看 数据库角色成员的信息使用系统存储过程 sp_helprolemember可以查看数据库角色成员的信息 。 sp_helprolemember的语法格式如下：
sp_helprolemember [ [ @rolename = ] 'role' ]
其中 role参数是当前数据库中某个角色的名称,如省略,表示查看所有数据库角色 。
第
11
章安全管理
< >
11.4.4 删除角色及其成员
1,从 SQLServer固定服务器角色中删除成员使 用 系 统 存 储 过 程 sp_dropsrvrolemember 可 以 从 SQL
Server固定服务器角色中删除成员 。 sp_dropsrvrolemember的语法格式如下：
sp_dropsrvrolemember [ @loginame = ] 'login',
[ @rolename = ] 'role?
【 例 11-8】 从服务器角色 sysadmin中删除 Useredu登录 。
EXEC sp_dropsrvrolemember 'Useredu','sysadmin'
2,从 数据库角色中删除成员使用系统存储过程 sp_droprolemember可以从 SQL Server数据库角色中删除成员 。 sp_droprolemember的语法格式如下：
sp_droprolemember [ @rolename = ]
'role',[ @membername = ] 'security_account'
【 例 11-9】 从 eduManagers数据库角色中删除用户 U_edu。
USE Educational
EXEC sp_droprolemember 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.4 删除角色及其成员
3,删除 数据库角色使用系统存储过程 sp_droprole可以从当前数据库中删除指定的数据库角色 。 sp_droprole的语法格式如下：
sp_droprole [ @rolename = ] 'role?
【 例 11-10】 从 Educational 数据库中删除
eduManagers角色 。
USE Educational
EXEC sp_droprole 'eduManagers'
上 例 在 当 前 数 据 库 Educational 中,删 除 了
eduManagers角色 。
第
11
章安全管理
< >
11.5 权限管理通过创建登录账号和用户账号,用户可以通过登录账号连接数据库服务器并取得对数据库的初步访问权限 （ 因每个用户都是 public角色的成员 ）,但要进行任何涉及更改数据库定义或访问数据的活动,则必须有相应的权限 。 使用权限规定用户可以使用哪些对象,对这些对象可以进行哪些操作,是否具有执行创建数据库对象语句的权限等,因此权限在 SQL Server
中非常重要 。
第
11
章安全管理
< >
11.5.1 权限类型
SQL Server通过权限加强系统的安全性 。 SQL Server中提供了三种类型的权限：默认权限,对象权限和语句权限 。
1,默认权限默认权限是指系统自动授予而不需要授权就有的权限,用于控制那些只能由预定义系统角色的成员或数据库对象所有者能执行的操作,包括固定服务器角色,数据库角色和数据库对象所有者所拥有的权限 。
2,对象权限对象权限就是对数据库对象的访问与操作权限,如用户没有对象权限,则不能访问该对象 。
3,语句权限语句权限用于控制用户创建数据库或数据库中对象的操作权限 。 语句权限仅用于控制语句自身,而不适用于数据库中定义的特定对象 。 对数据库对象的操作权限由对象权限决定 。
第
11
章安全管理
< >
11.5.2 授予权限可以使用 GRANT语句授予用户帐户执行语句和对象的权限 。
1,授予对象权限授予对象权限的 GRANT语句的语法格式如下：
GRANT
{ ALL [ PRIVILEGES ] | permission [,...n ] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]
第
11
章安全管理
< >
【 例 11-11】 授予 U_edu用户对表 Course 执行
SELECT,INSERT和 UPDATE语句的权限,并允许该用户将其权限授予其他用户 。
USE Educational
GRANT SELECT,INSERT,UPDATE
ON Course
TO U_edu
WITH GRANT OPTION /* 将其拥有的权限可以再授予其他用户 */
11.5.2 授予权限
2,授予语句权限授予语句权限的 GRANT语句的语法格式如下：
GRANT { ALL | statement [,...n ] }
TO security_account [,...n ]
其中各参数的含义如下：
l ALL：表示授予所有可用的权限 。 但只有 sysadmin角色成员可以使用 ALL。
l statement：是被授予权限的语句 。 语句列表请参见表 11-3。
【 例 11-12】 授予 U_edu用户具有执行 CREATE TABLE和
CREATE PROCEDURE语句的权限 。
USE Educational
GRANT CREATE TABLE,CREATE PROCEDURE
TO U_edu
第
11
章安全管理
< >
11.5.3 禁止权限在需要时,可能想限制某些用户或角色的权限,这可以使用 DENY语句实现 。 通过 DENY语句可以收回以前授予用户,组或角色的权限；拒绝给当前数据库内的安全帐户授予权限并阻止安全帐户通过其组或角色成员的方式继承权限 。
1,禁止对象权限禁止对象权限的 DENY语句的语法格式如下：
DENY
{ ALL [ PRIVILEGES ] | permission [,..,] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [,...n ]
[ CASCADE ]
第
11
章安全管理
< >
【 例 11-13】 禁止 U_edu用户对表 Course执行 SELECT、
INSERT和 UPDATE语句的权限,并将该用户授予其他用户的权限同时收回 。
USE Educational
DENY SELECT,INSERT,UPDATE
ON Course
TO U_edu
CASCADE /* 将 U_edu用户授予其他用户的权限收回
*/
上例在当前数据库 Educational 中禁止 U_edu 用户对
Course 表的 SELECT,INSERT和 UPDATE语句的权限,并将
U_edu用户授予其他用户的权限收回 。
11.5.3 禁止权限
2,禁止语句权限禁止语句权限的 DENY语句的语法格式如下：
DENY { ALL | statement [,...n ] }
TO security_account [,...n ]
其中参数与 GRANT语句相同,这里不再复述 。
【 例 11-14】 禁止 U_edu 用 户 具 有 执 行 CREATE
TABLE和 CREATE PROCEDURE语句的权限 。
USE Educational
DENY CREATE TABLE,CREATE PROCEDURE
TO U_edu
上例在当前数据库 Educational 中拒绝了 U_edu 用户执行 CREATE TABLE和 CREATE PROCEDURE语句的权限 。
第
11
章安全管理
< >
11.5.4 撤消权限撤消权限是指删除以前曾授予用户或角色的权限,类似于禁止,但与禁止权限不同的是撤消权限并不禁止安全帐户通过其组或角色成员的方式继承权限 。
可以使用 REVOKE语句撤消数据库用户的权限 。
1,撤消对象权限撤消对象权限的 REVOKE语句的语法格式如下：
REVOKE [ GRANT OPTION FOR ]
{ ALL [ PRIVILEGES ] | permission [,...n ] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
{ TO | FROM } security_account [,...n ]
[ CASCADE ]
[ AS { group | role } ]
第
11
章安全管理
< >
【 例 11-15】 撤消 U_edu用户对表 Course执行 SELECT、
INSERT和 UPDATE语句的权限,并删除该用户授予其他用户权限的能力 。
USE Educational
REVOKE GRANT OPTION FOR /* 删除 U_edu用户授予其他用户权限的能力 */
SELECT,INSERT,UPDATE
ON Course
TO U_edu
上例在当前数据库 Educational 中撤消 U_edu 用户对
Course 表的 SELECT,INSERT和 UPDATE语句的权限,并删除 U_edu用户授予其他用户权限的能力 。
11.5.4 撤消权限
2,撤消语句权限撤消语句权限的 REVOKE语句,其语法格式如下：
REVOKE { ALL | statement [,...n ] }
FROM security_account [,...n ]
其中参数与 GRANT语句相同,这里不再复述 。
【 例 11-16】 撤消 U_edu 用 户 具 有 执 行 CREATE
TABLE和 CREATE PROCEDURE语句的权限 。
USE Educational
REVOKE CREATE TABLE,CREATE PROCEDURE
FROM U_edu
上例在当前数据库 Educational 中撤消了 U_edu用户执行 CREATE TABLE和 CREATE PROCEDURE语句的权限 。
第
11
章安全管理
< >
11.5.5 使用企业管理器进行权限的授予、
禁止和撤消
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要授予权限的数据库 。
3） 右击数据库,在弹出的快捷菜单中执行,属性,命令
(如图 11-4所示 ),出现,数据库属性,对话框 。 单击,权限,
选项卡,授予语句权限,如图 11-6所示 。
4） 如要授予用户对某一语句的权限,可以在对应的方框上单击,直至出现一个绿色的,√,；如要禁止用户对某一语句的权限,可以在对应的方框上单击,直至出现一个红色的
,×,；如要撤消用户对某一语句的权限,可以在对应的方框上单击,直至出现一个空白的方块,□,。
5） 单击,确定,按钮,完成语句权限的授予,禁止和撤消工作,返回前一界面 。
6） 单击,用户,项 (如图 11-4所示 ),在右边的,用户项目,
列表框中列出了数据库中已创建的所有用户账号 。 右击需授予对象权限的用户,在弹出的快捷菜单中执行,属性,菜单项,
出现,数据库用户属性,对话框的常规选项页 。
第
11
章安全管理
< >
11.5.5 使用企业管理器进行权限的授予、
禁止和撤消
7） 在,数据库用户属性,对话框的常规选项页上单击,权限,按钮,出现如图 11-7所示的,数据库用户属性,对话框的权限选项页 。
8） 在,数据库用户,组合框中选择要授予对象权限的用户 。 下面的表格中将显示该用户所拥有的权限情况 。
9） 如要授予该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个绿色的,√,；如要禁止该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个红色的,×,；如要撤消该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个空白的方块,□,。
10） 单击,确定,按钮,完成权限的授予,禁止和撤消工作 。
第
11
章安全管理
< >
11.5.6 查看权限信息为了系统的安全,管理员随时需要查看并掌握用户在数据库中所拥有的权限,然后根据实际需要决定是否增加或删除该用户的权限 。 使用系统存储过程 sp_helprotect可以查看当前数据库中某对象的权限或语句权限的信息 。
sp_helprotect的语法格式如下：
sp_helprotect [ [ @name = ] 'object_statement' ]
[,[ @username = ] 'security_account' ]
[,[ @grantorname = ] 'grantor' ]
[,[ @permissionarea = ] 'type' ]
l object_statement：是当前数据库中要查看其权限的对象或语句的名称 。
l security_account：要查看权限的安全帐户名称 。
l grantor：已授权的安全帐户的名称 。
l type：表示是显示对象权限 （ 字符串 o）,语句权限
（ 字符串 s） 还是两者都显示 (o s)。
第
11
章安全管理
< >
11.1 安全认证模式
11.2 登录管理
11.3 用户管理
11.4 角色管理
11.5 权限管理
11.1 安全认证模式在 SQL Server系统中,安全性采用的是两级权限管理机制 。 第一级是服务器级的,连接权,,第二级是数据库的,访问权,。 用户使用 SQL Server时,需要通过两级权限的验证：
（ 1） 规定哪些用户能登录系统,即登录时的身份验证 。
用户登录时,系统将首先检验用户是否有连接 SQL Server
实例的权力,如用户通过了身份验证,仅表示该用户可以连接
SQL Server实例,而不是表示其对 SQL Server实例中的数据库有进行操作的权力 。
（ 2） 这些用户对相应数据库中的对象有哪些访问权限,即权限论证 。
通过了身份验证后,还必须在要访问的数据库中存在一个用户帐户,并将访问相应数据库的权限授予该登录用户 。 只有得到了访问数据库的权限,才能允许登录用户访问相关数据库中的数据 。 因此,权限论证可以控制用户对数据库中数据的操作 。
第
11
章安全管理
< >
11.1.1 身份验证
1,身份验证模式
SQL Server提供了两种身份验证模式,Windows身份验证模式和混合模式 。
（ 1） Windows身份验证模式
Windows身份验证模式使用 Windows操作系统本身提供的安全机制验证用户的身份 。 只要用户能够通过 Windows NT或
Windows 2000的用户帐户验证,就可连接到 SQL Server。
（ 2） 混合模式 （ Windows身份验证和 SQL Server身份验证 ）
混合模式下,用户的身份验证由 Windows和 SQL Server共同承担,系统将确认连接用户在 Windows操作系统下是否可信,
对于可信的连接用户,系统直接采用 Windows身份验证方式,
当用户使用指定的登录账号和密码进行非信任连接时,SQL
Server将检测登录账号的存在性和密码的匹配性,自行进行验证 。
第
11
章安全管理
< >
11.1.1 身份验证
2,设置身份验证模式
SQL Server的身份验证模式在安装时由管理员指定 。
1） 启动企业管理器,展开相应的服务器组 。
2） 右击需更改身份验证模式的服务器节点,在弹出的快捷菜单中执行,属性,命令,出现如图 11-1所示的,SQL Server
属性 （ 配置 ）,对话框 。
3） 单击,安全性,选项卡,进入,安全性,选项页 。 在
,安全性,组框中提供了两个选项：
l“身份验证,单选按钮组中设置身份验证模式 。
l“审核级别,单选按钮组中设置审核级别 。
4） 选择一种身份验证模式和审核级别 。
5） 单击,确定,按钮,关闭对话框 。
6） 重新启动 SQL Server,以使修改生效 。
第
11
章安全管理
< >
11.1.2 权限认证当登录用户通过了身份验证并允许其登录到 SQL Server实例之后,在用户必须访问的每个数据库中都要求有对应的用户帐户 。 在每个数据库中都要求用户帐户可防止用户访问服务器上的所有数据库,同时对于没有用户账号的数据库将无法访问 。
默认情况下,数据库的所有者 (dbo) 可以实现对该数据库的完全控制,还可以根据需要向其他用户授予或解除访问该数据库或对象的权限,以便让这些用户也拥有针对该数据库的访问权利或解除权力 。
用户可向 SQL Server发送各种 T-SQL语 句命令,用于完成相应的任务 。 但当 SQL Server 实例接收到 T-SQL语句时,将首先检查用户是否有在数据库中执行该语句的权限,如果用户没有执行语句的权限,或者没有访问该语句所使用对象的权限,
则 SQL Server将返回权限错误,命令执行将以失败告终 。 因此对于每个要求访问数据库的 SQL Server登录用户,应将其 SQL
Server用户帐户添加到相应数据库,如果用户未在数据库中创建,则登录用户就无法访问数据库 。
第
11
章安全管理
< >
11.2 登录管理
11.2.1 SQL Server的安全性
1,登录账号为登录和访问 SQL Server,用户必须提供正确的登录账号 。 这些登录账号既可以是 Windows中的登录账号,也可以是
SQL Server登录账号 。 登录账号是系统信息,存储在 master数据库的 sysxlogins系统表中 。
SQL Server安装完成后,系统中已存在两个登录帐户：
系统管理员 sa和临时账号 guest。
2,数据库用户每个用户必须通过登录帐户建立与 SQL Server实例的连接 （ 身份验证 ）,以获得对 SQL Server实例的访问权限 。 然后,
该登录还必须将登录账号映射到需访问数据库中的一个 SQL
Server 用户帐户,通过该用户帐户取得对数据库的执行权限
（ 权限验证 ） 。
用户账号信息存储在需要访问数据库的系统表 sysusers中,
其中每个记录表示一个 Windows用户账号,组账号,SQL
Server登录账号或 SQL Server角色 。
第
11
章安全管理
< >
11.2 登录管理
3,角色
SQL Server的角色相当于 Windows中的组,可以把某些用户集中到一种角色中,然后对该角色应用权限 。 对一个角色授予,拒绝或废除权限将适用于该角色中的任何成员,这样,只要对角色进行权限管理就可以实现对属于该角色中的所有用户的管理,如此可以大大减轻数据库管理员的工作量 。
第
11
章安全管理
< >
11.2.2 创建登录
1,创建 Windows用户或组创 建 Windows用户或组,就是将 Windows中的用户和组映射为 SQL Server的登录帐户 。
(1) 使用 T-SQL语句创建 Windows用户或组使用系统存储过程 sp_grantlogin将使 Windows的用户或组账号映射成 SQL Server的登录帐户 。 sp_grantlogin系统存储过程的基本语法如下：
sp_grantlogin [@loginame =] 'login'
其中 login
参数用于指定要添加的 Windows NT 用户或组的名称 。
Windows NT 组和用户必须用 Windows NT 域名限定,格式为
,域 \用户,,例如 JinlqServer\Jinlq。
【 例 11-1】 将 Windows Server 2000中本地组 users映射成
SQL Server的登录帐户 。
sp_grantlogin [BUILTIN\users]
第
11
章安全管理
< >
11.2.2 创建登录
(2) 使用企业管理器创建 Windows用户或组
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 单击并展开,安全性,节点,右击,登录,项,在弹出的快捷菜单中执行,新建登录,命令,出现如图 11-2所示的
,新建登录,对话框 。
3） 在,名称,输入框中输入 Windows中已存在的用户账号或工作组名称,这里输入,users”工作组 。
4） 在,身份验证,单选按钮组中选中,Windows身份验证,
单选项,表示该登录使用 Windows身份验证 。
5） 在,域,输入框中输入或选择一个使用的域名 。 这里输入,BUILTIN”,表示内置本地组 。
6） 在,默认设置,栏的,数据库,列表框中选择一个默认的数据库 。
7） 单击,确定,按钮,即可将 Windows工作组 users映射成
SQL Server的登录帐户 。
第
11
章安全管理
< >
2．创建 SQL Server 登录
(1) 使用 T-SQL语句创建 登录可 以 使 用 系 统 存 储 过 程 sp_addlogin 创建 SQL
Server登录账号 。 sp_addlogin的语法格式如下：
sp_addlogin 'loginID' [,'password' ] [,
'database' ]
其中各参数的含义如下：
l loginID：指定将要创建的登录账号名称；
l password：指定登录账号所对应的登录密码；
l database：指定登录后所连接到的默认数据库 。
【 例 11-2】 创建 SQL Server的登录帐户 Useredu，
并设置密码和默认数据库 。
sp_addlogin 'Useredu','edu','Educational'
第
11
章安全管理
< >
2．创建 SQL Server 登录
(2) 使用企业管理器创建 登录
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 单击并展开,安全性,节点,右击,登录,项,在弹出的快捷菜单中执行,新建登录,命令,出现如图 11-2所示的
,新建登录,对话框 。
3） 在,名称,输入框中输入需创建的 SQL Server登录名,
这里输入,eduTest”。
4） 在,身份验证,单选按钮组中选中,SQL Server身份验证,单选项,表示该登录使用 SQL Server身份验证 。
5） 在,密码,输入框中为该登录账号设置一个密码 。
6） 在,默认设置,栏的,数据库,列表框中选择一个默认的数据库 。
7） 单击,确定,按钮 。
8） 系统检查两次密码输入的正确性,若正确,则完成 SQL
Server登录帐户的创建工作；否则要求重输,直至正确 。
第
11
章安全管理
< >
11.2.3 管理登录
1,查看登录信息使用系统存储过程 sp_helplogins可以提供每个数据库中的登录及相关用户的信息 。 sp_helpuser的语法格式如下：
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]
2,修改登录信息数据库管理员或数据库所有者可以方便地用企业管理器来修改登录账号的属性,如默认数据库,默认语言,授权访问某一数据库等 。 如果是 SQL Server账号,则还可以修改口令；
若是 Windows NT/2000账号,则应使用 Windows NT/2000的用户管理器修改其口令 。
修改方法是：在企业管理器中按 11.2.2节中,使用企业管理器创建登录,相似,只是在第 2)步稍有不同,展开,安全性,
节点后,单击,登录,项,在右边的,登录项目,列表框中列出了系统中已创建的所有登录账号,右击需修改的登录账号,
在弹出的快捷菜单中选择,属性,命令,在出现的,登录属性,
对话框中进行必要的修改即可 。
第
11
章安全管理
< >
11.2.3 管理登录
3,删除登录
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开,安全性,节点,单击,登录,项,在右边的,登录项目,列表框中列出了系统中已创建的所有登录账号 。
3） 右击需删除的登录账号,在弹出的快捷菜单中执行,删除,命令,出现如图 11-3所示的,删除登录,
确认框 。
4） 单击,是,按钮,删除选定的登录账号 。 登录账号删除后,该登录账号所对应的数据库用户也一起被删除 。
第
11
章安全管理
< >
11.3 用户管理一个登录必须与每个数据库中的一个用户账号相关联后,
用这个登录账号登录的用户才能访问数据库中的对象 。
11.3.1 查看用户使用系统存储过程 sp_helpuser可以查看数据库中的有效帐户信息 。 sp_helpuser的语法格式如下：
sp_helpuser [[ @name_in_db = ] 'security_account' ]
其中 security_account参数指定当前数据库中 SQL Server
用户,Windows NT 用 户 或 数 据 库 角 色 的 名 称 。
security_account必须存在于当前的数据库中 。
第
11
章安全管理
< >
11.3.2 创建用户安全账号通过登录账号登录 SQL Server的用户,如果在数据库中没有授予该登录访问数据库的权限,则该用户还是无法访问数据库的 。 因此,必须将每一个需要访问数据库的登录账号添加到相关数据库中 。
1,使用 T-SQL语句创建用户账号使用系统存储过程 sp_grantdbaccess可以在当前数据库中添加一个用户安全帐户,并 使 其 能 够 被 授 予 在 数 据 库 中 执 行 活 动 的 权 限 。
sp_grantdbaccess的语法格式如下：
sp_grantdbaccess 'login' [,'name_in_db' [OUTPUT]]
其中各参数的含义如下：
l login：指定当前数据库中新安全帐户的登录名称 。 Windows NT组和用户必须用 Windows NT域名限定,格式为,域 \用户,。
l name_in_db：指定数据库中用户帐户的名称 。
【 例 11-3】 在 Educational数据库中为 Useredu登录账号创建一个名为
U_edu的用户账号 。
USE Educational
EXEC sp_grantdbaccess 'Useredu','U_edu'
第
11
章安全管理
< >
11.3.2 创建用户安全账号
2,使用企业管理器创建用户账号
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要创建用户账号的数据库节点,
这里为 Educational数据库 。
3） 单击,用户,项,在右边的,用户项目,列表框中列出了系统中已创建的所有用户账号,如图 11-4所示 。
4） 右击右边列表框的任意位置,在弹出的快捷菜单中执行
,新建数据库用户,命令,出现,新建用户,对话框 。
5） 在,登录名,组合框中选择一个关联的登录账号 。 选择后系统在下面的,用户名,输入框中自动填入与登录名相同的用户账号,可以根据需要修改用户账号名,但同一数据库中的用户账号必须惟一 。
6） 选择,登录名,后,在图 11-5下部的,数据库角色成员,
列表框中系统自动将,public”数据库角色应用到该用户,使该登录账号有访问 Educational数据库的权限 。
7） 单击,确定,按钮,完成创建用户账号及初步设置访问权限的工作 。
第
11
章安全管理
< >
11.3.3 删除数据库中的用户账号当需要撤消某一登录用户对指定数据库的访问权限时,最简单的办法就是在该数据库中删除该登录用户在指定数据库中的用户账号 。
1,使用 T-SQL语句删除数据库中的用户账号
sp_revokedbaccess [ @name_in_db = ] 'name'
其中 name参数指定数据库中需删除用户帐户的名称 。
【 例 11-4】 在 Educational数据库中删除 U_edu用户账号 。
USE Educational
EXEC sp_revokedbaccess 'U_edu'
2,使用企业管理器删除数据库中的用户账号
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要删除用户账号的数据库节点 。
3） 单击,用户,项,在右边的,用户项目,列表框中列出了数据库中已存在的所有用户账号,如图 11-4所示 。
4） 右击要删除的用户账号,在弹出的快捷菜单中执行,删除,命令,出现,确定删除用户,确认框,单击,是,按钮即可 。
第
11
章安全管理
< >
11.4 角色管理
SQL Server中提供了两种角色：固定服务器角色和固定数据库角色 。
固定服务器角色是服务器级权限,通过固定服务器角色可以授予用户在服务器上进行相应管理操作的权限 。 固定服务器角色信息存储在 master数据库的 sysxlogins系统表中,具体信息如表 11-1所示 。
固定数据库角色是数据库级权限,存在于每个数据库中,
通过固定数据库角色可以授予用户在数据库上进行相应管理操作的权限 。 固定数据库角色信息存储在 需访问数据库的系统表
sysusers中,具体信息如表 11-2所示 。
在 SQL Server中,一个用户可以属于多个角色,用户权限是多个角色权限的总和 。 权限在用户成为角色成员时自动生效 。
第
11
章安全管理
< >
表 11-1 固定服务器角色可以执行 BULK INSERT 语句bulkadmin
可以管理磁盘文件diskadmin
可以创建、更改和删除数据库dbcreator
可以管理在 SQL Server中运行的进程processadmin
可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码
securityadmin
可以管理链接服务器和启动过程setupadmin
可以设置服务器范围的配置选项，关闭服务器serveradmin
可以在 SQL Server中执行任何操作sysadmin
说 明固定服务器角色表 2 固定数据库角色不能更改数据库内任何用户表中的任何数据db_denydatawriter
不能选择数据库内任何用户表中的任何数据db_denydatareader
可以更改数据库内任何用户表中的所有数据db_datawriter
可以选择数据库内任何用户表中的所有数据db_datareader
可以发出 DBCC,CHECKPOINT 和 BACKUP 语句db_backupoperator
可以发出 ALL DDL，但不能发出 GRANT,REVOKE
或 DENY 语句
db_ddladmin
可以管理全部权限、对象所有权、角色和角色成员资格db_securityadmin
可以添加或删除用户帐户db_accessad in
在数据库中有全部权限db_owner
说 明固定数据库角色
11.4.1 创建用户定义数据库角色为了方便管理,有时需要自定义数据库角色 。 通过自定义数据库角色将一组相同的权限授予不同的用户 。 可以使用系统存储过程 sp_addrole在当前数据库中创建自定义数据库角色,sp_addrole的语法格式如下：
sp_addrole 'role' [,'owner' ]
其中各参数的含义如下：
l role：要创建的新角色的名称 。
l owner：新角色的所有者 。 必须是当前数据库中的某个用户或角色 。
【 例 11-5】 在 Educational数据库中创建一个名为
eduManagers的数据库角色 。
USE Educational
EXEC sp_addrole 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.2 添加角色成员在 SQL Server中,将需授予一定权限的用户添加到相应的角色中,就可实现权限的授予,而不必将权限直接应用到用户帐户上 。
可以将有效的用户添加到固定服务器角色,固定数据库角色和用户自定义数据库角色中 。
1,将用户添加到固定服务器角色使用系统存储过程 sp_addsrvrolemember可以将用户添加到固定服务器角色中,使其成为固定服务器角色的成员 。
sp_addsrvrolemember的语法格式如下：
sp_addsrvrolemember 'login' [,'role' ]
其中各参数的含义如下：
l login：是添加到固定服务器角色的登录名称 。
l role：要将登录添加到的固定服务器角色的名称 。
【 例 11-6】 将登录用户 Useredu添加到 sysadmin固定数据库角色中 。
EXEC sp_addsrvrolemember 'Useredu','sysadmin'
第
11
章安全管理
< >
11.4.2 添加角色成员
2,将用户添加到数据库角色使用系统存储过程 sp_addrolemember可以将用户添加到数据库角色中,使其成为数据库角色的成员 。 sp_addrolemember
的语法格式如下：
sp_addrolemember 'role','security_account'
其中各参数的含义如下：
l role：指定当前数据库中数据库角色的名称 。
l security_account：是添加到数据库角色的用户账号 。
【 例 11-7】 将 Educational数据库中的用户 U_edu添加到
eduManagers数据库角色中 。
USE Educational
EXEC sp_addrolemember 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.3 查看角色信息
1,查看 SQLServer固定服务器角色列表使用系统存储过程 sp_helpsrvrole可以查看 SQL Server固定服务器角色列表 。 sp_helpsrvrole的语法格式如下：
sp_helpsrvrole [ [ @srvrolename = ] 'role' ]
其中 role参数是要查看的固定服务器角色的名称,如省略,
表示查看所有 固定服务器角色 。
2,查看 当前数据库中角色的信息使用系统存储过程 sp_helprole可以查看当前数据库中数据库角色的信息 。 sp_helprole的语法格式如下：
sp_helprole [ [ @rolename = ] 'role' ]
其中 role参数是当前数据库中某个角色的名称,如省略,表示查看所有数据库角色 。
第
11
章安全管理
< >
11.4.3 查看角色信息
3,查看 固定服务器角色成员的信息使用系统存储过程 sp_helpsrvrolemember可以查看固定服务器角色成员的信息 。 sp_helpsrvrolemember的语法格式如下：
sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]
其中 role参数是要查看的固定服务器角色的名称,如省略,
表示查看所有 固定服务器角色 。
4,查看 数据库角色成员的信息使用系统存储过程 sp_helprolemember可以查看数据库角色成员的信息 。 sp_helprolemember的语法格式如下：
sp_helprolemember [ [ @rolename = ] 'role' ]
其中 role参数是当前数据库中某个角色的名称,如省略,表示查看所有数据库角色 。
第
11
章安全管理
< >
11.4.4 删除角色及其成员
1,从 SQLServer固定服务器角色中删除成员使 用 系 统 存 储 过 程 sp_dropsrvrolemember 可 以 从 SQL
Server固定服务器角色中删除成员 。 sp_dropsrvrolemember的语法格式如下：
sp_dropsrvrolemember [ @loginame = ] 'login',
[ @rolename = ] 'role?
【 例 11-8】 从服务器角色 sysadmin中删除 Useredu登录 。
EXEC sp_dropsrvrolemember 'Useredu','sysadmin'
2,从 数据库角色中删除成员使用系统存储过程 sp_droprolemember可以从 SQL Server数据库角色中删除成员 。 sp_droprolemember的语法格式如下：
sp_droprolemember [ @rolename = ]
'role',[ @membername = ] 'security_account'
【 例 11-9】 从 eduManagers数据库角色中删除用户 U_edu。
USE Educational
EXEC sp_droprolemember 'eduManagers','U_edu'
第
11
章安全管理
< >
11.4.4 删除角色及其成员
3,删除 数据库角色使用系统存储过程 sp_droprole可以从当前数据库中删除指定的数据库角色 。 sp_droprole的语法格式如下：
sp_droprole [ @rolename = ] 'role?
【 例 11-10】 从 Educational 数据库中删除
eduManagers角色 。
USE Educational
EXEC sp_droprole 'eduManagers'
上 例 在 当 前 数 据 库 Educational 中,删 除 了
eduManagers角色 。
第
11
章安全管理
< >
11.5 权限管理通过创建登录账号和用户账号,用户可以通过登录账号连接数据库服务器并取得对数据库的初步访问权限 （ 因每个用户都是 public角色的成员 ）,但要进行任何涉及更改数据库定义或访问数据的活动,则必须有相应的权限 。 使用权限规定用户可以使用哪些对象,对这些对象可以进行哪些操作,是否具有执行创建数据库对象语句的权限等,因此权限在 SQL Server
中非常重要 。
第
11
章安全管理
< >
11.5.1 权限类型
SQL Server通过权限加强系统的安全性 。 SQL Server中提供了三种类型的权限：默认权限,对象权限和语句权限 。
1,默认权限默认权限是指系统自动授予而不需要授权就有的权限,用于控制那些只能由预定义系统角色的成员或数据库对象所有者能执行的操作,包括固定服务器角色,数据库角色和数据库对象所有者所拥有的权限 。
2,对象权限对象权限就是对数据库对象的访问与操作权限,如用户没有对象权限,则不能访问该对象 。
3,语句权限语句权限用于控制用户创建数据库或数据库中对象的操作权限 。 语句权限仅用于控制语句自身,而不适用于数据库中定义的特定对象 。 对数据库对象的操作权限由对象权限决定 。
第
11
章安全管理
< >
11.5.2 授予权限可以使用 GRANT语句授予用户帐户执行语句和对象的权限 。
1,授予对象权限授予对象权限的 GRANT语句的语法格式如下：
GRANT
{ ALL [ PRIVILEGES ] | permission [,...n ] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]
第
11
章安全管理
< >
【 例 11-11】 授予 U_edu用户对表 Course 执行
SELECT,INSERT和 UPDATE语句的权限,并允许该用户将其权限授予其他用户 。
USE Educational
GRANT SELECT,INSERT,UPDATE
ON Course
TO U_edu
WITH GRANT OPTION /* 将其拥有的权限可以再授予其他用户 */
11.5.2 授予权限
2,授予语句权限授予语句权限的 GRANT语句的语法格式如下：
GRANT { ALL | statement [,...n ] }
TO security_account [,...n ]
其中各参数的含义如下：
l ALL：表示授予所有可用的权限 。 但只有 sysadmin角色成员可以使用 ALL。
l statement：是被授予权限的语句 。 语句列表请参见表 11-3。
【 例 11-12】 授予 U_edu用户具有执行 CREATE TABLE和
CREATE PROCEDURE语句的权限 。
USE Educational
GRANT CREATE TABLE,CREATE PROCEDURE
TO U_edu
第
11
章安全管理
< >
11.5.3 禁止权限在需要时,可能想限制某些用户或角色的权限,这可以使用 DENY语句实现 。 通过 DENY语句可以收回以前授予用户,组或角色的权限；拒绝给当前数据库内的安全帐户授予权限并阻止安全帐户通过其组或角色成员的方式继承权限 。
1,禁止对象权限禁止对象权限的 DENY语句的语法格式如下：
DENY
{ ALL [ PRIVILEGES ] | permission [,..,] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [,...n ]
[ CASCADE ]
第
11
章安全管理
< >
【 例 11-13】 禁止 U_edu用户对表 Course执行 SELECT、
INSERT和 UPDATE语句的权限,并将该用户授予其他用户的权限同时收回 。
USE Educational
DENY SELECT,INSERT,UPDATE
ON Course
TO U_edu
CASCADE /* 将 U_edu用户授予其他用户的权限收回
*/
上例在当前数据库 Educational 中禁止 U_edu 用户对
Course 表的 SELECT,INSERT和 UPDATE语句的权限,并将
U_edu用户授予其他用户的权限收回 。
11.5.3 禁止权限
2,禁止语句权限禁止语句权限的 DENY语句的语法格式如下：
DENY { ALL | statement [,...n ] }
TO security_account [,...n ]
其中参数与 GRANT语句相同,这里不再复述 。
【 例 11-14】 禁止 U_edu 用 户 具 有 执 行 CREATE
TABLE和 CREATE PROCEDURE语句的权限 。
USE Educational
DENY CREATE TABLE,CREATE PROCEDURE
TO U_edu
上例在当前数据库 Educational 中拒绝了 U_edu 用户执行 CREATE TABLE和 CREATE PROCEDURE语句的权限 。
第
11
章安全管理
< >
11.5.4 撤消权限撤消权限是指删除以前曾授予用户或角色的权限,类似于禁止,但与禁止权限不同的是撤消权限并不禁止安全帐户通过其组或角色成员的方式继承权限 。
可以使用 REVOKE语句撤消数据库用户的权限 。
1,撤消对象权限撤消对象权限的 REVOKE语句的语法格式如下：
REVOKE [ GRANT OPTION FOR ]
{ ALL [ PRIVILEGES ] | permission [,...n ] }
{
[ ( column [,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
{ TO | FROM } security_account [,...n ]
[ CASCADE ]
[ AS { group | role } ]
第
11
章安全管理
< >
【 例 11-15】 撤消 U_edu用户对表 Course执行 SELECT、
INSERT和 UPDATE语句的权限,并删除该用户授予其他用户权限的能力 。
USE Educational
REVOKE GRANT OPTION FOR /* 删除 U_edu用户授予其他用户权限的能力 */
SELECT,INSERT,UPDATE
ON Course
TO U_edu
上例在当前数据库 Educational 中撤消 U_edu 用户对
Course 表的 SELECT,INSERT和 UPDATE语句的权限,并删除 U_edu用户授予其他用户权限的能力 。
11.5.4 撤消权限
2,撤消语句权限撤消语句权限的 REVOKE语句,其语法格式如下：
REVOKE { ALL | statement [,...n ] }
FROM security_account [,...n ]
其中参数与 GRANT语句相同,这里不再复述 。
【 例 11-16】 撤消 U_edu 用 户 具 有 执 行 CREATE
TABLE和 CREATE PROCEDURE语句的权限 。
USE Educational
REVOKE CREATE TABLE,CREATE PROCEDURE
FROM U_edu
上例在当前数据库 Educational 中撤消了 U_edu用户执行 CREATE TABLE和 CREATE PROCEDURE语句的权限 。
第
11
章安全管理
< >
11.5.5 使用企业管理器进行权限的授予、
禁止和撤消
1） 启动企业管理器,展开相应的服务器组和服务器节点 。
2） 展开数据库节点,并展开要授予权限的数据库 。
3） 右击数据库,在弹出的快捷菜单中执行,属性,命令
(如图 11-4所示 ),出现,数据库属性,对话框 。 单击,权限,
选项卡,授予语句权限,如图 11-6所示 。
4） 如要授予用户对某一语句的权限,可以在对应的方框上单击,直至出现一个绿色的,√,；如要禁止用户对某一语句的权限,可以在对应的方框上单击,直至出现一个红色的
,×,；如要撤消用户对某一语句的权限,可以在对应的方框上单击,直至出现一个空白的方块,□,。
5） 单击,确定,按钮,完成语句权限的授予,禁止和撤消工作,返回前一界面 。
6） 单击,用户,项 (如图 11-4所示 ),在右边的,用户项目,
列表框中列出了数据库中已创建的所有用户账号 。 右击需授予对象权限的用户,在弹出的快捷菜单中执行,属性,菜单项,
出现,数据库用户属性,对话框的常规选项页 。
第
11
章安全管理
< >
11.5.5 使用企业管理器进行权限的授予、
禁止和撤消
7） 在,数据库用户属性,对话框的常规选项页上单击,权限,按钮,出现如图 11-7所示的,数据库用户属性,对话框的权限选项页 。
8） 在,数据库用户,组合框中选择要授予对象权限的用户 。 下面的表格中将显示该用户所拥有的权限情况 。
9） 如要授予该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个绿色的,√,；如要禁止该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个红色的,×,；如要撤消该用户对某一对象的权限,可以在对应的方框上单击,直至出现一个空白的方块,□,。
10） 单击,确定,按钮,完成权限的授予,禁止和撤消工作 。
第
11
章安全管理
< >
11.5.6 查看权限信息为了系统的安全,管理员随时需要查看并掌握用户在数据库中所拥有的权限,然后根据实际需要决定是否增加或删除该用户的权限 。 使用系统存储过程 sp_helprotect可以查看当前数据库中某对象的权限或语句权限的信息 。
sp_helprotect的语法格式如下：
sp_helprotect [ [ @name = ] 'object_statement' ]
[,[ @username = ] 'security_account' ]
[,[ @grantorname = ] 'grantor' ]
[,[ @permissionarea = ] 'type' ]
l object_statement：是当前数据库中要查看其权限的对象或语句的名称 。
l security_account：要查看权限的安全帐户名称 。
l grantor：已授权的安全帐户的名称 。
l type：表示是显示对象权限 （ 字符串 o）,语句权限
（ 字符串 s） 还是两者都显示 (o s)。
第
11
章安全管理
< >
