sql - 数据库只读但只有少数表可写

Question

我正在尝试使用以下命令将数据库设置为只读模式并且它有效。

ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT

但是，我只需要允许像 UserSession 等少数表是可写的。 可能吗？

我们在数据库中有大约 500 多个表，我们只需要允许 4 个表可写。

Answer 1

您可以拒绝 INSERT、UPDATE、DELETE 到 public 角色，例如:

DENY UPDATE ON tab1 TO public 
DENY INSERT ON tab1 TO public 
DENY DELETE ON tab1 TO public 

在那之后，普通用户(不是系统管理员)在尝试插入到 tab1 时会遇到错误:

INSERT INTO tab1 (id) VALUES (1)

--Msg 229, Level 14, State 5, Line 20
--The INSERT permission was denied on the object 'tab1', database 'test', schema 'dbo'.

如果您愿意将其添加到数据库中，那么您可以生成脚本来更改所有表，例如:

EXEC sp_msforeachtable '
PRINT ''
    DENY UPDATE ON ? TO public 
    DENY INSERT ON ? TO public 
    DENY DELETE ON ? TO public
''
'

确保您没有拒绝对仍然需要写入的 4 个表的写入。

不确定您的应用程序，但如果您不想拒绝对 public 角色的权限，您可以考虑创建自己的角色，拒绝对该角色的访问，并使用户成为该角色的一部分相反。

希望它有意义。