Skip to Main Content
  • Questions
  • User Requiring Access to SQL Tuning Advisor Getting ORA-13616

Breadcrumb

Easter

Question and Answer

Chris Saxon

Thanks for the question, Ahmad.

Asked: January 23, 2020 - 7:45 am UTC

Answered by: Chris Saxon - Last updated: January 24, 2020 - 10:45 am UTC

Category: Database Administration - Version: 12.2.0.1.0

Viewed 100+ times

You Asked

Hi,

we have created a User to manage the SQL Tuning Advisor.
Due to internal policy /Guideline we created a Role and assigned the Role the following Oracle Privs :


Role            Privs
------------------------------
PERF_OWN CREATE JOB
PERF_OWN ADMINISTER SQL TUNING SET
PERF_OWN ADVISOR
PERF_OWN SELECT ANY DICTIONARY


The user tried to create an SQL Tuning Task and received the following Error:

ORA-13616: The current user PERF has not been granted the ADVISOR privilege.


is it possible that These Oracle privs don't work if assigned to Role and the Role is then assigned to the User?

what if we can't grant These Privs directly to the User due to some internal Policy that relates to Security?


Looking Forward to your support.

Regards

and we said...

Have you granted the role to the user?

Using a role for the advisor privilege works fine for me:

drop user u cascade;
drop role r;

create role r;
grant advisor to r;

grant r, create session to u identified by u;

conn u/u

declare
  task_name    varchar2(30) := 'test_task';
  my_task_name varchar2(30);
begin
  my_task_name := dbms_sqltune.create_tuning_task (
     sql_text    => 'select * from dual'
  ,  user_name   => 'u'
  ,  scope       => 'comprehensive'
  ,  task_name   => task_name
  );
  dbms_sqltune.execute_tuning_task ( task_name=> task_name );
end;
/

select task_id, task_name, status, status_message
from   user_advisor_log;

set long 1000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task( 'test_task' )
from   dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_task
Tuning Task Owner  : U
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 01/23/2020 09:42:50
Completed at       : 01/23/2020 09:42:50
...


So what exactly are you doing?

But in any case:

we can't grant These Privs directly to the User due to some internal Policy that relates to Security

is a daft policy!

If you want to access objects (tables, views, etc.) in another schema using PL/SQL, then you HAVE to grant the privileges directly.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

So you should work to get this policy changed.

and you rated our response

  (3 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

January 23, 2020 - 11:34 am UTC

Reviewer: A reader

Hi Chris,


thx for the explaination - i noticed we have created the Role with "Admin Option" ... maybe this is causing us the problem?

Please look at our case below:

 -- Create User 
 CREATE USER "PERF"    
      DEFAULT TABLESPACE "TOOLS"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "NORMAL";

-- Create Role and assign Advisor,etc  to it... 
  CREATE ROLE PERF_OWN;
  GRANT ADMINISTER SQL TUNING SET TO "PERF_OWN";
  GRANT CREATE JOB TO "PERF_OWN";
  GRANT ADVISOR TO "PERF_OWN";
  GRANT SELECT ANY DICTIONARY TO "PERF_OWN";

-- Assign the Role to the User and enable Admin Option
  GRANT "PERF_OWN" TO "PERF" WITH ADMIN OPTION;




We enabled the Admin Option, because we noticed that " Select Any Dictionary" doesnt work properly if assigned to user via a Role.

Could you please test it at your side?

Thx
Chris Saxon

Followup  

January 23, 2020 - 11:59 am UTC

What's your complete test case - including the tuning task that you run?

The admin option shouldn't make a difference. It still works for me:

drop USER "PERF" cascade;
drop ROLE PERF_OWN;
CREATE USER "PERF"   
   identified by perf;
 

-- Create Role and assign Advisor,etc  to it... 
CREATE ROLE PERF_OWN;
GRANT ADMINISTER SQL TUNING SET TO "PERF_OWN";
GRANT CREATE JOB TO "PERF_OWN";
GRANT ADVISOR TO "PERF_OWN";
GRANT SELECT ANY DICTIONARY TO "PERF_OWN";
GRANT CREATE SESSION TO "PERF_OWN";

-- Assign the Role to the User and enable Admin Option
GRANT "PERF_OWN" TO "PERF" WITH ADMIN OPTION;

conn perf/perf

declare
  task_name    varchar2(30) := 'test_task';
  my_task_name varchar2(30);
begin
  my_task_name := dbms_sqltune.create_tuning_task (
     sql_text    => 'select * from dual'
  ,  user_name   => 'u'
  ,  scope       => 'comprehensive'
  ,  task_name   => task_name
  );
  dbms_sqltune.execute_tuning_task ( task_name=> task_name );
end;
/

select task_id, task_name, status, status_message
from   user_advisor_log;

set long 1000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task( 'test_task' )
from   dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_task
Tuning Task Owner  : PERF
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 01/23/2020 06:58:12
Completed at       : 01/23/2020 06:58:14
...

January 23, 2020 - 1:52 pm UTC

Reviewer: A reader

here is a test scenario -- we use tables and scheduler Job to execute SQL Tuning Tasks which have been created by normal developers



drop USER "PERF" cascade;
drop ROLE PERF_OWN;
CREATE USER "PERF"   identified by perf;
 

-- Create Role and assign Advisor,etc  to it... 
CREATE ROLE PERF_OWN;
GRANT ADMINISTER SQL TUNING SET TO "PERF_OWN";
GRANT CREATE JOB TO "PERF_OWN";
GRANT ADVISOR TO "PERF_OWN";
GRANT SELECT ANY DICTIONARY TO "PERF_OWN";
GRANT CREATE SESSION TO "PERF_OWN";

-- Assign the Role to the User and enable Admin Option
GRANT "PERF_OWN" TO "PERF" WITH ADMIN OPTION;

conn perf/perf

  CREATE TABLE "PERF"."TASKS" 
   ( "TASKID" NUMBER(12,0) NOT NULL ENABLE, 
 "SQL_ID" VARCHAR2(24) NOT NULL ENABLE, 
 "PLAN_HASH_VALUE" NUMBER(24,0), 
 "TIME_LIMIT" NUMBER(24,0) NOT NULL ENABLE, 
 "TASK_SCOPE" VARCHAR2(24) DEFAULT 'COMPREHENSIVE', 
 "TASK_NAME" VARCHAR2(24) NOT NULL ENABLE, 
 "DESCRIPTION" VARCHAR2(24), 
 "SNAP_FIRST" NUMBER(24,0), 
 "SNAP_LAST" NUMBER(24,0), 
 "STATUS" VARCHAR2(24), 
 "TO_BE_DELETED" VARCHAR2(4), 
 "ERROR_MESSAGE" CLOB
   );
   
   
   CREATE TABLE "PERF"."TASKS_RESULT" (
    "TASKID"   NUMBER(12,0),
    "RESULT"   CLOB
);


  CREATE OR REPLACE EDITIONABLE PROCEDURE "PERF"."LOAD_TASKS" AS

    stmt_task      VARCHAR2(64);
    err_code       VARCHAR2(24);
    err_msg        VARCHAR2(4000);
    output         CLOB;
    task_counter   NUMBER(24);
CURSOR C IS  SELECT *  FROM  PERF.tasks;

BEGIN
    FOR cr IN C LOOP
 BEGIN

      IF  cr.to_be_deleted = 'Y' THEN
              DELETE FROM PERF.tasks WHERE
                taskid = cr.taskid;

            DELETE FROM PERF.tasks_result WHERE
                taskid = cr.taskid;
 COMMIT;
        dbms_sqltune.drop_tuning_task(cr.task_name);
ELSIF  ( cr.to_be_deleted <>'Y' or cr.to_be_deleted is null) AND (cr.status <> 'EXECUTED' or cr.status is null) THEN

            task_counter := cr.taskid;
            stmt_task := dbms_sqltune.create_tuning_task(
                begin_snap    => cr.snap_first,
                end_snap      => cr.snap_last,
                sql_id        => cr.sql_id,
                scope         => cr.task_scope,
                time_limit    => cr.time_limit,
                task_name     => cr.task_name,
                description   => cr.description
            );

            dbms_sqltune.execute_tuning_task(cr.task_name);
            SELECT
                dbms_sqltune.report_tuning_task(
                    cr.task_name,
                    'TEXT',
                    'ALL'
                )
            INTO
                output
            FROM
                dual;

            INSERT INTO PERF.tasks_result (
                taskid,
                result
            ) VALUES (
                cr.taskid,
                output
            );

            UPDATE PERF.tasks
                SET
                    status = 'EXECUTED'
            WHERE
                taskid = cr.taskid;

        END IF;

EXCEPTION
    WHEN OTHERS THEN
        err_code := sqlcode;
        err_msg := sqlerrm;

  UPDATE PERF.tasks
                SET
                    status = 'FAILED', error_message =err_code || ':' || err_msg
            WHERE
                taskid = cr.taskid;
 END;
    END LOOP;
    COMMIT;
END;
/


BEGIN 
dbms_scheduler.create_job('"EXECUTE_TASKS"',
job_type=>'STORED_PROCEDURE', job_action=>'LOAD_TASKS',
number_of_arguments=>0,
start_date=>NULL,
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>FALSE, 
auto_drop=>FALSE,comments=>'Execute Tuning Tasks'
);
sys.dbms_scheduler.set_attribute('"EXECUTE_TASKS"','NLS_ENV','NLS_LANGUAGE=''GERMAN'' NLS_TERRITORY=''GERMANY'' NLS_CURRENCY=''€'' NLS_ISO_CURRENCY=''GERMANY'' NLS_NUMERIC_CHARACTERS='',.'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD.MM.RR'' NLS_DATE_LANGUAGE=''GERMAN'' NLS_SORT=''GERMAN'' NLS_TIME_FORMAT=''HH24:MI:SSXFF'' NLS_TIMESTAMP_FORMAT=''DD.MM.RR HH24:MI:SSXFF'' NLS_TIME_TZ_FORMAT=''HH24:MI:SSXFF TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD.MM.RR HH24:MI:SSXFF TZR'' NLS_DUAL_CURRENCY=''€'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('"EXECUTE_TASKS"');
COMMIT; 
END; 
/ 


-- Test the functionality : get an SQL_ID from DBA_HIST_SQLSTAT and two SNAP_IDs (SNAP_FIRST & SNAP_LAST) that references  time intervals when the SQL ran  


Insert into PERF.TASKS  (TASKID,SQL_ID,TIME_LIMIT,TASK_NAME,DESCRIPTION,SNAP_FIRST,SNAP_LAST) VALUES (1,??,30,'TEST','TEST',??,??);
COMMIT;

EXEC DBMS_SCHEDULER.RUN_JOB('EXECUTE_TASKS',FALSE);

-- Check result 

SELECT * from PERF.TASKS_RESULT;





Chris Saxon

Followup  

January 23, 2020 - 5:57 pm UTC

It's because you're doing it inside a PL/SQL procedure. You HAVE to have direct grants for this to work.

e.g. see what happens when you've got CREATE TABLE via a role vs directly:

grant create table to perf_own;
conn perf/perf

create or replace procedure p as
begin
  execute immediate 'create table t ( c1 int )';
end p;
/

exec p;

ORA-01031: insufficient privileges
ORA-06512: at "PERF.P", line 3

conn chris

grant create table to perf;

conn perf/perf

exec p;

desc t

Name   Null?   Type         
C1               NUMBER(38)  

January 24, 2020 - 7:01 am UTC

Reviewer: A reader

Hi Chris,


thx for the useful info - but what's the reason for this limitation!

we use roles to consolidate multiple privs .

Regards

Chris Saxon

Followup  

January 24, 2020 - 10:45 am UTC

It's how the privilege model in PL/SQL works - see the link in my original answer for discussion on this.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.