与其他schema下表同名视图实验
2019-01-03 15:23 by AllegroCantabile, ... 阅读, ... 评论, 收藏, 编辑客户提出需求:要求创建用户A,访问视图view,视图基表属于用户B,视图与表同名。OK,开始实验。
实验场景:创建用户CDC,访问scott.dba_tables的部分字段(OWNER,TABLE_NAME,TABLESPACE_NAME),通过同名视图访问
#创建简单表
SQL> create table scott.dba_tables as select * from dba_tables;
Table created.
#创建用户并授权
SQL> create user CDC identified by 123456;
User created.
grant create session to CDC;或者grant connect to CDC; --connect角色具有create session的权限
SQL> grant connect to CDC;
Grant succeeded.
#没有访问scott.dba_tables的权限时出现报错,ORA-00942表或视图不存在
SQL> create view CDC.dba_tables as select OWNER,TABLE_NAME,TABLESPACE_NAME from scott.dba_tables;
create view CDC.dba_tables as select OWNER,TABLE_NAME,TABLESPACE_NAME from scott.dba_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
#授权后成功创建视图
SQL> grant select on scott.dba_tables to CDC;
Grant succeeded.
SQL> create view CDC.dba_tables as select OWNER,TABLE_NAME,TABLESPACE_NAME from scott.dba_tables;
View created.
#对表scott.dba_tables进行更新测试
SQL> select count(*) from scott.dba_tables;
COUNT(*)
----------
2868
SQL> select count(*) from CDC.dba_tables;
COUNT(*)
----------
2868
SQL> insert into scott.dba_tables(OWNER,TABLE_NAME) values ('CDC','CDC');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.dba_tables;
COUNT(*)
----------
2869
SQL> select count(*) from CDC.dba_tables;
COUNT(*)
----------
2869
#回收权限后继续更新scott.dba_tables,出现报错
SQL> revoke select on scott.dba_tables from CDC;
Revoke succeeded.
SQL> insert into scott.dba_tables(OWNER,TABLE_NAME) values ('CDC1','CDC1');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.dba_tables;
COUNT(*)
----------
2870
SQL> select count(*) from CDC.dba_tables;
select count(*) from CDC.dba_tables
*
ERROR at line 1:
ORA-04063: view "CDC.DBA_TABLES" has errors
SQL> conn CDC/123456
Connected.
SQL> select count(*) from CDC.dba_tables;
select count(*) from CDC.dba_tables
*
ERROR at line 1:
ORA-04063: view "CDC.DBA_TABLES" has errors
#重新授权后可以正常访问
SQL> grant select on scott.dba_tables to CDC;
Grant succeeded.
SQL> select count(*) from CDC.dba_tables;
COUNT(*)
----------
2870
结论:可以实现不同用户下表的同名视图,但是用户本身可以直接查询scott.dba_tables,并且通过user_views查到基表的来源,所以存在数据泄露的风险。所以还是不建议如此实施。
SQL> select VIEW_NAME,TEXT from user_views;
VIEW_NAME TEXT
------------------------------ ----------------------------------------------------------------------
DBA_TABLES select OWNER,TABLE_NAME,TABLESPACE_NAME from scott.dba_tables
据说正常思路是这样子的,也就是不能同名视图,安全可靠:
SQL> create view scott.view_dba_tables as select * from scott.dba_tables;
View created.
SQL> grant select on scott.view_dba_tables to CDC;
Grant succeeded.
SQL> conn CDC/123456
Connected.
SQL> select count(*) from scott.view_dba_tables;
COUNT(*)
----------
2871