mysql create view 权限_mysql 视图(view) 权限修改

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修改视图权限

navicat修改视图权限