表截断的权限

Question

在MSSQL中，我需要授予用户什么权限才能够截断表？

我试图授予最小的权限集合，但是我不能使用DELETE，因为表很大，而且我希望操作快速。

- Shahar Mosek

Answer 1

你需要 ALTER 权限: 参见这里的权限部分。

请注意，你也可以使用带有 EXECUTE AS 的存储过程，这样运行存储过程的用户甚至不需要被授予 ALTER 权限。

- Shahar Mosek

---

这个回答不仅是正确的，而且还通过指向文档来“教人钓鱼”，以便OP在将来能够找到类似的答案。为什么要踩呢？ - Ben Thul

@Ben - 我怀疑是因为楼主在发布问题后不到一纳秒就发布了这个答案。虽然FAQ允许这样做，但我没有投反对票。 - Martin Smith

@Martin - 这不是一纳秒的事情。我花了2分钟时间才找到答案。 - Shahar Mosek

简而言之：授予<用户/角色>对<table>的更改权限。 - fiat

授予ALTER权限可能非常危险，请小心（请阅读“注意事项”框）：https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16 - mzuther

授予ALTER权限可能非常危险，请小心（阅读“注意事项”框）：https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16 - undefined

Answer 2

您可以创建一个存储过程，使用with execute as owner语法：

create procedure dbo.TruncTable
with execute as owner
as
truncate table TheTable
go

然后授予需要截断该表的人执行权限：

grant execute on TruncTable to TheUser

现在，TheUser 可以像这样截断表格：

exec dbo.TruncTable

- Andomar

Answer 3

最低所需权限为 ALTER on table_name。TRUNCATE TABLE 权限默认为表所有者、sysadmin 固定服务器角色的成员以及 db_owner 和 db_ddladmin 固定数据库角色，并且不可转让。但是，您可以将 TRUNCATE TABLE 语句纳入模块（例如存储过程）中，并使用 EXECUTE AS 子句向模块授予适当的权限。有关更多信息，请参阅使用 EXECUTE AS 创建自定义权限集。

来源

- Martin Smith

Answer 4

不要授权，隐藏...

CREATE TRIGGER TRG_MyTable_Foo 
WITH EXECUTE AS OWNER
INSTEAD OF DELETE
AS
IF CONTEXT_INFO() = 0x9999
BEGIN
    TRUNCATE TABLE MyTable
    SET CONTEXT_INFO 0x00
END
GO

SET CONTEXT_INFO 0x9999
DELETE MyTable WHERE 1=0

使用SET CONTEXT_INFO毫无疑问比普通DELETE和TRUNCATE TABLE更好地进行分离。

我还没有尝试过这个...

编辑：改用SET CONTEXT_INFO。

- gbn

---

+1 我会说 SET CONTEXT_INFO 是必须的，以避免出现不愉快的意外！ - Martin Smith

-1 这会使得删除零行的操作截断表。通常情况下，您是一个很好的回答者，但我认为这是一种难以维护的hack。 - Andomar

@Andomar：是的，已经更正了。我既没有完全考虑清楚，也没有及时更新我的答案。 - gbn

Answer 5

您可以创建一个使用所有者身份执行的存储过程，仅针对一个表或任何表格创建存储过程：

CREATE 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;

- user3854427

---

@Graeme，为什么这很危险？你和其他评论者都认为是危险的，但我不确定原因。 - FoxDeploy

嗨，@FoxDeploy-- 如果某些恶意用户使用SQL注入，例如这个-- DECLARE @nameTable VARCHAR(60) = 'SomeTable; DROP DATABASE YourDatabase; --'; EXECUTE dbo.spTruncate @nameTable; GO更多信息请参见：https://security.stackexchange.com/questions/128412/sql-injection-is-17-years-old-why-is-it-still-around - Graeme