Rem
Rem $Header: whdev/2.0/owb/shiphome/owb/UnifiedRepos/cat_owb_reposupgrade.sql /main/4 2009/04/30 15:18:07 sochen Exp $
Rem
Rem cat_owb_reposupgrade.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      cat_owb_reposupgrade.sql 
Rem
Rem    DESCRIPTION
Rem      Provides additional grants and revoke privileges when upgrading repository from Tokyo to Tahoe
Rem
Rem      
Rem
Rem    NOTES
Rem      Must be executed prior to running the Repository Assistant
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sochen      04/30/09 -bug 8450908 in Tahoe owb_designcenter_view role is Rem                           not protected by password anymore  
Rem    epaglina    04/03/09 - Bug 8326511, creates owbsys_audit if user does not exist
Rem    epaglina    03/27/09 - Bug 8326511, add additional privileges for owbsys for Repository Upgrade.
Rem                           Removed grants that are no longer being used by Tokyo/Tahoe.
Rem                           
Rem    epaglina    10/21/07 - repos upgrade feature
Rem    epaglina    10/21/07 - Created
Rem
SPOOL LOG_cat_reposupgrade_owb.TXT


alter role OWB_DESIGNCENTER_VIEW not identified;


grant create any trigger to OWBSYS with admin option;

grant execute on sys.dbms_lob to OWBSYS with grant option;

begin
    -- NAStart launches the name/address server process 
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'owb/bin/unix/NAStart.sh', 'execute'); 
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'owb/bin/win32/NAStart.bat', 'execute'); 
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'OWB/BIN/ADMIN/RTREPOS.PROPERTIES', 'read,write');
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'OWB/BIN/WIN32/RUN_SERVICE.BAT', 'read,execute');   
end;
/


BEGIN
  DECLARE
    found_user NUMBER;
    owbsys_tbs VARCHAR2(30);
  BEGIN
    SELECT COUNT(*) INTO found_user FROM ALL_USERS WHERE USERNAME = 'OWBSYS_AUDIT';
    IF found_user = 0 THEN
       SELECT default_tablespace INTO owbsys_tbs FROM dba_users WHERE USERNAME = 'OWBSYS';    
    
--create owbsys_audit to be used as default for JRT audit datasource
        EXECUTE IMMEDIATE 'CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE ' || owbsys_tbs;

        EXECUTE IMMEDIATE 'grant create session to owbsys_audit';
        EXECUTE IMMEDIATE 'grant alter session to owbsys_audit';

--uses private synonyms to avoid direct access to OWBSYS tables
--note that the schema object need not currently exist and you need not have privileges to access the object
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_sequence FOR OWBSYS.wb_rt_ht_audit_sequence';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_job FOR OWBSYS.wb_rt_ht_audit_job';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_phase FOR OWBSYS.wb_rt_ht_audit_phase';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_task FOR OWBSYS.wb_rt_ht_audit_task';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_step FOR OWBSYS.wb_rt_ht_audit_step';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_struct FOR OWBSYS.wb_rt_ht_audit_struct';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_error FOR OWBSYS.wb_rt_ht_audit_error';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_error_source FOR OWBSYS.wb_rt_ht_audit_error_source';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_trace FOR OWBSYS.wb_rt_ht_audit_trace';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_file FOR OWBSYS.wb_rt_ht_audit_file';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_param FOR OWBSYS.wb_rt_ht_audit_param';
        EXECUTE IMMEDIATE 'CREATE synonym owbsys_audit.wb_rt_ht_audit_msg FOR OWBSYS.wb_rt_ht_audit_msg';

        EXECUTE IMMEDIATE 'alter user owbsys_audit account lock';
    END IF;
  END;
END;
/


commit;

SPOOL OFF

exit;