转 User has no SELECT privilege on V$SESSION

本文详细记录了解决Oracle中使用dbms_xplan.display_cursor时遇到的权限问题的过程,包括所需的V$视图权限及具体授予方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 
使用dbms_xplan.display_cursor function的时候发现如下错误。
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
SQL> 
然后查看官网发现这么几句话:
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 table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------User has no SELECT privilege on V$SESSION
SQL>
依然出错,咋回事?然后继续看联机手册,又发现了这么一句话:
To use the DISPLAY_CURSOR functionality, the calling user must haveSELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL,V$SQL and V$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> 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> 

转载于:https://www.cnblogs.com/hbhzz/p/3998825.html

start /home/dm_dwzl.sql SQL> CREATE TABLESPACE ops_tbs DATAFILE '/dmdata/ops_tbs01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M; CREATE TABLESPACE ops_tbs DATAFILE '/dmdata/ops_tbs01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M; DATAFILE '/dmdata/ops_tbs01.dbf' SIZE 500M * line 2, column 55, nearby [M] has error[-2007]:Syntax error. used time: 0.215(ms). Execute id is 0. SQL> CREATE USER ops_admin IDENTIFIED BY "SecurePwd@2023" DEFAULT TABLESPACE ops_tbs QUOTA UNLIMITED ON ops_tbs; CREATE USER ops_admin IDENTIFIED BY "SecurePwd@2023" DEFAULT TABLESPACE ops_tbs QUOTA UNLIMITED ON ops_tbs; [-3405]:Error in line: 3 tablespace [ops_tbs] not already exist. used time: 1.633(ms). Execute id is 0. SQL> CREATE ROLE RESTRICTED_DBA; executed successfully used time: 1.584(ms). Execute id is 4103. SQL> GRANT SELECT ANY TABLE, -- 查询所有表 CREATE SESSION, -- 连接权限 CREATE TABLE, -- 建表权限 ALTER TABLESPACE, -- 表空间管理 BACKUP DATABASE, -- 数据库备份 SELECT ON V$ SESSIONS, -- 会话监控 SELECT ON V$ DATAFILE -- 数据文件监控 TO RESTRICTED_DBA; GRANT SELECT ANY TABLE, -- 查询所有表 CREATE SESSION, -- 连接权限 CREATE TABLE, -- 建表权限 ALTER TABLESPACE, -- 表空间管理 BACKUP DATABASE, -- 数据库备份 SELECT ON V$ SESSIONS, -- 会话监控 SELECT ON V$ DATAFILE -- 数据文件监控 TO RESTRICTED_DBA; SELECT ON V$ SESSIONS, -- 会话监控 * line 7, column 18, nearby [ON] has error[-2007]:Syntax error. used time: 0.109(ms). Execute id is 0. SQL> -- 5. 显式拒绝高危权限 DENY DROP ANY TABLE, -- 禁止删表 ALTER DATABASE, -- 禁止修改数据库 CREATE USER, -- 禁止创建用户 DROP USER, -- 禁止删除用户 GRANT ANY PRIVILEGE -- 禁止赋权 TO RESTRICTED_DBA; -- 5. 显式拒绝高危权限 DENY DROP ANY TABLE, -- 禁止删表 ALTER DATABASE, -- 禁止修改数据库 CREATE USER, -- 禁止创建用户 DROP USER, -- 禁止删除用户 GRANT ANY PRIVILEGE -- 禁止赋权 TO RESTRICTED_DBA; DROP ANY TABLE, -- 禁止删表 * line 3, column 7, nearby [DROP] has error[-2007]:Syntax error. used time: 0.063(ms). Execute id is 0. SQL> -- 6. 将角色授予用户 GRANT RESTRICTED_DBA TO ops_admin; -- 6. 将角色授予用户 GRANT RESTRICTED_DBA TO ops_admin; [-2509]:Error in line: 2 Invalid user or role[ops_admin]. used time: 0.111(ms). Execute id is 0. 修正一下
最新发布
06-25
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值