1
第八章 SQL Server权限管理
? SQLServer的身份验证
? 创建和管理用户登录的方法
? 固定服务器角色和服务器角色
? 管理数据库权限的方法
2
SQL Server 2000 安全模式
? SQL Server 2000 的安全性管理是建立在认证
(authentication) 和访问许可 (permission) 两者
机制上的 。 认证是指来确定登录 SQL Server 的用
户的登录账号和密码是否正确, 以此来验证其是
否具有连接 SQL Server 的权限 。 但是通过认证阶
段并不代表能够访问 SQL Server 中的数据, 用户
只有在获取访问数据库的权限之后, 才能够对服
务器上的数据库进行权限许可下的各种操作 (主要
是针对数据库对象如表, 视图, 存储过程等 ),这
种用户访问数据库权限的设置是通过用户账号来
实现的 。 同时在 SQL Server 中, 角色作为用户组
的代替物大大地简化了安全性管理 。 所以在 SQL
Server 的安全模型中包括以下几部分, SQL
Server 登录, 数据库用户, 权限, 角色
3
SQL Server 2000 登录认证
? 安全帐户认证是用来确认登录 SQL Server
的用户的登录帐号和密码的正确性,由此
来验证其是否具有连接 SQL Server的权限。
SQL Server 2000提供了两种确认用户的
认证模式,
? Windows 身份验证模式
? 混合模式
4
Windows 身份验证
? SQL Server数据库系统通常运行在 Windows
NT/2000服务器平台上, 而 NT作为网络操作系
统, 本身就具备管理登录, 验证用户合法性的
能力, 因此 Windows 身份验证模式正是利用了
这一用户安全性和帐号管理的机制, 允许 SQL
Server也可以使用 NT/2000的用户名和口令 。
在这种模式下, 用户只需要通过 Windows
NT/2000的认证, 就可以连接到 SQL Server,
而 SQL Server本身也就不需要管理一套登录数
据 。
? 当 SQL Server实例在 windows98/Me版上运行
时, windows身份验证模式不可用 。
5
SQL Server身份验证
? 在该认证模式下, 用户在连接 SQL Server 时
必须提供登录名和登录密码 。 这些登录信息存
储在系统表 syslogins 中, 与 NT 的登录账号
无关, SQL Server 自己执行认证处理 。 如果
输入的登录信息与系统表 syslogins 中的某条
记录相匹配, 则表明登录成功 。
? 提供 SQL Server身份验证是为了向后兼容性,
同时也因为 windows98上不支持 windows身份验
证模式。
6
Windows身份验证的优点
? WINDOWS 认证模式比起 SQL Server 认证模式来有许多优点,
原因在于 WNDOWS认证模式集成了 NT 或 WINDOWS 2000 的安
全系统, 并且 NT 安全管理具有众多特征, 如安全合法性,
口令加密, 对密码最小长度进行限制等 。 所以当用户试图
登录到 SQL Server 时, 它从 NT 或 WNDOWS 2000 的网络安
全属性中获取登录用户的账号与密码, 并使用 NT 或
WINDWOS 2000 验证账号和密码的机制来检验登录的合法性,
从而提高了 SQL Server 的安全性 。
? 在 WINDOWS NT 中使用了用户组, 所以当使用 WINDOWS 认证
时, 我们总是把用户归入一定的 NT 用户组, 以便当在 SQL
Server 中对 NT 用户组进行数据库访问权限设置时, 能够
把这种权限设置传递给单一用户 。 而且当新增加一个登录
用户时, 也总把它归入某一 NT 用户组 。 这种方法可以使用
户更为方便地加入到系统中, 并消除了逐一为每一个用户
进行数据库访问权限设置而带来的不必要的工作量 。
7
混合认证模式
? 在混合认证模式下, WINDOWS 认证和
SQL Server 认证这两种认证模式都是可
用的 。 NT 的用户既可以使用 NT 认证,
也可以使用 SQL Server 认证 。
8
选择身份验证模式
? 基于 windows和 SQL Server有两种身份验证模
式可选,windows身份验证模式和混合模式。
? 若选择 windows身份验证模式,则用户只能使
用 windows身份验证联接到 SQL Server ; 若选
择混合模式,则用户既可以 windows身份验证
连接到 SQLServer,又可以使用 SQL Server身
份验证连接到 SQL Server。
9
利用企业管理器选择身份验证模式
? 其主要过程如下,
? 1,打开企业管理器, 选择服务器组中希望
设置身份验证模式的服务器 。。
? 2,在该服务器上单击右键, 从快捷菜单中
选择, 属性,选项, 打开 SQL Server属性对话
框 。
? 3,在对话框中选择, 安全性, 选项 。
? 3,在身份验证区域中可以选择要设置的认
证模式:混合身份验证模式和仅 windows。
? 同时可以选择审核级别中的一个,
10
? 无:表示禁用审核 ? 成功:表示只审核成功登录尝试。
? 失败:表示只审核失败登录尝试。
? 全部:表示既审核成功登录尝试又审核
失败登录尝试。
? 5、单击“确定”按钮,则完成了身份验
证模式的选择和设置。
11
创建和管理用户登录
? 通过认证阶段并不代表用户能够访问
SQL Server中的数据,同时他还必须通
过许可确认。用户只有在具有访问数据
库的权限之后,才能够对服务器上的数
据库进行权限许可下的各种操作,这种
用户访问数据库权限的设置是通过用户
帐号来实现的。
12
服务器登录帐号和用户帐号管理
? 1,SQL Server服务器登录管理
? SQL Server有三个默认的用户登录帐号:
即 sa,administrators\builtin和 guest。
? 2,用户帐号管理
13
1.利用企业管理器创建、管理 SQL
Server登录帐号
? 其具体执行步骤如下,
? (1)打开企业管理器,单击需要登录的服务
器左边的,+”号,然后展开安全性文件夹。
? ( 2 ) 用右键单击, 登录,, 从快捷菜单中选
择新建登录 ( new login) 选项, 则出现 SQL
Server登录属性 — 新建登录对话框, 如图 1所
示 。
? ( 3)在名称编辑框中输入登录名,在身份验
证选项栏中选择新建的用户帐号是 Windows
NT认证模式,还是 SQL Server认证模式。
14
? (4) 选择服务器角色标签,图 2所示,在服
务器角色列表框中,列出了系统的固定服务器
角色。在该选项内选择将分配给登录帐户的固
定服务器角色。
? (5)选择数据库访问标签,如图 3所示。上
面的列表框列出了该帐号可以访问的数据库,
单击数据库左边的复选框,表示该用户可以访
问相应的数据库以及该帐号在数据库中的用户
名。 下面是该用户在数据库中的角色。
? (6)设置完成后,单击“确定”按钮即可完
成登录帐号的创建。
15
图 1 新建登录帐号对话框
16
图 2 服务器角色对话框
17
图 3 数据库访问对话框
18
使用 SQL Server的创建登录向
导工具创建登录帐号。
? 其具体操作过程如下,
? 图 4 --图 10
19
图 4 欢迎使用创建登录向导对话框
20
图 5 选择身份验证模式对话框
21
图 6 选择对用户帐号的安全性设置对话框
22
图 7 输入登录信息对话框
23
图 8 选择安全性角色对话框
24
图 9 选择允许登录帐号访问的数据库对话框
25
图 10 完成创建登录向导对话框
26
服务器角色
? 服务器角色是指根据 SQL Server的管理任务,
以及这些任务相对的重要性等级来把具有 SQL
Server管理职能的用户划分为不同的用户组,
每一组所具有的管理 SQL Server的权限都是
SQL Server内置的,即不能对其进行添加、修
改和删除,只能向其中加入用户或者其他角色。
27
七种常用的固定服务器角色
系统管理员:拥有 SQL Server所有的权限许可 。
服务器管理员:管理 SQL Server服务器端的设置 。
磁盘管理员:管理磁盘文件 。
进程管理员:管理 SQL Server系统进程 。
安全管理员:管理和审核 SQL Server系统登录 。
安装管理员:增加, 删除连接服务器, 建立数据库复制
以及管理扩展存储过程 。
数据库创建者:创建数据库, 并对数据库进行修改 。
28
数据库角色
? 数据库角色是为某一用户或某一组用户授予不
同级别的管理或访问数据库以及数据库对象的
权限,这些权限是数据库专有的,并且还可以
使一个用户具有属于同一数据库的多个角色。
SQL Server提供了两种类型的数据库角色:即
固定的数据库角色和用户自定义的数据库角色。
29
固定的数据库角色
public,维护全部默认许可 。
db_owner,数据库的所有者, 可以对所拥有的
数据库执行任何操作 。
db_accessadmin,可以增加或者删除数据库用
户, 工作组和角色 。 (使用 sp_grantdbaccess存
储过程 )
db_ddladmin,可以增加, 删除和修改数据库
中的任何对象 。 (使用 creat,alter和 drop语句 )
db_securityadmin,执行语句许可和对象许可 。
(使用 grant,revoke和 deny语句 )
db_backupoperator,可以备份和恢复数据库 。
(使用 DBCC,CheckPoint和 Backup语句 )
30
db_datareader,能且仅能对数据库中的任何表
执行 select操作, 从而读取所有表的信息 。 (有
Select权限 )
db_datawriter,能够增加, 修改和删除表中的
数据, 但不能进行 select操作 。 (可以对表或视
图使用 insert,update和 delete语句 )
db_denydatareader,不能读取数据库中任何表
中的数据 。
db_denydatawriter,不能对数据库中的任何表
执行增加, 修改和删除数据操作 。
31
? 可以使用 sp_helpdbfixedrole系统存储命令 查
看这 9种固定数据库角色。
? Public数据库角色:是 Sql Server中一个特殊的
数据库角色。它有如下特点,
? 捕获数据库中用户的所有默认权限。
? 无法将用户、组或角色指派给它,因为默认情
况下它们就属于该角色。
? 含在每个数据库中,包括 master,msdb、
tempdb,model以及所有用户数据库中
? 无法删除。
32
使用企业管理器管理用户登录
? 可以在企业管理器中, 为数据库角色添加 /删除一个或多个
登录 。 步骤,
? 1,在企业管理器中, 展开指定的服务器以及指定的数据库 。
? 2,展开该数据库节点的下一级节点, 角色,
? 3,在需要添加登录的数据库角色上单击鼠标右键, 然后从
弹出菜单中选择命令, 属性, 。 此时弹出, 数据库角色属性,
对话框, 在该对话框中显示了该数据库角色所包含的所有登
录帐号 。
? 4,若希望将一个登录从该数据库角色中删除, 选中该登录
帐号, 按, 删除, 按钮即可 。 若希望添加, 则按, 添加, 按
钮 。
? 5,单击, 权限, 按钮可以从弹出的窗口中查看该数据库角
色的所有权限 。 若该数据库角色是自定义的数据库角色, 还
可对这些权限进行修改 。
? 6,单击, 确定, 按钮, 结束 。
33
使用系统存储过程管理登录帐户
? sp_addrolemember,向数据库角色中添加一个安
全帐户。语法为,
sp_addrolemember 角色,安全帐户
? sp_droprolemember,从数据库角色中删除一个
安全帐户。语法为,
? sp_droprolemember 角色,安全帐户
? 注意:只有 sysadmin固定服务器角色,db_owner
和 db_secruityadmin固定数据库角色的成员才能
执行 sp_droprolemember。
? 只有 db_owner固定数据库角色的成员才可以从固
定数据库角色中删除用户。
34
? 例:在数据库角色 db_ddladmin中添加一个安
全帐户 Headquaters/adam,
? Use company
? Exec sp_addrolemember
‘db_ddladmin’,’Headquaters\adam’
? 例:删除刚加入到服务器角色 db_ddladmin中
的成员 Headquaters/adam
? Exec sp_dropaddrolemember
‘db_ddladmin’,’Headquaters\adam’
35
使用系统存储过程管理服务器角色
? 在 SQL Server中,管理服务器角色的存储
过程主要有两个,
? sp_addsrvrolemember,在一个服务器角色
中添加一个登录
? sp_dropsrvrolemember,从一个服务器角
色中取消一个登录
36
? 系统存储过程 sp_addsrvrolemember可以将某
一登录帐号加入到服务器角色中, 使其成为该
服务器角色的成员 。 其语法形式如下,
? sp_addsrvrolemember ‘登录帐户名 ’,
‘ 角色 ’
? 注意,sysadmin固定服务器的成员可以将成
员添加到任何固定服务器角色。
? 在将登录添加到固定服务器角色时,该登录
就会得到愈此固定服务器角色相关的权限。
? 不能更改 sa登录的角色成员资格。
37
? 系统存储过程 sp_dropsrvrolemember可以
将某一登录者从某一服务器角色中删除,
当该成员从服务器角色中被删除后, 便不
再具有该服务器角色所设置的权限 。 其语
法形式如下,
? sp_dropsrvrolemember ‘ 登录帐户
名 ’, ‘ 角色 ’
38
? 系统存储过程 sp_dropsrvrolemember可以将某一登录
者从某一服务器角色中删除, 当该成员从服务器角色
中被删除后, 便不再具有该服务器角色所设置的权限 。
其语法形式如下,
? sp_dropsrvrolemember ‘登录帐户名 ’, ‘ 角色 ’
? 注意:只有 sysadmin才可以执行该存储过程, 从 固
定服务器角色中删除任意登录 。 一个固定服务器角
色的成员可以删除相同固定服务器角色中的其它成
员 。
? 如果某些权限与某个服务器角色相关,那么从该服
务器角色中删除一个登录后,给登录就不能再执行
基于这些权限的活动了。
? 不能从任何固定服务器中删除 sa登录。
39
? 例:再服务器角色 serveradmin中添加一个登录
adam
? Exec sp_addsrvrolememver
‘adam’,’serveradmin’
? 例:删除刚加入的成员。
? Exec sp_dropsrvrolememver
‘adam’,’serveradmin’
? 可以使用系统存储过程 sp_srvrolepermisiion查
看固定服务器角色所拥有的权限,语法为,
? sp_srvrolepermission 固定服务器角色
? 例,sp_srvrolepermission serveradmin
40
创建数据库角色
? 创建用户定义的数据库角色就是创建一组用户,
这些用户具有相同的一组许可 。 如果一组用户
需要执行在 SQL Server中指定的一组操作并且
不存在对应的 Windows NT组, 或者没有管理
Windows NT用户帐号的许可, 就可以在数据
库中建立一个用户自定义的数据库角色 。 用户
自定义的数据库角色有两种类型:即标准角色
和应用程序角色 。
41
? 标准角色通过对用户权限等级的认定而将用
户划分为不用的用户组, 使用户总是相对于一个
或多个角色, 从而实现管理的安全性 。
? 应用程序角色是一种比较特殊的角色 。 当我
们打算让某些用户只能通过特定的应用程序间接
地存取数据库中的数据而不是直接地存取数据库
数据时, 就应该考虑使用应用程序角色 。 当某一
用户使用了应用程序角色时, 他便放弃了已被赋
予的所有数据库专有权限, 他所拥有的只是应用
程序角色被设置的角色 。
标准角色和应用程序角色
42
使用企业管理器管理数据库角色
? 在企业管理器中可以实现数据库角色的创建、授权
和删除等操作。步骤如下,
? 1、打开企业管理器,选中指定的数据库
? 2、选中该数据库节点的下一级节点“角色”,单
击鼠标右键,选择“新建数据库角色”
? 3、在对话框“数据库角色属性-新建角色”中作
如下操作:输入名称、选择角色类型(标准角色,
应用程序角色)、若选中的是标准角色,还可以单
击“添加”按钮向该角色中添加用户。
? 4、单击“确定”即可。
43
? 若要删除一个数据库角色,在企业管理
器中选择该角色,然后右击鼠标并选择
“删除”即可。
? 注意:不能删除固定数据库角色。
44
使用系统存储过程管理数据库角色
? sp_addrole,用来创建一个新的数据库角色
? sp_addrole ‘role’[,’owner’]
? 其中,role是新的数据库角色的名称。 Owner是新
角色的所有者,默认值为 dbo。 Owner必须是当前
数据库中的某个用户或角色。
? 例:创建一个数据库角色 customers
? Use company
? Go
? Exec sp_addrole ‘customers’
? 在添加角色之后,可以使用 sp_addrolemember添
加安全帐户,使其称为该角色的成员。
45
? sp_droprole,用于删除一个数据库角色
? sp_droprole role
? 例:删除刚建立的 customers数据库角色
? Exec sp_droprole ‘customers’
? 注意:只能删除用户自己创建的数据库
角色,不能删除 sql server中的固定数
据库角色。
46
权限
? 权限用来指定授权用户可以使用的数据库对象
和这些授权用户可以对这些数据库对象执行的
操作。用户在登录到 SQL Server之后,其用户
帐号所归属的 NT组或角色所被赋予的许可
(权限)决定了该用户能够对哪些数据库对象
执行哪种操作以及能够访问、修改哪些数据。
在每个数据库中用户的权限独立于用户帐号和
用户在数据库中的角色,每个数据库都有自己
独立的权限系统,在 SQL Server中包括三种类
型的许可:即对象权限、语句权限和暗示性权
限。
47
三种权限类型
? 1,对象权限
? 表示对特定的数据库对象,即表、视图、字段
和存储过程的操作许可,它决定了能对表、视图等
数据库对象执行哪些操作。数据库对象的所有者可
以将对象权限授予指定的数据库用户。
? 对于表和视图,所有者可以授予 Insert,Update、
Select和 References权限,或授予 All权限。用户必
须对表有 Insert,Update,Select,Delete权限,才
能在 Insert,Update,Select,Delete语句中指定该
表。
? References权限使得另一个表的所有者可以对你
的表中的列应用他们表中的 Reference Foreign Key
约束。
48
? 2、语句权限:对用于创建数据库和数据库中的
对象(如表、视图和存储过程等)的 T-SQL语
句所拥有的权限。语句权限包括,Create
Database,Create Table,Create View、
Create Rule,Create Procedure,Create
Function,Create Default,Backup Database、
Backup Log
? 3、暗示性权限:指有 Sql Server预定义的服务
器角色、数据库的所有者和数据库对象所有者
所拥有的权限。如:数据库拥有者 dbo可以在
数据库范围内进行任何操作,数据库对象的所
有者可以对所拥有的对象执行一切活动。
? 暗示性权限不需要明确指定,也不能进行设置。
49
? 管理对象权限某些数据库中的用户根据
它们在数据库中的角色被设定了某些缺
省权限。这样的用户有 4类。
? 一、系统管理员:可以创建和删除数据
库,配置服务器。
? 二、数据库属主:可以创建和管理数据
库中的对象以及管理整个数据库。
? 三、对象属主:可以在对象上进行授予、
废除和拒绝权限的操作,而且可以删除
对象。
? 四、除此以外的都属于第四类。
50
权限管理
? 对象权限可以由 sysadmin服务器角色、
数据库所有者或特定数据库角色中的成
员进行授予、拒绝和取消。管理对象权
限有两种方法,
? 企业管理器
? T-SQL
51
使用企业管理器管理对象权限
? 步骤,
? 1、选择数据库节点
? 2、选中需要查看或修改权限的表,单击鼠标
右键,选择菜单命令“属性”,打开“表属性”
对话框。
? 3、在“表属性”对话框中单击“权限”按钮。
? 4、若希望修改对某个数据库对象的访问权,
可以单击相应的方格,有如下三种形式,
52
? V授予权限:表示允许某个用户或角色对
一个对象执行某种操作。
? X禁止权限:表示在不撤销用户访问权限
的情况下,禁止某个用户或角色对一个
对象执行某种操作。
? 空 剥夺权限:表示不允许某个用户或角
色对一个对象执行某种操作。
53
? 5,还可以单击一个特定的用户或角色,
然后单击“列”按钮,打开“列权限”
对话框,将权限控制到字段的级别。
? 6、单击“确定”按钮,结束设置。
54
使用 T-SQL语句管理对象权限
? 可以使用 Grant,deny,Revoke语句完
成权限的授予、禁止和剥夺。语法如下,
? grant 权限名称 [,… n] on 表名 |视图名 |存
储过程名 to 用户帐户
? deny 权限名称 [,… n] on 表名 |视图名 |存
储过程名 to 用户帐户
? revoke 权限名称 [,… n] on 表名 |视图名 |
存储过程名 from 用户帐户
55
? 例:将表 employee的 select权限授予给一个 SQL
Server登录 adam,一个用户自定义的数据库角
色 salesmanagers和一个 windows组
headquaters\mims
? Grant select on employee to
adam,salesmanager,[headquarters\mims]
? 可以在 grant语句中使用 with grant option子句
在授予权限的同时,将授予权限的权限授予该
用户、角色或组。
? 例,grant select on employee to salesmanager
with grant option
可以使用系统存储过程 sp_helprotect查看一个数
据库中现有的对象权限。
例,use company
Exec sp_helprotect ‘employee’
56
管理语句权限
? 可以管理的方式,
? 企业管理器
? T-SQL语句
57
使用企业管理器管理语句权限
? 步骤,
? 1、选择指定的数据库节点
? 2、选中要查看或修改权限的数据库,单
击鼠标右键,选择菜单命令“属性”,
打开“数据库属性”对话框。
? 3、在该对话框中选择“权限”标签,可
以设置相应的语句权限。
58
使用 T-SQL语句管理语句权限
? 可以使用 Grant,deny,Revoke语句完成语
句权限的授予、禁止和剥夺。语法如下,
? grant 语句名称 [,… n] to 用户帐户 [,… n]
? deny语句名称 [,… n] to 用户帐户 [,… n]
? revoke语句名称 [,… n] from 用户帐户 [,… n]
? 注意:管理语句权限只能在 master系统数
据库中进行。
59
? 例:将 Create Table 的权限授予给一个
SQL Server登录 adam
? Grant create table to adam
? 例:拒绝用户帐号 adam使用 create view
语句。
? Deny create view to adam