项目9-CJGL数据库的安全管理

项目项目 9 CJGL 数据库的安全管理数据库的安全管理 对任何企业组织来说 数据的安全性最为重要 安全性主要是指允许那些具有相应的数据 访问权限的用户 能够登录到 SQL Server 并访问数据以及对数据库对象实施各种权限范围 内的操作 但是要拒绝所有的非授权用户的非法操作 因此安全性管理与用户管理是密不 可分的 SQL Server 2005 提供了内置的安全性和数据保护 SQL Server 2005 的安全性管理是建立在认证 Authentication 和访问许可 permission 两者机制上的认证 是指来确定登录 SQL Server 的用户的登录账号和密码是否正确以此来 验证其是否具有连接 SQL Server 的权限 但是通过认证阶段并不代表能够访问 SQL Server 中的数据用户 只有在获取访问数据库的权限之后才能够对服务器上的数据库进行权限许 可下的各种操作 主要是针对数据库对象 如表 视图 存储过程等 这种用户访问数据 库权限的设置是通过用户帐号来实现的 同时在 SQL Server 中角色作为用户组的代替物大 大地简化了安全性管理 能力目标 能够配置服务器的身份验证模式 能够创建并配置登录账户 能够为登录账户配置数据库访问许可 能够为数据库用户指定数据访问权限 能够通过角色设置批量实现用户权限管理 知识目标 熟悉 SQL Server 2005 的安全机制 掌握服务器登录账户的管理方法 掌握数据库用户的权限管理方法 理解角色的作用 工作任务 服务器登录账户管理 数据库用户管理 任务任务 1 服务器登录账户管理 服务器登录账户管理 任务描述任务描述 用户要访问数据库 必须具备登录 SQL Server 服务器的权限 可以通过 两种途径登录服务器 一是通过 Windows 系统用户身份 二是通过有效的 SQL Server 账 户身份 用户如果不通过默认账户登录 则必须为其创建登录账户 子任务 1 创建登录账户 Win User 通过 Windows 身份验证 并映射到 CJGL 数据库 子任务 2 创建登录账户 Sql User 通过 SQL Server 身份验证 并映射到 CJGL 数据库 预备知识预备知识 1 SQL Server 2005 身份验证机制 安全身份验证用来确认登录 SQL Server 的用户的登录账号和密码的正确性 由此来检验 该用户是否具有连接 SQL Server 服务器的权限 任何用户在使用 SQL Server 服务器之前 必须经过身份验证 SQL Server 2005 提供了两种身份验证模式 分别是 Windows 身份验证和 SQL Server 身 份验证 1 1 Windows 身份验证 SQL Server 服务器运行于 Windows 服务器之上 而 Windows 作为网络操作系统 本身 具备管理登录 验证用户是否合法的能力 因此 Windows 身份验证模式就是用户通过自 己合法的 Windows 账户和口令进行验证 只要用户具备登录操作系统的权限 即可连接 SQL Server 服务器 1 2 SQL Server 身份验证 启用该模式通常也称为 混合验证模式 该认证模式下 用户连接 SQL Server 服务器必 须提供登录名和密码 这些登录信息存储在系统表 syslogins 中 与 Windows 账号无关 注意 如果 SQL Serve 服务器启用了混合验证模式 则不但可以通过 SQL Server 账号登 录 也可以请求 Windows 验证 然后通过 Windows 账户登录 Windows 身份验证安全性要比 SQL Server 验证高的多 因为 SQL Server 服务器相比 操作系统具有更高的安全机制 任务实施任务实施 子任务 1 创建登录账户 Win User 通过 Windows 身份验证 并映射到 CJGL 数据库 1 创建 Windows 系统账户 账户名为 Win User 步骤如下 1 在 桌面 右键单击 选择 管理 打开计算机管理窗口 2 展开 本地用户和组 节点 右键单击 用户 选择 新用户 命令 如下所 示 3 然后在弹出的新用户对话框中输入用户名及密码 如下所示 4 单击 创建 按钮 2 将 Win User 设置为 SQL Server 登录账户 1 以系统管理员账户登录 SQL Server 服务器 如下所示 2 将 SQL Serve 身份验证方式改为 Windows 验证 在服务器节点上右键单击 选择 属性 如下所示 在弹出的服务器属性窗口中 选择 安全性 节点 然后在右边选中 Windows 身份验证模式 如下所示 更改后 必须重启服务器才能是设置生效 如下所示 单击 确定 后 在服务器节点上右键单击 选择 重新启动 如下所示 3 展开选择服务器节点下的 安全性 节点 在 登录名 节点右键单击 选择 新建登录名 4 在弹出的 登录名 窗口中 单击 搜索 按钮 如果要输入登录名 必须确 保输入正确 如下所示 5 在弹出的 选择用户和组 对话框中 单击 高级 按钮 如下所示 6 然后单击 立即查找 按钮 在下面列出的对象中选择 Win User 如下所 示 7 单击 确定 按钮 这时候在登录名文本框中会出现 FANXINGANG Win User 这里的 FANXINGANG 视具体机器而定 3 用户映射 即使该登录账户具有访问数据库的权限 在 登录名 窗口中 选择 用户映射 项 然后在数据库列表中选择 CJGL 数据库角色成员身份 保持默 认 如下所示 4 将 Win User 设置为 SQL Server 登录账户后 可以通过 Windows 系统的切换用户方 式 以 Win User 登录操作系统 然后再连接 SQL Server 服务器 过程略 5 登录账号创建成功后 可以通过该账号进行连接 SQL Server 服务器 登录服务器后 只能访问 CJGL 数据库 而不具备访问其他数据库的权限 比如要向访问 BooK 数 据库 将会出现以下错误 子任务 2 创建登录账户 Sql User 通过 SQL Server 身份验证 并映射到 CJGL 数据库 1 将 SQL Server 服务器身份验证方式改为 SQL Server 和 Windows 身份验证模式 过程同上 2 重启服务器后 如上述过程打开 登录名 窗口 然后在登录名文本框中输入 SQL User 选中 SQL Server 身份验证 单项按钮 输入密码及确认密码 如下 所示 3 用户映射方式同上 不再赘述 任务任务 2 数据库用户管理 数据库用户管理 任务描述任务描述 创建了登录账户后 实际上只具备了连接服务器的权限 即便在创建登录账 户的时候进行了用户映射 默认状态下 映射后的数据库用户的数据库角色是 public 仍旧不具备访问用户定义对象的权限 因此需要为数据库创建相应用户并赋予相应权限 子任务 1 为 CJGL 数据库创建用户 DB User 该用户通过登录账户 SQL User 登录 服务器 子任务 2 对用户 DB User 进行权限设置 其权限如下表所示 表示具备权限 权限 安全对象 SELECTINSERTUPDATEDELETE department Class Student Course Teacher Timetable score 子任务 3 创建数据库角色 User Role 其安全对象及权限设置为上表所示 并将数据库 用户 DB User 的数据库角色设置为 User Role 预备知识预备知识 1 数据库用户与架构 1 1 数据库用户 登录名 服务器方的一个实体 使用一个登录名只能进入服务器 但是不能让用户访问服 务器中的数据库资源 每个登录名的定义存放在 master 数据库的 syslogins 表中 数据库用户名 一个或多个登录对象在数据库中的映射 可以对用户对象进行授权 以便 为登录对象提供对数据库的访问权限 用户定义信息存放在每个数据库的 sysusers 表中 SQL SERVER 把登录名与用户名的关系称为映射 用登录名登录 SQL SERVER 后 在 访问各个数据库时 SQLSERVER 会自动查询此数据库中是否存在与此登录名关联的用户 名 若存在就使用此用户的权限访问此数据库 若不存在就是用 guest 用户访问此数据库 一个登录名可以被授权访问多个数据库 但一个登录名在每个数据库中只能映射一次 即 一个数据库中只能有一个用户名通过该登录名登录 好比 SQLSERVER 就象一栋大楼 里 面的每个房间都是一个数据库 登录名只是进入大楼的钥匙 而用户名则是进入房间的钥 匙 一个登录名可以有多个房间的钥匙 但一个登录名在一个房间只能拥有此房间的一把 钥匙 连接 SQL Server 服务器使用的是登录名而不是数据库用户名 SQLSERVER 中有几个特殊的登录名和用户名 我们常见的 dbo 用户名 是指以 sa 登录名 或 windows administration Windows 集成验证登录方式 登录的用户 也就是说数据库管理员在 SQL SERVER 中的用户名就叫 dbo 而不叫 sa 这一点看起来有点蹊跷 因为通常用户名与登录名相同 不是强制相同 但为了一目了然通常都在创建用户名时使用与登录名相同的名字 例如创建了一个登录名 称为 me 那么可以为该登录名 me 在指定的数据库中添加一个同名用户 使登录名 me 能够访问该数据库中的数据 当在数据库中添加了一个用户 me 后 之后以 me 登录名登 录时在该数据库中创建的一切对象 表 函数 存储过程等 的所有者都为 me 如 me table1 me fn test 而不是 dbo table1 dbo fn test SQL Server 中还有一个特殊的数据库角色 public 它存在于每一个数据库中 包括 系统数据库 如 master msdb model 和用户数据库 数据库的所有用户都属于 public 角色 并且不能从 public 角色中删除 在 SQLSERVER 数据库中 guest 帐户是特殊的用户帐户 如果用户使用 USE database 语句访问的数据库中没有与此用户关联的帐户 此用户就与 guest 用户相关联 1 2 架构 schema 以往 SQL Server 内的对象命名是 服务器 数据库 用户名 对象 但 SQL Server 2005 版本 后的对象命名改为 服务器 数据库 Schema 对象 这让你规划数据库对象命名时更有弹性 架构是形成单个命名空间的数据库实体的集合 命名空间是一个集合 其中每个元素的 名称都是唯一的 数据库引擎权限层次结构之间的关系图 在 SQL Server 2005 中 架构独立于创建它们的数据库用户而存在 亦即用户与架构分离 这种机制具有以下优点 多个用户可以通过角色成员身份或 Windows 组成员身份拥有一个架构 这扩展了允 许角色和组拥有对象的用户熟悉的功能 极大地简化了删除数据库用户的操作 删除数据库用户不需要重命名该用户架构所包含的对象 因而 在删除创建架构 所含对象的用户后 不再需要修改和测试显式引用这些对象的应用程序 用户与架构 schema 分开 让数据库内各对象 再绑在某个用户账号上 可以 解决之前版本 用户离开公司 问题 也就是在拥有该对象的用户离开公司 或 离开该职务时 必要大费周章地 改该用户所有的对象属于新的用户所有 另外 也可让 DBA 在安装某个套装软件时 设置该套装软件所用的数据库对象 都属于某个特定的架构 容 区别 也就是说 在单一数据库内 同部门或目 的的对象 可以通过架构区分 同的对象命名原则与权限 可以用比早期版本中的粒度更大的粒度管理架构和架构包含的对象的权限 2 服务器角色和数据库角色 2 1 服务器角色 是固化在 SQL Server 服务器中的角色 每个服务器角色都有其隐含的权限 使用系统存 储过程 sp srvrolepermission 可以浏览每个固定服务器角色的权限 该系统过程的语法形 式为 sp srvrolepermission srvrolename role name 固定服务器角色权限说明 sysadmin被赋予了 SQL Server 中的任何权限 serveradmin 向该服务器角色中添加其他登录 运行 dbcc pintable 命令 从而使表常驻于主内存中 运行系统过程 sp configure 以显示或更改系统选项 运行 reconfigure 选项 以更新系统过程 sp configure 所做的所 有改动 使用 shutdown 命令关掉数据库服务器 运行系统过程 sp tableoption 为用户自定义表设置选项的值 setupadmin 向该服务器角色中添加其他登录 添加 删除或配置链接的服务器 执行一些系统过程 如 sp serveroption securityadmin 向该服务器角色中添加其他登录 读取 SQL Server 的错误日志 运行如下的系统过程 如 sp addlinkedsrvlogin sp addlogin sp defaultdb sp defaultla nguage sp denylogin sp droplinkedsrvlogin sp droplogin sp grantlogin sp helplogins sp remoteoption 和 sp revokelogin 所有这些系统过程都与系统安全相关 processadmin 向该服务器角色中添加其他登录 执行 KILL 命令 以取消用户进程 dbcreator 向该服务器角色中添加其他登录 运行 CREATE DATABASE 和 ALTER DATABASE 语句 使用系统过程 sp renamedb 来修改数据库的名称 diskadmin 向该服务器角色中添加其他登录 运行如下系统过程 sp ddumpdevice 和 sp dropdevice 运行 DISK INIT 语句 2 2 数据库角色 数据库角色被限制在一个数据库内 数据库角色分为两类 固定数据库角色和用户定义数 据库角色 固定数据库角色包括如下几类 固定数据库角色权限说明 db owner可以执行数据库中技术所有动作的用户 db accessadmin可以添加 删除用户的用户 db datareader可以查看所有数据库中用户表内数据的用户 db datawriter可以添加 修改或删除所有数据库中用户表内数据的用户 db ddladmin可以在数据库中执行所有 DDL 操作的用户 db securityadmin可以管理数据库中与安全权限有关所有动作的用户 db backoperator 可以备份数据库的用户 并可以发布 DBCC 和 CHECKPOINT 语句 这两个语句一般在备份前都会被执行 db denydatareader不能看到数据库中任何数据的用户 db denydatawriter不能改变数据库中任何数据的用户 除了上表中列出的固定数据库角色之外 还有一种特殊的固定数据库角色 名为 public public 角色是一种特殊的固定数据库角色 数据库的每个合法用户都属于该角色 它为数 据库中的用户提供了所有默认权限 这样就提供了一种机制 即给予那些没有适当权限的 所有用户以一定的 通常是有限的 权限 public 角色为数据库中的所有用户都保留了默认的 权限 因此是不能被删除的 一般情况下 public 角色允许用户进行如下的操作 使用某些系统过程查看并显示 master 数据库中的信息 执行一些不需要一些权限的语句 例如 PRINT 3 安全对象与权限管理 安全对象是指授权用户可以访问的对象集合 权限是指授权用户在拥有的安全对象上可以执行的操作 权限管理包括权限的授予 拒绝和收回 可以通过 SSMS 向导和 T SQL 语句实现权限管 理 任任务务实实施施 子任务 1 为 CJGL 数据库创建用户 DB User 该用户通过登录账户 SQL User 登录 服务器 注意 开始本任务之前 需将 CJGL 数据库用户 SQL User 任务 1 子任务 2 创建 删除 因为一个登录名只允许一个数据库用户绑定 1 展开数据库 CJGL 展开其 安全性 节点 选择 新建用户 命令 如下所示 2 在弹出的 数据库用户 窗口中 在用户名文本框中输入 DB User 如下所示 3 单击登录名文本框右边的选择按钮 打开 选择用户名 对话框 如下所示 4 单击 浏览 按钮 打开 查找对象 对话框 在对象列表中选择 SQL User 见 任务 1 子任务 2 如下所示 5 依次单击 确定 按钮 将创建数据库用户 DB User 但是该用户目前没有任何实际 权限 子任务 2 对用户 DB User 进行权限设置 其权限如下表所示 表示具备权限 权限 安全对象 SELECTINSERTUPDATEDELETE department Class Student Course Teacher Timetable score 任务分析 子任务 1 中创建的数据库用户 DB User 因为不具备实际权限 因此以该用户身份登录服务 器后 在 CJGL 数据库中没有任何可用表及视图对象 如下所示 要想为 DB User 分配安全对象及权限 必须以管理员账号登录服务器 因为 DB User 尚 不具备权限管理的权限 1 通过 SSMS 向导完成权限的设置 1 以 Windows 管理员账号登录 SQL Server 服务器 2 展开 CJGL 数据库的 安全性 节点 展开 用户 节点 3 在用户 DB User 上右键单击 选择 属性 命令 如下所示 4 在弹出的 数据库用户 窗口中 选择 安全对象 页 如下所示 5 在 安全对象 栏下单击 添加 按钮 6 在弹出的 添加对象 对话框中 选择 特定类型的所有对象 单项按钮 然后单击 确定 按钮 如下所示 7 在弹出的 选择对象类型 对话框中选中 表 然后单击 确定 按钮 如下所示 8 回到 安全对象 页后 依次选中安全对象 然后在下面的权限选择列表中选中相应 的权限 如下所示 以 department 安全对象为例 9 将所有安全对象的权限设置完成后 单击 确定 按钮即可 2 利用 T SQL 语句完成权限设置 1 以 Windows 管理员账号登录 SQL Server 服务器 2 在查询窗口中输入如下 T SQL 语句 为DB User用户授予department表的查询 插入 更新权限 GRANT SELECT INSERT UPDATE ON department TO DB User GO 为DB User用户授予class表的查询 插入 更新权限 GRANT SELECT INSERT UPDATE ON class TO DB User GO 为DB User用户授予student表的查询 插入 更新 删除权限 GRANT SELECT INSERT UPDATE DELETE ON student TO DB User GO 为DB User用户授予course表的查询 插入权限 GRANT SELECT INSERT ON course TO DB User GO 为DB User用户授予teacher表的查询 插入 删除权限 GRANT SELECT INSERT DELETE ON teacher TO DB User GO 为DB User用户授予timetable表的查询权限 GRANT SELECT ON timetable TO DB User GO 为DB User用户授予score表的查询权限 GRANT SELECT ON score TO DB User GO 3 若要收回权限 则需要 REVOKE 语句实现 比如收回 DB User 在 class 表的插入权 限 REVOKE INSERT ON class FROM DB User 4 若要拒绝某个用户的权限 则需要 DENY 语句实现 比如拒绝 DB User 在 class 表的 插入权限 DENY INSERT ON class TO DB User 子任务 3 创建数据库角色 User Role 其安全对象及权限设置为上表所示 并将数据库 用户 DB User 的数据库角色设置为 User Role 任务分析 假设有 N 位用户访问 CJGL 数据库 并且所有用户都拥有相同的安全对象和权限 那么 作为 DBA 是否需要一一为每个用户进行子任务 2 那样的设置 数据库角色解决了这个难题 DBA 只要将所有用户都加入到相同的数据库角色 即可 免去一一设置权限的 繁殖劳动 1 展开 CJGL 数据库的 安全性 节点 展开 角色 节点 2 在 数据库角色 节点上右键单击 选择 新建数据库角色 如下所示 3 在 数据库角色 窗口中 在角色名称文本框中输入 User Role 如下所示 4 然后选择 安全对象 页 如子任务 2 所示 将相关安全对象及权限设置完成 5 角色创建好后 打开用户 DB User 的 属性 窗口 在 数据库角色 选择列表 中选中 User Role 然后单击 确定 按钮即可 如下所示 习题 9 一 选择题 1 一个系统管理员正在考虑应该使用一种安全模型 所有需要访问SQLServer的用户都有 WindowsNT的帐户 而且SQLServer被安装在和用户帐户域相同域中的一个stand alone server上 所有的用户都使用TCP IP和服务器连接 系统管理员应该使用哪种安全模式 A 混合安全模式 B NT验证模式 C 同时使用模式和混合安全模式 2 一个系统管理员正在考虑应该使用一种安全模型 用户使用多种网络协议对网络进行访 问 而且SQL Server所在的服务器是网络上唯一的一台Windows NT服务器 系统管理员应 该使用哪种安全模式 A 混合安全模式 B NT验证模式 C 同时使用混合安全模式和NT验证模式 3 当系统管理员将他的服务器配制成NT验证模式后 用户开始对他抱怨说他们无法使用他 们的WindowsNT登录名访问服务器 下面哪两个选项是最可能导致这种情况发生的原因 A 系统管理员没有重新启动管理所有安全请求的SQL Server Agent服务 B 系统管理员没有重新启动SQL Server服务 C 系统管理员没有为任何用户授予对服务器进行管理的访问权 D 系统管理员没有使用SQL Server Management Studio对Windows NT帐户的登录名进行映 射 4 下面哪种网络库可用于NT验证模式 A TCP IP Sockets B SPX C Named Pipes D All of the above 5 关于安全性模式的信息保存在哪里 A 注册表 B master sysconfigures C eventlog D master syslogins 6 系统管理员在为他新的助手Dave创建帐户时遇到了问题 Dave需要管理服务器上的两个 数据库 但无权访问其余的数据库 服务器当前被配置使用标准安全模式 系统管理员怎 样才能最好地完成这项任务 A 给Dave提供sa登录名的口令 B 为Dave新建一个登录名 在相应的数据库上用db changeowner 使Dave成为数据库的属 主 C 为Dave新建一个登录名 在相应的数据库上将dbo作为这个登录名的别名 D 使用SQL Server Management Studio给Dave分配数据库属主的权限 7 假定G