Freigeben über

SYSK 165: Are You Encrypting Your Stored Procedures?

  • Artikel

If you’re concerned about unauthorized users looking at the business logic implemented in SQL stored procs (e.g. deploying database to client computers or using a shared SQL server), this blog is for you.

 

SQL Server can encrypt the following components: definitions of stored procedures, views, triggers, user-defined functions, defaults, rules, as well as the data sent between the server and the client.

 

Encrypting a stored procedure is as simple as using the WITH option:

CREATE PROCEDURE ...

WITH ENCRYPTION AS…

 

As stated in documentation, WITH ENCRYPTION option indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

 

For information on encrypting data transmitted across a network between an instance of SQL Server and a client application, check out http://msdn2.microsoft.com/en-us/library/ms189067.aspx.

Comments

  • Anonymous
    July 27, 2006
    Can you not deny appropriate access to some user's so they can't view these objects but still run them?  That would seem more secure than obfuscation.
  • Anonymous
    July 27, 2006
    You can encrypt stored procedures?!?!? Well holy cow you learn something new every day.
  • Anonymous
    July 27, 2006
    You can (and should) deny 'view definition' to certain users/roles to have a more secure environment.  However, encryption addresses the security from a different view point -- it prevents anybody, known user or not, from easily viewing object definitions.  If you have an environment where you don't have full control, e.g. you're using a hosting company, encryption sounds like a valuable feature to me.
  • Anonymous
    July 27, 2006
    The only problem is that the encyption can easily be undone - search for procs to decrypt encrypted procs. I prefer the option of removing public read rights from the system tables thus stopping all below dbo from seeing the code. Back it up with encryption if you like, but any dbo user or above would be able to decrypt the code.
    There is one other really big disadvantage with encrypted procs, you can't use profiler for tuning.