Dynamic SQL and digital signatures in SQL Server 2005


   As I already mentioned, dynamic SQL is a quite powerful, but also quite dangerous. In SQL Server 2005 we introduced a new feature that is also quite powerful and when used properly can be quite useful; but it is important to learn and understand any such feature in order to use it properly.  In this small article I will describe a little bit more about the interaction between these two features.


 


  First, I will describe briefly digitally signing modules in SLQ Server 2005, but this explanation will not be thorough, so I strongly recommend reading the references I include at the end of the article to learn more about this subject. After that I will explain how digital signatures affect dynamic SQL, what are the pitfalls you should try to avoid.


 


  Digitally signing modules


 


Hopefully you are familiar with the concept of digital signatures. In SQL server 2005 we introduced digital signatures for modules, the concept per se is not far from the one you may be familiar with: have a piece of data (in this case I will talk about a T-SQL module) and use a digital certificate and its private key to create a signature; this signature and its relationship with the certificate can be used to a) verify that the original piece of data has not been modified since it was signed and b) that the data was signed by the owner of the give certificate.


 


Before SQL server 2005 there were very few reliable mechanisms to verify that the code issued by any ISV was not tampered. While typically not a problem for the consumer of the module as sysadmins and DBOs typically have good control (based on permissions and roles) on who can write executable modules (SPs, UDFs, etc.), ISVs have sometimes difficulties validating any unsupported modifications to their applications.


 


In SQL Server 2005, it is possible for ISVs to deliver digitally signed modules to their customers without granting any additional permissions or privileges based on the signature itself. This may allow them to help in support scenarios where customers (either by mistake or as an explicit act from a rogue employee) modified a module and their application is in an unsupported state. For example:


 


CREATE CERTIFICATE [cert_demo01]


  WITH SUBJECT = ‘Cert demo – simple siganture’


go


 


CREATE PROC [sp_demo01]


AS


  PRINT ‘hello world’


go


 


ADD SIGNATURE TO [sp_demo01] BY CERTIFICATE [cert_demo01]


go


 


— Let’s see the signature


declare @thumb varbinary(32)


select @thumb = thumbprint from sys.certificates where name = ‘cert_demo01’


select object_name(major_id) as ‘object_name’,


  crypt_property as signature


  from sys.crypt_properties where thumbprint = @thumb


go


 


— Now alter the module to verify that the signature is gone


ALTER PROC [sp_demo01]


AS


  PRINT ‘hello world again’


go


— Let’s see the signatures again… should be empty


declare @thumb varbinary(32)


select @thumb = thumbprint from sys.certificates where name = ‘cert_demo01’


select object_name(major_id) as ‘object_name’,


  crypt_property as signature


  from sys.crypt_properties where thumbprint = @thumb


go


  


 Another common issue DB administrators may face is the requirement to allow users o access some of the resources (such as tables) only via limited modules (i.e. users who should be able to execute an application, should not be able to access the tables directly). In SQL Server 2000, the common mechanism to achieve this was ownership chaining (OC), but OC has a lot of limitations because of its own nature (limited to DML, permissions are completely bypassed, security considerations for allowing cross-DB OC, etc.).


 


  In SQL Server 2005, using digital signatures can be used to modify the execution context and add a user (mapped from the certificate) as a secondary identity that will affect the permission checks for the duration of the module (without bleeding to a subsequent module). Another way to explain this usage of signatures is to “extend permissions via signature” or “granting permission to the module”. In the following example I have some additional explanations in the comments:


 


CREATE CERTIFICATE [cert_demo02] WITH SUBJECT = ‘Cert demo – signature as secondary identity’


go


 


— Create a schema to store all resources, and a loginless user to be the schema owner


CREATE USER [usr_resources_owner] WITHOUT LOGIN


go


CREATE SCHEMA [sch_resources] AUTHORIZATION [usr_resources_owner]


go


 


— Create a schema to store all modules, and a loginless user to be the schema owner


— this will break Ownership chaining


CREATE USER [usr_module_owner] WITHOUT LOGIN


go


CREATE SCHEMA [sch_modules] AUTHORIZATION [usr_module_owner]


go


 


— mCreate a simple table


CREATE TABLE [sch_resources].[t_Demo02]( data nvarchar(100) )


go


 


— and a module to access it


CREATE PROC [sch_modules].[sp_demo02]


AS


  SELECT * FROM sys.user_token ORDER BY usage, type, name


  SELECT * FROM [sch_resources].[t_Demo02]


go


 


— Add a siganture to the newly created module


ADD SIGNATURE TO [sch_modules].[sp_demo02] BY CERTIFICATE [cert_demo02]


go


 


— Create a user for our signing cert, but no permissions granted yet


CREATE USER [usr_cert_demo02] FOR CERTIFICATE [cert_demo02]


go


 


— Now let’s create a low-privielged user to test our module


CREATE USER [usr_lowpriv] WITHOUT LOGIN


go


GRANT EXECUTE ON [sch_modules].[sp_demo02] TO [usr_lowpriv]


go


 


— Let’s see what happens when the low priv user executes the module:


EXECUTE AS USER = ‘usr_lowpriv’


go


EXEC [sch_modules].[sp_demo02]


go


— What happened?


— We can see that the user token during the module execution is different than


— the token outside the call (below).


— The signature is affecting the execution context based on the module siganture


— and any permissions granted to the signing certificate will be added to the token.



SELECT * FROM sys.user_token ORDER BY usage, type, name


go


REVERT


go


 


— Grant permission to access the table to the certificate


GRANT SELECT ON [sch_resources].[t_Demo02] TO [usr_cert_demo02]


go


 


— and run the script from above again


— Let’s see what happens when the low priv user executes the module:


EXECUTE AS USER = ‘usr_lowpriv’


go


EXEC [sch_modules].[sp_demo02]


go


REVERT


go


 


— Now, it is impornat to notice that the siganture is added to the current token


— not completetly replaced, and it is also importnat to notice that the permission checks


— will still be evaluated based on this token (i.e. OC will bypass permission checks).



 


CREATE USER [usr_DeniedPrivs] WITHOUT LOGIN


go


— Permission to execute the module, but not to access the table



DENY SELECT ON [sch_resources].[t_Demo02] TO [usr_DeniedPrivs]


GRANT EXECUTE ON [sch_modules].[sp_demo02] TO [usr_DeniedPrivs]


go


 


— Should fail to select from table thanks to the DENY permission


EXECUTE AS USER = ‘usr_DeniedPrivs’


go


EXEC [sch_modules].[sp_demo02]


go


REVERT


go


 


Digital signatures and dynamic SQL


 


  Using the digital signature as a mechanism to extend permissions affects any operation on the body of the signed module, including dynamic SQL executed in it. What does it mean? It means that the signer should understand that the module to be signed will execute dynamic code that is also going to be signed. For example:


 


CREATE PROC [sch_modules].[sp_demo03] ( @Id int )


AS


  DECLARE @cmd nvarchar(max)


  DECLARE @params nvarchar(max)


  — the follwoing code will be also afected by the siganture


  SET @cmd = ‘SELECT * FROM sys.user_token ORDER BY usage, type, name; SELECT * FROM [sch_resources].[t_Demo02] WHERE Id = @Id;’


  SET @params = ‘@Id int’


  EXEC sp_executesql @cmd, @params, @Id = @Id


go


 


— Add a siganture to the newly created module


ADD SIGNATURE TO [sch_modules].[sp_demo03] BY CERTIFICATE [cert_demo02]


go


GRANT EXECUTE ON [sch_modules].[sp_demo03] TO [usr_lowpriv]


go


 


— Let’s see what happens when the low priv user executes the module:


EXECUTE AS USER = ‘usr_lowpriv’


go


— Will succeed


EXEC [sch_modules].[sp_demo03] 2


go


REVERT


go


 


This characteristic is a useful one, but it can also be dangerous in case of an arbitrary code execution or SQL injection.  In the following example I will try to demonstrate these dangers in case of an injection.


 


 


— The following module is subject to SQL injection



CREATE PROC [sch_modules].[sp_demo04] ( @Table_name sysname )


AS


  ————————————————————


  — WARNING: The following code is subject to SQL injection!!!


  ————————————————————


  DECLARE @cmd nvarchar(max)


  — the follwoing code will be also afected by the siganture


  SET @cmd = ‘SELECT * FROM sys.user_token ORDER BY usage, type, name; SELECT * FROM ‘


  + @table_name


  EXEC ( @cmd )


go


 


— Add a siganture to the newly created module


ADD SIGNATURE TO [sch_modules].[sp_demo04] BY CERTIFICATE [cert_demo02]


go


GRANT EXECUTE ON [sch_modules].[sp_demo04] TO [usr_lowpriv]


go


 


EXECUTE AS USER = ‘usr_lowpriv’


go


— Using the module as originally intented


EXEC [sch_modules].[sp_demo04] ‘[sch_resources].[t_Demo02]’


go


— .. but now abusing the signature…


EXEC [sch_modules].[sp_demo04] ‘[sch_resources].[t_Demo02]; EXEC sp_addrolemember ”db_owner”, ”usr_lowpriv”;’


go


— Notice that the attack failed thanks to the


— limited permissions granted to the certificate.


— This is one of the reasons why I always recommend


— following the least privilege principle


REVERT


go


 


— Now, what would happen if we would have granted


— a much higher permission?



GRANT CONTROL TO [usr_cert_demo02]


go


 


EXECUTE AS USER = ‘usr_lowpriv’


go


— Your DB would be compromised!!!


EXEC [sch_modules].[sp_demo04] ‘[sch_resources].[t_Demo02]; SELECT * FROM fn_my_permissions( NULL, ”DATABASE”); print ”Insert your favorite attack here”’


go


REVERT


go


 


— remove the extremely-high privilege from the cert


REVOKE CONTROL TO [usr_cert_demo02]


go


 


  As you can see from the example, it is a good idea to follow the least-privilege principle when using signatures as a mechanism to extend the execution context.


 


 Now, the natural question to follow: Why would SQL Server allow carrying the signature to dynamic SQL? The answer is not as simple, and I am sure there may be some people who won’t like it, but the truth is that SQL Server is a platform and digital signatures is a feature that, when used properly and responsibly, can be extremely useful and safe, and in case the application developer really don’t want to execute dynamic SQL with a signature, there is an alternative: Move the dynamic SQL to a non-signed module (the signature will not be carried to a different module). For example:


 


— Based on demo04 and still carrying injectable dynamic SQL



CREATE PROC [sch_modules].[sp_demo05_dyn] ( @Table_name sysname )


AS


  ————————————————————


  — WARNING: The following code is subject to SQL injection!!!


  ————————————————————


  DECLARE @cmd nvarchar(max)


  — the follwoing code will be also afected by the siganture


  SET @cmd = ‘SELECT * FROM sys.user_token ORDER BY usage, type, name; SELECT * FROM ‘ + @table_name


  EXEC ( @cmd )


go


 


CREATE PROC [sch_modules].[sp_demo05] ( @Table_name sysname )


AS


  SELECT * FROM sys.user_token ORDER BY usage, type, name;


  SELECT * FROM [sch_resources].[t_Demo02];


  — Call the module that will do dynamic SQL


  — Noticed that the signature will be lost


  EXEC [sch_modules].[sp_demo05_dyn] @table_name


go


 


ADD SIGNATURE TO [sch_modules].[sp_demo05] BY CERTIFICATE [cert_demo02]


go


GRANT EXECUTE ON [sch_modules].[sp_demo05] TO [usr_lowpriv]


go


 


— Let’s see what happens when the low priv user executes the module:


EXECUTE AS USER = ‘usr_lowpriv’


go


— The signature is not carried to the 2nd module as we intended


EXEC [sch_modules].[sp_demo05] ‘[sch_resources].[t_Demo02];’


go


REVERT


go


 


Conclusions


 


 Digital signatures in SQL Server 2005 are a quite powerful tool, but as any such tool, it has to be used with care to avoid unnecessary risks and potential damage. When using digital signatures remember to be careful on what you are signing, and be extra careful when signing a module that includes dynamic SQL as it will be affected by the signature. Don’t sign unnecessary code, and keep the escalated (signed) code to a minimum.


 


  I also strongly suggest following the least-privilege principle when using signatures. Grant the minimum permission necessary to the certificate, and if necessary, split the code and sign different pieces of the code by different certificates.


 


Additional references


 


From BOL:


·         Module Signing (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)


·         Understanding Execution Context (http://msdn2.microsoft.com/en-us/library/ms187096.aspx)


Laurentiu Cristofor’s blog:


·         SQL Server 2005: procedure signing demo (http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx)


·         SQL Server 2005: An example for how to use counter signatures  (http://blogs.msdn.com/lcris/archive/2006/10/19/sql-server-2005-an-example-for-how-to-use-counter-signatures.aspx)


 


I hope this article will be useful. Please let me know either here in the blog comments or in the SQL Server Security forum () if you have any feedback or comments on this topic.

Comments (5)

  1. Adam Machanic says:

    Great timing on this post — I’m doing a webcast on this topic tomorrow for MSDN:

    http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032334738

  2. creif says:

    Using your excellent samples I have pretty much worked out everything that I need to encrypt one column, including maintaining both the

    encrpyted data and a one way hash for searches.  I have a view which decrypts the data properly when the symmetric key has been opened (and obviously returns null when the key is not open).

    I want the view to return the decrypted data only when the user is accessing the database from a single application.  This application maintains a single database connection per session.  My thought was to open the key when the database connection is established by the application and close it when the application exits, thereby granting access only through the application.  Is that an acceptable practice?

    If I do that, should I protect the key with a password that is then compiled in the application so that I can open the key?  This means that every installation will have a key protected by the same password.  Or is there a better way to do that?  Does this post on digital signing have any relevance to my situation?

  3.   Thanks a lot for your comments. I am glad I am able to help.

       We have discussed this particular topic (security based on application identity) in great detail in the forum. Below is a link to that particular thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793129&SiteID=1

      I invite you to continue the discussion in the forum so more people have a chance to read and benefit from it.

     Thanks a lot,

    -Raul