Sql-server – Truncating table without using Alter Table permission

alter-tablesql servert-sql

So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate frees up space too).

Now in order to use truncate minimum permission required is "Alter table". (Source)

But there was also a solution described to "create a stored procedure with execute as owner to only one table or a stored procedure to any table" (given by user3854427).

My question is in response to the code he provided. Embedding the code for easy reference:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
ALTER PROCEDURE [dbo].[spTruncate]
    @nameTable varchar(60)  


WITH EXECUTE AS OWNER
AS

SET NOCOUNT OFF;

DECLARE @QUERY NVARCHAR(200);

SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'


EXECUTE sp_executesql @QUERY;

My questions are:

  1. Is the above code creating a stored procedure "on behalf of" OWNER?
  2. Who has to create this procedure (DBA?) since I definitely can't create it.
  3. Once the DBA create this procedure any member can use it to truncate the table even if they don't have the Alter table permission?

Please help.

Best Answer

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila.

There are basic issue:

  • table names are NVARCHAR, not VARCHAR
  • table names are length 128, not 60
  • sysname is a handy type to represent object names, is an alias for NVARCHAR(128)
  • tables are qualified by schema and name, not by name only
  • procedures that build dynamic SQL must properly quote the names, using QUOTENAME

Lets try again:

 CREATEPROCEDURE [dbo].[spTruncate]
    @schemaName sysname,
    @tableName sysname
 WITH EXECUTE AS CALLER
 AS
 SET NOCOUNT OFF;
 DECLARE @QUERY NVARCHAR(max);
 SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
 EXECUTE sp_executesql @QUERY;

No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.

Related Question