SET ECHO ON TERM ON;
REM
REM $Header: 215187.1 sqguser.sql 11.2.9.7 2009/08/17 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqguser.sql
REM
REM DESCRIPTION
REM   This script creates some grants for the user of SQLTXPLAIN
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM
REM PARAMETERS
REM   1. Application user executing SQLTXPLAIN (required)
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqguser.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqguser.sql [application user executing SQLTXPLAIN]
REM   SQL> start sqguser.sql APPS
REM
REM NOTES
REM   1. This script is executed by sqcusr.sql
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";
BEGIN
  IF USER != 'SYS' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Grant failed - it should connect as SYS, not as '||USER);
  END IF;
END;
/

/* ---------------------------------------------------------------------- */

PRO
PRO Parameter 1: application user executing SQLTXPLAIN (required)
PRO
DEF appl_schema = '&1';

PRO
PRO ... Grants for application user
PRO
GRANT SELECT_CATALOG_ROLE                      TO &&appl_schema;
GRANT EXECUTE ON sys.dbms_metadata             TO &&appl_schema;

PRO
PRO ... Grants for 10g and 11g
PRO
DECLARE
  rdbms_release NUMBER;
BEGIN
  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;
  IF rdbms_release >= 10 THEN
    EXECUTE IMMEDIATE 'GRANT ADMINISTER SQL TUNING SET        TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT ADVISOR                          TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT ALTER ANY SQL PROFILE            TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT CREATE ANY SQL PROFILE           TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT DROP ANY SQL PROFILE             TO &&appl_schema';
  END IF;
  IF rdbms_release >= 11 THEN
    EXECUTE IMMEDIATE 'GRANT ADMINISTER SQL MANAGEMENT OBJECT TO &&appl_schema';
    -- following directory grants are needed by TCB
    EXECUTE IMMEDIATE 'GRANT READ  ON DIRECTORY SQLT$STAGE    TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY SQLT$STAGE    TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT READ  ON DIRECTORY SQLT$UDUMP    TO &&appl_schema';
    EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY SQLT$UDUMP    TO &&appl_schema';
  END IF;
END;
/

UNDEFINE APPL_SCHEMA
