User has no SELECT privilege on V$SESSION
来源:互联网 发布:淘宝怎么养号快 编辑:程序博客网 时间:2024/05/22 17:08
使用dbms_xplan.display_cursor function的时候发现如下错误。
SQL> select * from amy_dept;
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
然后查看官网发现这么几句话:
This package runs with the privileges of the calling user, not the package owner (
SYS
). The table functionDISPLAY_CURSOR
requires to have select privileges on the following fixed views:V$SQL_PLAN
,V$SESSION
and V$SQL_PLAN_STATISTICS_ALL.
也就是说要有,v$sql_plan,v$session,v$sql_plan_statistics_all权限。
如下:
SQL> conn sys/root as sysdba
Connected.
SQL> grant select on v_$sql_plan to rhys;
Grant succeeded.
SQL> grant select on v_$session to rhys;
Grant succeeded.
SQL> grant select on v_$sql_plan_statistics_all to rhys;
Grant succeeded.
SQL> conn rhys/amy
Connected.
SQL> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from amy_dept;
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
To use the
DISPLAY_CURSOR
functionality, the calling user must haveSELECT
privilege on the fixed viewsV$SQL_PLAN_STATISTICS_ALL
,V$SQL
andV$SQL_PLAN
, otherwise it shows an appropriate error message.呵呵。那么这就明了,要想使用display_cursor那么必须要有对v$sql,v$sql_plan_statistics_all,v$session,v$sql_pan这四个视图的权限。先看看rhys账户有哪些权限吧:
SQL> select
2 substr(grantee,1,20),
3 substr(owner,1,20),
4 substr(table_name,1,20),
5 substr(grantor,1,20),
6 substr(privilege,1,30),
7 substr(grantable,1,20),
8 substr(hierarchy,1,20)
9 from user_tab_privs;
SUBSTR(GRANTEE,1,20) SUBSTR(OWNER,1,20) SUBSTR(TABLE_NAME,1, SUBSTR(GRANTOR,1,20) SUBSTR(PRIVILEGE,1,30) SUB SUB
-------------------- -------------------- -------------------- -------------------- ------------------------------ --- ---
RHYS SYS V_$SESSION SYS SELECT NO NO
RHYS SYS V_$SQL_PLAN SYS SELECT NO NO
RHYS SYS V_$SQL_PLAN_STATISTI SYS SELECT NO NO
SQL>
SQL> conn sys/root as sysdba
Connected.
SQL> grant select on v_$sql to rhys;
Grant succeeded.
SQL> conn rhys/amy
Connected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dyk4dprp70d74, child number 0
-------------------------------------
SELECT DECODE('A','A','1','2') FROM DUAL
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 rows selected.
SQL>
- User has no SELECT privilege on V$SESSION
- User has no SELECT privilege on V$SESSION(执行计划相关)
- 49.The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table
- user * lacks CREATE SESSION privilege;logon denied
- user ... lacks CREATE SESSION privilege:logon denied
- has no select() function
- ORA-01045: user DEMO lacks CREATE SESSION privilege; logon denied
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- 解决:ORA-01045:user * lacks CREATE SESSION privilege;logon denied
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- oracle创建用户ORA-01045:user lacks CREATE SESSION privilege;
- ORA-01045: user lacks CREATE SESSION privilege解决办法
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- ORA-01045:user lacks CREATE SESSION privilege;logon denied
- ORA-01045: user lacks CREATE SESSION privilege; logon denied
- 'WSGIRequest' object has no attribute 'user'
- XML
- Deer is Better! ural 1740
- 配置tomcat以指定的身份运行(Linux平台)
- CODE 30: Binary Tree Zigzag Level Order Traversal
- iOS 5 ARC 入门 (1/3)
- User has no SELECT privilege on V$SESSION
- iOS 5 ARC 入门 (2/3)
- CODE 31: Binary Tree Level Order Traversal
- 区间dp-hdu-4745-Two Rabbits
- 如何终止 DIV 的 float 属性
- Mozilla Thunderbird如何设置开启密码
- poj1966Cable TV Network(无向图最小点割集 ISAP+邻接矩阵)
- android EditText取消自动获取焦点 改善用户体验
- HDU 2191 悼念512汶川大地震遇难同胞——珍惜现在,感恩生活 解题报告