SQL Server权限管理与数据恢复

SQL Server的安全机制

SOL Server 的安全性是建立在认证和访问许可两种安全机制之上的，其中，认证用来确定登录 SQlL Server 的用户的登录账户和密码是否正确，以此来验证其是否具有连接 SQL. Server的权限：访问许可用来授予用户或组能够在数据库中执行哪些操作，如创建表、查询数据等。

SOL Server的安全机制主要包括以下四个方面:

>客户机的安全机制。

>服务器的安全机制。

>数据库的安全机制。

>数据对象的安全机制。

用户必须能够登录客户机，然后才能使用SQlL Server 应用程序或管理工具来访问数据库。对于使用Windows系统的客户来说，它主要涉及操作系统的安全，主要是Windows账号的安全. 用户登录服务器时，必须使用一个账号（也称为登录账号）和密码，服务器会按照不同的身份验证方式来判断这个账号和密码的正确性。

任何能够登录服务器的账号和密码都对应着一个默认的数据库，SQL Server 对数据库级的权限管理采用的是“数据库用户”的概念。用户通过前面三道防线后才能访问数据库中的数据对象，对数据对象能够做什么样的访问称为访问权限，常见的访问权限包括数据的查询，更新，插入和删除。

SQL Server 的身份验证模式

有以下两种身份验证模式。

> Windows身份验证模式

> SOL Server和Windows身份验证模式（混合验证模式）

Windows 身份验证模式

当使用Windows用户身份验证时，SOlL Server会使用操作系统中的Windows用户和密码。也就是说.用户身份由Windows进行确认.SOL Server不要求提供密码，也不执行身份验证.Windows 身份验证使用了Kerberos安全协议.比SQL Server身份验证更为安全，可以保护SOL Server免受大部分来自lnternet的攻击. Windows身份验证模式适用于在局域网内部（如AD域）访问数据库的情况。

SQL Server和Windows身份验证模式

SQL Server 和Windows 身份验证模式也称为混合验证模式，允许用户使用Windows身份验证或 SOL Server身份验证进行连接。该模式通常用于运行在非Windows系统环境的用户、Internet用户或混杂的工作组用户访问SQLServer时.例如，外界用户访问数据库或不能登录indows域时。当使用SOL Server身份验证时，在SOL Server中创建的登录名并不是基于Windows用户账户的。用户名和密码均通过使用SOLServer创建并存储在SOL Server中，如果必须使用SOL Server身份验证，必须为所有SQL Server 账户启用密码策略，如强制密码过期，密码复杂度等。

访问许可权限设置

SOL Server 中的访问许可权限可以分别从服务器，数据库及对象三个级别进行设置。可以把SQL Server想象为一幢大楼，要进入这幢大楼需要一张通行证，这就是登录账户，而这张通行证的类型（内部员工还是来宾）能否进入大楼，主要取决于保安的检查方式（即身份验证模式）.数据库则可以想象为大楼中的房间，进入大楼之后还不可以访问每个房间，要访问哪个房间还需要哪个房间的钥匙，这就是数据库用户.最后，每个房间里都包含一些对象（如椅子、计算机、铅笔等），可以把它们想象为数据库中的表，并不是每个进入房间的人都有权使用这些对象。这些对象以架构为单位，可以分配用户权限去访问架构，通过架构可以访问架构下的所有对象。当然，如果是这幢大楼的房间拥有者，那么他可以拥有房间内的任何对象，甚至可以让别人去使用这些对象。

服务器级别权限设置

在SOLServer 2016中内置了一组服务器角色（也称为固定服务器角色）.这些角色用来执行服务器级别的管理，如创建数据库、管理和审核登录账户、中止SQL Server实例中长时间运行的进程等，它们作用于整台服务器，而非单独某个数据库. SQL Server 2016中的“角色”类似于Windows中的“组”。通过将不同的角色赋予不同的用户，用户可以具有不同的操作权限。一个用户可以拥有多个固定服务器角色权限，但是固定服务器角色不能被修改、添加和删除。

SQL Server中还有一个特殊的服务器角色public,所有登录名都属于该角色。该角色只拥有查看数据库的权限。

为用户分配服务器级别权限的具体步骤如下。在SSMS中依次展开“SOL Server实例”→“安全性”→“登录名”节点，右击某个登录名，在弹出的快捷菜单中选择“属性”命令，在打开的“登录属性”窗口中选择“服务器角色”选项，可以设置需要赋予该用户的权限.

数据库级别权限设置

新建数据库用户

建立了SQL Server登录账户后，需要赋予用户权限，用户才能够对数据库进行查询与更新等操作.登录账户用于访问SQL Server实例，数据库用户则用于访问数据库.如果需要访问某个数据库，则需要在该数据库中建立相应的数据库用户。实际上，数据库用户是映射到登录账户上的。

内置数据库用户

创建的任何一个数据库中默认包含两个特殊用户dbo（Database Owner)和guest，且不能被修改或删除。 doo是数据库的所有者，对该数据库具有所有权限。默认情况下，创建数据库的登录账户自动对应于该数据库的dbo 用户.dbo 用户又属于db_owner 数据库角色的成员，而 db_owner 数据库角色具有对该数据库的全部管理权限，因此可以说，哪个登录账户创建了数据库，这个登录账户就具备了该数据库的所有管理权限。此外，登录账户sa对应所有系统数据库的dbo用户. guest是来宾账户，同样不能被删除。guest 用户不映射任何登录账户，所有没映射数据库用户的登录账户都将以guest 身份访问数据库.guest用户默认没有任何权限，且处于禁用状态，因此，启用并为其授予权限时一定要慎重。

SQL Server备份和还原

在Access、FoxPro这样的桌面型数据库中，备份就是将数据库文件复制到另外一个安全的地方，还原则是将这些文件复制回原位置，而SQL Server 数据库则不仅仅是复制文件这么简单. 在 SOlL. Server 中提供了三种常用的备份类型，分别是完整备份，差异备份和事务日志备份。

1.完整备份

完整备份包括对整个数据库、部分事务日志、数据库结构和文件结构的备份。完整备份代表的是备份完成时刻的数据库. 完整备份是备份的基础，提供了任何其他备份的基准.其他备份（如差异备份）只有在执行完整备份之后才能被执行。

2.差异备份

差异备份是指对上一次完整备份之后所有更改的数据做备份，备份过程能够识别出数据库中哪一部分被修改了，并只对这一部分做备份。差异备份的优势是速度快，备份数据库所需要的时间很少，但差异备份要求事先已执行过一次完整备份.

3.事务日志备份

事务日志备份记录了数据库的所有改变。在备份事务日志时需要考虑以下因素。

>在执行了至少一次完整备份后，才能备份事务日志，

>在简单恢复模式下不能备份事务日志。

>如果没有与其相一致的数据库备份，则不能恢复事务日志。

在执行数据库事务日志备份期间，SQLServer将做以下工作。

>备份事务日志：从上一次成功的事务日志备份到当前事务日志的尾部.

>截断事务日志：到事务日志活动部分的开始，丢弃不活动的部分。

此外，事务日志备份主要是T-SQL.语句，而不是整个数据库结构，文件结构或数据。

恢复模式

不管是备份还是还原，都是在一定的恢复模式中进行的。恢复模式是数据库的一种特性，控制着数据库备份和还原的基本行为，数据库管理员应根据企业系统实际运作的需求及对数据可用性和恢复的需求来选择合适的恢复模式。 SQL Server 提供了三种恢复模式，分别是简单恢复模式，完整恢复模式和大容量日志恢复模式.

1.简单恢复模式

在简单恢复模式下，不活动的日志将被删除，所以不支持事务日志备份，进行了一些数据库备份。在最近的备份t5之后的一段时间.此数据库中出现了数据丢失，数据库管理员将使用t5备份来将数据库还原到备份完成的时间点，之后对数据库进行的更改都将丢失。

由于不支持事务日志备份，数据库只能恢复到备份的时间点，而无法将数据库还原到故障点或特定的时间点.因此，简单恢复模式主要用于小型数据库和不经常更改的数据库.。

2.完整恢复模式

对于十分重要的生产数据库，如银行、电信系统，在发生故障时可能要求恢复到历史上某个时刻。一旦发生故障时，必须保证数据不丢失，保证数据能够恢复到发生故障时的状态，这样，就必须采用完整恢复模式. 完整恢复模式可在最大范围内防止出现故障时丢失数据，它包括数据库备份和事务日志备份，并提供全面保护，使数据库免受媒体故障的影响。这个模式使用数据库和所有日志的备份去恢复数据库，如果日志没有损坏，SQL Server可以恢复所有数据，除了在失败那一刻的事务。由于支持事务日志备份，因此完整恢复模式可以将数据库恢复到任意一个指定的时间点。

执行了一个数据库备份（Db_1）和两个例行的日志备份（Log_1和Log_2）.在执行Log_2日志备份后，数据库中的数据发生丢失。在还原这三个备份之前，数据库管理员必须先备份日志尾部，然后还原Db_1.Log_1和Log_2.接着还原并恢复尾日志备份（Tail).这能将数据库恢复到故障点，从而恢复所有数据。恢复操作可以还原所有已提交的事务，正在进行的事务将回滚。

3.大容量日志恢复模式

DBA在某些场合需要对数据库执行一些大批量的数据插入.更新或删除操作，如一次需要导入上百万条数据。如果在完整恢复模式下，那么这些操作将产生大量的日志记录，导致数据库性能很低。在这种环境下，可以采用大容量日志恢复模式来提高性能.

虽然大容量日志恢复模式会完整地记录其他事务，但它只对大容量操作进行最小记录，大容量日志恢复模式是对完整恢复模式的补充，提供了良好的性能，且占用较少的日志空间，但是，大容量日志恢复模式增加了这些大容量复制操作丢失数据的风险，因为最小日志记录大容量操作不会逐个记录事务。只要日志备份包含大容量操作，数据库就只能恢复到日志备份的结尾，而不能恢复到某个时间点或日志备份中某个标记的事务。