Search My Oracle Blog

Custom Search

24 September, 2014

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.
.
.
.


2 comments:

Anonymous said...

Excellent post for the newbie.Now, here's the catch I see : From this step i have followed the steps & i am getting a FTS for the INSERT statement. Could you confirm why optimizer is favoring a FTS instead of an index ?.

Regards,
Shadab

Hemant K Chitale said...

Anonymous,
I am not sure if your test is exactly the same as mine, with the rows, cardinality, order of testing sqls, number of executions, version and instance and optimizer parameters !

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016