mysql视图学习

# 分析该语句

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_user_query` AS select `user`.`user_id` AS `user_id`,`user`.`user_code` AS `user_code`,`user`.`user_name` AS `user_name`,`user`.`user_company` AS `user_company`,`user`.`department_id` AS `department_id`,`user`.`user_mail` AS `user_mail`,`user`.`user_mobile` AS `user_mobile`,`user`.`user_post` AS `user_post`,`user`.`user_status` AS `user_status`,`user`.`create_user_id` AS `create_user_id`,`user`.`create_time` AS `create_time`,`user`.`update_user_id` AS `update_user_id`,`user`.`last_update_time` AS `last_update_time`,`user`.`uid` AS `uid`,`user`.`employee_number` AS `employee_number`,`user`.`level` AS `level`,`user`.`level_name` AS `level_name`,`user`.`function` AS `function`,`user`.`display_order` AS `display_order`,`user`.`belong_dept` AS `belong_dept`,`user`.`position_level` AS `position_level`,`user`.`employee_type` AS `employee_type`,`user`.`is_have_announcement` AS `is_have_announcement`,`user`.`user_type` AS `user_type`,`user`.`lead_id` AS `lead_id`,`user`.`user_sync_time` AS `user_sync_time`,`user`.`guid` AS `guid`,`user`.`areaId` AS `areaId`,`user`.`birthday` AS `birthday`,`user`.`c` AS `c`,`user`.`description` AS `description`,`user`.`startTime` AS `startTime`,`user`.`endTime` AS `endTime`,`user`.`entryTime` AS `entryTime`,`user`.`facsimiletelephonenumber` AS `facsimiletelephonenumber`,`user`.`gender` AS `gender`,`user`.`idcardNumber` AS `idcardNumber`,`user`.`duty` AS `duty`,`user`.`l` AS `l`,`user`.`mobile` AS `mobile`,`user`.`nation` AS `nation`,`user`.`photo` AS `photo`,`user`.`postalAddress` AS `postalAddress`,`user`.`postalCode` AS `postalCode`,`user`.`religion` AS `religion`,`user`.`sn` AS `sn`,`user`.`telephoneNumber` AS `telephoneNumber`,`user`.`workOrg` AS `workOrg`,`user`.`status` AS `status`,`user`.`supporterCorpName` AS `supporterCorpName`,`user`.`supporterDept` AS `supporterDept`,`user`.`supporterCorpContact` AS `supporterCorpContact`,`user`.`superviseDept` AS `superviseDept`,`user`.`supervisor` AS `supervisor`,`user`.`category` AS `category`,`user`.`syncId` AS `syncId` from `ums_user_sev_tab` `user`

 

我们在mysql创建view、trigger、function、procedure、等时都需要定义以下几个属性

ALGORITHM=UNDEFINED：指定视图的处理算法；①

DEFINER=`root`@`localhost`：指定视图创建者；

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；②

任意用户X访问此VIEW时，能否成功取决于X是否有调用该VIEW的权限，以及definer是否有view中的SELECT的权限。

只需要修改创建同名用户或者修改definer即可。

alter DEFINER = 'xx'@'localhost' view xxxx as ……