mysql修改视图权限不足_怪异的视图权限不足的问题?

怪异的视图权限不足的问题

在比较不同的分页写法时，发现了一个怪异的现象：

使用数据字典视图all_objects时，不能生成执行计划，但是使用dba_objects视图时却能够正常产生执行计划。于是就查阅相关资料【concepts】：

以下是测试情况：

SQL> set autot traceonly

SQL> select * from(select rownum rn,A.object_id,A.object_name from all_objects A ) where rn between 15 and 35

2  ;

已选择21行。

已用时间:  00: 00: 06.05

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

320395  consistent gets

0  physical reads

0  redo size

1529  bytes sent via SQL*Net to client

514  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

21  rows processed

SQL> select * from(select rownum rn,A.object_id,A.object_name from dba_objects A ) where rn between 15 and 35;

已选择21行。

已用时间:  00: 00: 00.05

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   VIEW

2    1     COUNT

3    2       VIEW OF 'DBA_OBJECTS'

4    3         UNION-ALL

5    4           FILTER

6    5             TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

7    6               NESTED LOOPS

8    7                 TABLE ACCESS (FULL) OF 'USER$'

9    7                 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

10    5             TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

11   10               INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

12    4           NESTED LOOPS

13   12             TABLE ACCESS (FULL) OF 'USER$'

14   12             INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics

----------------------------------------------------------

7  recursive calls

0  db block gets

32840  consistent gets

0  physical reads

0  redo size

1529  bytes sent via SQL*Net to client

514  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

21  rows processed

SQL> select count(*) from all_objects;

已用时间:  00: 00: 05.00

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

7  recursive calls

0  db block gets

320395  consistent gets

0  physical reads

0  redo size

378  bytes sent via SQL*Net to client

503  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

当前测试数据库版本信息：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE        9.2.0.1.0        Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

于是也就顺便对两个视图做了一下观察：

SQL> select count(*) from all_objects;

COUNT(*)

----------

39346

SQL> select count(*) from dba_objects;

COUNT(*)

----------

39925

理论上将dba_objects的记录数应该是比all_objects多的，测试结果与实际相符。

SQL> select a.object_id,a.object_name

2  from all_objects a

3  where not exists (select 1 from dba_objects where a.object_id=object_id);

未选定行

所有的all_objects记录都在dba_objects中存在。而dba_objects中有些记录不在all_objects中。

SQL> select count(*)

2  from dba_objects a

3  where not exists (select 1 from all_objects where a.object_id=object_id);

COUNT(*)

----------

579

所以非常纳闷，为啥使用 dba_objects能正常工作，而使用all_objects却不能正常工作呢？希望知道的给个说明？非常感谢！