RESOLUTION 1:
Create the quest_sl_sqlab_role. Use the Server Side Object Installation Wizard (in Toad under the Tools menu) or run the salabrole.sql script from the C:\Program Files\Quest Software\Toad for Oracle\Tuning_Lab\ directory. This script must be run in SQL*Plus using a logon that has been granted a DBA role.
Once the role has been created grant the quest_sl_sqlab_role to users of SQL Tuning.
NOTE: For the role's privileges to be recognized the quest_sl_sqlab_role must be in the default role list.
RESOLUTION 2:
This is the comprehensive list of privileges needed for all features of SQL Tuning. The database privileges script is provided below the functionality and privilege details.
Database Privileges:
Oracle database privileges limit the access of individual users. The following lists the functionality that requires specific Oracle database privileges. If you do not have one of these privileges, you can still use the functionality in the other features of the program.
Functionality and Privileges:
SQL Inspector Inspector type: Snapshot
Access is needed to these system views:
SYS.V_$SQLAREA and
SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_$SQLTEXT depending on your version of Oracle)
In Oracle 9 or later, V$_SQL_PLAN
SQL Inspector Inspector type: Snapshot
Access is needed to these system views:
SYS.V_$OPEN_CURSOR,
SYS.V_$SESSION,
SYS.V_$SQLAREA and
SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_$SQLTEXT depending on your version of Oracle)
In Oracle 9 or later, V$_SQL_PLAN
SQL Inspector Flush Oracle shared pool
ALTER SYSTEM privilege is needed.
Inspected SQL Viewer Execution Plan Information
Oracle 9 or above is required.
Access is needed to this system view: V_$SQL_Plan
Retrieve runtime statistics
Access is needed to these system views:
SYS.V_$PARAMETER,
SYS.V_$MYSTAT, and
SYS.V_$STATNAME
Retrieve time related statistics
The TIMED_STATISTICS parameter in the INIT.ORA file must be set to TRUE.
Index Expert
Oracle 8i or above is required. ALTER SESSION privilege is needed.
All Modules
If the Oracle init parameter O7_DICTIONARY_ACCESSIBILITY (Oracle 8 or above) is set to false, objects under SYS are not accessible, even for a user with the SELECT ANY TABLE privilege granted. In this case, the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role is needed to access the objects under SYS.
SQL Classification Preference: Retrieve table size by counting
Access is needed to this system view:
SYS.DBA_SEGMENTS
-OR-
these system tables:
SYS.SEG$, SYS.OBJ$, SYS.TS$,SYS.SYS_OBJECTS & SYS.USER$
Altering session parameters for executing SQL
Access is needed to this system view:
SYS.V_$PARAMETER
Cross Index Analysis
Oracle 8i or above is required. ALTER SESSION privilege is needed.
Outline Manager and Deploy Outlines
Oracle 8i or above is required.
DBA role is needed.
CREATE ANY OUTLINE and DROP ANY OUTLINE privileges are needed.
Access to this package is needed: SYS.OUTLN_PKG
Access is needed to these system views:
SYS.USER_OUTLINES and SYS.USER_OUTLINE_HINTS
-OR-
SYS.DBA_OUTLINES and SYS.DBA_OUTLINE_HINTS
Database Privileges Script:
The commands for granting the Oracle database privileges that are needed for various functions in SQL Tuning are provided in the script below.
/* A sample script for granting all the privileges required by the SQL Tuning user. */
/* In order to grant privileges on SYS objects, you must login with the SYS user */
/* Please verify the script below before executing. */
/* Create new role */
create role sqltuning_user;
/* Grant EXECUTE on SYS.DBMS_SQL to role for Run on Server function. */
grant execute on SYS.DBMS_SQL to sqltuning_user;
/* Grant SELECT on SYS.V_SESSTAT, SYS.V_STATNAME, & SYS.V_$PARAMETER to role to view Run Time Statistics */
grant select on SYS.V_$MYSTAT to sqltuning_user;
grant select on SYS.V_$STATNAME to sqltuning_user;
grant select on SYS.V_$PARAMETER to sqltuning_user;
/* Grant SELECT on SYS.V_$PARAMETER alter session parameters */
grant select on SYS.V_$PARAMETER to sqltuning_user;
/* Grant SELECT on SYS.V_$SQLAREA, SYS.V_$SQLTEXT_WITH_NEWLINES, SYS.V_$OPENCURSOR & SYS.V_$SESSION to role for the SQL Inspector module */
grant select on SYS.V_$SQLAREA to sqltuning_user;
grant select on SYS.V_$SQLTEXT_WITH_NEWLINES to sqltuning_user;
grant select on SYS.V_$OPEN_CURSOR to sqltuning_user;
grant select on SYS.V_$SESSION to sqltuning_user;
/*Oracle 9 or later */
grant select on V_$SQL_Plan to sqltuning_user;
/* Grant ALTER SYSTEM to role for flushing the shared pool area in the SQL Inspector function*/
grant alter system to sqltuning_user;
/* Grant ALTER SESSION to role for the Index Expert function*/
grant alter session to sqltuning_user;
/* Grant ALTER SESSION to role Cross Index Analysis function*/
grant alter session to sqltuning_user;
/* Grant CREATE ANY OUTLINE & DROP ANY OUTLINE, EXECUTE on SYS.OUTLN_PKG, DBA, and SELECT on SYS.USER_OUTLINES & SYS.USER_OUTLINE_HINTS to role for Stored Outline. */
grant create any outline to sqltuning_user;
grant drop any outline to sqltuning_user;
grant execute on SYS.OUTLN_PKG to sqltuning_user;
grant dba to sqltuning_user;
grant select on SYS.USER_OUTLINES to sqltuning_user;
grant select on SYS.USER_OUTLINE_HINTS to sqltuning_user;
/* Grant SELECT_CATALOG_ROLE to role. If the Oracle init parameter O7_DICTIONARY_ACCESSIBILITY (Oracle 8 or above) is set to false, SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role is needed */
grant SELECT_CATALOG_ROLE to sqltuning_user;
/* Grant SELECT on SYS.DBA_SEGMENTS to role to retrieve table size for the SQL Classification function */
grant select on SYS.DBA_SEGMENTS to sqltuning_user;
/* Grant role to user */
grant sqltuning_user to &username;
