在Sql Server中创建只读视图

Question

根据MSDN，由简单select组成的视图会自动允许您在表上使用insert/update/delete语句。是否有一种方法可以防止这种情况-告诉Sql服务器该视图是只读的，并且不能用于修改表？

- Idan Arye

Answer 1

最好的方法是在视图上删除UPDATE/DELETE/INSERT权限。

除此之外，您可以在视图上创建一个INSTEAD OF触发器，什么也不做以使更新默默失败，或者有相当多的结构使视图无法更新。因此，您可以选择一个不会改变语义或效率的构造，然后违反它。 编辑：下面似乎符合要求。

CREATE VIEW Bar
AS
SELECT TOP 100 PERCENT x
FROM foo
WITH CHECK OPTION

- Martin Smith

---

+1 权限应该防止这种情况，而不是代码。但如果人们可以通过视图编写，则我怀疑基本表上也有权限... - gbn

谢谢，运行得很好。这些视图实际上将引用另一个数据库，用户无法访问该数据库。我想使它们只读，这样用户就无法通过他可以访问的数据库中的视图修改他无权访问的数据库。我想使用每个数据库的权限而不是每个对象的权限来保持简单，并更加确信我没有错过权限定义中的漏洞。 - Idan Arye

我会指出附加到代码末尾的一个危险。它似乎依赖于“WITH CHECK OPTION”。如果你回来并使用SQL Server Management Studio进行后期编辑视图，它将不包括“WITH CHECK OPTION”，因此视图将再次变为可读/写。 - BIBD

对于其他寻求澄清的人，需要在视图末尾将 SELECT TOP 100 PERCENT 与 WITH CHECK OPTION 结合起来，以使对该视图的写入尝试失败。在我的2014年版本测试中，只有其中一个仍然允许更新视图。 - Ben

对于其他正在研究此问题的人，我发现它对我来说似乎没有表现出一致的行为。我使用了“SELECT TOP 100 PERCENT”和“WITH CHECK OPTION”创建了我的视图，但是如果我尝试从视图中删除数据，则删除会影响基础表。这是在SQL Server 2016上的情况。 - m81

Answer 2

您可以在SQL Server中指定一个UNION操作符，以使INSERT/UPDATE/DELETE 操作失败，像这样：

create view SampleView
as
  select ID, value from table
  union all
  select 0, '0' where 1=0

最后一个查询根本没有返回任何行，但必须与第一个查询具有相同数量的字段和相同的数据类型，才能安全使用 UNION。欲了解更多信息，请参见此链接：在 SQL Server 数据库中使表只读的不同方法

- Guillermo Gutiérrez

---

在SQLServer 2014中，您还可以添加一个"UNION SELECT TOP 0 * FROM table"语句。 - Christoph

Answer 3

处理这个问题的最佳方式是允许对视图进行选择性访问。或者拒绝给定用户的插入/更新/删除访问权限。这样可以完美地解决问题。同时创建视图 "WITH (NOLOCK)"。

- T.Saba