Dynamic TSQL in a secure context

Question

I have a database where the primary user logging into the database has no permissions to any of the tables directly. THe user needs to execute stored procedures which have been explicitly granted to it. This covers any straightforward CRUD operation that is needed. But now, I have a need to execute SQL Dynamically but I want to maintain the same level of security on my user. An example would be

UPDATE [Table] SET [Column 1] = @Column1 

But in this situation [Column 1] and its value would be set at runtime.

THe only way I know to execute dynamic code in a stored procedure is to use sp_runsql, but creating a stored procedure that executes a dynamic query using sp_runsql fails for security reasons (and in general isnt very smart)

Can anyone think of a way to achieve this level of functionality and security?

Answer 1

Your stored procedure will need to dynamically construct the SQL and then execute it using EXEC (documentation)

Looking at the documentation it seems that no specific permissions are needed to run EXEC, other than the permissions on the underlying objects (Although I've not had a chance to test this myself)

Note that in order for your stored procedure to still be considered secure, you need to make sure that you validate any user input - in this case you can use the list of valid column to validate any user input.

Answer 2

The only way to dynamically define the column name in TSQL is to use string concatenation:

DECLARE @SQL = 'UPDATE [your_table] 
                   SET '+ @ColumnName + ' = @ColumnValue '

BEGIN

  EXEC sp_executesql @SQL, 
                     N'@ColumnValue INT',
                     @ColumnValue        

END

...because if you used:

DECLARE @SQL = 'UPDATE [your_table] 
                   SET @ColumnName = @ColumnValue '

...you'd find the dynamic SQL would comma delimit the @ColumnName value.

The caveat is that, though a stored procedure means parameterized queries, that won't insulate you from SQL injection.

An Alternative to Consider

The EXECUTE AS might be a better alternative, so the stored procedures can be executed with someone elses privilege. It's more secure, not providing the risk of SQL injection.

Answer 3

Avoid the dynamic SQL altogether. Consider a scenario whereby your stored proc takes all the params possible, but would update the table only with those values that the caller specifies, or rather, are non-null.

Make use of ISNULL(), and have the UPDATE statement use the row's current values. COALESCE could also be used.

CREATE PROC UpdateCustomer

@ID   int,
@FirstName varchar(100),
@LastName varchar(100),
@LastLoginTime smalldatetime

AS

UPDATE Customer
SET    FirstName = ISNULL(@FirstName,FirstName)
      ,LastName = ISNULL(@LastName ,LastName)
      ,LastLoginDate = ISNULL(@LastLoginTime ,LastLoginDate )
WHERE ID = @ID

You'd just have to set it up that your client code passes NULL for the values that it wants to be retained on the row.

There IS the case where you'd want to persist NULL, and have the column's current value overwritten. You'd have to write around that, or send some other flag, if that were the case.

Answer 4

Currently your procedures work because of ownership chaining:

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

As soon as you add dynamic SQL to the mix, the ownership chain is broken and your table access get explicitly checked for access privileges and the check fails. The best solution is to grant those permission, but not to the user, to the procedure itself, using code signing. The steps to achieve this can be dauntingly complicated to understand, but for your scenarios is actually pretty straight forward:

• add an EXECUTE AS CALLER clause to your procedure
• create a certificate in the database
• sign the procedure
• drop the private key of the certificate (top prevent further use)
• create a user derived from the certificate
• grant the required privilege (SELECT/UPDATE/DELETE/INSERT on the table) to the certificate derived user

This setup is very secure, the procedure itself gets the privilege to modify the tables, not the user calling the procedure. The procedure cannot be modified, doing so results in dropping the signature and the process of signing has to be repeated again.

Erland Sommarskog has a good article about this topic: Giving Permissions through Stored Procedures, but unlike his other articles, this one gets a number of things wrong (as most articles about security do...) so you have to take it with a grain of salt.