普通用户调用dbms_xplan.display_cursor函数权限问题

dbms_xplan.display_cursor函数可以用来获取sql执行路径，不带任何参数可以获取当前session的最后一条sql语句的执行路径，或者可以指定参数获取指定sql的执行路径。
普通数据库用户在调用该函数时，若没有赋予相关表的查询权限，将会报错，如下：

点击(此处)折叠或打开

1. SQL> select count(*) from t;
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.      99296
   
6. 
   
7. SQL> select * from table(dbms_xplan.display_cursor);
   
8. 
   
9. PLAN_TABLE_OUTPUT
   
10. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
11. User has no SELECT privilege on V$SESSION

此时可知该用户没有查询v$session视图的权限，需要赋予v_$session视图的权限

点击(此处)折叠或打开

1. SQL> grant select on v_$session to test;
   
2. 
   
3. Grant succeeded.

再次执行该语句，发现依然报错，原来调用该函数，还需要赋予查询v$sql、v$sql_plan等表的权限

点击(此处)折叠或打开

1. SQL> select * from table(dbms_xplan.display_cursor);
   
2. 
   
3. PLAN_TABLE_OUTPUT
   
4. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
5. User has no SELECT privilege on V$SESSION

点击(此处)折叠或打开

1. SQL> grant select on v_$sql to test;
   
2. 
   
3. Grant succeeded.
   
4. 
   
5. SQL> grant select on v_$sql_plan to test;
   
6. 
   
7. Grant succeeded.

点击(此处)折叠或打开

1. SQL> select * from table(dbms_xplan.display_cursor);
   
2. 
   
3. PLAN_TABLE_OUTPUT
   
4. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
5. SQL_ID 3694ctwp1817g, child number 1
   
6. -------------------------------------
   
7.  select * from table(dbms_xplan.display_cursor)
   
8. 
   
9. Plan hash value: 3713220770
   
10. 
    
11. ----------------------------------------------------------------------------------------------------
    
12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
13. ----------------------------------------------------------------------------------------------------
    
14. | 0 | SELECT STATEMENT | | | | 29 (100)| |
    
15. | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 2 | 4 | 29 (0)| 00:00:01 |
    
16. ----------------------------------------------------------------------------------------------------
    
17. 
    
18. Note
    
19. -----
    
20.    - cardinality feedback used for this statement
    
21. 
    
22. 
    
23. 17 rows selected.

可知，普通数据库用户调用dbms_xplan.display_cursor函数，需赋予表v$session、v$sql、v$sql_plan的查询权限。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24420807/viewspace-1343818/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24420807/viewspace-1343818/