/* $Header: wfsysgnt.sql 26.36 2005/01/28 11:00:08 dmani ship $ */

/*=======================================================================*
          Copyright (c) 2004, 2005 Oracle. All rights reserved.
 +=======================================================================+
   DESCRIPTION
    Create Workflow User Account and grant access.
    requires USERNAME, PASSWORD, and SYS PASSWORD,
    DEFAULT TABLESPACE NAME, TEMP TABLESPACE as parameters.
 *=======================================================================*/
 
connect sys/&3 as sysdba 
 
set verify off;
set term off;

rem Create Workflow user if the user does not exist
rem Do not grant any priv. with create user as during upgrade, this will not be executed.
rem If the user exists with Tablespace, do not change that (to preserve the upgrade)

declare
lv_username varchar2(30);

begin

select username into lv_username 
  from dba_users 
 where username=UPPER('&&1');

exception
  when NO_DATA_FOUND then
        execute immediate 'create user &&1 identified by &2';
        execute immediate 'alter user &&1 default tablespace &4';
        execute immediate 'alter user &&1 temporary tablespace &5';

  when others then
     raise_application_error(-20000, 'Oracle Error = '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

whenever sqlerror exit;

grant unlimited tablespace to &1;

grant create session, create table, create view, create sequence, create synonym, create public synonym, alter session, create type, create procedure, drop public synonym to &1;

grant select_catalog_role to &1;

grant select on v_$timer to &1;

grant aq_administrator_role to &1;
grant select on sys.v_$instance to &1;
grant select on sys.v_$parameter to &1;

rem grant access on dba_users and roles for db directory service.
grant select on dba_users to &1;
grant select on dba_roles to &1;
grant select on dba_role_privs to &1;
grant select on dba_queue_schedules to &1;
grant select on gv_$aq to &1;
grant select on sys.user$ to &1;
grant query rewrite to &1;
grant select on gv_$session to &1;

 
whenever sqlerror continue;

rem Drop the synonyms before re-creating them this
rem is to ensure that the synonyms correctly point to objects
rem on db upgrade

drop public synonym xmlparser;
drop public synonym xmldom;
drop public synonym xslprocessor;

drop view WF_ALL_JOBS;
drop public synonym WF_ALL_JOBS;

execute dbms_aqadm.grant_type_access('&1');
grant execute on dbms_aq to &1;
grant execute on dbms_aqadm to &1;


DECLARE

  l_version  varchar2(30);
BEGIN
   --Verify the db version here . We need to check only 
   --10g here anything else will use SYS as the owner of 
   --parser packages.
   begin
     select '10g'
     into   l_version
     from   v$version
     where  banner like 'Oracle%10g%';

     execute immediate 'create public synonym xmldom for XDB.dbms_xmldom';
     execute immediate 'create public synonym xmlparser for XDB.dbms_xmlparser';
     execute immediate 'create public synonym xslprocessor for XDB.dbms_xslprocessor';
     execute immediate 'create public synonym WF_ALL_JOBS for SYS.USER_JOBS';

   exception
     when no_data_found then
      --We take it as < 10g db version and depend on
      --SYS objects.

      execute immediate 'create public synonym xmldom for SYS.xmldom';
      execute immediate 'create public synonym xmlparser for SYS.xmlparser';
      execute immediate 'create public synonym xslprocessor for SYS.xslprocessor ';

      execute immediate 'create or replace view WF_ALL_JOBS as select j.* from SYS.dba_jobs j where j.priv_user = sys_context(''USERENV'', ''CURRENT_SCHEMA'')';

      execute immediate 'grant select on WF_ALL_JOBS to public';
      execute immediate 'create public synonym WF_ALL_JOBS for SYS.WF_ALL_JOBS';

   end;

EXCEPTION
  when others then
     --To raise a meaningful error with the error message 
     raise_application_error(-20000, 'Oracle Error = '
                                ||to_char(sqlcode)||' - '||sqlerrm);

END;
/

whenever sqlerror exit failure;

grant execute on xmldom to &1;
grant execute on xmlparser to &1;
grant execute on xslprocessor to &1;


exit;