mysql视图无法访问，mysql视图无权限，Access denied for user ‘xxx‘@‘%‘ (using password: YES)”

本文链接：https://blog.youkuaiyun.com/sunwork888/article/details/129233612

新部署的服务器上，给予新账号数据所有权限后，程序运行异常，发现是视图访问问题。视图创建时DEFINER为sunwork@%.解决方案是修改视图的DEFINER为当前操作的账号如dev或root@localhost，或者批量修改所有DEFINER为新的定义者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

新部署的服务器，安装好数据后，新建账号给与单独数据的所有权限，在程序运行发生异常，找到问题是视图无法访问，使用远程账号dev访问提示如下：

在服务器使用root访问提示如下：

sunwork为开发时候的账号

创建定义内容为

CREATE ALGORITHM = UNDEFINED DEFINER = `sunwork` @`%` SQL SECURITY DEFINER VIEW `view_article_master` AS SELECT
`a`.`article_id` AS `article_id`,
`a`.`tenant_code` AS `tenant_code`,
`a`.`article_title` AS `article_title`,
`a`.`catalog_id` AS `catalog_id`,
`a`.`summary` AS `summary`,
`a`.`content` AS `content`,
`a`.`author` AS `author`,
`a`.`weight` AS `weight`,
`a`.`key_word` AS `key_word`,
`a`.`page_view` AS `page_view`,
`a`.`release_time` AS `release_time`,
`a`.`recommend_flag` AS `recommend_flag`,
`a`.`hot_flag` AS `hot_flag`,
`a`.`del_flag` AS `del_flag`,
`a`.`main_image_url` AS `main_image_url`,
`a`.`sort` AS `sort`,
`a`.`create_user_id` AS `create_user_id`,
`a`.`create_time` AS `create_time`,
`a`.`update_user_id` AS `update_user_id`,
`a`.`update_time` AS `update_time`,
`b`.`catalog_name` AS `catalog_name` 
FROM
    (
        `article_master` `a`
        LEFT JOIN `catalog_master` `b` ON (((
                    `a`.`tenant_code` = `b`.`tenant_code` 
                ) 
    AND ( `a`.`catalog_id` = `b`.`catalog_id` ))))

解决方案

新建sunwork账号赋予权限
修改 DEFINER定义者为 dev 或 root

Alter ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `view_article_master` AS SELECT
`a`.`article_id` AS `article_id`,
`a`.`tenant_code` AS `tenant_code`,
`a`.`article_title` AS `article_title`,
`a`.`catalog_id` AS `catalog_id`,
`a`.`summary` AS `summary`,
`a`.`content` AS `content`,
`a`.`author` AS `author`,
`a`.`weight` AS `weight`,
`a`.`key_word` AS `key_word`,
`a`.`page_view` AS `page_view`,
`a`.`release_time` AS `release_time`,
`a`.`recommend_flag` AS `recommend_flag`,
`a`.`hot_flag` AS `hot_flag`,
`a`.`del_flag` AS `del_flag`,
`a`.`main_image_url` AS `main_image_url`,
`a`.`sort` AS `sort`,
`a`.`create_user_id` AS `create_user_id`,
`a`.`create_time` AS `create_time`,
`a`.`update_user_id` AS `update_user_id`,
`a`.`update_time` AS `update_time`,
`b`.`catalog_name` AS `catalog_name` 
FROM
    (
        `article_master` `a`
        LEFT JOIN `catalog_master` `b` ON (((
                    `a`.`tenant_code` = `b`.`tenant_code` 
                ) 
    AND ( `a`.`catalog_id` = `b`.`catalog_id` ))))

批量修改view的definer

select concat("alter DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `",TABLE_SCHEMA,"`.",TABLE_NAME," as ",VIEW_DEFINITION,";") from
information_schema.VIEWS where DEFINER = 'sunwork@%';