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.
I know there are a lot of papers that talk about dynamic SQL in more depth than what I am going to cover, but as SQL injection is still one of the biggest security problems in the relation databases world, that I decided to include this part as a quick (and hopefully helpful) reminder.
First, allow me to define dynamic SQL as any mechanism used to programmatically generate and execute T-SQL statements, including statements generated in some application (using C#, C++ or any other programming language) and strings executed using the SQL Server sp_executesql stored procedure or the EXECUTE statement.
Being able to interpret any string (or variable) as a SQL statement is a really useful and powerful feature; unfortunately, as happens with most powerful tools, it is really easy to misuse it and leave the door open for somebody to abuse it. In this case, the abuse typically takes form as SQL injection attacks.
SQL injection occurs when the attacker provides malicious data that will change the semantics of the intended SQL you are generating, affecting the way it will be interpreted in the system. For example:
-- An innocent looking SP
CREATE PROC [sp_demo_injection01]( @name sysname )
AS
-- ...with an obvious SQL injection-vulnerable sample
EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name + '''' )
go
-- This is how it was intended to be used
declare @var sysname
SET @var = 'Some Name'
EXEC [sp_demo_injection01] @var
go
-- As you can see, I can easily abuse this module in the following manner
declare @var sysname
SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'
EXEC [sp_demo_injection01] @var
go
When the attacker runs this query the system will concatenate the input to the command we defined in the SP:
EXEC ( 'SELECT * FROM sys.database_principals WHERE name = ''' + 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!' + '''' )
The attacker is able to close the quote in the user name (notice the trailing quote in Some Name’) and converted the rest of what should have been a user name into a different SQL statement, causing the following command to be executed:
SELECT * FROM sys.database_principals WHERE name = 'Some Name'; GRANT CONTROL TO [Malicious User]; PRINT 'Game over! This system is no longer yours!'-- Malicious User now can control the database!!!'
As you can see the attacker was able to add extra SQL statements that were not intended by the author of the stored procedure, in this case granting CONTROL on the database to herself and printing a note.
Parameterization
In most of these scenarios there is an alternative to the example used above using parameterization. Using parameterization gives you the advantage that you can clearly specify the data type and avoid pitfalls as well as the final T-SQL statement generated will reference the parameters as variables and not directly use the user defined input to generate the statement.
If you are using T-SQL directly to generate dynamic SQL, you can take advantage of sp_ExecuteSql to execute parameterized queries, for example:
-- An improved version of [sp_demo_injection01]
CREATE PROC [sp_demo_injection02]( @name sysname )
AS
declare @cmd nvarchar(max)
declare @parameters nvarchar(max)
set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'
set @parameters = '@name sysname'
EXEC sp_executesql @cmd, @parameters, @name = @name
go
-- This is how it was intended to be used
declare @var sysname
SET @var = 'Some Name'
EXEC [sp_demo_injection02] @var
go
-- The previous attack no longer has any effect!
declare @var sysname
SET @var = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'
EXEC [sp_demo_injection02] @var
go
CREATE PROC [sp_demo_injection03]( @Value nvarchar(100) )
AS
declare @cmd nvarchar(max)
declare @parameters nvarchar(max)
set @cmd = N'SELECT * FROM sys.database_principals WHERE principal_id = @Value'
set @parameters = '@Value int'
EXEC sp_executesql @cmd, @parameters, @value = @value
go
-- Should work
declare @var sysname
SET @var = '1'
EXEC [sp_demo_injection03] @var
go
-- Expect error 8114
-- Error converting data type nvarchar to int.
declare @var sysname
SET @var = '1; select * from sys.objects'
EXEC [sp_demo_injection03] @var
go
But be careful, using sp_executesql is not a guarantee that the SQL statement to be executed is not susceptible to SQL injection; the parameters should be used properly in order to really take advantage of this feature. The following example is a demonstration of a common mistake I have seen a few times: constructing the @cmd parameter using user-defined data instead of using it as a parameter.
-------------------------------------------------------------
-- Incorrect usage of sp_executeSql
CREATE PROC [sp_demo_injection04]( @name sysname )
AS
declare @cmd nvarchar(max)
declare @parameters nvarchar(max