与其他schema下表同名视图实验
2019-01-03 15:23 AllegroCantabile 阅读(352) 评论(0) 编辑 收藏 举报客户提出需求:要求创建用户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