Delphi TADOQuery Recordset.Resync需底层表SELECT权限问题求助

用Delphi 10.1(Berlin)开发的VCL应用，通过ADO组件连接SQL Server 2019，Provider为Microsoft OLE DB Provider for SQL Server。SQL Server中存在表OMEGACA.ACC_POL，基于该表创建了视图OMEGACA.V_ACC_POL，应用使用的账号仅拥有此视图的SELECT权限，无底层表的访问权限。

Form1通过TADOQuery执行select * from OMEGACA.V_ACC_POL将数据展示在DBGrid中，Form2用于编辑选中记录。编辑完成后，尝试用以下代码刷新当前记录（避免重新查询整个数据集）：

form1.ADOQuery1.UpdateCursorPos;
form1.ADOQuery1.Recordset.Resync(adAffectCurrent, adResyncAllValues);
form1.ADOQuery1.Resync([rmExact,rmCenter]);

运行后报错：

The SELECT permission was denied on the object 'ACC_POL', database 'MY_DB', schema 'OMEGACA'.

给账号授予底层表的SELECT权限后恢复正常，但需求是通过视图隔离表、用存储过程处理增删改，同时实现单条记录刷新，不想开放底层表权限。

ADO的Resync方法会绕开视图直接访问底层表同步数据。即便查询基于视图，OLE DB Provider执行Resync时仍会尝试从原表获取数据，而应用账号无底层表的SELECT权限，因此触发权限错误。

方法1：重新查询单条记录覆盖当前行 #

放弃使用Resync，通过主键从视图查询最新数据，再更新当前记录的字段值：

var
  TempQuery: TADOQuery;
  CurrentID: Integer;
begin
  CurrentID := form1.ADOQuery1.FieldByName('POLICY_ID').AsInteger;
  
  TempQuery := TADOQuery.Create(nil);
  try
    TempQuery.Connection := form1.ADOQuery1.Connection;
    TempQuery.SQL.Text := 'SELECT * FROM OMEGACA.V_ACC_POL WHERE POLICY_ID = :ID';
    TempQuery.Parameters.ParamByName('ID').Value := CurrentID;
    TempQuery.Open;
    
    if not TempQuery.Eof then
    begin
      form1.ADOQuery1.Edit;
      // 逐个更新需要同步的字段
      form1.ADOQuery1.FieldByName('POLICY_NAME').AsString := TempQuery.FieldByName('POLICY_NAME').AsString;
      form1.ADOQuery1.FieldByName('POLICY_DESC').AsString := TempQuery.FieldByName('POLICY_DESC').AsString;
      form1.ADOQuery1.FieldByName('STATUS_ID').AsInteger := TempQuery.FieldByName('STATUS_ID').AsInteger;
      // ... 其他字段依次更新
      form1.ADOQuery1.Post;
    end;
  finally
    TempQuery.Free;
  end;
end;

该方式全程仅访问视图，符合权限隔离要求。

方法2：配置可更新视图（如需直接通过视图修改数据） #

当前视图基于单表、无聚合/分组且包含主键，本身具备可更新条件。修改视图并添加WITH CHECK OPTION，同时授予账号视图的UPDATE权限：

ALTER VIEW [OMEGACA].[V_ACC_POL]
WITH CHECK OPTION
AS
SELECT  
POLICY_ID, POLICY_NAME, POLICY_DESC, 
STATUS_ID, 
Status_Code = CASE 
    WHEN STATUS_ID = 0 THEN 'Inactive'
    WHEN STATUS_ID = 1 THEN 'Active'
    ELSE 'Error'
END,
-- 其余视图内容保持不变
FROM OMEGACA.ACC_POL
GO

-- 授予应用账号视图的更新权限
GRANT UPDATE ON OMEGACA.V_ACC_POL TO [你的应用账号]

之后可直接通过TADOQuery.Edit/Post更新视图数据，刷新仍使用方法1的单条查询方式，避免触发底层表访问。

方法3：通过存储过程获取单条最新记录 #

创建存储过程封装视图查询逻辑，授予账号存储过程执行权限：

CREATE PROCEDURE [OMEGACA].[GetPolicyById]
    @PolicyID INT
AS
BEGIN
    SELECT * FROM OMEGACA.V_ACC_POL WHERE POLICY_ID = @PolicyID
END
GO

-- 授予应用账号存储过程执行权限
GRANT EXECUTE ON OMEGACA.GetPolicyById TO [你的应用账号]

应用中调用存储过程获取数据并更新当前行，逻辑与方法1一致，仅查询方式替换为存储过程：

var
  TempQuery: TADOQuery;
  CurrentID: Integer;
begin
  CurrentID := form1.ADOQuery1.FieldByName('POLICY_ID').AsInteger;
  
  TempQuery := TADOQuery.Create(nil);
  try
    TempQuery.Connection := form1.ADOQuery1.Connection;
    TempQuery.SQL.Text := 'EXEC OMEGACA.GetPolicyById :ID';
    TempQuery.Parameters.ParamByName('ID').Value := CurrentID;
    TempQuery.Open;
    
    if not TempQuery.Eof then
    begin
      form1.ADOQuery1.Edit;
      // 更新字段...
      form1.ADOQuery1.Post;
    end;
  finally
    TempQuery.Free;
  end;
end;

表定义 #

CREATE TABLE [OMEGACA].[ACC_POL](
    [POLICY_ID] [int] IDENTITY(1,1) NOT NULL,
    [POLICY_NAME] [nvarchar](200) NOT NULL,
    [POLICY_DESC] [nvarchar](1000) NULL,
    [STATUS_ID] [int] NOT NULL,
    [RULE_EVAL] [int] NOT NULL,
    [AUDIT_OPTION] [int] NOT NULL,
    [FORMULA] [nvarchar](2000) NULL,
    [DEBUG_LOG] [int] NOT NULL,
    [USER_APPLY] [int] NOT NULL,
    [USE_CACHE] [int] NOT NULL,
    [DATE_UPD] [datetime] NULL,
    [USER_UPD] [nvarchar](200) NULL,
    [DATE_CREATED] [datetime] NOT NULL,
    [USER_CREATED] [nvarchar](200) NOT NULL,
 CONSTRAINT [ACC_POL_PK] PRIMARY KEY CLUSTERED 
(
    [POLICY_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

视图定义 #

CREATE   VIEW [OMEGACA].[V_ACC_POL]
AS
SELECT  
POLICY_ID, POLICY_NAME, POLICY_DESC, 

STATUS_ID, 
Status_Code = 
CASE 
    WHEN STATUS_ID = 0 THEN 'Inactive'
    WHEN STATUS_ID = 1 THEN 'Active'
    ELSE 'Error'
END,

RULE_EVAL, 
Rule_Eval_Code = 
CASE 
    WHEN RULE_EVAL = 1 THEN 'Any True'
    WHEN RULE_EVAL = 2 THEN 'All True'
    WHEN RULE_EVAL = 3 THEN 'Formula'
    ELSE 'Error'
END,

AUDIT_OPTION, 
Audit_Option_Code =
CASE 
    WHEN AUDIT_OPTION = 0 THEN 'Disabled'
    WHEN AUDIT_OPTION = 1 THEN 'On Failure'
    WHEN AUDIT_OPTION = 2 THEN 'On Success/Failure'
    ELSE 'Error'
END,

FORMULA,

DEBUG_LOG, 
Debug_Log_Code = 
CASE 
    WHEN DEBUG_LOG = 0 THEN 'No'
    WHEN DEBUG_LOG = 1 THEN 'Yes'
    ELSE 'Error'
END,

USER_APPLY,
User_Apply_Code = 
CASE 
    WHEN USER_APPLY = 0 THEN 'All Users'
    WHEN USER_APPLY = 1 THEN 'Users Apply'
    WHEN USER_APPLY = 2 THEN 'Users Exclude'
    ELSE 'Error'
END,

USE_CACHE,
Use_Cache_Code = 
CASE 
    WHEN USE_CACHE = 0 THEN 'No'
    WHEN USE_CACHE = 1 THEN 'Yes'
    ELSE 'Error'
END,

DATE_UPD, USER_UPD,

DATE_CREATED, USER_CREATED

FROM OMEGACA.ACC_POL

GO

内容的提问来源于stack exchange，提问作者altink