如何模糊SQL Server数据库架构？ [英] Way to obscure SQL Server database schema?

问题描述

有什么方法可以掩盖SQL Server上数据库的架构吗？

Is there any way to obscure the schema of a database on SQL Server?

如果我在客户端站点上安装了SQL Server Express，是否可以使模式和数据模糊不清，以使其他人无法进入模式并学习模式，以从中抽取数据并将其提取到另一个产品中？

If I have SQL Server Express installed on a client site, is there a way to obscure the schema and data so that someone else cannot come along and learn the schema in order to extract data out of it and into another product?

推荐答案

这是一个棘手的问题，甚至可能无法100％完成。但是，有一些技巧可以设置它：

This is a tricky one and may not even be 100% possible. However, there are a few tricks to setting it up:

1. 使用以下命令安装新的 named SQL Server实例：定制的SA帐户（名称和密码）。 SQL Server有一种安装方法，称为无人参与安装，它允许您在ini文件中指定SQL Server的所有安装参数，然后以静默方式运行安装。在此处查看文档：无人参与的SQL Server 2008 r2安装


2. 使用魔术SQL安装脚本创建数据库，表，过程等（如果需要，请使用加密的存储过程，但它们也很容易破解）


3. 为 custom SA帐户添加/验证架构权限，并为所有管理员角色删除所有架构权限。此处的目标是没有 roles 对您的数据库具有任何模式权限，而只有您的自定义SA user 具有权限（不是按角色分配，而是直接分配给用户）。 / li>
   

1. Install a new named instance of SQL server with a custom SA account (both name and password). There is an installation method for SQL server call "Unattended Installation" which allows you to specify all the installation parameters for SQL server in an ini file and then run the install silently. Check out the documentation here: Unattended Installation of SQL Server 2008 r2
2. Create your database, tables, procedures, etc. with your magic SQL install script (use encrypted stored procs if you want, but they too are crackable)
3. Add/Verify the schema permissions for the custom SA account and Drop all schema permissions for all Administrator roles. The goal here is that no roles have any schema permissions to your database and only your custom SA user has permission (not assigned by role, but directly to the user).

我知道有几个商业应用程序甚至没有告诉您它们正在安装MS SQL Express实例。他们也将使用命名的SA帐户创建自己的命名实例。我不能说我喜欢作为客户（因为SQL严重影响了CPU，并且我不希望在工作站上运行秘密实例）。但是，只要您提前向客户披露此信息，他们就可以理解。

There are several commercial applications that I know of that don't even tell you they are installing an instance of MS SQL express. They too will create their own named instance with a named SA account. I can't say I like that as a customer (as SQL takes a hit on the CPU and I don't want "secret" instances running on my workstation). But so long as you disclose this to your customers upfront, they may understand.

**请记住，熟练的DBA可能会弄乱系统表以及什么知识不要手动授予对数据库的访问权限。这些技术实际上只是模糊处理，不会百分百证明。

**Keep in mind a skilled DBA may have the knowledge to mess with system tables and what not to manually grant access to your database. These techniques really are just "obfuscation" and won't be 100% bullet proof.

注意：由于拥有大量可用的第三方数据层和网络服务技术，我认为许多公司发现仅凭其数据库架构已不再那么专有或有价值。曾经有一段时间，仅数据库模式就可以代表数百小时的编码。但是如今，诸如EntityFramework，NHibernate，Linq-to-SQL，XPO等工具都基于软件类定义和代码属性为您创建数据库架构。因此，仅查看数据库表并不是很有价值。另外，您可能会在软件中编写不在数据库架构中的大量业务逻辑，统计分析或其他辅助方法。我认为，这是在您的软件的业务逻辑，分析和报告功能中而不是在原始数据表中找到当今 增值的地方。

As a side note: With the plethora of available 3rd party datalayers and webservice technologies, I think many companies are finding their database schema alone isn't so proprietary or valuable anymore. There was a time when the database schema alone could have represented hundreds of hours of coding. But today tools like EntityFramework, NHibernate, Linq-to-SQL, XPO, etc all create your database schema for you based on your software class definitions and in code attributes. So just seeing a DB table isn't really very valuable. Plus you might write a bunch of business logic, statistical analysis or other helper methods in your software that aren't in your database schema. In my opinion, this is where today's "value add" is found, in the business logic, analysis and reporting functionality of your software - not in the raw datatables.

这也是为什么另一个发布者建议混淆存储过程的原因，因为如果您编写了一些不错的分析和报告过程，则存储过程可能是数据库架构本身工作的许多倍。这也是客户最可能希望根据自己的报告需求进行自定义的内容。您可能倾向于制定一项政策，即只能由您的公司完成自定义报告（嘿，即使是像SAP这样的大公司也对谁可以修改内容持粘性）。

This is also why another poster recommended obfuscating stored procedures, because these could be many times the work of the database schema itself if you have some nice analysis and reporting procedures written up. Its also what customer's would most likely want to customize for their own reporting needs. You may be inclined to have a policy that custom reporting can only be done by your company (hey, even the big guys like SAP are sticky with who can modify what).

这篇关于如何模糊SQL Server数据库架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！