例句:
Create View
CREATE ALGORITHM=UNDEFINED DEFINER=hck@192.168.%.% SQL SECURITY DEFINER VIEW view_activity_coupon_record_grid AS
select ra.ra_title AS ra_title, ra.ra_start_date AS ra_start_date, ra.ra_end_date AS ra_end_date, ra.ra_is_official AS ra_is_official, rac.rac_type AS rac_type, rac.rac_floor_money AS rac_floor_money, rac.rh_id AS rh_id, rac.rac_begin_date AS rac_begin_date, rac.rac_past_date AS rac_past_date, rac.rhr_id AS rhr_id, rac.rhrt_id AS rhrt_id, rac.rac_lease_month AS rac_lease_month, rac.rac_money AS rac_money, rac.rac_discount AS rac_discount, rac.rac_monthly_rent AS rac_monthly_rent, ra.ra_key AS ra_key, rgac.mua_id AS mua_id, rgac.rgac_secret_key AS rgac_secret_key, rgac.rhr_id AS astrict_rhr_id, rgac.rgac_is_use AS rgac_is_use, rac.rac_key AS rac_key, rgac.rgac_key AS rgac_key, rac.pc_id AS pc_id, ra.ra_state AS ra_state, rac.rac_state AS rac_state, rgac.rgac_state AS rgac_state, ra.ra_status AS ra_status from ((r_activity ra left join r_activity_coupon rac on ((ra.ra_key = rac.ra_id))) left join r_get_activity_coupon rgac on ((rac.rac_key = rgac.rac_id)))
ALGORITHM=UNDEFINED:指定视图的处理算法;
DEFINER=root@localhost:指定视图创建者;
SQL SECURITY DEFINER:指定视图查询数据时的安全验证方式;
任意用户X访问此VIEW时,能否成功取决于X是否有调用该VIEW的权限,以及definer是否有view中的SELECT的权限。
只需要修改创建同名用户或者修改definer即可。
alter DEFINER = [email protected] view xxxx as ……
ALGORITHM可取三个值:MERGE、TEMPTABLE或UNDEFINED。
如果没有ALGORITHM子句,默认算法是UNDEFINED(未定义的)。算法会影响MySQL处理视图的方式。
对于MERGE,会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分。
对于TEMPTABLE,视图的结果将被置于临时表中,然后使用它执行语句。
对于UNDEFINED,MySQL自己选择所要使用的算法。如果可能,它倾向于MERGE而不是TEMPTABLE,
这是因为MERGE通常更有效,而且如果使用了临时表,视图是不可更新的。
DEFINER 表示按定义者拥有的权限来执行
INVOKER 表示用调用者的权限来执行。默认情况下,系统指定为DEFINER