root:
create table tab2(c1 int, c2 int);
create SQL SECURITY INVOKER view v_tab2 as select * from tab2; -- 指定INVOKER,执行期间使用调用者的权限
create user user1;
grant insert on test.v_tab2 to user1;
user1:
mysql> insert into test.v_tab2 values(1,1); -- 失败
ERROR 1356 (HY000): View 'test.v_tab2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
root:
grant insert on test.tab2 to user1;
user1:
mysql> insert into test.v_tab2 values(1,1); -- 成功
Query OK, 1 row affected (0.00 sec)
通过create view v_tab3 as select * from tab3;这种方式创建视图,在向视图插入数据时,
mysql需要用户有视图上的INSERT权限,同时,还需要视图的创建者要有基表上的INSERT权限,
ob只需要用户有视图上的INSERT权限:
root:
create table tab3(c1 int, c2 int);
create user user1;
create user user2;
grant create view on test.* to user2;
grant select on test.tab3 to user2;
user2:
create view v_tab3 as select * from tab3;
root:
grant insert on test.v_tab3 to user1;
user1:
insert into test.v_tab3 values(1,1); -- mysql失败,ob成功
mysql报错:ERROR 1356 (HY000): View 'test.v_tab3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
以下在mysql中执行:
root:
grant insert on test.tab3 to user2; -- 给视图的创建者授权基表tab3上的INSERT权限
user1:
insert into test.v_tab3 values(1,1); -- 成功