有没有一种方法可以禁用更新/删除，但仍允许触发器执行更新/删除？ [英] Is there a way to disable updates/deletes but still allow triggers to perform them?

问题描述

基本上，我希望能够使用 REVOKE 命令禁用 UPDATE 和删除，但我仍然希望表上的触发器更新我的行。

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 将其禁用。 （我看到了这样的帖子）

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)

是否可以在 UPDATE / INSERT 命令>触发器但禁用其余部分？

Is there a way to keep using the UPDATE/INSERT commands in TRIGGERS but disabling the rest?

推荐答案

是的，这是可能的。

触发器以触发器功能的特权运行，默认为 SECURITY INVOKER ，这意味着触发器函数将以特权有效地执行 current_user 中的一个，在您的情况下为插入行。

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.

但是，您可以使用 安全定义 ，以使触发函数以该函数的 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.

如果您的超级用户具有 own 触发功能，则它可以做一切-这可能会造成安全隐患。请考虑手册中有关写<$ c的说明$ c>安全定义器安全运行。

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.

还考虑

Also consider this related question on dba.SE concerning the privileges on the function itself.

这篇关于有没有一种方法可以禁用更新/删除，但仍允许触发器执行更新/删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！