Why opensource database like Postgresql and Mysql don't have encrypted stored proc?

Question

Why opensource database like Postgresql and Mysql don't have encrypted stored proc?

Is it because of their innate open source philosophy?

What are the compelling reasons to encrypt the stored procs?

Answer 1

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/

Answer 2

Properly because nobody has had a need for it.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

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.

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.

I don't know if the NULL-out-the-source-BLOB trick is possible in MySQL or PostgreSQL.

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.

Answer 7

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.

Answer 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.

Answer 9

Hi Bill Karwin, (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.

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.

My 2 cents