我在MySQL手册中发现DEFINER的解释令人困惑,所以我不确定应用程序运行的’执行用户’需要什么特权.为了安全起见,我喜欢将“执行用户”限制为所需的最少权限.

我知道触发器/存储过程的创建者需要SUPER权限,但“执行用户”是否还需要SUPER权限？

我在用户下创建了一个触发器,最终失去了对我的数据库的权限. ‘执行用户’没有SUPER权限和触发失败的MySQL UPDATE.

我给’执行用户’提供了SUPER权限,我通过删除和创建触发器将DEFINER更改为root,这一切都有效.我是否必须为’执行用户’提供SUPER权限,还是我必须确保DEFINER用户仍在使用且具有超级权限？

使用TRIGGERS和STORED PROCEDURES for MySQL进行用户管理的最佳实践是什么？

解决方法:

区分存储的例程和触发器有一些特殊性.在这里,我将尝试帮助解决触发器的问题.

我希望以下摘要有用.

首先要确定您使用的MySQL版本.

根据文件：

MySQL 5.0：13.1.11. CREATE TRIGGER Syntax

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

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

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

  • The SUPER privilege.

  • 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.

  • 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.

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

• 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. CREATE TRIGGER Syntax

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

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

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

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

  • 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.

  • 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.