1 2 Previous Next 17 Replies Latest reply: Jul 10, 2013 4:18 AM by user8058501 RSS

    Privileges needed to use the dbms_sqltune as a non-SYS user

    user8058501
      Hi,

      I gave a user created with the following privileges:

      grant create session
      grant advisor
      grant resource
      grant select on sys.dba_hist_baseline
      grant select on sys.dba_hist_sqltext
      grant select on sys.dba_hist_sqlstat
      grant select on sys.dba_hist_sqlbind
      grant select on sys.dba_hist_optimizer_env
      grant select on sys.dba_hist_snapshot
      grant select on sys.dba_advisor_tasks
      grant select on dba_hist_active_sess_history
      grant select on v_$sql
      grant select on v_$sql_bind_capture
      grant select on v_$sqlarea_plan_hash

      Yet, when I run a sub-program like:

      dbms_sqltune.create_tuning_task
      (
      sql_id => '<sql id>',
      begin_snap => <from snap>,
      end_snap => <to snap>,
      task_name => '<task name>',
      description => null
      );
      exception
      when others then
      raise;
      end;
      /

      I get the error:

      ERROR at line 1:
      ORA-13774: insufficient privileges to select data from the workload repository
      ORA-06512: at line 14

      What are the necessary minimal privileges to run the above sub-program as a non-SYS user?

      Thanks and Regards.
        • 1. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
          Karan
          did you try the following

          SQL> grant ADMINISTER SQL TUNING SET to user;

          SQL> grant ADMINISTER ANY SQL TUNING SET to user;

          SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to user;
          • 2. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
            Karan
            Also if you are trying access cursor cache also along with AWR you must have SELECT ANY DICTIONARY also.
            • 3. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
              user8058501
              Hi,

              Adding the privileges ADMINISTER SQL TUNING SET, ADMINISTER ANY SQL TUNING SET, ADMINISTER SQL MANAGEMENT OBJECT to the others didn't help...

              Of course, if I add SELECT ANY DICTIONARY, it works.

              But I would like to restrict it to just the necessary privileges (security concerns).

              Any other idea?

              Kind Regards.
              • 4. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                Karan
                Do you know how to post a question on forum ? 1st basic thing is to tell which version which you are using like 10.2.0.5 or 11.2 and so on...
                • 5. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                  Karan
                  If it is 11.2.0.1.0 you must be kidding :) See below

                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  SQL> 
                    1  declare
                    2  a varchar2(300);
                    3  begin
                    4  a := dbms_sqltune.create_tuning_task(
                    5  task_name=>'t24',
                    6  sql_id=>'acb5ahx8945w7',
                    7  scope=>'comprehensive');
                    8 end;
                  /
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> select * from session_privs;
                  
                  PRIVILEGE
                  ----------------------------------------
                  CREATE SESSION
                  SELECT ANY DICTIONARY
                  ADVISOR
                  ADMINISTER SQL MANAGEMENT OBJECT
                  
                  SQL> show user
                  USER is "A"
                  SQL> 
                  • 6. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                    Girish Sharma
                    Below link will give you answer in detail :

                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDIGIEJ

                    Regards
                    Girish Sharma
                    • 7. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                      Karan
                      You dont need ADMINISTER SQL TUNING SET, ADMINISTER ANY SQL TUNING SET, ADMINISTER SQL MANAGEMENT OBJECT AT ALL , You just need advisor privilege and yes for select any dictionary it would be too heavy for security reasons, in that case you need to make sure the user is able access AWR sql history.

                      Edited by: Karan on May 14, 2013 10:15 PM
                      • 8. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                        Karan
                        According to my second last reply as i said giving select any dictionary would be too powerful, the following is enough

                        grant select on sys.dba_hist_baseline to a;
                        grant select on sys.dba_hist_sqltext to a;
                        grant select on sys.dba_hist_sqlstat to a;
                        grant select on sys.dba_hist_sqlbind to a;
                        grant select on sys.dba_hist_optimizer_env to a;
                        grant select on sys.dba_hist_snapshot to a;
                        grant select on sys.dba_advisor_tasks to a;
                        grant select on dba_hist_active_sess_history to a;
                        grant select on v_$sql to a;
                        grant select on v_$sql_bind_capture to a;
                        grant select on v_$sqlarea_plan_hash to a;

                        Edited by: Karan on May 14, 2013 10:18 PM
                        • 9. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                          user8058501
                          Thanks to all.

                          @Karan:

                          There is a difference between the code you executed and the one I executed.
                          My SQL ID is from AWR (not to be found in v$session), and becaise of that, in my code I use begin_snap and end_snap.

                          Hera are the steps I have done:


                          SQL> show user
                          USER is "SYS"
                          SQL> create user a identified by "a"
                          default tablespace USERS; 2

                          User created.

                          SQL> grant create session to a;

                          Grant succeeded.

                          SQL> grant select on sys.dba_hist_baseline to a;
                          grant select on sys.dba_hist_sqltext to a;
                          grant select on sys.dba_hist_sqlstat to a;
                          grant select on sys.dba_hist_sqlbind to a;
                          grant select on sys.dba_hist_optimizer_env to a;
                          grant select on sys.dba_hist_snapshot to a;
                          grant select on sys.dba_advisor_tasks to a;
                          grant select on dba_hist_active_sess_history to a;
                          grant select on v_$sql to a;
                          grant select on v_$sql_bind_capture to a;
                          grant select on v_$sqlarea_plan_hash to a;
                          grant advisor to a;

                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>
                          Grant succeeded.

                          SQL>

                          SQL> connect a
                          Enter password:
                          Connected.
                          SQL> show user
                          USER is "A"
                          SQL>

                          SQL> declare
                          2 tune_task varchar2(30) := null;
                          begin
                          3 4 tune_task := dbms_sqltune.create_tuning_task
                          (
                          sql_id => 'gjm43un5cy843',
                          begin_snap => 143,
                          end_snap => 145,
                          task_name => 'MYTEST',
                          description => null
                          5 6 7 8 9 10 11 );
                          12 exception
                          13 when others then
                          14 raise;
                          15 end;
                          16 /
                          declare
                          *
                          ERROR at line 1:
                          ORA-13774: insufficient privileges to select data from the workload repository
                          ORA-06512: at line 14


                          If I run the code without the snapshots values:

                          SQL> show user
                          USER is "A"
                          SQL> declare
                          tune_task varchar2(30) := null;
                          begin
                          tune_task := dbms_sqltune.create_tuning_task
                          (
                          sql_id => 'gjm43un5cy843',
                          task_name => 'MYTEST',
                          description => null
                          );
                          exception
                          when others then
                          raise;
                          end;
                          /
                          2 3 4 5 6 7 8 9 10 11 12 13 14
                          PL/SQL procedure successfully completed.

                          SQL>



                          I can execute that task, but when I want to see the report:

                          USER is "A"
                          SQL>
                          SQL> set long 10000 longchunksize 10000 linesize 200 pagesize 000
                          select dbms_sqltune.report_tuning_task(task_name => 'MYTEST') from dual;
                          SQL> GENERAL INFORMATION SECTION
                          -------------------------------------------------------------------------------
                          Tuning Task Name : MYTEST
                          Tuning Task Owner : A

                          ...


                          I got at the end:

                          -------------------------------------------------------------------------------
                          ERRORS SECTION
                          -------------------------------------------------------------------------------
                          - ORA-00942: table or view does not exist

                          -------------------------------------------------------------------------------

                          Well, this is another problem...


                          Any idea what there is a difference wehen I pass snaphots ID or not?

                          Thanks and Regards.
                          • 10. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                            Karan
                            Yes there is a difference when you access cursor cache and when you access AWR tables, Cursor cache is in memory (shared pool) and AWR tables are in SYSAUX repository.
                            Most of the AWR table names are like WRM$_SNAPSHOT or WRH$_ACTIVE_SESSION_HISTORY. Sometimes in oracle white papers i think you will see the AWR tables also refereed to as the select workload repository (SWRF) tables. AFAIK oracle also offers several tables that allow you to query the AWR repository mostly start with DBA_HIST, followed by a name that describes the table like for example DBA_HIST_FILESTATS, DBA_HIST_DATAFILE or DBA_HIST_SNAPSHOT.

                            Some tables from AWR are listed below

                            DBA_HIST_SYSSTAT
                            DBA_HIST_SEG_STAT
                            DBA_HIST_SEG_STAT_OBJ
                            DBA_HIST_SQLTEXT
                            DBA_HIST_ACTIVE_SESS_HISTORY
                            DBA_HIST_SNAPSHOT
                            DBA_HIST_SQLSTAT
                            • 11. Re: Privileges needed to use the dbms_sqltune as a non-SYS user
                              user8058501
                              Hi,

                              Thanks. I have found that the minimum privileges to create a task are:

                              grant select on sys.dba_hist_sqltext to a;
                              grant select on sys.dba_hist_sqlstat to a;
                              grant select on sys.dba_hist_sqlbind to a;
                              grant select on sys.dba_hist_optimizer_env to a;
                              grant select on sys.dba_hist_snapshot to a;
                              grant select on sys.dba_advisor_tasks to a;
                              grant select on dba_hist_active_sess_history to a;
                              grant select on v_$sql to a;
                              grant select on v_$sql_bind_capture to a;
                              grant select on v_$sqlarea_plan_hash to a;
                              grant advisor to a;
                              grant select on sys.v_$database to a;
                              grant select on sys.v_$instance to a;

                              Then I can execute and run the report for the task.

                              But I end up with another missing privilege:
                              ...
                              -------------------------------------------------------------------------------
                              ERRORS SECTION
                              -------------------------------------------------------------------------------
                              - ORA-00942: table or view does not exist

                              -------------------------------------------------------------------------------


                              The execution plan is not displayed. This is a different problem...

                              Kind Regards.
                              • 12. Re-Privileges needed to use the dbms_sqltune as a non-SYS user
                                user8058501
                                I moved the remaining issue to another thread.
                                • 13. Re: Re-Privileges needed to use the dbms_sqltune as a non-SYS user
                                  DK2010
                                  Hi ,

                                  please check the below test result, you can check the metalink ID 262687.1
                                  SQL> grant create session to test identified by test;
                                  
                                  Grant succeeded.
                                  
                                  SQL> GRANT ADVISOR TO test;
                                  GRANT SELECT_CATALOG_ROLE TO test; # this is required
                                  GRANT EXECUTE ON DBMS_SQLTUNE TO test;
                                  Grant succeeded.
                                  
                                  SQL> 
                                  Grant succeeded.
                                  
                                  SQL> 
                                  
                                  Grant succeeded.
                                  
                                  SQL> conn test/test
                                  Connected.
                                  SQL> show user
                                  USER is "TEST"
                                  SQL> declare
                                  tune_task varchar2(30) := null;
                                  begin
                                  tune_task := dbms_sqltune.create_tuning_task
                                  (
                                  sql_id => '96g93hntrzjtr',
                                  begin_snap => 169,
                                  end_snap => 170,
                                  task_name => 'MYTEST',
                                  description => null
                                  );
                                  exception
                                  when others then
                                  raise;
                                  end;
                                  /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'MYTEST');
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  SQL> 
                                  HTH
                                  • 14. Re-Privileges needed to use the dbms_sqltune as a non-SYS user
                                    user8058501
                                    Hi,

                                    When giving to user A the above privileges, I can create the task.
                                    I just get the error - ORA-00942: table or view does not exist, when I run the report select dbms_sqltune.report_tuning_task(task_name => 'MYTEST').

                                    Even if I add to use the privilege SELECT_CATALOG_ROLE, I still get that error.

                                    Kind Regards.
                                    1 2 Previous Next