mysql 视图(view) 权限修改

张映 发表于 2020-03-10

分类目录: mysql

标签:, ,

mysql的视图,不推荐大家使用。mysql使用增,删,改,查,事务等,最基本的东西了就好了。其他的还是少尝试,例如存储过程,触发器,事件,视图等。

mysql的视图,有一套自己权限系统,如果权限没有处理好,就算是mysql的超级管理员root,也没法读取视图的里面的数据。

1,mysql命令行修改视图权限

mysql> select * from v_task;  //权限不对,无法读取
ERROR 1143 (42000): SELECT command denied to user 'root'@'%' for column 'id_owner' in table 't_task'

mysql> show create view v_task\G;  //查看视图
*************************** 1. row ***************************
                View: v_task
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_task` AS (select distinct `b`.`id` AS `id`,`b`.`username` AS `username` from (`crm1`.`t_user` `a` join `crm1`.`v_user_min` `b` on((`a`.`id_manager` = `b`.`id`))))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

//修改视图
mysql> ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_task` AS (select distinct `b`.`id` AS `id`,`b`.`username` AS `username` from (`crm1`.`t_user` `a` join `crm1`.`v_user_min` `b` on((`a`.`id_manager` = `b`.`id`))));
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_task;  //读取视图就OK了
Empty set (0.00 sec)

2,phpmyadmin和navicat修改视图权限

phpmyadmin修改视图权限

phpmyadmin修改视图权限

navicat修改视图权限

navicat修改视图权限



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/2381.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   1X2=?