Grants Example

Examples from the Docs

CONFIGURING PROFILING

Configuring Oracle

Oracle users need access to V$ views. In order to configure Oracle to provide users with these privleges:

• If you are setting up Oracle 10 or later, ensure you are logged in as sys or system with the sysdba role, or the SELECT_CATALOG_ROLE has been granted to user_name.

• If you are setting up an earlier version of Oracle, ensure you are logged in as sys or system with the sysdba role.

CONFIGURING TUNING

Set Roles and Permissions on Data Sources

In order to take advantage of all tuning features, each user must have a specific set of permissions. The code below creates a role with all required permissions. To create the required role, execute the SQL against the target data source, modified according to the specific needs of your site:

/* Create the role */

CREATE ROLE SQLTUNING NOT IDENTIFIED

/

GRANT SQLTUNING TO "CONNECT"

/

GRANT SQLTUNING TO SELECT_CATALOG_ROLE.

/

GRANT ANALYZE ANY TO SQLTUNING

/

GRANT CREATE ANY OUTLINE TO SQLTUNING

/

GRANT CREATE ANY PROCEDURE TO SQLTUNING

/

GRANT CREATE ANY TABLE TO SQLTUNING

/

GRANT CREATE ANY TRIGGER TO SQLTUNING

/

GRANT CREATE ANY VIEW TO SQLTUNING

/

GRANT CREATE PROCEDURE TO SQLTUNING

/

GRANT CREATE SESSION TO SQLTUNING

/

GRANT CREATE TRIGGER TO SQLTUNING

/

GRANT CREATE VIEW TO SQLTUNING

/

GRANT DROP ANY OUTLINE TO SQLTUNING

/

GRANT DROP ANY PROCEDURE TO SQLTUNING

/

GRANT DROP ANY TRIGGER TO SQLTUNING

/

GRANT DROP ANY VIEW TO SQLTUNING

/

GRANT SELECT ON SYS.V_$SESSION TO SQLTUNING /

GRANT SELECT ON SYS.V_$SESSTAT TO SQLTUNING /

GRANT SELECT ON SYS.V_$SQL TO SQLTUNING

/

GRANT SELECT ON SYS.V_$STATNAME TO SQLTUNING /

Once complete, you can assign the role to users who will be running tuning jobs:

/* Create a sample user*/

CREATE USER TUNINGUSER IDENTIFIED BY VALUES '05FFD26E95CF4A4B'

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS

PROFILE DEFAULT

ACCOUNT UNLOCK

/

GRANT SQLTUNING TO TUNINGUSER

/

ALTER USER TUNINGUSER DEFAULT ROLE SQLTUNING /

>>>>>>>>>>>>