mysql指定user和host，部分应用无法连接到数据库问题处理(mysql存储过程问题处理)

本文链接：https://blog.csdn.net/niuwj666/article/details/139068209

原本，业务使用的是名为"userA"的MySQL用户，该用户的主机权限设置为允许从任何(“%”)主机连接。为了适应业务连接的需求变化，我们决定调整MySQL的用户与主机对应关系，以便更精细地控制用户"userA"的访问权限,调整后如下表：

user	host
userA	192.168.1.1
userA	192.168.1.2
userA	192.168.1.3

调整用户权限后，重启应用，应用报错，提示：The user specified as a definer ('userA'@'%') does not exist.
我们在mysql创建view、trigger、function、procedure、event时都会定义一个Definer=‘user@host’,
例如当它调用DEFINER='userA@%'的存储过程的时候，mysql会检查mysql.user用户表中是否存在admin@%这个用户，mysql的检查结果是admin@%这个用户不存在，所以会提示这个报错。

解决办法：



 ## 1、修改函数存储过程security_type，将指定数据库中所有存储过程和函数的安全类型统一设置为INVOKER，意味着之后执行这些存储过程或函数时，将使用调用者的权限级别来决定访问权限。
update  mysql.proc set security_type='INVOKER' where db='数据库名称'

##  2、只修改security\_type，不修改definer 脚本：将相应视图的安全上下文改为INVOKER

    SELECT CONCAT ( "alter  SQL SECURITY INVOKER VIEW " ,"`",TABLE_SCHEMA ,"`","." ,TABLE\_NAME ," as " ,VIEW\_DEFINITION ,";" ) as "执行此列SQL"
    FROM information\_schema.VIEWS where table\_schema='数据库名';

## 3、重新生成触发器，先查询库上是否有触发器

    select TRIGGER\_SCHEMA,TRIGGER\_NAME,DEFINER from information\_schema.triggers where TRIGGER\_SCHEMA ='数据库名称';

##   如有，执行如下脚本，生成删除和重新创建的脚本，并保存删除和重建脚本。

    SELECT concat( 'drop trigger ', TRIGGER\_SCHEMA, '.', trigger\_name, ';' ) AS trigger\_del, concat( 'create trigger ',TRIGGER\_SCHEMA, '.', trigger\_name, ' ', ACTION\_TIMING, ' ', EVENT\_MANIPULATION, ' on ', EVENT\_OBJECT\_SCHEMA, '.', EVENT\_OBJECT\_TABLE, ' for each row ', ACTION\_STATEMENT, ';' ) AS trigger\_create FROM information\_schema.`TRIGGERS` WHERE trigger\_schema = '数据库名'

##  脚本删除后，普通用户执行重建脚本。

##  4、event的修改

#   查询event

    select  definer,db from  mysql.event where db='数据库名'

#   修改

    update mysql.EVENT set definer='user'@'host';

在MySQL中，存储过程或视图的安全上下文可以通过SQL SECURITY子句指定为DEFINER或INVOKER，这影响了存储过程或视图执行时所使用的权限：

DEFINER:
- 当存储过程或视图的安全性设置为DEFINER时，不论是谁调用了这个存储过程或视图，MySQL都将使用定义该对象的用户的权限来执行它。
- 这意味着存储过程将继承创建者的权限，即使调用者没有足够的权限访问存储过程中涉及的表或执行特定操作，只要定义者有这些权限，存储过程就能正常执行。
- 如果DEFINER用户不存在或者权限不足，则存储过程或视图可能无法正确执行。
INVOKER:
- 如果设置为INVOKER，则执行存储过程或视图时，MySQL将使用调用者的权限。
- 这意味着存储过程能执行哪些操作完全取决于调用它的用户的权限。如果调用者没有足够的权限，那么即使存储过程原本设计的功能需要更高权限，也会因为权限不足而失败。
- 这种模式更符合最小权限原则，确保用户只能访问和操作他们被明确授权的内容。