Limiting permissions with “Execute as” when using dynamic SQL

Leave a Comment / ETL, TSQL, Uncategorized / By

When using dynamic SQL there is always the possibility that someone will inject SQL that does harm. They can put queries that can drop objects or can create sysadmin logins. Running the scripts as low permission users can reduce the potential harm. But that takes time to research and is easy to cut. You can add an additional layer of protection by using a stored procedure using the “with execute as” clause.

In an Azure Data Factory pipeline, I load ten Oracle tables from SQL server using queries against SQL Server. I put the queries into a table with the Oracle table information:

  • Source Query
  • Target Schema
  • Target Name

An ADF Lookup activity feeds a ForEach activity which uses a Copy activity to copy from the SQL Query to the Oracle table.

But here’s the problem: someone could add a row to the source query like the following.

Create login testevil1 with password=N'password1'; 
Alter role sysadmin add member testevil1;

If the SQL Server linked service is configured to use a sysadmin account, this code will execute successfully and will create a new sysadmin user.

I found a way to limit what runs in the copy activity. Rather than using just source query , I call a stored procedure. This stored procedure simply receives the source query as a parameter value, and calls sp_executesql. However, the stored procedure is set up with an option “with execute as.” CREATE PROCEDURE (Transact-SQL)

CREATE PROCEDURE dbo.usp_Executesql_Safe
   @SQL NVARCHAR(MAX)
WITH EXECUTE AS 'LimitedPermission'
AS 
print 'Current User:  ' + user_name();
exec sys.sp_executesql @sql;

I created ‘LimitedPermission’ as a database user with no login:

CREATE USER LimitedPermission WITHOUT LOGIN;

I then granted to “LimitedPermission” just the permissions that my application needs. The Copy activity needs to select from a small number of tables. The user does not need permission to insert/update/delete from these tables. This user cannot run a server script.

I tested the following script using the sp_executesql and then using my revised stored procedure with the Execute As clause:

USE Test_Logins;
SELECT SUSER_NAME() LoginName
, USER_NAME() UserName
, IS_SRVROLEMEMBER('sysadmin') IsSysAdmin
, IS_ROLEMEMBER('db_owner') IsDBOwner ;

declare @sql nvarchar(max) = N'Use Master; CREATE LOGIN testEvil1  with password = N''Password*''
, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [testEvil1];';

With sp_executesql, no error is returned:

sp_executesql

The script executes and creates the login without any error. This is unacceptable.

I dropped the login and reran the script using my usp_Execute_Safe stored procedure. The script errors and doesn’t create the login.

Results of using the safe sp_executesql. The script errors because the LimitedPermissions user does not have sysadmin right and doesn't have access to the system

As I wanted, my revised stored procedure prevents execution of this malicious code.

If I only give select permission on a small number of tables to LimitPermissions, this stored procedure will block:

  • Dropping objects like tables
  • Inserting/updating/deleting rows from these tables
  • Selecting from tables the LimitedPermissions doesn’t have permission to

If sysadmins or db owners try to use my safe procedure to perform these tasks, they will be blocked.

Does this approach make sense? How can you get around this?

I will presenting this topic remotely this year at SQLBits (2022) at 9:50 AM GMT on Saturday March 12, 2022 (https://arcade.sqlbits.com/sessions/). Join me if you can.

Leave a Reply