SqlCommandBuilder() 为基础表创建插入/更新，而不是为视图创建插入/更新

本文关键字：插入 更新 创建 视图 为基础 SqlCommandBuilder | 更新日期: 2023-09-27 17:55:10

我有两个模式，像这样：

  • 模式"数据"-->保存表，没有人可以从外部访问它们
  • 架构 'ui' --> 包含可从外部访问的视图;这个想法是您可以选择/删除/更新/插入这些视图。因此，我正在做所有权链。

例如：

create table data.tblTest (TestKey int not null primary key);
create view ui.vwTest as select * from data.tblTest;

现在，如果我以用户身份使用 SQL Studio 进行连接，一切正常： 

select * from ui.vwTest; -- WORKS (this is correct)
select * from data.tblTest; -- ERROR (this is correct)
insert into   ui.vwTest  (TestKey) values (17); -- WORKS (this is correct)
insert into data.tblTest (TestKey) values (17); -- ERROR (this is correct)

但是，如果我用 .NET/C# 编写一个使用 SqlCommandBuilder 的程序：

SqlDataAdapter ada = new SqlDataAdapter('select * from ui.vwTest', conn);
SqlCommandBuilder b = new SqlCommandBuilder(mSQLAda);
ada.UpdateCommand = b.GetUpdateCommand();
ada.InsertCommand = b.GetInsertCommand();
ada.DeleteCommand = b.GetDeleteCommand();

==> 然后在下面，插入不起作用！

[编辑]：

SqlCommandBuilder 正在分析视图，而不是创建类似 

INSERT INTO ui.vwTest ...

它正在创造 

INSERT INTO data.tblTest ...

因此，实际上，SqlCommandBuilder试图变得"智能"并访问视图的基础表，而不是访问视图。

问题：这种行为可以改变吗？

顺便说一句，为了更清楚，我在这里进行所有权链接。

我的用户有权查看架构 UI 中的视图，但他们无权查看架构数据。但是，由于所有权追逐，用户可以通过架构数据中的视图间接访问表。

具体而言，用户附加到自定义角色（例如"role_user"），并且该角色对架构具有权限，如下所示：

GRANT SELECT, UPDATE, INSERT, DELETE ON SCHEMA ui TO role_user ;

但该角色对架构"数据"没有权利！！

这种设置的好处是您可以应用行级别安全性。使用视图中的 where 筛选器，您可以仅选择允许用户查看的记录。

如前所述，它在SQL窗口中工作正常，但在SQLCommandBuilder中则不能。SQLCommandBuilder 分析视图，并尝试直接访问基础表，而不是访问视图。

7年前，有人问：https://stackoverflow.com/a/320684/2504785然后他的解决方案是自己编写SQL命令。但是，现在可能存在另一种解决方案吗？但是，到目前为止我没有找到...

[/编辑]

SqlCommandBuilder() 为基础表创建插入/更新，而不是为视图创建插入/更新

好的，现在答案肯定是：

SqlCommandBuilder试图变得"聪明"。如果您使用类似 SELECT * FROM vwTest 的命令打开它，那么它会分析视图并为基础表创建命令，例如 INSERT into tblTest ...

所以问题是：SqlCommandBuilder为基础表而不是视图创建命令。

溶液：

到目前为止，我还没有找到改变SqlCommandBuilder行为的方法。

因此，我重写了所有加载和更新，现在我正在手动完成所有操作。现在，装载纯粹是用SqlDataReader进行的——不用SqlDataAdapter装载到DataTable中。并且所有更新都是通过创建和执行SqlCommand来完成的，无需SqlCommandBuilder。

这是很多工作，但作为奖励，该应用程序现在速度极快。加载速度远远快于SqlCommandBuilder和SqlDataAdapter。可能我会在某个时候进行基准比较。但是，当加载需要 5 秒时，现在它是"立即"完成的。

请注意所有权链接文档的这一部分：

通过链访问对象时，SQL Server 首先将该对象的所有者与调用对象的所有者进行比较。这是链中的前一个环节。如果两个对象具有相同的所有者，则不会评估对引用对象的权限。

因此，为了使所有权链接正常工作，您的表和视图必须由相同的原则拥有。

您可以通过对对象执行 ALTER AUTHORIZATION sql 语句来更改视图的所有者或表的所有者。
请注意，此语句仅更改所有者，而不更改对象所属的架构。在您的情况下，我建议将UI架构的所有者更改为Data架构的同一所有者，同时保持使用UI架构的数据库主体的权限不变。

ALTER AUTHORIZATION ON SCHEMA::UI TO <owner_of_the_data_schema>;

注意：<owner_of_the_data_schema>是我使用的占位符，因为我不知道所有者名称。

这样，应用程序用户仍然只能访问ui架构中的任何内容，但所有权链接允许ui架构中的对象与data架构中的对象进行交互。

相关文章：