mysql8 兼容 mysql5 创建视图,创建只能访问指定视图的账号
#查看mode
select @@sql_mode;
#临时设置mode,去掉ONLY_FULL_GROUP_BY (下次重连mysql后mode会恢复)
set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
#创建视图
CREATE VIEW view_yl_jianlian_accessory AS SELECT * FROM yl_jianlian_accessory;
#创建账号test3 , %是任意访问源的意思. 注意,这里是%下面也要对应是%
create user test3@'%' identified by '123456abc!B';
#给账号赋予访问指定视图的权限
grant all privileges on unionpbx.view_yl_jianlian_accessory to test3@'%';
#允许账号密码认证
ALTER USER 'test3'@'%' IDENTIFIED WITH mysql_native_password BY '123456abc!B';
#刷新. 注:这个必须要执行
flush privileges;