在MySQL中，执行触发器需要哪些特权? [英] In MySQL what privileges are required for executing a trigger?

问题描述

我在MySQL手册中发现DEFINER的说明令人困惑，因此我不确定在其下运行应用程序的执行用户"需要哪些特权.为了安全起见，我想将执行用户"限制为所需的最少权限.

I find the explanation of DEFINER in the MySQL manual confusing, so I am not sure what privileges are required for the 'execute user' under which the application is running. For security, I like to limit the 'execute user' to the least amount of permissions needed.

我了解触发器/存储过程的创建者需要SUPER特权，但是执行用户"也需要SUPER许可吗?

I understand that the creator of a trigger/stored procedure requires SUPER privileges, but does the 'execute user' also require SUPER permissions?

我在一个用户下创建了一个触发器，该触发器最终失去了对数据库的特权. 执行用户"不具有SUPER特权，并且具有触发失败的MySQL UPDATE.

I created a trigger under a user that eventually lost privileges to my database. The 'execute user' did Not have SUPER privileges and a MySQL UPDATE that had a trigger failed.

我为执行用户"赋予了SUPER特权，并通过拖放并创建触发器将DEFINER更改为root，并且一切正常.我是否必须为执行用户"赋予SUPER特权，还是必须确保DEFINER用户仍然存在并拥有SUPER特权?

I gave SUPER privileges to the 'execute user' and I changed the DEFINER to root by dropping and creating the triggers and it all works. Did I have to give SUPER privileges to the 'execute user' or do I have to be sure the DEFINER user is still around and has SUPER privileges?

使用TRIGGERS和MySQL存储程序进行用户管理的最佳实践是什么?

What are the best practices for user management with TRIGGERS and STORED PROCEDURES for MySQL?

推荐答案

在存储的例程和触发器之间有一些区别.在这里，我将尝试解决触发器的问题.

There are some peculiarities distinguishing between stored routines and triggers. Here I will try to help with the issue of triggers.

我希望以下摘要对您有所帮助.

I hope the following summary is helpful.

第一件事是确定您正在使用的MySQL版本.

The first thing is to determine the MySQL version you are using.

根据文档:

MySQL 5.0: 13.1.11.创建触发器语法

MySQL 5.0: 13.1.11. CREATE TRIGGER Syntax

从MySQL 5.0.17开始，MySQL在以下情况下考虑DEFINER用户: 检查触发特权，如下所示:

From MySQL 5.0.17 on, MySQL takes the DEFINER user into account when checking trigger privileges as follows:

• 在CREATE TRIGGER时，发出该语句的用户必须具有SUPER特权.

• At CREATE TRIGGER time, the user who issues the statement must have the SUPER privilege.

在触发激活时，将根据DEFINER用户检查特权.该用户必须具有以下特权:

At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:

• SUPER特权.

• The SUPER privilege.

如果在触发器主体中使用OLD.col_name或NEW.col_name引用表列，则为主题表提供SELECT特权.

The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.

如果表列是SET NEW.col_name =触发器主体中的值分配的目标，则主题表具有UPDATE特权.

The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.

触发器执行的语句通常需要其他特权.

Whatever other privileges normally are required for the statements executed by the trigger.

在MySQL 5.0.17之前，DEFINER不可用，MySQL检查触发 这样的特权:

Before MySQL 5.0.17, DEFINER is not available and MySQL checks trigger privileges like this:

• 在CREATE TRIGGER时，发出该语句的用户必须具有SUPER特权.

• At CREATE TRIGGER time, the user who issues the statement must have the SUPER privilege.

在触发器激活时，针对其操作导致触发器被激活的用户检查特权.该用户必须具有 执行语句通常需要的特权 通过触发器.

At trigger activation time, privileges are checked against the user whose actions cause the trigger to be activated. This user must have whatever privileges normally are required for the statements executed by the trigger.

MySQL 5.1及更高版本: 13.1.19.创建触发器语法

MySQL 5.1 and above: 13.1.19. CREATE TRIGGER Syntax

MySQL在检查触发器时将DEFINER用户考虑在内 特权如下:

MySQL takes the DEFINER user into account when checking trigger privileges as follows:

• 在CREATE TRIGGER时，发出该语句的用户必须具有TRIGGER特权. (在MySQL 5.1.6之前为SUPER.)

• At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)

在触发激活时，将根据DEFINER用户检查特权.该用户必须具有以下特权:

At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:

• TRIGGER特权. (在MySQL 5.1.6之前为SUPER.)

• The TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)

如果使用触发器主体中的OLD.col_name或NEW.col_name引用表列，则为主题表提供SELECT特权.

The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.

如果表列是SET NEW.col_name =触发器主体中的值分配的目标，则主题表具有UPDATE特权.

The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.

触发器执行的语句通常需要其他特权.

Whatever other privileges normally are required for the statements executed by the trigger.

这篇关于在MySQL中，执行触发器需要哪些特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！