如何最小化授予普通用户查看执行计划所需要的权限

原创 JiekeXu All China Database Union 2021-12-08

1735

通常在 scott 用户下，查看有几张表我都用 “select * from TAB;” 而不用 user_tables,但是今天想看一下这个 SQL 的执行计划。结果郁闷了，普通用户无法查看，报错没有权限查看视图“V$SESSION”。

SQL> show user
USER is "SCOTT"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       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.22
SQL> 
SQL> grant select on v_$sql_plan to scott;

Grant succeeded.

Elapsed: 00:00:00.09
SQL> grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

Elapsed: 00:00:00.07
SQL>  grant select on v_$sql to scott;

Grant succeeded.
复制

图片.png

下面使用普通用户 Scott 查看执行计划

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

Elapsed: 00:00:00.01
SQL> 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 |       |       |          |
------------------------------------------------------------------------------
复制

图片.png

总结

说了这么多来总结一下吧，普通用户使用 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
————————————————————————----———

图片.png