如何最小化授予普通用户查看执行计划所需要的权限
作者 | JiekeXu
来源 | JiekeXu DBA之路（ID: JiekeXu_IT）
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来讨论一下如何最小化授予普通用户查看执行计划所需要的权限，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
前 言
通常在 scott 用户下，查看有几张表我都用 “select * from TAB;” ,但今天想看一下这个 SQL 的执行计划。结果郁闷了，普通用户 scott 无法查看，报错没有权限查看视图“V$SESSION”。
SQL> show userUSER is "SCOTT"SQL> select * from tab;
TNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLE
SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------User has no SELECT privilege on V$SESSION没有查询视图的权限，那就给他查询视图是我权限，众所周知，“V$SESSION” 视图是来源于 “V_$SESSION”,那么赋予普通用户 Scott 查询 “V_$SESSION” 的权限就可以了，那么来试试看。
SQL> grant select on v_$session to scott;
Grant succeeded.
Elapsed: 00:00:00.22再次使用 DBMS_XPLAY.DISPLAY_CURSOR 查看执行计划还是一样的报错！
SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------User has no SELECT privilege on V$SESSION
Elapsed: 00:00:00.23那么，这个 DBMS_XPLAY 到底需要什么样的权限呢？看来按照报错赋予权限还是不够的。我们来看看官方文档有没有相关信息。
官方文档链接：https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-1CDFFBEE-FD15-4245-B3A5-0D54F21CEB0C
The DBMS_XPLAN package supplies five table functions.
These functions are listed below:
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requires SELECT or READ privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. This function also requires SELECT/READ permissions on V$SQL.
DISPLAY_AWR Function requires the user to have SELECT or READ privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.
DISPLAY_SQLSET Function requires the user to have the SELECT or READ privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS.
DISPLAY_SQL_PLAN_BASELINE Function requires the user to have the SELECT or READ privilege on DBA_SQL_PLAN_BASELINES as well as the privileges to execute the SQL statement for which the user is trying to get the plan.
The preceding privileges are granted automatically as part of SELECT_CATALOG_ROLE.我们来翻译一下：
该 DBMS_XPLAN 包提供五个表函数。
下面列出了这些功能：
DISPLAY - 格式化和显示计划表的内容。
DISPLAY_AWR - 格式化并显示 AWR 中存储的 SQL 语句的执行计划的内容。
DISPLAY_CURSOR - 格式化和显示任何加载游标的执行计划的内容。
DISPLAY_SQL_PLAN_BASELINE - 显示由 SQL 句柄标识的 SQL 语句的一个或多个执行计划
DISPLAY_SQLSET - 格式化并显示存储在 SQL 调整集中的语句的执行计划的内容。
DBMS_XPLAN 安全模型
这个包以调用用户的特权运行，而不是包所有者(SYS)。
表函数 DISPLAY_CURSOR 需要在以下固定视图上有 SELECT 或
READ 权限:V$SQL_PLAN, V$SESSION 和 V$SQL_PLAN_STATISTICS_ALL。这个函数还需要对
V$SQL 有 SELECT/READ 权限。
DISPLAY_AWR 功能需要用户拥有 SELECT 或 READ 特权 DBA_HIST_SQL_PLAN，DBA_HIST_SQLTEXT 和 V$DATABASE。
DISPLAY_SQLSET 功能需要用户拥有 SELECT 或 READ 特权的 ALL_SQLSET_STATEMENTS 和 ALL_SQLSET_PLANS。
DISPLAY_SQL_PLAN_BASELINE 函数要求用户具有 SELECT 或 READ 权限 DBA_SQL_PLAN_BASELINES 以及执行用户试图获取计划的 SQL 语句的权限。
上述特权作为 SELECT_CATALOG_ROLE 的一部分自动授予。
说的比较明确了，DISPLAY_CURSOR 需要有 V$SQL_PLAN, V$SESSION 和 V$SQL_PLAN_STATISTICS_ALL 和 V$SQL 四个视图的查询权限。那么看完上面的官方解释后，我们再来试一试。
SQL> grant select on v_$session to scott;
Grant succeeded.
Elapsed: 00:00:00.22SQL> SQL> grant select on v_$sql_plan to scott;
Grant succeeded.
Elapsed: 00:00:00.09SQL> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.
Elapsed: 00:00:00.07SQL> grant select on v_$sql to scott;
Grant succeeded.普通用户 Scott 查看执行计划
SQL> select * from tab;
TNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLE
Elapsed: 00:00:00.01SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 767pug2dbpqpc, child number 0-------------------------------------select * from tab
Plan hash value: 3762034736
------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | | ||* 1 | FILTER | | | | | || 2 | NESTED LOOPS OUTER | | 1437 | | | ||* 3 | HASH JOIN | | 1437 | 1645K| 1645K| 1530K (0)|| 4 | INDEX FULL SCAN | I_USER2 | 167 | | | ||* 5 | INDEX RANGE SCAN | I_OBJ5 | 1437 | | | || 6 | TABLE ACCESS CLUSTER| TAB$ | 1 | | | ||* 7 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | || 8 | NESTED LOOPS | | 1 | | | ||* 9 | INDEX SKIP SCAN | I_USER2 | 1 | | | ||* 10 | INDEX RANGE SCAN | I_OBJ4 | 1 | | | |------------------------------------------------------------------------------欧耶，可以查看执行计划了，那么普通用户使用 DISPLAY_AWR 查看执行计划只要授予查询此三视图 DBA_HIST_SQL_PLAN，DBA_HIST_SQLTEXT 和 V$DATABASE 的权限，也是没有问题的，这里就不再演示了。
总 结
说了这么多来总结一下吧，普通用户使用 DBMS_XPLAN.DISPLAY_CURSOR
查看执行提示没有权限时，由于对权限的严格把控，既不能直接授予 DBA 权限也不能授予 select any table 和 select any
dictionary 权限!!!只需要单独授予 V$SQL_PLAN, V$SESSION 和
V$SQL_PLAN_STATISTICS_ALL 和 V$SQL 这四个视图的查询权限即可。
grant select on v_$sql_plan to scott;grant select on v_$session to scott;grant select on v_$sql_plan_statistics_all to scott;grant select on v_$sql to scott;~本次分享到此结束啦~
❤️ 欢迎关注我的公众号，来一起玩耍吧！！！
——————————————————————--—--————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————----———