SQL Server 2005+对象加密的缺点?

时间:2022-12-30 23:44:00

For a database that is shipped with a remotely deployed product, are there any drawbacks to encrypting all code objects (procs, functions, views) via the WITH ENCRYPTION clause in MS SQL Server 2005 and 2008?

对于远程部署产品附带的数据库,通过MS SQL Server 2005和2008中的WITH ENCRYPTION子句加密所有代码对象(过程,函数,视图)是否有任何缺点?

The advantages are a deterrent to "custom" changes in the field that solve one need while creating many more. The goal is not to protect IP or data, but to help prevent unauthorized code changes when the rights to alter objects cannot be managed (the security is out of the developers hands, but the responsibility to fix issues caused by others is).

优势是对领域中“自定义”变化的威慑,解决了创造更多需求的需求。目标不是保护IP或数据,而是在无法管理更改对象的权限时帮助防止未经授权的代码更改(安全性不在开发人员手中,但是修复由其他人引起的问题的责任是)。

What new problems could be caused by taking this approach?

采用这种方法可能导致哪些新问题?

4 个解决方案

#1


Microsoft created this feature specifically for this purpose. The only drawback I can see is that the protection is weak; it can be circumvented by publically available scripts, and SQL Profiler can be used to view the clear text of the decrypted procedures. But it should still act as an effective deterrent.

Microsoft专门为此目的创建了此功能。我能看到的唯一缺点就是保护力弱;它可以通过公开可用的脚本来规避,SQL Profiler可用于查看解密过程的明文。但它仍然应该起到有效的威慑作用。

#2


We implemented encryption in an enterprise product that we deployed to 200+ sites for the reasons you indicated and to deter reverse engineering.

我们在企业产品中实施加密,我们根据您指示的原因部署到200多个站点,并阻止逆向工程。

In our case, it was more trouble than it was worth. Our clients never really changed anything and it made it difficult for us to debug issues in the field. Profiler does not give you the detail needed when everything is encrypted. We would often have to load an unencrypted version of our SPROCs etc. to debug issues.

在我们的案例中,它比它的价值更麻烦。我们的客户从未真正改变过任何事情,这使我们很难在现场调试问题。在加密所有内容时,Profiler不会为您提供所需的详细信息。我们经常需要加载未加密版本的SPROC等来调试问题。

Also, it makes it harder to varify your schema is consistant with the version you are expecting. Most differencing tools can't diff encrypted objects.

此外,它使您的架构变得更难以与您期望的版本一致。大多数差异工具都无法区分加密对象。

#3


You are down the wrong path. WITH ENCRYPTION is an obsolete option from the SQL 2000 days. The ways around it are well known and can be discovered by anyone with minimal google search skills. The proper way is to use code signing. Best way is to create a certificate, sign the procedures, then drop the private key. This way nobody, not even you, can change code afterward without destroying the signatures. This does not prevent anybody from modifying the procedures, but once modified they destroy the signature seal and the tampering is evident. You can take it one step further and make sure the procedures in themselves have no authority to execute their purpose (ie. cannot read the tables the try to access) but the authority is derived from their signatures (the certificate used to sign them is granted the needed rights). This way if they tamper with the code not only they destroy the seal, they also cause the system to halt. Explaining the consequences of these actions is usually a strong enough deterrent.

你走错了路。 WITH ENCRYPTION是SQL 2000天的一个过时选项。围绕它的方式是众所周知的,任何具有最小谷歌搜索技能的人都可以发现它。正确的方法是使用代码签名。最好的方法是创建证书,签署程序,然后删除私钥。这样,没有人,甚至你,都可以在不破坏签名的情况下改变代码。这并不妨碍任何人修改程序,但一旦修改它们就会破坏签名封条并且篡改是显而易见的。您可以更进一步,确保程序本身无权执行其目的(即无法读取尝试访问的表)但权限来自其签名(用于签署它们的证书被授予所需的权利)。这样,如果他们篡改代码,不仅会破坏密封,还会导致系统停止运行。解释这些行为的后果通常具有足够的威慑力。

#4


There are many tools to decrypt stored procedures inside SQL Server. There is commercially available tool called SQL Shield that alter encryption mechanism and makes it much harder to reveal and change the source code. However I must say encrypting procedures makes it harder to maintain: compare, make diffs , etc.

有许多工具可以解密SQL Server中的存储过程。有一种名为SQL Shield的商用工具可以改变加密机制,使得更难以显示和更改源代码。但是我必须说加密程序使维护更难:比较,制作差异等。

#1


Microsoft created this feature specifically for this purpose. The only drawback I can see is that the protection is weak; it can be circumvented by publically available scripts, and SQL Profiler can be used to view the clear text of the decrypted procedures. But it should still act as an effective deterrent.

Microsoft专门为此目的创建了此功能。我能看到的唯一缺点就是保护力弱;它可以通过公开可用的脚本来规避,SQL Profiler可用于查看解密过程的明文。但它仍然应该起到有效的威慑作用。

#2


We implemented encryption in an enterprise product that we deployed to 200+ sites for the reasons you indicated and to deter reverse engineering.

我们在企业产品中实施加密,我们根据您指示的原因部署到200多个站点,并阻止逆向工程。

In our case, it was more trouble than it was worth. Our clients never really changed anything and it made it difficult for us to debug issues in the field. Profiler does not give you the detail needed when everything is encrypted. We would often have to load an unencrypted version of our SPROCs etc. to debug issues.

在我们的案例中,它比它的价值更麻烦。我们的客户从未真正改变过任何事情,这使我们很难在现场调试问题。在加密所有内容时,Profiler不会为您提供所需的详细信息。我们经常需要加载未加密版本的SPROC等来调试问题。

Also, it makes it harder to varify your schema is consistant with the version you are expecting. Most differencing tools can't diff encrypted objects.

此外,它使您的架构变得更难以与您期望的版本一致。大多数差异工具都无法区分加密对象。

#3


You are down the wrong path. WITH ENCRYPTION is an obsolete option from the SQL 2000 days. The ways around it are well known and can be discovered by anyone with minimal google search skills. The proper way is to use code signing. Best way is to create a certificate, sign the procedures, then drop the private key. This way nobody, not even you, can change code afterward without destroying the signatures. This does not prevent anybody from modifying the procedures, but once modified they destroy the signature seal and the tampering is evident. You can take it one step further and make sure the procedures in themselves have no authority to execute their purpose (ie. cannot read the tables the try to access) but the authority is derived from their signatures (the certificate used to sign them is granted the needed rights). This way if they tamper with the code not only they destroy the seal, they also cause the system to halt. Explaining the consequences of these actions is usually a strong enough deterrent.

你走错了路。 WITH ENCRYPTION是SQL 2000天的一个过时选项。围绕它的方式是众所周知的,任何具有最小谷歌搜索技能的人都可以发现它。正确的方法是使用代码签名。最好的方法是创建证书,签署程序,然后删除私钥。这样,没有人,甚至你,都可以在不破坏签名的情况下改变代码。这并不妨碍任何人修改程序,但一旦修改它们就会破坏签名封条并且篡改是显而易见的。您可以更进一步,确保程序本身无权执行其目的(即无法读取尝试访问的表)但权限来自其签名(用于签署它们的证书被授予所需的权利)。这样,如果他们篡改代码,不仅会破坏密封,还会导致系统停止运行。解释这些行为的后果通常具有足够的威慑力。

#4


There are many tools to decrypt stored procedures inside SQL Server. There is commercially available tool called SQL Shield that alter encryption mechanism and makes it much harder to reveal and change the source code. However I must say encrypting procedures makes it harder to maintain: compare, make diffs , etc.

有许多工具可以解密SQL Server中的存储过程。有一种名为SQL Shield的商用工具可以改变加密机制,使得更难以显示和更改源代码。但是我必须说加密程序使维护更难:比较,制作差异等。