如何锁定表以便用户无法对其执行插入，更新，删除操作（仅限触发器可以）

So I have my table shown below

所以我的表格如下所示

CREATE TABLE [dbo].[Test]
(
    [Id] INT NOT NULL,
    [CommaSeperatedString] nvarchar(Max) NOT NULL, 
    CONSTRAINT [PK_Test] PRIMARY KEY ([Id]) 
)

and I would like to lock down this table so that it can only be updated through triggers.

我想锁定这个表，以便它只能通过触发器更新。

I.e. I don't want a user to be able to run

即我不希望用户能够运行

Insert

插

Update

更新

Delete

删除

Against this table. It may only be updated through a some triggers I have set up.

反对这张桌子。它可能只是通过我设置的一些触发器更新。

Is this possible?

这可能吗？

3 个解决方案

#1

Well here INSTEAD OF Triggers comes into picture.

那么INSTEAD OF触发器就会出现。

you can create trigger for INSTEAD OF INSERT and INSTEAD OF UPDATE and same for delete.

您可以为INSTEAD OF INSERT和INSTEAD OF UPDATE创建触发器，并为删除创建相同的触发器。

CREATE TRIGGER Trig_INS_Test ON Test
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON

   INSERT INTO Test (CommaSeperatedString)
      SELECT CommaSeperatedString
      FROM inserted

END

#2

yes, using instead-of-Trigger we can conditionally prevent the CURD operations from others. But we can grant or deny this operations completely from some users also. For that we are using the commands like

是的，使用替代触发器，我们可以有条件地阻止其他人的CURD操作。但我们也可以完全从某些用户那里批准或拒绝这些操作。为此我们正在使用像这样的命令

GRANT - gives a user permission to perform certain tasks on database objects
DENY - denies any access to a user to perform certain tasks on database objects
REVOKE - removes a grant or deny permission from a user on certain database objects

Please refer this sites

请参考这些网站

https://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/

https://www.mssqltips.com/sqlservertip/1851/prevent-accidental-update-or-delete-commands-of-all-rows-in-a-sql-server-table/

All the Best.....

祝一切顺利.....

#3

In management studio you can go to the userrole --> properties --> securables.

在管理工作室中，您可以转到userrole - > properties - > securables。

Add the table here and you can deny rights to the table. Deny will overrule any allow rights. So even if the user has another role that says allow, it will still be denied the rights.

在此处添加表，您可以拒绝对表的权限。否认将否决任何允许权利。因此，即使用户有另一个允许的角色，它仍然会被拒绝权利。

More info: Understanding GRANT, DENY, and REVOKE in SQL Server

更多信息：了解SQL Server中的GRANT，DENY和REVOKE

Edit: If your triggers are on the same table use an instead of trigger, like the other answers said. Otherwise use rights.

编辑：如果您的触发器在同一个表上，请使用而不是触发器，就像其他答案所说的那样。否则使用权利。

#1