更新加入权限不足

发布于 12-08 23:19 字数 1073 浏览 0 评论 0原文

场景:我们生成请求记录以供经理批准。在等待期间,经理发生变化(从 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!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

海的爱人是光2024-12-15 23:19:38

尝试使用 ISO 批准的更新语句格式,看看是否有效。 ISO 没有规定直接在 Update 语句中使用 Join。相反,您只能通过子查询使用联接。

此外,这可能说明了原始 Update 语句中的一些问题,例如,如果用于设置 approver_id 的子查询返回多于一行,这显然会导致异常,您需要确定如何找到应该为每一行设置的唯一一个supervisor_id。

Update gs3.request_workflow
Set approver_id =   (
                    Select gup.supervisor_id
                    From gs3.user_role As gur
                        Join gsu.user_profile As gup
                            On gur.user_id = gup.user_id
                    Where gup.user_role_id = gs3.request_workflow.user_role_id
                        And gup.supervisor_id != grw.approver_user_id
                    )
Where auth_status_cd = 'SUBMITTED'                          
    And Exists  (
                Select 1
                From gs3.user_role As gur
                    Join gsu.user_profile As gup
                        On gur.user_id = gup.user_id
                Where gup.user_role_id = gs3.request_workflow.user_role_id
                    And gup.supervisor_id != grw.approver_user_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.

Update gs3.request_workflow
Set approver_id =   (
                    Select gup.supervisor_id
                    From gs3.user_role As gur
                        Join gsu.user_profile As gup
                            On gur.user_id = gup.user_id
                    Where gup.user_role_id = gs3.request_workflow.user_role_id
                        And gup.supervisor_id != grw.approver_user_id
                    )
Where auth_status_cd = 'SUBMITTED'                          
    And Exists  (
                Select 1
                From gs3.user_role As gur
                    Join gsu.user_profile As gup
                        On gur.user_id = gup.user_id
                Where gup.user_role_id = gs3.request_workflow.user_role_id
                    And gup.supervisor_id != grw.approver_user_id
                )
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文