有没有办法禁用更新/删除但仍然允许触发器执行它们? [英] Is there a way to disable updates/deletes but still allow triggers to perform them?
问题描述
基本上，我希望能够使用 REVOKE 命令来禁用 UPDATE 和 DELETE，但我仍然希望触发器在表来更新我的行.
Basically, I want to be able to use the REVOKE command to disable UPDATE and DELETE, but I still want the triggers on a table to update my rows.
我的触发器对新插入的行执行，并更新特定字段.所以我仍然想要这种行为，但它们不会被 REVOKE 或 RULE 禁用.(我看到了一个 SO 帖子)
My triggers perform on newly inserted rows, and update a specific field. So I still want this behaviour, but wouldn't they be disabled with REVOKE or with a RULE. (I saw an SO post)
有没有办法继续使用 TRIGGERS 中的 UPDATE/INSERT 命令但禁用其余的命令?
Is there a way to keep using the UPDATE/INSERT commands in TRIGGERS but disabling the rest?
推荐答案
是的，这是可能的.
触发器以触发器函数的权限运行，默认为SECURITY INVOKER，也就是说，触发器函数以current_user的权限有效执行，在你的case 插入行.
Triggers are run with the privileges of the trigger function, defaulting to SECURITY INVOKER which means, the trigger function is effectively executed with the privileges of the current_user, in your case the one inserting rows.
如果当前用户没有您的触发器函数操作的表所需的权限，您在底层表中的原始操作将出错.
If the current user does not have the required privileges for the tables your trigger function operates on, your original operation in the underlying table will error out.
但是，您可以使用SECURITY DEFINER 用于触发函数使该函数以函数的OWNER 的权限运行.
However, you can use SECURITY DEFINER for the trigger function to have this function run with the privileges of the OWNER of the function.
如果您有一个超级用户拥有触发器功能，它可以做任何事情 - 这将是一个可能的安全隐患.考虑手册中关于 Writing SECURITY 的说明DEFINER 安全运行.
If you have a superuser own the trigger function, it can do everything - which would be a possible security hazard. Consider the instructions in the manual about Writing SECURITY DEFINER Functions Safely.
但更明智的做法是使用触发器函数的必要权限 OWNER 来创建普通角色.你甚至可以只创建一个守护进程".无需登录的角色，充当此类操作的特权包.然后，您将仅授予此守护程序角色所需的权限(在模式、表、序列上……).对于更复杂的设计，您应该将权限捆绑在组角色"中.(再次，没有登录)并将这些组角色授予需要它的角色(在此示例中为守护程序角色)，有效地使他们成为组成员".我经常这样做.
But it's wiser to make a plain role with just the necessary privileges OWNER of the trigger function. You can even just create a "daemon" role without login, acting as privilege bundle for such operations. You would then grant only the needed privileges (on schemas, tables, sequences ...) to this daemon role. For more sophisticated designs you should bundle privileges in "group roles" (again, no login) and grant these group roles to roles that need it (to the daemon role in this example), effectively making them "member of the group". I do that a lot.
考虑 dba.SE 上有关函数本身权限的相关答案:
Consider this related answer on dba.SE concerning the privileges on the function itself:
这篇关于有没有办法禁用更新/删除但仍然允许触发器执行它们?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
