问题描述
关于 同义词v$block_change_tracking 的原数据,理论上应该是从数据字典的基表访问。
但我发现 同义词 根据 视图创建
视图 又根据同义词创建
感觉是个死循环
查看oracle 创建视图语句:
cd $ORACLE_HOME/rdbms/admin
[oracle@edbjr2p1 admin]$ grep block_change_tracking *.sql
catfusrg.sql: 'from v$block_change_tracking where status = ''ENABLED''';
cdfixed.sql:create or replace view v_$block_change_tracking as
cdfixed.sql: select * from v$block_change_tracking;
cdfixed.sql:create or replace public synonym v$block_change_tracking for
cdfixed.sql: v_$block_change_tracking;
cdfixed.sql:grant select on v_$block_change_tracking to select_catalog_role;
$more cdfixed.sql
create or replace view v_$block_change_tracking as
select * from v$block_change_tracking;
create or replace public synonym v$block_change_tracking for
v_$block_change_tracking;
grant select on v_$block_change_tracking to select_catalog_role;
专家解答
首先要理解SYS和PUBLIC
SYS下的对象没授权之前是无法访问的,具体看下面的注解
create or replace view v_$block_change_tracking as --SYS的V_$视图,用于授权语句
select * from v$block_change_tracking; --SYS的V$基表视图,基表其实是oracle在内存中虚拟出来的表
create or replace public synonym v$block_change_tracking for --PUBLIC的同义词,可被所有用户访问
v_$block_change_tracking; --这是刚刚创建出来的VIEW
grant select on v_$block_change_tracking to select_catalog_role; --将SYS的V_$视图的select权限授予角色select_catalog_role
以上语句执行完后,使得具备select_catalog_role角色的用户,都可以用V$视图的名字访问到SYS下的V$同名视图,但此时如果你用 SYS.V$视图去访问,你是没权限的
可以借助执行计划查看相关基表。
SQL> EXPLAIN PLAN FOR SELECT * FROM v$block_change_tracking; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- Plan hash value: 1391620196 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 60 | 0 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 1 | 60 | 0 (0)| 00:00:01 | | 2 | MERGE JOIN CARTESIAN | | 1 | 17 | 0 (0)| 00:00:01 | | 3 | FIXED TABLE FULL | X$KCCDI2 | 1 | 4 | 0 (0)| 00:00:01 | | 4 | BUFFER SORT | | 1 | 13 | | | | 5 | VIEW | | 1 | 13 | | | | 6 | SORT AGGREGATE | | 1 | 26 | | | | 7 | FIXED TABLE FULL | X$KRCFH | 1 | 26 | | | |* 8 | FIXED TABLE FIXED INDEX| X$KCCFN (ind:1) | 1 | 43 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter("FNTYP"(+)=200 AND "FNNUM"(+)="DI2CTFN") 20 rows selected.