三空道人的博客

原创 图说 SQL 的 JOIN

OUTER 通常省略，所以我们在查询语句中看到的大都是 FULL JOIN、LEFT JOIN、RIGHT JOIN。可以看到id 的值2、5是TableA 独有的，而id=1是TableB仅有的；全连接，返回两个表全部数据，不存在的以NULL表示。

原创 SQL Server 动态数据掩藏（Dynamic Data Masking）探索和实施

动态数据掩藏（DDM）是SQL Server 2016 CTP 2.1引入的新功能数据库加密技术本质上改变了数据存储结构，而DDM只是在返回客户端的时候对数据进行隐藏。sysadmin的成员登陆账户或者db_owner角色的用户有查看DDM数据的权限。数据库迁移助手评估DDM候选者您的数据库包含需要对终端用户掩藏的数据，但是您并不能确定哪些列为Dynamic Data Masking（DDM）候选者。我们如何确认这些列并实施DDM来保护这些数据？您可以使用Databa...

原创 如何在SSMS中查看Always Encrypted 加密列的解密数据

通常情况下，我们在SSMS客户端看到Always Encrypted 加密列的数据都是密文的。那么是否可以查看解密后的数据呢？如何配置才能实现呢？本文将阐述该过程的实现。要在SSMS客户端查看Always Encrypted加密列解密后的数据，需要三个前提： 对应数据库需要合适的权限 客户端所在计算机安装Always Encrypted证书 设置连接属性 权限设置要查看数据，对象SELECT权限是必要的，其次要有查看列主秘钥和列加密秘钥的权限--创建登陆账户.

原创 SQL Server Always Encrypted

过去SQL Server有多种加密数据的方式，如透明数据加密（TDE）。这种技术是在数据库文件或者备份被盗用时，保护静态数据。然而对于可以访问数据库本身，或者任何拥有数据库的用户，可以获取秘钥、证书和密码（系统管理员、黑客诸如此类），是没有效果的。SQL Server 2016新引入了Always Encrypted 功能，其设计的目的即时保护敏感数据，如手机号、身份证、银行卡号等等，可以同时加密静态和动态数据（内存中的数据也会被加密）。因此，这可以保护数据免受流氓管理员、备份窃贼和中间人攻击。和TDE

原创 SQL Server 安全漏洞评估工具

为避免SQL Server遭受攻击，组织必须不断评估数据库系统来确定是否存在漏洞。这是一种有效的主动防御方法，用于分析数据库中的潜在漏洞及其与标准设置的偏差，如太大的权限、敏感数据的暴露、错误配置等。这也是满足遵循法规的需求。安全合规，辨别确认安全漏洞对组织来说是非常重要的。一些可能的数据库漏洞： 用户和组权限过大 敏感数据，如信用卡、银行账户等 SQL注入 实例或数据库配置 孤立数据库用户、角色或组 此前，使用SQL Server，我们需要使用自定义.

原创 SQL Server 扩展秘钥管理（EKM）

无论是SQL Server对称秘钥、非对称秘钥管理，还是使用对称秘钥或非对称秘钥对数据进行加密解密，以及数据库TDE，我们都有提到EKM（Extensible Key Manager），那么什么是EKM呢？为何要使用EKM呢？请看下文分享。SQL Server 的EKM 使得第三方EKM/HSM 供应商能够在SQL Server中注册他们的模块。当注册完成后，SQL Server 用户可以使用存储在EKM模块上的秘钥。这使得SQL Server可以使用这些模块提供的高级功能，如批量加密和解密，钥匙管..

原创 SQL Server 数据加密实战——应用用户密码的加密和验证

在应用开发中，我们会经常遇到一个问题，即用户密码如何加密？登录应用时如何验证用户密码的正确性？本文将使用EncryptByPassPhrase函数实现这两个过程。首先我们在数据库EncryptionTest库中创建会员信息表MemberInfo，这里我们只创建两个列，一个会员编号，一个密码列，实际生产中需要更多的列来记录信息，这里就不列举了。USE EncryptionTestGOCREATE TABLE MemberInfo( MemberID INT NOT NULL PRIMARY

原创 SQL Server 数据加密：对称秘钥 VS 非对称秘钥

加密过程的区别首先我们使用两个简易的加密、解密过程图来说明对称秘钥和非对称秘钥的区别。对称秘钥加密解密图示过程：对称秘钥有且只有一个秘钥，加密解密使用相同秘钥，一旦用户获得秘钥，就可以解密任何你使用其加密的数据。非对称秘钥加密解密的过程：非对称秘钥加密算法，通常被称为公-私钥加密算法，有两个秘钥。一个可以分发给需要对数据加密的用户，称为公钥。因此如果某人想发送给你一些信息，并且要対信息进行加密，他们仅仅需要知道公钥即可。另外一个秘钥应该被安全管理，其被称为私钥。非对称秘钥算.

原创 SQL Server 非对称秘钥管理

​在《SQL Server 非对称秘钥在数据加密中的应用》一文中，我们了解到可以使用非对称秘钥进行数据加密。本文带着大家深入了解非对称秘钥的生命周期，包括创建、修改和删除。为了熟悉非对称秘钥，我们先创建一个数据库作为测试环境：CREATE DATABASE AsymmetricKeyAdmin ON PRIMARY(NAME='AsymmetricKeyAdmin',FILENAME='D:\database\AsymmetricKeyAdmin.mdf')LOG ON(NAME='Asym

原创 SQL Server 非对称秘钥在数据加密中的应用

​对称秘钥对数据加密解密使用了ENCRYPTBYMKEY、DECRYPTBYKEY两个函数，而非对称秘钥使用的加密解密函数分别是ENCRYPTBYASYMKEY、DECRYPTBYASYMKEY。本文将说明这两个函数如何对数据加密和解密，以及如何应用于生产实践中。我们仍然使用《SQL Server 对称密钥在数据加密中的应用》中创建的数据库，然后创建一个新表，存储非对称密钥加密的数据。接着从文件中创建一个非对称密钥，这里因为前文已经在EncryptionTest 数据库中创建了数据库主密钥，所以不需要再

原创 SQL Server 对称秘钥的管理

对称秘钥，加密和解密使用相同的秘钥。对称秘钥的应用：《SQL Server 对称密钥在数据加密中的应用》对称秘钥的管理包括创建、打开、修改、关闭和删除，本文将逐一使用实例进行介绍。创建对称秘钥创建对称秘钥必定要指定一种加密方式，而对称秘钥有五种加密方式，分别为证书、密码、对称秘钥、非对称秘钥和PROVIDER。我们将按照对称秘钥的加密方式给出创建秘钥的样例。为了演示创建过程，我们先创建一个测试数据库：--创建测试数据库CREATE DATABASE SymmetricKeyAdm

原创 SQL Server 对称密钥在数据加密中的应用

3月19日，网上爆出微博数据泄露。微博方亦迅速回应，表示数据泄露属实。微博表示一直有提供根据通讯录手机号查询微博好友昵称的服务，用户授权后可以使用该服务。但微博不提供用户性别和身份证号等信息，也没有“根据用户昵称查手机号”的服务。因此这起数据泄露不涉及身份证、密码，对微博服务没有影响。目前微博已经及时强化安全策略，并将不断强化。相对来说此次数据泄露引起的危险性不大，不过又引起了我们对数据安全和隐私的关注，且近几年重大的数据泄露事件不断，数据泄露带来的影响也是不可估量的，这足以引起我们

原创 SQL Server 对称秘钥的加密算法

MD5和SHA-1算法都先后被密码学家王小云破解，SQL Server对称秘钥加密算法还安全吗？前文说明TDE的时候，使用了AES_256，SQL Server对称秘钥加密算法还有有哪些？哪些算法可用，有哪些区别？当我们讨论对称秘钥的时候，这些算法或者通过输入未加密数据返回加密数据，或者输入加密数据返回解密数据。为了达到这样的目的，他们使用了秘钥，并且无论是加密还是解密，秘钥都相同。在加密算法方面，对称加密算法要比非对称加密算法快得多。因此，它们是在SQL Server中加密数据的最佳选择。SQL

原创 SQL Server 2016 启用数据库TDE 压缩性能提升

在《SQL Server 透明数据加密（TDE）的影响》一文中，我们给出启用数据库TDE的备份时，不启用压缩（COMPRESSION ）选项建议（有些片面，现在看来需要加上一些限定条件）。因为在SQL Server 2016版本以前，对TDE数据库备份时使用COMPRESSION，不但不能减少数据库备份文件占用空间，而且备份的时间、CPU都会显著升高。从 SQL Server 2016开始，启用TDE 的数据库，使用COMPRESSION选项进行备份，其压缩效果和未启用TDE之前有类似的效果。但是有个前

原创 SQL Server WITH ENCRYPTION VS TDE

在SQL Server 2014（12.x）版本中，在BACKUP DATABASE的参数中增加了ENCRYPTION参数，即备份时，对写入文件的数据进行加密。那么该参数是如何使用的呢？WITH ENCRYPTION 加密对性能有怎样的影响？其和TDE有怎样的关系，两者对性能影响情况如何？本文将通过实例说明，给出该参数用法，并与TDE进行对比。创建测试环境创建数据库test，并在test库...

原创 SQL Server还原透明加密（TDE）数据库遇到的错误

​错误1：请在数据库中创建一个主密钥或在会话中打开该主密钥执行 Transact-SQL 语句或批处理时发生了异常。在执行此操作之前，请在数据库中创建一个主密钥或在会话中打开该主密钥。错误号: 15581严重性: 16状态: 3行号: 1发生这个错误有三种情况： 确实没有创建数据库主秘钥 创建了主秘钥，但是有使用ALTERMASTERKEYDROPE...

转载 2019年SQL Server 调查报告

原创 SQL Server 透明数据加密（TDE）的影响

在开启透明数据加密之前，严谨的管理者脑中会出现一些疑问：开启TDE会有哪些影响？会不会造成数据库性能严重下降？哪些影响可以避免、如何避免？本文将一一给出解答。本文采取试验的方式来确认TDE的影响，因为试验步骤比较冗长，这里我们先将结果告知读者。值得注意的是，这里我们只做了简单的测试，其结论只可当做参考。因为您所在的环境包括物理环境（硬盘、CPU、内存）、生产环境（数据库结构、逻辑复杂度等）的不...

原创 SQL Server 透明数据加密（TDE）何时开启

从前文中，我们了解到，使用TDE是非常有必要的，但很多同行开始时可能并不知道该功能，或者知道该功能时产品已经运行较长一段时间，那么我们是否可以立刻开始进行TDE了呢？我们先来深入了解TED的过程。TDE对数据库文件的加密是在页级别实现的，被称为静态数据加密，意思是对数据和日志文件同时进行加密。对于数据的变更，TDE实施的实时加密和解密数据和日志文件的过程，先进行加密，再写入磁盘；对于已经存在的...

原创 Marriott用户信息泄露后的深思——SQL Server敏感数据审计

​当地时间3月31日万豪（Marriott）国际集团表示正在调查一起涉及多达520万客户个人信息泄露事件。在官网的声明中万豪表示涉及到的客户信息包括：姓名、公司、生日、住址、电话号码、邮箱地址、会员账号，以及积分余额、关联的航空公司等。声明同时表示：目前事件仍在调查中，目前来看，客户的支付卡信息、护照、身份证号或驾照号码等信息还没有受到影响。新浪博客前不久刚...

原创 SQL Server 2008 新增的透明数据加密功能（TDE）

在任何组织中，安全都是备受关注的。数据库备份很可能会被盗，并且可以在任何等同于或高于备份数据库版本的SQL Server实例下进行还原。当我们查看SQL Server 2008新功能时，我们发现一个可能很有趣的功能，被称为透明加密，其可以用来加密我们的数据库备份。你想了解一下该如何使用这个新功能吗？SQL Server 2008版本开始，新增了透明数据加密Transparent Data En...

原创 SQL Server 账户、数据库用户管理实战指南

作为一名SQL Server数据库DBA，你有责任保证你的SQL Server数据库的安全。最近写了一系列博文分别阐述了SQL Server 账户、数据库用户安全管理的各个点，但是感觉很零散，我们不能很好的get到如何安全管理SQL Server账户、数据库用户这个面。本文将把各个点汇集成面，希望能够在我们脑中构建出管理SQL Server账户、数据库用户的全局框架。SQL Server账户...

原创 SQL Server 中深入理解GRANT、REVOKE 和 DENY 的区别

在SQL Server数据库登陆账户、数据库用户权限管理中，经常会授予权限、回收权限，有时还会拒绝权限。GRANT、REVOKE是我们常用的，但有时会遇到使用DENY的情形。从英文单词的字面意思来看，GRANT是赋予权限，REVOKE是收回我们已经授予的权限；而DENY是禁掉某个权限。这三者内部到底有什么联系、区别？什么情景下使用GRANT？什么情景下使用REVOKE？什么情景下使用DENY？单独...

原创 解决因SQL Server数据库引用程序集而不得不开启trustworthy方法

​前文中提到有提到，当SQL Server 数据库开启trustworthy时，存在着用户提升权限的风险。那么，我们是不是直接查出开启trustworthy的数据库，将其关闭就解决风险了呢？如下脚本可以直接关闭实例下所有开启trustworthy数据库的trustworthy属性：use mastergodeclare @sql varchar(max) set @sql=''...

原创 小心被你的同事填坑

每个人都在各自权限内做事，循规蹈矩，那么将万事大吉。但当有人越权处理事情时，尤其当你不知道他在行使你的权利时，那么事情将变得非常糟糕。作为一个数据库管理员，给同事创建数据库账户并分配合适的权限，是DBA工作中不可或缺的一部分。当你给了不合适的权限，而又没有做辅助的审计措施，那么很不幸的告诉你，你将可能成为一个背锅人。下面我将通过三个场景，分享SQL Server数据库账户做越权的勾当。情...

原创 SQL Server 中sa账号的安全管理

​对于众所周知账户，如Windows的administrator账户，SQL Server 的sa账户，我们都需要确认其是否安全。下面我们给出加强sa账户安全的四种方案： 给sa强密码 修改sa名称 禁用sa账户 只使用Windows登陆验证 如下给出前三种加强sa账户安全的脚本：use mastergo--给sa强密码alter login s...

原创 创建SQL Server 身份验证登陆账户

SQL Server中共有四种类型的登陆名： SQL Server 身份验证登录名 Windows 身份验证登录名 证书映射登录名 非对称密钥映射登录名 本文主要描述常用的SQL Server 身份验证登录名的创建方式及相关内容。创建SQLServer 身份验证登陆名创建SQL Server身份验证登陆名有两种赋予密码的方式 以字...

转载 删库跑路又发生了！微盟系统惨遭运维破坏，宕机36小时

删库容易跑路难，近日，又有一起删库跑路事件发生！来自微盟官网的消息，微盟的业务系统数据库（包括主备）遭遇其公司运维人员的删除。目前微盟技术团队正在努力恢复数据，但数据恢复较慢。目前对新用户服务已经恢复正常，但老用户数据官方预计要到2月28日才有结果……微盟是一家什么样的企业？微盟是一家从事智能商业生态的互联网多元化集团企业。早期主要业务是上海...

翻译 理解SQL Server中的跨数据库所有权链

译自K. Brian Kelley的博文我已经掌握了所有权链，当我想知道跨数据库的所有权链是怎样的？其是如何工作的？如果拥有者是基于数据库的用户的，那么跨数据库的拥有者如何确定？跨数据库所有权链，是所有权链的一个扩展，除非其确实跨越了数据库的边界。如果你不熟悉所有权链，你应该从这篇《所有权链在SQL Server安全特性或安全风险》早期文章开始。跨数据库所有权链可能发生的一个实例是...

翻译 SQL Server 中的动态SQL 和所有权链

像前文一样，我试图使用所有权链，但似乎并不能正常运行。在我的案例中，我在存储过程中创建执行一个拼接SQL（动态SQL），然后使用EXEC 或者 EXEC sp_executesql。然而，当我执行字符串时，我一直收到拒绝访问的错误。两个对象在同一个架构下，每一个对象都没有显示指定拥有者。我哪里做错了呢？无论什么时候你使用EXEC 或者 EXEC sp_executesql执行SQL 字符串时，...

翻译 所有权链是SQL Server安全特性或安全风险

​译自 K. Brian Kelley的博文我听说过，SQL Server内部存在某种被称为“所有权链”的内容，但我并不知道那是什么，或者其是怎么工作的。我想知道其是一个安全风险还是一个安全特性。如果是安全特性，我怎么使用它；如果是一个风险点，我想知道如何对其进行防御。所有权链是SQL Server内部的一个安全特性，而不是安全风险点。所有对象，如表和视图，都有所有者。在SQL Serv...

原创 SQL Server数据库级权限

前面博文《SQL Server服务器级权限》介绍了服务器级安全主题的权限，本文将继其之后，来介绍一下数据库级安全主题具有的权限，以及如何赋予、拒绝数据库用户、角色权限，并介绍查看用户、角色具有哪些权限的方法。本文同样使用sys.fn_builtin_permissions来查看安全主题数据库具有的权限SELECT * FROM sys.fn_builtin_permissions('...

原创 SQL Server登陆账户、数据库用户（包含用户）及权限的自动化审计（续）

不负韶华，只争朝夕！在抗击新冠疫情的日子里，从每天关注疫情动态的恐慌，在家中的彷徨，到坚定自己的信念。也许上天认为我们走的太匆忙，希望我们静下来，重新规划一下未来；希望可以进行沉淀，以让我们走的更高、更远。在最近的日子里，在完成线上工作之余，我开始总结，并把一些经验记录下来，分享出来，和大家共勉。闲话不多说，接下来还是直接上正文。在《SQL Server登陆账户、数据库用户（包含用户）及权...

原创 SQL Server登陆账户、数据库用户（包含用户）及权限的自动化审计

登陆账户及权限变更的审计因为系统视图sys.server_permissions没有记录登陆账户权限变更的时间，同时为了更及时、全面的了解到登陆账户及权限变更的信息，保障数据库的安全，使用DDL触发器事件DDL_LOGIN_EVENTS、DDL_GDR_SERVER_EVENTS、ADD_SERVER_ROLE_MEMBER、ALTER_SERVER_ROLE、CREATE_SERVER_R...

原创 SQL Server服务器级权限

SQLServer 2008共有26个服务器级权限，分别为 ADMINISTER BULK OPERATIONS ALTER ANY CONNECTION ALTER ANY CREDENTIAL CREATE ANY DATABASE ALTER ANY DATABASE VIEW ANY DATABASE CREATE END...

原创 SQL Server 登陆账户/包含用户密码批量修改

SQL Server 数据库管理中，账户安全是数据库安全的一道重要屏障。除对密码复杂度有严格要求外，周期性修改账户密码，是保证数据库安全的一个重要举措。如下给出修改登陆账户/包含用户密码的脚本：--更换登陆帐户密码USE masterGOALTER LOGIN Jack WITH PASSWORD='I8@^mV7(Tyz0Jov',CHECK_POLICY=ON,CHECK_EXPI...

原创 克隆SQL Server 用户及权限

​《克隆 SQL Server 登陆名及权限》、《SQL Server 登陆账户权限克隆——sp_DBA_LoginClone 升级版》两文介绍了如何生成一个账户的创建脚本及权限赋予脚本，《SQL Server 登录账户权限异步同步》给出了登陆账户及权限克隆的一个应用。通过自动化管理，极大的减轻了DBA的负担，同时也降低了管理时间成本。接下来的脚本给出了数据库用户（包括包含用户）、数据库角色、应用...

原创 SQL Server 部分包含数据库

包含是针对数据库独立性而来的，非包含数据库（SQL Server 2012以前版本的所有数据库，以及SQL Server 2012及以后的数据库属性CONTAINMENT 设置为NONE的数据库）是在实例master库下进行登陆账户密码验证的；而包含数据库可以通过本身带有密码的用户直接与应用进行交互。应用场景部分包含数据库和非包含数据库最大的差异在于排序规则，即如果数据库的排序规则和实例排...

原创 SQL Server 登录账户权限异步同步

在SQL Server 故障转移（高可用方案）中，除FCI（实例级故障转移）外，无论是Always On、Replication、Log Shipping 、Databaes Mirroring，还是异步备份还原，都容易忽略一个问题，那就是SQL Server 登陆账户的同步，因为数据库级别的高可用方案都没有同步登陆账户（除了SQL Server 2012 的Contained Database，...

原创 SQL Server 登陆账户权限克隆 ——sp_DBA_LoginClone 升级版

《克隆 SQL Server 登陆名及权限》一文中对登陆名的克隆相对适用范围较窄，为更好的应用，对脚本进行了扩充和完善，具体如sp_DBA_LoginPermissionsClone。sp_DBA_LoginPermissionsClone有两个用途，区别主要在于第二、三个参数。@newLoginName和@loginName相同或者为NULL时，进行的是登陆账户权限克隆（生成的脚本账户名、权...