Home > Encryption > Best Methods to Encrypt Functions and Stored Procedures.: New blog doc : SQL server Administration

Best Methods to Encrypt Functions and Stored Procedures.: New blog doc : SQL server Administration

Best Methods to Encrypt Functions and Stored Procedures.

Using Signatures on Procedures to Grant Permissions

A new—and, in my experience, little known—feature in SQL Server 2005 is the use of a signing certificate to sign stored procedures, assemblies, views, and functions. Administrators can assign permissions to the signing certificate itself and can then be sure that the permissions granted cannot be inadvertently modified by changing the stored procedure, assembly, and so on. Signed executable code cannot be changed without breaking the signature, which consequently invalidates any permissions granted to the signature—that is, unless the changed procedure, assembly, view, or function is re-signed with that signature.

The syntax for adding a signature is simple and includes the ability to add an existing signature (presumably created on another server) with a certificate containing only a public key and to specify a password if the certificate is password protected:


[{ WITH PASSWORD = ‘password’ | WITH SIGNATURE=binary_signature}]

The need for signing code may seem rather exotic. In most production environments, administrators do not need to worry about someone other than administrators changing procedures. However, there are important scenarios where this could be very useful:

• A signature enables a low-privileged account to execute a specific, pre-approved task that the account would not otherwise have permissions to perform.

• ISVs may find signing certificates useful for ensuring that the stored procedures, assemblies, and so on, that are released with their product are not changed.

• Signatures solve a difficult problem with cross-database authentication.

The ability to deploy signed stored procedures, assemblies, functions, and views can allow an ISV to effectively prevent code tampering by a customer’s database administrator. This can help prevent local changes that would otherwise become support issues. Signed procedures are easy to include in any deployment scenario: the signatures can be backed up and restored, attached, or scripted as a blob with the ADD SIGNATURE Transact-SQL statement. Technically, a system administrator may be able to temporarily work around this by implementing his or her own certificate of the same name and signing all of the same procedures, but this would not be trivial. It would also be detectable by the ISV.

The real power of signatures lies in cases where data for a particular query is spread among multiple databases on the server instance and there are different security models (or database owners) among the databases.

One way to address this is to ensure that the server logins are mapped as users in both databases and that the users have valid permissions in both databases. Every user granted permission to execute a query in the first database must also be granted appropriate permissions in the second database. Needless to say, this can quickly become unsupportable, especially when the databases are owned by different groups.

Reference article: http://technet.microsoft.com/en-us/library/cc837966(v=sql.100).aspx

Categories: Encryption
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: