SQL-Server的安全管理.ppt

2020 3 26 SQLServer2005 1 SQLServer数据库应用技术第10章SQLServer安全管理 第10章SQLServer的安全管理 数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露 更改或破坏 系统安全保护措施是否有效是数据库系统的主要指标之一 数据库安全性和计算机系统安全性 包括操作系统 网络系统的安全性 是紧密联系 相互支持的 主要内容 SQLServer2005安全性概述 SQLServer2005的验证模式 登录管理 用户管理 角色管理 权限管理及系统加密机制等 目录 10 1SQLServer2005安全性概述10 2SQLServer2005的验证模式10 3登录管理10 4用户管理10 5角色管理10 6权限管理10 7加密机制练习题10上机实习9 10 1SQLServer2005安全性概述 返回本节首页 SQLServer2005安全系统的构架建立在用户和用户组的基础上 Windows中的用户和本地组及全局组可以映射到SQLServer2005中的安全登录帐户 也可以创建独立Windows帐户的安全登录帐户 SQLServer提供了2种安全管理模式 Windows身份验证模式混合身份验证模式 10 1SQLServer2005安全性概述 返回本节首页 1 两个安全性阶段在SQLServer2005中工作时 用户要经过两个安全性阶段 身份验证 每个用户必须通过登录帐户建立自己的连接能力 身份验证 以获得对SQLServer2005实例的访问权限 权限验证 授权 该登录必须映射到用于控制在数据库中所执行的活动 权限验证 的SQLServer用户帐户 如果数据库中没有用户帐户 则即使用户能够连接到SQLServer实例 也无法访问该数据库 10 1SQLServer2005安全性概述 返回本节首页 2 用户权限当用户连接到SQLServer2005实例后 他们可以执行的活动由授予以下帐户的权限确定 用户安全帐户 用户安全帐户所属Windows组或角色层次结构 用户若要进行任何涉及更改数据库定义或访问数据的活动 则必须有相应的权限 管理权限包括授予或废除执行以下活动的用户权限 处理数据和执行过程 对象权限 创建数据库或数据库中的项目 语句权限 利用授予预定义角色的权限 暗示性权限 10 1SQLServer2005安全性概述 返回本节首页 3 视图安全机制视图作为安全机制 SQLServer2005通过限制可由用户使用的数据 数据子集 用户可以访问某些数据 进行查询和修改 但是表或数据库的其余部分是不可见的 也不能进行访问 视图授权 对SQLServer2005来说 无论在基础表 一个或多个 上的权限集合有多大 都必须授予 拒绝或废除访问视图中数据子集的权限 10 1SQLServer2005安全性概述 返回本节首页 4 加密方法SQLServer2005支持加密或可以加密的内容为 SQLServer中存储的登录和应用程序角色密码 作为网络数据包而在客户端和服务器端之间发送的数据 SQLServer2005中如下对象的定义内容 存储过程 用户定义函数 视图 触发器 默认值 规则等 例如 SQLServer2005可使用安全套接字层 SSL 加密在应用程序计算机和数据库计算机上的SQLServer实例之间传输的所有数据 10 1SQLServer2005安全性概述 返回本节首页 5 审核活动SQLServer2005提供审核功能 用以跟踪和记录每个SQLServer实例上已发生的活动 如成功和失败的记录 SQLServer2005还提供管理审核记录的接口 即SQL事件探查器 只有sysadmin固定安全角色的成员才能启用或修改审核 而且审核的每次修改都是可审核的事件 10 2SQLServer2005的验证模式 返回本节首页 10 2 1Windows身份验证模式10 2 2混合身份验证模式 也称SQLServer身份验证模式 10 2 3设置验证模式10 2 4SQLServer系统登录验证过程 10 2SQLServer2005的验证模式 返回本节首页 SQLServer2005有两种安全验证模式 Windows身份验证模式混合身份验证模式 也称SQLServer身份验证模式 返回本节首页 图10 2SQLServer系统登录验证过程 10 2 3设置验证模式 返回本节首页 Windows身份验证模式是指用户通过Windows用户帐户连接到SQLServer 即用户身份由Windows系统来验证 默认的身份验证模式 比混合验证模式安全得多 Windows身份验证使用Kerberos安全协议 通过强密码的复杂性验证提供密码策略强制 提供帐户锁定支持 并且支持密码过期 用户和组是由Windows维护的 因此当用户进行连接时 SQLServer将读取有关该用户在组中的成员资格信息 10 2 1Windows身份验证模式 返回本节首页 使用Windows身份验证有如下特点 Windows验证模式下由Windows管理登录帐户 数据库管理员主要是使用该帐户 Windows有功能很强的工具与技术去管理用户的登录帐户 可以在SQLServer中增加用户组 可以使用用户组 SQLServer支持的登录帐户 能在系统表syslogins或目录视图sys syslogins中找到 10 2 1Windows身份验证模式 返回本节首页 混合身份验证模式允许用户使用Windows身份和SQLServer身份进行连接 通过Windows登录帐户连接的用户可使用Windows验证的受信任连接 当用户使用指定登录名称和密码进行非信任连接时 SQLServer检测输入的登录名和密码是否与系统Syslogins表记录的情况相同 据此进行身份验证 如果不存在该用户的登录帐户 则身份验证失败 提供SQLServer身份验证是为了考虑非Windows客户兼容及向后兼容 早期SQLServer的应用程序可能要求使用SQLServer登录和密码 10 2 2混合身份验证模式 也称SQLServer身份验证模式 返回本节首页 混合身份验证模式有如下特点 1 混合模式允许非Windows客户 Internet客户和混合的客户组连接到SQLServer中 2 增加了完全性方面的选择 如果必须选择 混合身份验证模式 并要求使用SQL登录以适应旧式应用程序 则必须为所有SQL帐户设置强密码 这对于属于sysadmin角色的帐户 特别是sa帐户 尤其重要 10 2 2混合身份验证模式 也称SQLServer身份验证模式 返回本节首页 在ManagementStudio中的两种设置方法 方法之一 1 打开ManagementStudio 2 在 视图 已注册的服务器 子窗口中要设置验证模式的服务器上按鼠标右键 然后在弹出的快捷菜单上选择 属性 系统弹出如图10 1所示的 编辑服务器注册属性 窗口 10 2 3设置验证模式 返回本节首页 图10 1服务器注册属性 10 2 3设置验证模式 返回本节首页 方法之二 1 在ManagementStudio对象资源管理器中 右键单击服务器 再单击 属性 2 在 安全性 页上的 服务器身份验证 下 选择新的服务器身份验证模式 再单击 确定 以上两种方法 均需要重新启动SQLServer后 才能生效 10 2 3设置验证模式 10 2 3设置验证模式 10 3登录管理 返回本节首页 10 3 1系统管理员登录帐户10 3 2使用ManagementStudio管理SQLServer登录帐户10 3 3用T SQL管理SQLServer登录帐户10 3 4管理登录的最新T SQL命令 10 3登录管理 返回本节首页 登录是基于服务器级使用的用户名称 在SQLServer中要成为数据库的用户 必须先成为服务器的登录 一个登录可以成为多个数据库的用户 在Windows验证模式下 可以在Windows全局组或域用户中创建登录 在SQLServer验证模式下 还可以在Windows的非全局组 非域用户甚至非Windows用户中创建登录 返回本节首页 SQLServer有两个默认的系统管理员登录帐户 sa和BUILTIN Administrators sa是一个特殊的登录名 是SQLServer身份验证机制下SQLServer的系统管理员 sa始终关联dbo数据库用户 并且没有为sa帐号指定口令 应尽快地给系统管理员帐户指定口令 BUILTIN Administrators是Windows系统的系统管理员组 10 3 1系统管理员登录帐户 返回本节首页 在ManagementStudio中能方便地创建 查看 修改 删除登录帐户 映射Windows登录帐户为SQLServer登录帐户在ManagementStudio中可以将一个Windows帐户或一个组映射成SQLServer登录帐户具体操作 选择 服务器 安全性 登录名 新建登录名 10 3 2使用ManagementStudio管理SQLServer登录帐户 10 3 2使用ManagementStudio管理SQLServer登录帐户 在ManagementStudio中能方便地创建 查看 修改 删除登录帐户 2 创建SQLServer登录帐户 10 3 2使用ManagementStudio管理SQLServer登录帐户 在ManagementStudio中可以将一个Windows帐户或一个组映射成SQLServer登录帐户具体操作 选择 服务器 安全性 登录名 新建登录名 10 3 2使用ManagementStudio管理SQLServer登录帐户 返回本节首页 3 在ManagementStudio中查看 修改或删除登录帐户方法如下 启动ManagementStudio 分别展开 服务器 安全性 登录名 单击 登录名 下的某一个登录帐户 在系统弹出菜单上点击 属性 可进入 登录属性 对话框查看该登录帐户的信息 sp helplogins系统存储过程也能提供有关每个数据库中的登录及相关用户的信息 sp helplogins LoginNamePattern login 10 3 2使用ManagementStudio管理SQLServer登录帐户 返回本节首页 1 映射Windows登录帐户为SQLServer登录帐户映射Windows登录帐户为SQLServer登录帐户的相关系统存储过程和命令有 sp grantlogin CREATELOGINsp denylogin ALTERLOGINsp revokelogin DROPLOGIN 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 1 sp grantlogin 创建SQLServer登录名 其语法 sp grantlogin loginame login 例如为Windows用户Corporate BobJ 创建SQLServer登录名 命令为 sp grantlogin Corporate BobJ 改用CREATELOGIN首选实现方法命令为 CREATELOGIN Corporate BobJ FROMWINDOWS 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 2 sp denylogin 禁止Windows用户或组连接到SQLServer实例 其语法 sp denylogin loginame login 例10 1本例禁用Windows用户Corporate BobJ的SQLServer登录名 命令为 sp denylogin Corporate BobJ 改用ALTERLOGIN首选实现方法命令为 ALTERLOGIN Corporate GeorgeW DISABLE 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 3 sp revokelogin 从SQLServer中删除使用CREATELOGIN sp grantlogin或sp denylogin为Windows用户或组创建的登录项 其语法 sp revokelogin loginame login 10 2本例删除Windows用户Corporate BobJ的登录项 EXECsp revokelogin Corporate BobJ 或EXECsp revokelogin Corporate BobJ 改用DROPLOGIN首选实现方法命令为 droplogin Corporate BobJ 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 2 管理SQLServer登录帐户管理SQLServer登录帐户的相关系统存储过程和命令有 sp addlogin CREATELOGINsp droplogin sp revokelogin DROPLOGIN 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 1 sp addlogin 创建新的SQLServer登录 该登录允许用户使用SQLServer身份验证连接到SQLServer实例 其语法为 sp addlogin loginame login passwd password defdb database deflanguage language sid sid encryptopt encryption option 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 例10 3本例为用户Michael创建SQLServer登录 密码为B548bmM f6 默认数据库为AdventureWorks 默认语言为us english SID为0 x0123456789ABCDEF0123456789ABCDEF EXECsp addlogin Michael B548bmM f6 AdventureWorks us english 0 x0123456789ABCDEF0123456789ABCDEF改用CREATELOGIN首选实现方法命令为 CREATELOGINMichaelWITHPASSWORD B548bmM f6 DEFAULT DATABASE AdventureWorks SID 0 x0123456789ABCDEF0123456789ABCDEF DEFAULT LANGUAGE us english 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 2 sp droplogin 删除SQLServer登录 禁止以该登录名访问SQLServer实例 其语法 sp droplogin loginame login 例10 4本例从SQLServer实例中删除Michael登录 sp droplogin Michael 改用DROPLOGIN首选实现方法命令为 droplogin Michael 10 3 3用T SQL管理SQLServer登录帐户 返回本节首页 管理登录的最新T SQL命令为 CREATELOGIN ALTERLOGIN DROPLOGIN 1 CREATELOGIN创建新的SQLServer登录名 语法为 CREATELOGINlogin name WITH FROM WINDOWS WITH CERTIFICATEcertname ASYMMETRICKEYasym key name 10 3 4管理登录的最新T SQL命令 返回本节首页 例10 5创建映射到凭据的登录名 将创建RLaszlo登录名 此登录名将映射到LaszloR凭据 CREATELOGINRLaszloWITHPASSWORD 978 Mmz0KWnkdo26985 CREDENTIAL LaszloR例10 6从证书创建登录名 将从master中的证书创建FOgisu登录名 USEMASTER CREATECERTIFICATEFOgisuCert09WITHSUBJECT FOgisucertificateinmasterdatabase EXPIRY DATE 02 02 2009 GOCREATELOGINFOgisuFROMCERTIFICATEFOgisuCert09 例10 7从Windows域帐户创建登录名 将从Windows域帐户创建 ADVWORKS fogisu 登录名 CREATELOGIN ADVWORKS fogisu FROMWINDOWS 10 3 4管理登录的最新T SQL命令 返回本节首页 2 ALTERLOGIN更改SQLServer登录帐户的属性 语法 ALTERLOGINlogin name WITH 10 3 4管理登录的最新T SQL命令 返回本节首页 例10 8启用已禁用的登录 本例将启用KittiLert登录 ALTERLOGINKittiLertENABLE 例10 9更改登录密码 本例将KittiLert登录密码更改为3948wJ698FFF7 ALTERLOGINKittiLertWITHPASSWORD 3948wJ698FFF7 例10 10更改登录名称 本例将KittiLert登录名称更改为MacraeS ALTERLOGINKittiLertWITHNAME MacraeS 例10 11将登录名映射到凭据 本例将登录名MacraeS映射到凭据Custodian04 ALTERLOGINMacraeSWITHCREDENTIAL Custodian04 10 3 4管理登录的最新T SQL命令 返回本节首页 3 删除SQLServer登录帐户 语法 DROPLOGINlogin name 参数 login name指定要删除的登录名 例10 12将删除登录名WilliJo 命令为 DROPLOGINWilliJo 10 3 4管理登录的最新T SQL命令 10 4用户管理 返回本节首页 10 4 1登录名与数据库用户名的关系10 4 2使用ManagementStudio管理数据库用户10 4 3用T SQL管理数据库用户10 4 4改变数据库所有权 10 4用户管理 返回本节首页 用户是基于数据库的名称 是和登录帐户相关联的 可以使用ManagementStudio和系统存储过程sp grantdbaccess向数据库添加用户 只有数据库所有者及数据库管理员才有执行系统存储过程sp grantdbaccess的权力 返回本节首页 登录名是访问SQLServer的通行证 每个登录名的定义存放在master数据库的表syslogins 登录名是服务器级的 中 登录名本身并不能让用户访问服务器中的数据库资源 要访问具体数据库中的资源 还必须有该数据库的用户名 新的登录创建以后 才能创建数据库用户 数据库用户用于数据库权限的控制 定义信息存放在与其相关的数据库的sysusers表 用户名是数据库级的 中的 这个表包含了该数据库的所有用户对象以及和它们相对应的登录名的标识 数据库用户在特定的数据库内创建 必须和某个登录名相关联 用户名没有密码和它相关联 大多数情况下 用户名和登录名使用相同的名称 10 4 1登录名与数据库用户名的关系 返回本节首页 用户用一个登录名登录SQLServer 以数据库用户的身份访问服务器上的数据库 登录帐户试图访问数据库时查找sysusers中对应的用户名 或者是Guest用户 找不到将失败 一个登录帐户可以与服务器上的所有数据库进行关联 而数据库用户是一个登录帐户在某数据库中的映射 一个登录帐户可以映射到不同的数据库 产生多个数据库用户 但一个登录帐户在一个数据库至多只能映射一个数据库用户一个数据库用户只能映射到一个登录帐户 允许数据库为每个用户对象分配不同的权限 这一特性为在组内分配权限提供了最大的自由度与可控性 10 4 1登录名与数据库用户名的关系 返回本节首页 管理数据库用户包括对数据库用户的创建 查看 修改 删除等管理操作 创建数据库用户的两种方法 1 在创建登录帐户的同时指定该登录帐户允许访问的数据库 同时生成该登录帐户在数据库中的用户 2 先创建登录帐户 再将登录帐户映射到某数据库 在其中创建同名用户名 10 4 2使用ManagementStudio管理数据库用户 返回本节首页 1 在ManagementStudio中创建数据库用户1 启动ManagementStudio 分别展开 服务器 数据库 sales 安全性 用户 在 用户 文件夹下能看到该数据库的已有用户 2 右击 用户 文件夹 选择 新建数据库用户 弹出出 数据库用户 新建 对话框 3 输入要创建的数据库用户的名字 然后在 登录名 对应的文本框中输入相对应的登录名 或点击 浏览 按钮 在系统中选择相应的登录名 4 按 确定 按钮 将新创建的数据库用户添加到数据库中 10 4 2使用ManagementStudio管理数据库用户 10 4 2使用ManagementStudio管理数据库用户 返回本节首页 2 在ManagementStudio中查看 修改或删除数据库用户1 启动ManagementStudio 分别展开 服务器 数据库 sales 安全性 用户 在 用户 文件夹下能看到该数据库的已有用户 2 右击某要操作的用户 在系统弹出的快捷菜单中含有 属性 删除 等菜单项 3 若按 属性 菜单项 可以查看或修改用户的权限信息 如 常规 中的 拥有架构 角色成员 安全对象 中的具体权限设置及 扩展属性 等 4 若按 删除 菜单项 可从数据库中删除该用户 10 4 2使用ManagementStudio管理数据库用户 返回本节首页 利用T SQL命令对数据库用户同样有创建 查看 修改 删除等操作方法 主要使用到的T SQL命令有CREATEUSER ALTERUSER DROPUSER等 10 4 3用T SQL管理数据库用户 返回本节首页 1 用CREATEUSER语句创建数据库用户CREATEUSERuser name FOR FROM LOGINlogin name CERTIFICATEcert name ASYMMETRICKEYasym key name WITHOUTLOGIN WITHDEFAULT SCHEMA schema name 注意 不能使用CREATEUSER创建guest用户 因为每个数据库中均已存在guest用户 可通过授予guest用户CONNECT权限来启用该用户 GRANTCONNECTTOGUEST可以在sys database principals目录视图中查看有关数据库用户的信息 10 4 3用T SQL管理数据库用户 返回本节首页 例10 13创建数据库用户 创建名为AbolrousHazem且具有密码的服务器登录名 然后在AdventureWorks中创建对应的数据库用户AbolrousHazem CREATELOGINAbolrousHazemWITHPASSWORD 340 Uuxwp7Mcxo7Khy USEAdventureWorks CREATEUSERAbolrousHazem 10 4 3用T SQL管理数据库用户 返回本节首页 2 用ALTERUSER语句创建数据库用户ALTERUSERuser nameWITH n NAME new user name DEFAULT SCHEMA schema name例10 16更改数据库用户的名称 本例将数据库用户AbolrousHazem的名称更改为Abolrous ALTERUSERAbolrousHazemWITHNAME Abolrous例10 17更改用户的默认架构 本例将用户Abolrous的默认架构更改为Purchasing ALTERUSERAbolrousWITHDEFAULT SCHEMA Purchasing 10 4 3用T SQL管理数据库用户 返回本节首页 3 数据库中删除数据库用户 sp revokedbaccess name in db name 例10 18本例从当前数据库中删除映射到Edmonds LolanSo的数据库用户 EXECsp revokedbaccess Edmonds LolanSo 但请避免在新的开发工作中使用sp revokedbaccess命令 因为后续版本中将不再支持它了 请改用DROPUSER 10 4 3用T SQL管理数据库用户 返回本节首页 在SQLServer2005中 可以更改当前数据库的所有者 任何可以访问到SQLServer的连接的用户 SQLServer登录帐户或Windows用户 都可成为数据库的所有者 无法更改系统数据库的所有权 sp changedbowner loginame login map remap alias flag 10 4 4改变数据库所有权 返回本节首页 例10 19本例将登录名U1作为当前数据库的所有者 并映射到已分配给旧的数据库所有者的U1现有别名 EXECsp changedbowner U1 当前数据库的所有者改为U1gosp helpuser 查看数据库用户及所有者情况EXECsp changedbowner sa 当前数据库的所有者改还为sa 设原为sa 10 4 4改变数据库所有权 10 5角色管理 返回本节首页 10 5 1public角色10 5 2固定服务器角色10 5 3数据库角色10 5 4用户定义的角色10 5 5应用程序角色 10 5角色管理 返回本节首页 SQLServer2005数据库管理系统利用角色设置 管理用户的权限 大大减少了管理员的工作量 角色的使用与Windows组的使用很相似 通过角色 可以将用户集中到一个单元中 然后对这个单元应用权限 对角色授予 拒绝或吊销权限时 将对其中的所有成员生效 角色可以嵌套 嵌套的深度没有限制 但不允许循环嵌套 数据库用户可以同时是多个角色的成员 返回本节首页 public角色在每个数据库 包括系统数据库master msdb tempdb和model 中都存在 它也是数据库角色成员 Public角色供提供数据库中用户的默认权限 不能删除 其功能相当于Windows环境中的Everyone组 每个数据库用户都自动是此角色的成员 因此 无法在此角色中添加或删除用户 当尚未对某个用户授予或拒绝对安全对象的特定权限时 则该用户将继承授予该安全对象在public角色中对应的权限 10 5 1public角色 返回本节首页 固定服务器角色的作用域在服务器范围内 它们存在于数据库之外 固定服务器角色的每个成员都能够向该角色中添加其它登录 打开ManagementStudio 用鼠标单击 对象资源管理器 窗口中某数据库引擎的 安全性 目录下的 服务器角色 显示当前数据库服务器的所有服务器角色 共有8个 具体名称及角色描述如下 1 Bulkadmin 固定服务器角色的成员可以运行BULKINSERT语句 2 Dbcreator 固定服务器角色的成员可以创建 更改 删除和还原任何数据库 3 diskadmin 固定服务器角色用于管理磁盘文件 4 Processadmin 固定服务器角色的成员可以终止SQLServer实例中运行的进程 5 securityadmin 固定服务器角色的成员将管理登录名及其属性 它们可以GRANT DENY和REVOKE服务器级权限 也可以GRANT DENY和REVOKE数据库级权限 另外 它们可以重置SQLServer登录名的密码 6 serveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器 7 setupadmin 固定服务器角色的成员可以添加和删除链接服务器 并且也可以执行某些系统存储过程 8 Sysadmin 固定服务器角色的成员可以在服务器中执行任何活动 默认情况下 WindowsBUILTIN Administrators组 本地管理员组 的所有成员都是sysadmin固定服务器角色的成员 10 5 2固定服务器角色 返回本节首页 1 固定服务器角色的权限固定服务器角色可以映射到SQLServer2005包括的更为具体的权限 下表说明了固定服务器角色与权限的映射 10 5 2固定服务器角色 返回本节首页 1 在ManagementStudio中添加或删除固定服务器角色成员方法一 打开ManagementStudio 用鼠标单击 对象资源管理器 窗口 某数据库引擎 安全性 服务器角色 方法二 打开ManagementStudio 用鼠标单击 对象资源管理器 窗口 某数据库引擎 安全性 登录名 在某登录名上右击 按 属性 菜单 出现 登录属性 对话框或按 新建登录名 出现 登录 新建 对话框 点击 服务器角色 选项卡 10 5 2固定服务器角色 10 5 2固定服务器角色 返回本节首页 2 利用T SQL添加或删除固定服务器角色成员固定服务器角色成员添加或删除 主要使用到的系统存储过程有 sp addsrvrolemember sp dropsrvrolemember sp helpsrvrole sp helpsrvrolemember 10 5 2固定服务器角色 返回本节首页 1 sp addsrvrolemember用于添加登录 使其成为固定服务器角色的成员 sp addsrvrolemember loginame login rolename role 备注 role可以是下列任意值 sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin例10 20本示例将Windows登录名Corporate HelenS添加到sysadmin固定服务器角色中 EXECsp addsrvrolemember Corporate HelenS sysadmin 10 5 2固定服务器角色 返回本节首页 2 sp dropsrvrolemember用于从固定服务器角色中删除SQLServer登录或Windows用户或组 语法 sp dropsrvrolemember loginame login rolename role 例10 21本示例从sysadmin固定服务器角色中删除登录Corporate HelenS EXECsp dropsrvrolemember Corporate HelenS sysadmin 10 5 2固定服务器角色 返回本节首页 3 sp helpsrvrole用于返回SQLServer固定服务器角色的列表 语法 sp helpsrvrole srvrolename role 以下查询返回可用固定服务器角色列表 EXECsp helpsrvrole 10 5 2固定服务器角色 返回本节首页 4 sp helpsrvrolemember用于返回有关SQLServer固定服务器角色成员的信息 语法 sp helpsrvrolemember srvrolename role 例10 22本示例将列出sysadmin固定服务器角色的成员 EXECsp helpsrvrolemember sysadmin 10 5 2固定服务器角色 返回本节首页 固定数据库角色在数据库级别定义以及每个数据库中都存在 db owner和db security管理员角色的成员可以管理固定数据库角色的成员身份 1 db accessadmin 可以为Windows登录帐户 Windows组和SQLServer登录帐户添加或删除访问权限 2 db backupoperator 可以备份该数据库 3 db datareader 可以读取所有用户表中的所有数据 4 db datawriter 可以在所有用户表中添加 删除或更改数据 5 db ddladmin 可以在数据库中运行任何数据定义语言 DDL 命令 6 db denydatareader 不能读取数据库内用户表中的任何数据 7 db denydatawriter 不能添加 修改或删除数据库内用户表中的任何数据 8 db owner 可以执行数据库的所有配置和维护活动 9 db securityadmin 可以修改角色成员身份和管理权限 10 5 3数据库角色 返回本节首页 通过用户定义的角色可以轻松地管理数据库中的权限 用户定义的角色只适用于数据库级别 并且只对创建时所在的数据库起作用 1 数据库角色创建 修改与删除数据库角色的创建 修改与删除操作可以通过ManagementStudio或T SQL两种方法来操作 1 在ManagementStudio中创建 修改或删除数据库角色 10 5 4用户定义的角色 10 5 4用户定义的角色 返回本节首页 2 利用T SQL创建 修改或删除数据库角色数据库角色的创建 修改或删除 主要使用到的T SQL命令有 CREATEROLE sp addrole ALTERROLE DROPROLE sp droprole sp helprole 10 5 4用户定义的角色 返回本节首页 1 CREATEROLE在当前数据库中创建新数据库角色 CREATEROLErole name AUTHORIZATIONowner name 例10 23本示例将创建用户BenMiller拥有的数据库角色buyers USEAdventureWorks CREATEROLEbuyersAUTHORIZATIONBenMiller 10 5 4用户定义的角色 返回本节首页 2 sp addrole在当前数据库中创建新的数据库角色 sp addrole rolename role ownername owner 例10 24本示例向当前数据库中添加名为Managers的新角色 EXECsp addrole Managers 尽量改用CREATEROLE 10 5 4用户定义的角色 返回本节首页 3 ALTERROLE更改数据库角色的名称 ALTERROLErole nameWITHNAME new name例10 25本示例将角色buyers的名称更改为purchasing USEAdventureWorks ALTERROLEbuyersWITHNAME purchasing 10 5 4用户定义的角色 返回本节首页 4 DROPROLE从数据库删除角色 DROPROLErole name例10 26本示例从AdventureWorks删除数据库角色purchasing USEAdventureWorks DROPROLEpurchasing 10 5 4用户定义的角色 返回本节首页 5 sp droprole从当前数据库中删除数据库角色 语法 sp droprole rolename role 例10 27本示例删除应用程序角色Sales EXECsp droprole Sales 尽量改用DROPROLE 10 5 4用户定义的角色 返回本节首页 6 sp helprole返回当前数据库中有关角色的信息 语法 sp helprole rolename role 例10 28本查询将返回当前数据库中的所有角色 EXECsp helprole 10 5 4用户定义的角色 返回本节首页 2 数据库角色成员的添加与删除数据库角色成员的添加与删除操作可以通过ManagementStudio或T SQL两种方法来操作 1 在ManagementStudio中添加与删除数据库角色成员方法一 在上面提到过的某数据库角色的 数据库角色属性 对话框中 常规 选项卡上 右下角色成员操作区 按 添加 或 删除 按钮实现操作 方法二 通过 对象资源管理器 窗口 某数据库引擎 数据库 某具体数据库 安全性 用户 某具体用户 按鼠标右击 按 属性 菜单 出现 数据库用户 对话框 在右下角色成员操作区 通过多选按钮直接实现该用户从某个或某些数据库角色中添加或删除的操作功能 10 5 4用户定义的角色 10 5 4用户定义的角色 返回本节首页 2 利用T SQL添加与删除数据库角色成员数据库角色成员添加与删除 主要使用到的T SQL命令有 sp addrolemember sp droprolemember sp helprolemember 10 5 4用户定义的角色 返回本节首页 1 sp addrolemember为当前数据库中的数据库角色添加数据库用户 数据库角色 Windows登录或Windows组等 sp addrolemember rolename role membername security account 例10 29本示例将Windows登录Sammamish SandeepK作为Sandeep用户添加到AdventureWorks数据库 然后将Sandeep添加到Production角色 USEAdventureWorksEXECsp grantdbaccess Sammamish SandeepK Sandeep GOEXECsp addrolemember Production Sandeep 10 5 4用户定义的角色 返回本节首页 2 sp droprolemember从当前数据库的SQLServer角色中删除安全帐户 sp droprolemember rolename role membername security account 例10 30本示例将删除角色Sales中的用户JonB EXECsp droprolemember Sales Jonb 10 5 4用户定义的角色 返回本节首页 3 sp helprolemember返回有关当前数据库中某个角色的成员的信息 sp helprolemember rolename role 例10 31本示例显示Sales角色的成员 EXECsp helprolemember Sales 10 5 4用户定义的角色 返回本节首页 应用程序角色是一个数据库主体 它使应用程序能够用其自身的 类似用户的特权来运行 使用应用程序角色 可以只允许通过特定应用程序连接的用户访问特定数据 与数据库角色不同的是 应用程序角色默认情况下不包含任何成员 而且是非活动的 使用时需要激活 应用程序角色可以使用两种身份验证模式 应用程序角色可使用sp setapprole激活 但此过程需要应用程序角色名和密码 因为应用程序角色是数据库级主体 所以它们只能通过其它数据库中为guest授予的权限来访问这些数据库 因此 其它数据库中的应用程序角色将无法访问任何已禁用guest的数据库 10 5 5应用程序角色 返回本节首页 应用程序角色切换安全上下文的过程包括下列步骤 1 创建应用程序角色 2 对该应用程序角色分配权限 3 用户执行客户端应用程序 4 客户端应用程序作为用户连接到SQLServer 5 然后应用程序用一个只有它才知道的密码执行sp setapprole存储过程 6 如果应用程序角色名称和密码都有效 将激活应用程序角色 此时 连接将失去用户权限 而获得应用程序角色权限 通过应用程序角色获得的权限在连接期间始终有效 10 5 5应用程序角色 返回本节首页 1 应用程序角色创建与删除 1 在ManagementStudio中创建与删除应用程序角色 2 利用T SQL创建 修改与删除应用程序角色创建 修改与删除应用程序角色 主要使用到的T SQL命令有 CREATEAPPLICATIONROLEALTERAPPLICATIONROLE DROPAPPLICATIONROLE sp setapprole与sp unsetapprole 10 5 5应用程序角色 返回本节首页 1 CREATEAPPLICATIONROLE向当前数据库中添加应用程序角色 CREATEAPPLICATIONROLEapplication role nameWITHPASSWORD password DEFAULT SCHEMA schema name 可以在sys database principals目录视图中查看应用程序角色 例10 32本示例创建名为weekly receipts的应用程序角色 该角色使用密码987Gbv876sPYY5m23和Sales作为其默认架构 CREATEAPPLICATIONROLEweekly receiptsWITHPASSWORD 987Gbv876sPYY5m23 DEFAULT SCHEMA Sales 10 5 5应用程序角色 返回本节首页 2 ALTERAPPLICATIONROLE更改应用程序角色的名称 密码或默认架构 ALTERAPPLICATIONROLEapplication role nameWITH n NAME new application role name PASSWORD password DEFAULT SCHEMA schema name重要事项 密码过期策略不应用于应用程序角色密码 为此 选择强密码时要格外谨慎 调用应用程序角色的应用程序必须存储其密码 例10 33本示例将同时更改应用程序角色receipts ledger的名称 密码和默认架构 ALTERAPPLICATIONROLEreceipts ledgerWITHNAME weekly ledger PASSWORD 897yUUbv77bsrEE00nk2i DEFAULT SCHEMA Production 10 5 5应用程序角色 返回本节首页 3 DROPAPPLICATIONROLE从当前数据库删除应用程序角色 DROPAPPLICATIONROLErolename例10 34本示例从数据库中删除应用程序角色 weekly ledger DROPAPPLICATIONROLEweekly ledger 10 5 5应用程序角色 返回本节首页 4 sp setapprole与sp unsetapprolesp setapprole激活与当前数据库中的应用程序角色关联的权限 语法 sp setapprole rolename role password encryptN password password encrypt none odbc fCreateCookie true false cookie cookieOUTPUT sp unsetapprole恢复应用程序角色激活前的上下文语法 sp unsetapprole cookie 10 5 5应用程序角色 返回本节首页 例10 35本例使用密码fdsd896 gfdbfdkjgh700mM激活Sales11应用程序角色并创建一个cookie 该示例返回当前用户的名称 然后通过执行sp unsetapprole恢复到原始上下文中 DECLARE cookievarbinary 8000 EXECsp setapprole Sales11 fdsd896 gfdbfdkjgh700mM fCreateCookie true cookie cookieOUTPUT 应用程序角色现在已被激活SELECTUSER NAME 这