in Oracle

EXECUTE Privilege on DBMS_SPM not sufficient

In 11.2.0.2

Here is a quick demo to show that the “ADMINISTER SQL MANAGEMENT OBJECT” privilege is required for a non-DBA user to use DBMS_SPM even if EXECUTE has been granted on DBMS_SPM.

SQL> create user spm_test identified by spm_test quota unlimited on users;

User created.

SQL> alter user spm_test default tablespace users;

User altered.

SQL> grant create session, create table to spm_test;

Grant succeeded.

SQL> connect spm_test/spm_test
Connected.
SQL> create table spm_test_table (id_column number primary key, data_col varchar2(15));

Table created.

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

Session altered.

SQL> insert into spm_test_table select rownum, to_char(rownum) from dual connect by level < 10001;

10000 rows created.

SQL> commit;

Commit complete.

SQL> variable qrn number;
SQL> exec :qrn := 5;

PL/SQL procedure successfully completed.

SQL> select * from spm_test_table where id_column=:qrn;

ID_COLUMN DATA_COL
---------- ---------------
5 5

SQL> select * from spm_test_table where id_column=:qrn;

ID_COLUMN DATA_COL
---------- ---------------
5 5

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

Session altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL>
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed
2 from dba_sql_plan_baselines
3 where creator='SPM_TEST'
4 /

SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ENA ACC FIX
--- --- ---
SQL_6ceee9b24e9fd50a SQL_PLAN_6tvr9q979zp8a1e198e55
select * from spm_test_table where id_column=:qrn
YES YES NO


SQL>
SQL> connect spm_test/spm_test
Connected.
SQL> declare
2 ret_value pls_integer;
3 begin
4 ret_value := dbms_spm.drop_sql_plan_baseline(
5 sql_handle=>'SQL_6ceee9b24e9fd50a',plan_name=>'SQL_PLAN_6tvr9q979zp8a1e198e55');
6 end;
7 /
declare
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4


SQL> select table_name, grantee, privilege
2 from all_tab_privs
3 where table_name='DBMS_SPM'
4 order by 2,3;

TABLE_NAME GRANTEE
------------------------------ ------------------------------
PRIVILEGE
----------------------------------------
DBMS_SPM PUBLIC
EXECUTE


SQL>
SQL> connect / as sysdba
Connected.
SQL> grant execute on dbms_spm to spm_test;

Grant succeeded.

SQL> connect spm_test/spm_test
Connected.
SQL> declare
2 ret_value pls_integer;
3 begin
4 ret_value := dbms_spm.drop_sql_plan_baseline(
5 sql_handle=>'SQL_6ceee9b24e9fd50a',plan_name=>'SQL_PLAN_6tvr9q979zp8a1e198e55');
6 dbms_output.put_line(ret_value);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4


SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> grant administer sql management object to spm_test;

Grant succeeded.

SQL>
SQL> connect spm_test/spm_test
Connected.
SQL> declare
2 ret_value pls_integer;
3 begin
4 ret_value := dbms_spm.drop_sql_plan_baseline(
5 sql_handle=>'SQL_6ceee9b24e9fd50a',plan_name=>'SQL_PLAN_6tvr9q979zp8a1e198e55');
6 dbms_output.put_line(ret_value);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed
2 from dba_sql_plan_baselines
3 where creator = 'SPM_TEST'
4 /

no rows selected

SQL>

Thus, although EXECUTE on DBMS_SPM had been granted to PUBLIC and even explicitly to this ordinary user, it couldn’t execute DROP_SQL_PLAN_BASELINE. The ADMINISTER SQL MANAGEMENT OBJECT privilege was required.
.
.
.