更新加入权限不足
场景:我们生成请求记录以供经理批准。在等待期间,经理发生变化(从 HR 源中隔夜更新)。我们需要更新请求以指示新的经理。
这是 would 应该执行的查询的缩写版本:
update (select grw.approver_user_id, gup.supervisor_id
from gs3.user_role gur
join gsu.user_profile gup
on gur.user_id = gup.user_id
join gs3.request_workflow grw
on gur.user_role_id = grw.user_role_id
and gup.supervisor_id != grw.approver_user_id -- records with new mgr
where grw.auth_status_cd = 'SUBMITTED') -- reapprovals currently open
set grw.approver_id = gup.supervisor_id;
问题:执行此查询的帐户仅具有 gsu.user_profile
的读取权限。
内部选择工作正常并返回我需要更新的所有行...但即使我没有更新 gup.supervisor_id ,似乎我需要对该表具有写访问权限。如果我以对 gsu.user_profile 具有写入权限的用户身份执行此操作,则更新会成功。
这有逻辑上的理由吗?我宁愿不向不需要的帐户授予权限。
谢谢!
更新
接受托马斯的回答...虽然它并没有真正回答我的问题,即为什么执行更新联接的帐户需要对其未更新的表的更新权限,但我可以看到其中的逻辑“不要使用更新连接,它们不是 ISO 标准”。
很遗憾,因为我的建议和托马斯的建议之间的区别在于我的建议中没有任何嵌套选择。如果有人知道在没有嵌套选择的情况下执行此类查询的 ISO 标准方法,我很想知道!
谢谢,托马斯!
Scenario: We generate records for requests to be approved by a manager. While pending, the manager changes (updated overnight from HR feeds). We need to update the requests to indicate the new manager.
Here's an abbreviated version of the query that would should do that:
update (select grw.approver_user_id, gup.supervisor_id
from gs3.user_role gur
join gsu.user_profile gup
on gur.user_id = gup.user_id
join gs3.request_workflow grw
on gur.user_role_id = grw.user_role_id
and gup.supervisor_id != grw.approver_user_id -- records with new mgr
where grw.auth_status_cd = 'SUBMITTED') -- reapprovals currently open
set grw.approver_id = gup.supervisor_id;
The problem: the account executing this query only has read privileges on gsu.user_profile
.
The inner select works fine and returns all the rows I need to update... but even though I'm not updating gup.supervisor_id
, it seems I'm required to have write access to that table. If I execute this as a user that does have write access to gsu.user_profile
, the update is successful.
Is there a logical reason for this? I would rather not grant permissions to an account that it does not need.
Thanks!
Update
Accepting Thomas' answer... though it doesn't really answer my question of why the account executing an update join would need update privileges to a table it's not updating, I can see the logic in saying "Don't use update joins, they're not ISO standard".
It's a shame, because the difference between what I have and Thomas's suggestion is that there aren't any nested selects in mine. If anyone knows an ISO standard way of doing a query like this without nested selects, I'd love to know!
Thanks, Thomas!
尝试使用 ISO 批准的更新语句格式,看看是否有效。 ISO 没有规定直接在 Update 语句中使用 Join。相反,您只能通过子查询使用联接。
此外,这可能说明了原始 Update 语句中的一些问题,例如,如果用于设置
approver_id
的子查询返回多于一行,这显然会导致异常,您需要确定如何找到应该为每一行设置的唯一一个supervisor_id。Try using the ISO approved format for an Update statement and see if that works. ISO does not provide for using a Join directly in an Update statement. Rather, you can only use joins via subqueries.
In addition, this may illustrate some problems in your original Update statement if, for example, the subquery used to set
approver_id
returns more than one row which will obviously cause an exception and you'll need to determine how to find the one and only one supervisor_id that should be set for each row.