网站首页技术博客

Access denied; you need (at least one of) the SUPER privilege(s) for this operatio​

洞天水月2021-03-12 14:29:283488人次阅读
摘要数据库报错[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operatio

数据库报错[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operatio


数据库报错you need (at least one of) the SUPER privilege(s) for this operation

问题描述:
navicat执行脚本,数据库报错
[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

仔细检查脚本语句使用DEFINER约束,直接删除DEFINER约束条件,重新运行就好了。

在阿里云数据库中,这样的约束过不去

改之前

DROP VIEW IF EXISTS `goods_in_out_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`xmxy`@`localhost` SQL SECURITY DEFINER VIEW `goods_in_out_view` AS 
SELECT
    `xmxy_stock`.`goods_in`.`id` AS `id`,
    `xmxy_stock`.`goods_in`.`shop_id` AS `shop_id`,
    CONVERT('in' USING utf8mb4) AS `type`,
    `xmxy_stock`.`goods_in`.`goods_id` AS `goods_id`,
    `xmxy_stock`.`goods_in`.`in_price` AS `price`,
    `xmxy_stock`.`goods_in`.`number` AS `number`,
    `xmxy_stock`.`goods_in`.`supplier_id` AS `supplier_id`,
    0 AS `customer_id`,
    `xmxy_stock`.`goods_in`.`user_name` AS `user_name`,
    `xmxy_stock`.`goods_in`.`create_time` AS `create_time`,
    `xmxy_stock`.`goods_in`.`update_time` AS `update_time`,
    `xmxy_stock`.`goods_in`.`delete_time` AS `delete_time`
FROM
    `xmxy_stock`.`goods_in`
WHERE
    (`xmxy_stock`.`goods_in`.`delete_time` = 0)
UNION
SELECT
    `xmxy_stock`.`goods_out`.`id` AS `id`,
    `xmxy_stock`.`goods_out`.`shop_id` AS `shop_id`,
    CONVERT('out' USING utf8mb4) AS `type`,
    0 AS `goods_id`,
    `xmxy_stock`.`goods_out`.`price` AS `price`,
    `xmxy_stock`.`goods_out`.`number` AS `number`,
    0 AS `supplier_id`,
    `xmxy_stock`.`goods_out`.`customer_id` AS `customer_id`,
    `xmxy_stock`.`goods_out`.`user_name` AS `user_name`,
    `xmxy_stock`.`goods_out`.`create_time` AS `create_time`,
    `xmxy_stock`.`goods_out`.`update_time` AS `update_time`,
    `xmxy_stock`.`goods_out`.`delete_time` AS `A`
FROM
    `xmxy_stock`.`goods_out`
WHERE
    (
        `xmxy_stock`.`goods_out`.`delete_time` = 0
    )

删除 

 DEFINER=`xmxy`@`localhost`

改之后

DROP VIEW IF EXISTS `goods_in_out_view`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `goods_in_out_view` AS 
SELECT
    `xmxy_stock`.`goods_in`.`id` AS `id`,
    `xmxy_stock`.`goods_in`.`shop_id` AS `shop_id`,
    CONVERT('in' USING utf8mb4) AS `type`,
    `xmxy_stock`.`goods_in`.`goods_id` AS `goods_id`,
    `xmxy_stock`.`goods_in`.`in_price` AS `price`,
    `xmxy_stock`.`goods_in`.`number` AS `number`,
    `xmxy_stock`.`goods_in`.`supplier_id` AS `supplier_id`,
    0 AS `customer_id`,
    `xmxy_stock`.`goods_in`.`user_name` AS `user_name`,
    `xmxy_stock`.`goods_in`.`create_time` AS `create_time`,
    `xmxy_stock`.`goods_in`.`update_time` AS `update_time`,
    `xmxy_stock`.`goods_in`.`delete_time` AS `delete_time`
FROM
    `xmxy_stock`.`goods_in`
WHERE
    (`xmxy_stock`.`goods_in`.`delete_time` = 0)
UNION
SELECT
    `xmxy_stock`.`goods_out`.`id` AS `id`,
    `xmxy_stock`.`goods_out`.`shop_id` AS `shop_id`,
    CONVERT('out' USING utf8mb4) AS `type`,
    0 AS `goods_id`,
    `xmxy_stock`.`goods_out`.`price` AS `price`,
    `xmxy_stock`.`goods_out`.`number` AS `number`,
    0 AS `supplier_id`,
    `xmxy_stock`.`goods_out`.`customer_id` AS `customer_id`,
    `xmxy_stock`.`goods_out`.`user_name` AS `user_name`,
    `xmxy_stock`.`goods_out`.`create_time` AS `create_time`,
    `xmxy_stock`.`goods_out`.`update_time` AS `update_time`,
    `xmxy_stock`.`goods_out`.`delete_time` AS `A`
FROM
    `xmxy_stock`.`goods_out`
WHERE
    (
        `xmxy_stock`.`goods_out`.`delete_time` = 0
    )


文章评论