[Err] 1449 - The user specified as definer ('bforce'@'%') dose not exist

小灰灰 2022-05-30 07:28 159阅读 0赞

### 错误描述 ###

执行如下sql报错

SELECT area_id, getAreaCodeName_func(area_id)AS area_val FROM agent_area_info WHERE agent_id =( SELECT agent_id FROM agent_info WHERE user_id = 'UID081508893860976' LIMIT 1 ) AND is_deleted = 0 AND area_state = 0 AND end_date > now();

报错内容为：

[Err] 1449 - The user specified as definer ('bforce'@'%') dose not exist

### 分析 ###

在执行getAreaCodeName\_func这个函数时，会报错。  
提示没有bforce这个用户不存在，就是bforce不具备操作getAreaCodeName\_func函数的权限。  
因为年前数据库备份，使用了root用户创建的函数。所以认为当前用户没用权限。

### 解决办法 ###

连接mysql，授权给bforce用户所有sql的权限。

login as: root
    root@192.168.18.242's password:
    Last login: Wed Mar  7 13:52:56 2018 from 192.168.18.187
    [root@test ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3170204
    Server version: 5.7.13-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> grant all privileges on *.* to bforce@"%" identified by ".";
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.02 sec)