Freigeben über

Dynamic SQL and digital signatures in SQL Server 2005

  • Artikel

   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 (https://msdn2.microsoft.com/en-us/library/ms345102.aspx)

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

Laurentiu Cristofor’s blog:

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

· SQL Server 2005: An example for how to use counter signatures (https://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