要补充我的评论,“如果您不希望人们UPDATE
特定列,使用基于权限的方法会更好吗?” 会做这样的事情:
CREATE TABLE dbo.YourTable (id int IDENTITY,GoodColumn varchar(10),BadColumn varchar(10));
GO
CREATE USER TestUser WITHOUT LOGIN;
GO
GRANT SELECT,UPDATE,DELETE,INSERT ON dbo.YourTable TO TestUser;
GO
DENY UPDATE ON dbo.YourTable(BadColumn) TO TestUser;
GO
INSERT INTO dbo.YourTable (GoodColumn,BadColumn)
VALUES('test123','example12'),('test456','example34');
GO
EXECUTE AS USER = 'TestUser';
GO
UPDATE dbo.YourTable
SET GoodColumn = 'Test789'
WHERE BadColumn = 'example34';
GO
UPDATE dbo.YourTable
SET BadColumn = 'Example56'
WHERE GoodColumn = 'test123';
GO
REVERT;
GO
SELECT *
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;
GO
DROP USER TestUser;
如果出于某种原因,您必须使用TRIGGER
,则可以执行以下操作,但是,我仍然建议使用权限方法:
CREATE TRIGGER dbo.NoUpdateBad ON dbo.YourTable
AFTER UPDATE
AS
IF EXISTS (SELECT 1
FROM Inserted i
JOIN deleted d ON i.id = d.id
WHERE d.BadColumn != i.BadColumn
OR (d.BadColumn IS NULL AND i.BadColumn IS NOT NULL)
OR (d.BadColumn IS NOT NULL AND i.BadColumn IS NULL))
THROW 51000,N'Cannot change the value of the column "BadColumn".',1;
GO
UPDATE dbo.YourTable
SET BadColumn = 'asdjk'
WHERE id = 2;
GO
UPDATE dbo.YourTable
SET BadColumn = NULL
WHERE id = 1;
GO
SELECT *
FROM dbo.YourTable;
但是,正如我所提到的,这两个都不会停止sa
帐户。 DENY
特权帐户将忽略sysadmin
,并且sa
可以很容易地禁用以上触发;即使您给他们第一个跳跃的障碍。这很像是想着xp_cmdshell
好转会阻止某人访问sa
帐户。并非如此,这只是通往他们之路的一秒钟颠簸,因为他们只是启用了它。
本文链接:https://www.f2er.com/3093825.html