Why opensource database like Postgresql and Mysql don't have encrypted stored proc?
为什么像Postgresql和Mysql这样的开源数据库没有加密的存储过程?
Is it because of their innate open source philosophy?
是因为他们天生的开源哲学吗?
What are the compelling reasons to encrypt the stored procs?
加密存储过程有哪些令人信服的理由?
9 个解决方案
#1
While I'm sure encrypted stored procs are a simple matter of programming in PostgreSQL at least (MySQL and stored procs do not have a long history together), with PgSQL's custom languages support, I don't see why this would be useful. They would need to be decrypted at some point to execute them so anyone who administers the database/database server would be able to, with sufficient skill, be able to get the unencrypted sp. The DBA can already see all the data, all the relationships, seemingly making obfuscating the SP code pointless.
虽然我确信加密的存储过程是一个简单的PostgreSQL编程问题(MySQL和存储过程并没有很长的历史),而PgSQL的自定义语言支持,我不明白为什么这会有用。他们需要在某些时候解密才能执行它们,因此管理数据库/数据库服务器的任何人都能够以足够的技巧获得未加密的sp。 DBA已经可以看到所有数据,所有关系,似乎使SP代码混淆无意义。
#2
if this is a really cool feature (I have never used it, nor needed it), I suggest you register a new feature request in the repected mailing lists.
如果这是一个非常酷的功能(我从未使用它,也不需要它),我建议你在备受推荐的邮件列表中注册一个新的功能请求。
Postgresql: http://www.postgresql.org/community/lists/
MySQL: http://lists.mysql.com/
#3
Properly because nobody has had a need for it.
正确的是因为没有人需要它。
#4
Because encrypted stored procedures are a distinctly bad idea.
因为加密的存储过程是一个明显不好的主意。
Business programming should not be centered around the DBA who has sole access to the entirety of the application code, which is written in SQL and stored, encrypted, in the database. That way lies madness.
业务编程不应该以DBA为中心,DBA只能访问整个应用程序代码,这些代码使用SQL编写并在数据库中存储,加密。那种方式就是疯狂。
#5
I guess the most important reason is that open source databases are written by really smart people. Who know, that encrypting the procedure doesn't actually protect you from some rogue admin getting access to unencrypted source of it.
我想最重要的原因是开源数据库是由非常聪明的人编写的。谁知道,加密程序实际上并没有保护你免受某些流氓管理员访问未加密的来源的影响。
In proprietary software world, people tend to think that "encrypting will solve the problem". But when you'll consider the fact that your source of database is open, it means that encrypting has to be done in a really smart way, and doesn't work in all cases.
在专有软件世界中,人们倾向于认为“加密将解决问题”。但是,当您考虑到数据库源是开放的这一事实时,这意味着加密必须以非常智能的方式完成,并且在所有情况下都不起作用。
Basically - if you'd want to protect your data against "disk being stolen" - you can use hard disk encryption. If you want to protect backups - encrypt backups. But if you want to protect the database against "too curious" admin - hire lawyers, and not try to think about (more or less) impossible ways to hide the data.
基本上 - 如果您想保护您的数据免受“磁盘被盗” - 您可以使用硬盘加密。如果要保护备份 - 加密备份。但是,如果你想保护数据库免受“过于好奇”的管理员 - 聘请律师,而不是试图(或多或少)不可能的方式来隐藏数据。
#6
To answer the "compelling reasons to encrypt the stored procs?" question I've seen this used when an application uses fairly complex proprietary logic in the stored procedures and the application is hosted on a customers database system.
回答“加密存储过程的令人信服的理由?”问题我已经看到,当应用程序在存储过程中使用相当复杂的专有逻辑并且应用程序托管在客户数据库系统上时,会使用此问题。
I am aware that this is not a perfect solution but it stops trivial attempts to rip off the code or prevent the clients DBA from altering stuff they shouldn't be touching.
我知道这不是一个完美的解决方案,但它会阻止扯掉代码或阻止客户DBA改变他们不应该触摸的东西的琐碎尝试。
#7
When I used to provide support for InterBase, this question would come up from time to time. The reason customers wanted this feature is to protect their investment of "intellectual property" -- i.e. source code -- when they develop commercial software applications.
当我以前为InterBase提供支持时,这个问题会不时出现。客户想要这个功能的原因是为了保护他们在开发商业软件应用程序时对“知识产权”(即源代码)的投资。
Of course you can restrict access to the source code of a compiled language like C++, Java, or Delphi. A customer can reverse-engineer compiled code and learn something of your algorithms, but that's not the same as having access to the source code. There's a lot of information they don't get by decompiling.
当然,您可以限制对C ++,Java或Delphi等编译语言的源代码的访问。客户可以对编译的代码进行逆向工程并学习一些算法,但这与访问源代码不同。他们通过反编译无法获得大量信息。
But if you implement parts of your application in the bodies of triggers and stored procedures, these remain readable by any customer who buys the software product. There might be ways to obscure or encrypt this code, but it cannot be irreversible encryption, or else the database engine wouldn't be able to run the code.
但是,如果您在触发器和存储过程的实体中实现应用程序的某些部分,那么购买该软件产品的任何客户都可以读取这些内容。可能存在隐藏或加密此代码的方法,但它不能是不可逆转的加密,否则数据库引擎将无法运行代码。
InterBase stored a copy of the trigger/proc source in a BLOB field, and a compiled version of the same routine in another BLOB. So you could NULL out the BLOB field containing the source, and leave the compiled code. But this is only as effective as shipping compiled application code; it can still be reverse-engineered by a pirate with enough skill and motivation.
InterBase在BLOB字段中存储了触发器/ proc源的副本,在另一个BLOB中存储了相同例程的编译版本。因此,您可以将包含源的BLOB字段清空,并保留已编译的代码。但这仅与运送已编译的应用程序代码一样有效;它仍然可以由具有足够技能和动力的海盗进行逆向工程。
I don't know if the NULL-out-the-source-BLOB trick is possible in MySQL or PostgreSQL.
我不知道在MySQL或PostgreSQL中是否可以使用NULL-out-the-source-BLOB技巧。
The bottom line is:
底线是:
There is no way to absolutely restrict access to data or metadata in a database after you hand it over to a customer.
And it's worth noting that this is an artificial requirement. I've never heard of anyone who had their software IP stolen due to having readable code in triggers & stored procedures. It's not necessary anyway. A pirate can use other means to duplicate your software.
值得注意的是,这是一个人为的要求。我从来没有听说过因为触发器和存储过程中的可读代码而导致软件IP被盗的人。反正没有必要。盗版者可以使用其他方式复制您的软件。
#8
Here is, imho, a good scenario for encrypting stored procedures/views. The company I work for writes a database driven app. We house the database on the client network on a sql server or a workstation running SQL Express (for smaller clients with less users). The stored procedures and views are used to audit data given to us by the client. In effort to keep the client's DBA or IT staff from simply ripping us off or stealing the proprietary algorithms, we encrypt the stored procedures and views. We maintain the original, plaintext scripts in our version control software (ie Visual Source Safe) so that a support team from our end has access to see how the procedure or view is coded. I wish some of the Open Source engines supported it so we could move away from the limitations of SQL Express.
这是imho,一种加密存储过程/视图的好方案。我工作的公司写了一个数据库驱动的应用程序。我们在sql server或运行SQL Express的工作站上为客户端网络提供数据库(对于用户较少的较小客户端)。存储过程和视图用于审核客户端提供给我们的数据。为了防止客户的DBA或IT人员仅仅将我们剥离或窃取专有算法,我们对存储过程和视图进行加密。我们在我们的版本控制软件(即Visual Source Safe)中维护原始的纯文本脚本,以便我们的支持团队可以访问以查看过程或视图的编码方式。我希望一些开源引擎支持它,这样我们就可以摆脱SQL Express的限制。
#9
(RE: bold text) I don't think this is quite right. If you have ever looked at ACT! contact management they install MSSQL Express as a separate instance and use a private username & password for SQL and then the db. So there are ways of not sharing the database with your clients.
(RE:粗体文字)我不认为这是对的。如果你曾经看过ACT!联系人管理他们将MSSQL Express作为单独的实例安装,并使用SQL的私有用户名和密码,然后使用db。因此,有一些方法可以不与您的客户共享数据库。
Not to start a religious war, but db servers like MSSQL has proc encryption capabilities. The idea being, if you are an ISV you can deploy your solution with encrypted procs to protect your IP. As a DBA you cannot unencrypt these procs.
不要发动宗教战争,但像MSSQL这样的数据库服务器具有proc加密功能。我们的想法是,如果您是ISV,您可以使用加密过程部署您的解决方案以保护您的IP。作为一名DBA,你无法解密这些过程。
My 2 cents
我的2美分
#1
While I'm sure encrypted stored procs are a simple matter of programming in PostgreSQL at least (MySQL and stored procs do not have a long history together), with PgSQL's custom languages support, I don't see why this would be useful. They would need to be decrypted at some point to execute them so anyone who administers the database/database server would be able to, with sufficient skill, be able to get the unencrypted sp. The DBA can already see all the data, all the relationships, seemingly making obfuscating the SP code pointless.
虽然我确信加密的存储过程是一个简单的PostgreSQL编程问题(MySQL和存储过程并没有很长的历史),而PgSQL的自定义语言支持,我不明白为什么这会有用。他们需要在某些时候解密才能执行它们,因此管理数据库/数据库服务器的任何人都能够以足够的技巧获得未加密的sp。 DBA已经可以看到所有数据,所有关系,似乎使SP代码混淆无意义。
#2
if this is a really cool feature (I have never used it, nor needed it), I suggest you register a new feature request in the repected mailing lists.
如果这是一个非常酷的功能(我从未使用它,也不需要它),我建议你在备受推荐的邮件列表中注册一个新的功能请求。
Postgresql: http://www.postgresql.org/community/lists/
MySQL: http://lists.mysql.com/
#3
Properly because nobody has had a need for it.
正确的是因为没有人需要它。
#4
Because encrypted stored procedures are a distinctly bad idea.
因为加密的存储过程是一个明显不好的主意。
Business programming should not be centered around the DBA who has sole access to the entirety of the application code, which is written in SQL and stored, encrypted, in the database. That way lies madness.
业务编程不应该以DBA为中心,DBA只能访问整个应用程序代码,这些代码使用SQL编写并在数据库中存储,加密。那种方式就是疯狂。
#5
I guess the most important reason is that open source databases are written by really smart people. Who know, that encrypting the procedure doesn't actually protect you from some rogue admin getting access to unencrypted source of it.
我想最重要的原因是开源数据库是由非常聪明的人编写的。谁知道,加密程序实际上并没有保护你免受某些流氓管理员访问未加密的来源的影响。
In proprietary software world, people tend to think that "encrypting will solve the problem". But when you'll consider the fact that your source of database is open, it means that encrypting has to be done in a really smart way, and doesn't work in all cases.
在专有软件世界中,人们倾向于认为“加密将解决问题”。但是,当您考虑到数据库源是开放的这一事实时,这意味着加密必须以非常智能的方式完成,并且在所有情况下都不起作用。
Basically - if you'd want to protect your data against "disk being stolen" - you can use hard disk encryption. If you want to protect backups - encrypt backups. But if you want to protect the database against "too curious" admin - hire lawyers, and not try to think about (more or less) impossible ways to hide the data.
基本上 - 如果您想保护您的数据免受“磁盘被盗” - 您可以使用硬盘加密。如果要保护备份 - 加密备份。但是,如果你想保护数据库免受“过于好奇”的管理员 - 聘请律师,而不是试图(或多或少)不可能的方式来隐藏数据。
#6
To answer the "compelling reasons to encrypt the stored procs?" question I've seen this used when an application uses fairly complex proprietary logic in the stored procedures and the application is hosted on a customers database system.
回答“加密存储过程的令人信服的理由?”问题我已经看到,当应用程序在存储过程中使用相当复杂的专有逻辑并且应用程序托管在客户数据库系统上时,会使用此问题。
I am aware that this is not a perfect solution but it stops trivial attempts to rip off the code or prevent the clients DBA from altering stuff they shouldn't be touching.
我知道这不是一个完美的解决方案,但它会阻止扯掉代码或阻止客户DBA改变他们不应该触摸的东西的琐碎尝试。
#7
When I used to provide support for InterBase, this question would come up from time to time. The reason customers wanted this feature is to protect their investment of "intellectual property" -- i.e. source code -- when they develop commercial software applications.
当我以前为InterBase提供支持时,这个问题会不时出现。客户想要这个功能的原因是为了保护他们在开发商业软件应用程序时对“知识产权”(即源代码)的投资。
Of course you can restrict access to the source code of a compiled language like C++, Java, or Delphi. A customer can reverse-engineer compiled code and learn something of your algorithms, but that's not the same as having access to the source code. There's a lot of information they don't get by decompiling.
当然,您可以限制对C ++,Java或Delphi等编译语言的源代码的访问。客户可以对编译的代码进行逆向工程并学习一些算法,但这与访问源代码不同。他们通过反编译无法获得大量信息。
But if you implement parts of your application in the bodies of triggers and stored procedures, these remain readable by any customer who buys the software product. There might be ways to obscure or encrypt this code, but it cannot be irreversible encryption, or else the database engine wouldn't be able to run the code.
但是,如果您在触发器和存储过程的实体中实现应用程序的某些部分,那么购买该软件产品的任何客户都可以读取这些内容。可能存在隐藏或加密此代码的方法,但它不能是不可逆转的加密,否则数据库引擎将无法运行代码。
InterBase stored a copy of the trigger/proc source in a BLOB field, and a compiled version of the same routine in another BLOB. So you could NULL out the BLOB field containing the source, and leave the compiled code. But this is only as effective as shipping compiled application code; it can still be reverse-engineered by a pirate with enough skill and motivation.
InterBase在BLOB字段中存储了触发器/ proc源的副本,在另一个BLOB中存储了相同例程的编译版本。因此,您可以将包含源的BLOB字段清空,并保留已编译的代码。但这仅与运送已编译的应用程序代码一样有效;它仍然可以由具有足够技能和动力的海盗进行逆向工程。
I don't know if the NULL-out-the-source-BLOB trick is possible in MySQL or PostgreSQL.
我不知道在MySQL或PostgreSQL中是否可以使用NULL-out-the-source-BLOB技巧。
The bottom line is:
底线是:
There is no way to absolutely restrict access to data or metadata in a database after you hand it over to a customer.
And it's worth noting that this is an artificial requirement. I've never heard of anyone who had their software IP stolen due to having readable code in triggers & stored procedures. It's not necessary anyway. A pirate can use other means to duplicate your software.
值得注意的是,这是一个人为的要求。我从来没有听说过因为触发器和存储过程中的可读代码而导致软件IP被盗的人。反正没有必要。盗版者可以使用其他方式复制您的软件。
#8
Here is, imho, a good scenario for encrypting stored procedures/views. The company I work for writes a database driven app. We house the database on the client network on a sql server or a workstation running SQL Express (for smaller clients with less users). The stored procedures and views are used to audit data given to us by the client. In effort to keep the client's DBA or IT staff from simply ripping us off or stealing the proprietary algorithms, we encrypt the stored procedures and views. We maintain the original, plaintext scripts in our version control software (ie Visual Source Safe) so that a support team from our end has access to see how the procedure or view is coded. I wish some of the Open Source engines supported it so we could move away from the limitations of SQL Express.
这是imho,一种加密存储过程/视图的好方案。我工作的公司写了一个数据库驱动的应用程序。我们在sql server或运行SQL Express的工作站上为客户端网络提供数据库(对于用户较少的较小客户端)。存储过程和视图用于审核客户端提供给我们的数据。为了防止客户的DBA或IT人员仅仅将我们剥离或窃取专有算法,我们对存储过程和视图进行加密。我们在我们的版本控制软件(即Visual Source Safe)中维护原始的纯文本脚本,以便我们的支持团队可以访问以查看过程或视图的编码方式。我希望一些开源引擎支持它,这样我们就可以摆脱SQL Express的限制。
#9
(RE: bold text) I don't think this is quite right. If you have ever looked at ACT! contact management they install MSSQL Express as a separate instance and use a private username & password for SQL and then the db. So there are ways of not sharing the database with your clients.
(RE:粗体文字)我不认为这是对的。如果你曾经看过ACT!联系人管理他们将MSSQL Express作为单独的实例安装,并使用SQL的私有用户名和密码,然后使用db。因此,有一些方法可以不与您的客户共享数据库。
Not to start a religious war, but db servers like MSSQL has proc encryption capabilities. The idea being, if you are an ISV you can deploy your solution with encrypted procs to protect your IP. As a DBA you cannot unencrypt these procs.
不要发动宗教战争,但像MSSQL这样的数据库服务器具有proc加密功能。我们的想法是,如果您是ISV,您可以使用加密过程部署您的解决方案以保护您的IP。作为一名DBA,你无法解密这些过程。
My 2 cents
我的2美分