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

喜欢 (0)赏

[搞代码]

分享 (0)