11gR1中关于oracle误报权限

session 1:

SQL> SELECT id FROM t1 WHERE rownum<2;
 
        ID
----------
      7499
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'iostats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
USER has no SELECT privilege ON V$SQL_PLAN_STATISTICS_ALL

session 2:

SQL> GRANT SELECT ON v_$sql_plan_statistics_all TO luoxuan;
 
GRANT succeeded.

session 1:

SQL> SELECT id FROM t1 WHERE rownum<2;
 
        ID
----------
      7499
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'iostats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------
USER has no SELECT privilege ON V$SQL_PLAN_STATISTICS_ALL

这里明明给权限了,但还是报没有这个视图的访问权限!

由于调用display_cursor函数还要访问v$sql\v$sql_plan(对v$session权限已有)函数,如果要报错,也应该是报这两个视图没有访问权限!

session 2:

SQL> GRANT SELECT ON v_$sql_plan TO luoxuan;
 
GRANT succeeded.

session 1:

SQL> SELECT id FROM t1 WHERE rownum<2;
 
        ID
----------
      7499
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'iostats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------
USER has no SELECT privilege ON V$SQL_PLAN_STATISTICS_ALL

还是报这个错误

session 2:

SQL> GRANT SELECT ON v_$sql TO luoxuan;
 
GRANT succeeded.

session 1:

SQL>  SELECT id FROM t1 WHERE rownum<2;
 
        ID
----------
      7499
 
SQL>  SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'iostats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  ax0j79fzs5k4j, child NUMBER 0
-------------------------------------
 SELECT id FROM t1 WHERE rownum<2
 
Plan hash VALUE: 3836375644
 
--------------------------------------------
| Id  | Operation          | Name | E-ROWS |
--------------------------------------------
|*  1 |  COUNT STOPKEY     |      |        |
|   2 |   TABLE ACCESS FULL| T1   |      1 |

One Response to “11gR1中关于oracle误报权限”

  1. 编程小强说道:

    我用11.1.0.6.0也出现了这样的情况,上午测试的时候还好好的,下午测试的时候就报没权限了,汗..

Post a Comment