SPOOL LOG_cat_owb.TXT

prompt Enter Tablespace Name for OWBSYS user:
set termout off
define owbsys_tbs = &1
set termout on

CREATE OR REPLACE PACKAGE dbms_owb AS
PROCEDURE validate;
END dbms_owb;
/

CREATE OR REPLACE PACKAGE BODY dbms_owb AS
PROCEDURE validate IS
BEGIN
  dbms_registry.valid('OWB');
END validate;
END dbms_owb;
/

WHENEVER SQLERROR EXIT FAILURE

prompt Create user OWBSYS with default tablespace &owbsys_tbs ...

CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE &owbsys_tbs;

prompt User OWBSYS has been created.

WHENEVER SQLERROR CONTINUE

BEGIN
      IF dbms_registry.status('OWB') = 'VALID' THEN
        DBMS_OUTPUT.PUT_LINE('OWB is VALID');
      ELSE
        DBMS_OUTPUT.PUT_LINE('OWB Begin dbms register ');
        dbms_registry.loading('OWB','OWB','dbms_owb.validate','OWBSYS');
      END IF;
END;
/
GRANT EXECUTE ON DBMS_REGISTRY to OWBSYS;


rem +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
rem Run this script to grant all the necessary roles and system/object 
rem privileges to OWB unified repository OWBSYS.
rem +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
--roles:

grant aq_administrator_role to OWBSYS;
grant aq_user_role to OWBSYS;
--grant javaidpriv to OWBSYS;
grant javauserpriv to OWBSYS;
grant java_admin to OWBSYS;
--grant select_catalog_role to OWBSYS;
grant connect to OWBSYS with admin option;

--system privileges:

-- grant CREATE ROLE to OWBSYS; 
grant CREATE ANY CONTEXT to owbsys;
--grant select any dictionary to OWBSYS;
--grant select any sequence to OWBSYS;
--grant select any table to OWBSYS;
grant alter session to OWBSYS with admin option;
--Bug 7507745: ANALYZE PRIVILEGES LIKE *ANY* TO OWBSYS WITH ADMIN OPTION 
--grant create any directory to OWBSYS with admin option;
grant create database link to OWBSYS with admin option;
grant create dimension to OWBSYS with admin option;
grant create indextype to OWBSYS with admin option;
grant create procedure to OWBSYS with admin option;
grant create job to OWBSYS with admin option;
grant CREATE MATERIALIZED VIEW to OWBSYS with admin option;
grant create sequence to OWBSYS with admin option;
grant create session to OWBSYS with admin option;
grant create synonym to OWBSYS with admin option;

grant create public synonym to owbsys;
grant drop public synonym to owbsys;

grant create table to OWBSYS with admin option;
grant create trigger to OWBSYS with admin option;
grant create view to OWBSYS with admin option;
grant create type to OWBSYS with admin option;
--Bug 7507745: ANALYZE PRIVILEGES LIKE *ANY* TO OWBSYS WITH ADMIN OPTION 
--grant drop any directory to OWBSYS with admin option;
grant global query rewrite to OWBSYS with admin option;
grant unlimited tablespace to OWBSYS with admin option;
grant alter user to OWBSYS;
grant create any trigger to OWBSYS with admin option;

--object privileges:

grant select on sys.dba_free_space to OWBSYS;
grant select on sys.dba_data_files to OWBSYS; 
grant select on sys.dba_tablespaces to OWBSYS;
grant execute on sys.dbms_aq_bqview to OWBSYS;
grant execute on sys.dbms_capture_adm to OWBSYS;
grant execute on sys.dbms_job to OWBSYS;
grant execute on sys.dbms_lob to OWBSYS with grant option;
grant execute on sys.dbms_lock to OWBSYS; 
grant select on sys.dbms_lock_allocated to OWBSYS;
grant execute on sys.dbms_snapshot to OWBSYS; 
grant execute on sys.dbms_streams_adm to OWBSYS;
grant select on sys.v_$lock to OWBSYS;
grant select on sys.v_$parameter to OWBSYS; 
grant select on sys.v_$system_parameter to OWBSYS; 
grant execute on sys.aq$_agent to OWBSYS with grant option;
grant select on sys.dba_advisor_log to OWBSYS;
grant select on sys.dba_apply_error to OWBSYS;
grant select on sys.dba_role_privs to OWBSYS;
grant execute on sys.dbms_aqadm to OWBSYS with grant option;
grant execute on sys.dbms_aq to OWBSYS with grant option; 
grant execute on sys.dbms_crypto to OWBSYS with grant option;
--grant execute on sys.dbms_obfuscation_toolkit to OWBSYS with grant option;
grant select on sys.gv_$session to OWBSYS with grant option; 
grant execute on sys.interactionExecute to OWBSYS with grant option;
grant select on sys.v_$active_instances to OWBSYS with grant option;
grant select on sys.v_$instance to OWBSYS with grant option;
grant select on sys.v_$session to OWBSYS with grant option; 
grant select on sys.v_$mystat to OWBSYS with grant option;

--fix bug 5906692: used by wb_rt_mapaudit_util to search for a object name(resolve_name procedure)
grant select on sys.dba_tables to OWBSYS;
grant select on sys.dba_synonyms to OWBSYS;

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/ADMIN/RTREPOS.PROPERTIES', 'read,write');
  
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'owb/bin/unix/run_service.sh', 'read,execute');
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'OWB/BIN/WIN32/RUN_SERVICE.BAT', 'read,execute'); 
  dbms_java.grant_permission(upper('OWBSYS'),'java.io.FilePermission', 'owb/bin/win32/run_service.bat', 'read,execute');  
  
  dbms_java.grant_permission(upper('OWBSYS'), 'java.util.PropertyPermission', '*', 'read,write');
  dbms_java.grant_permission(upper('OWBSYS'),'java.lang.RuntimePermission','*','writeFileDescriptor');
  dbms_java.grant_permission(upper('OWBSYS'), 'java.net.SocketPermission','*', 'connect');
end;
/


begin
  execute immediate('grant create external job to OWBSYS');
exception
  when others
  then
    null;
end;
/
commit;


begin
  execute immediate('grant OLAP_USER to OWBSYS with admin option');
  execute immediate('grant select on olapsys.xml_load_log to OWBSYS with admin option');
exception
  when others
  then
    null;
end;
/
commit;

begin
  execute immediate('grant CWM_USER to OWBSYS with admin option');
exception
  when others
  then
    null;
end;
/
commit;

begin
  execute immediate('grant all on olapsys.xml_loadid_sequence to OWBSYS with grant option');
exception
  when others
  then
    null;
end;
/
commit;


begin
  execute immediate('grant all on olapsys.xml_load_log to OWBSYS with grant option');
exception
  when others
  then
    null;
end;
/
commit;

begin
  execute immediate('grant execute on olapsys.dbms_awm to OWBSYS with grant option');
exception
  when others
  then
    null;
end;
/
commit;




--first create the role,and use a dummy password, will alter the password later...
CREATE ROLE OWB$CLIENT IDENTIFIED by s;
GRANT OWB$CLIENT to OWBSYS with ADMIN OPTION;

--fix bug 5560480
CREATE ROLE  OWB_DESIGNCENTER_VIEW;
GRANT  OWB_DESIGNCENTER_VIEW  to OWBSYS with ADMIN OPTION;
CREATE ROLE OWB_USER;
GRANT OWB_USER to OWBSYS with ADMIN OPTION;




--then grant the necessary sys obj privs 
grant select on sys.v_$system_parameter to OWB$CLIENT;
grant select on sys.v_$session to OWB$CLIENT;
grant select on sys.gv_$session to OWB$CLIENT;
grant select on sys.v_$instance to OWB$CLIENT;
grant select on sys.v_$mystat to OWB$CLIENT;
grant execute on sys.dbms_lock to OWB$CLIENT;
grant select on sys.dbms_lock_allocated to OWB$CLIENT;
grant select on sys.v_$lock to OWB$CLIENT;
grant execute on sys.dbms_snapshot to OWB$CLIENT;
--grant execute on sys.dbms_obfuscation_toolkit to OWB$CLIENT;
grant execute on sys.dbms_stats to OWB$CLIENT;
grant select on sys.dba_tablespaces to OWB$CLIENT;
--grant select on sys.dba_role_privs to OWB$CLIENT;





--then grant other system privileges
--fix bug 5115009 , move to wb_rti_target_schema_privs.plb 
--move this back otherwize we need to grant analyze any with grant option to repos owner...
grant ANALYZE ANY to OWB$CLIENT;
grant CREATE DATABASE LINK to OWB$CLIENT;
--fix bug 4715583,add the following 2 back since find them in grantprivs.sql 
grant CREATE DIMENSION to OWB$CLIENT;
grant CREATE INDEXTYPE to OWB$CLIENT;
grant CREATE SEQUENCE to OWB$CLIENT;
grant CREATE SESSION to OWB$CLIENT;
grant ALTER SESSION to OWB$CLIENT;
grant create snapshot to OWB$CLIENT;
grant CREATE SYNONYM  to OWB$CLIENT;
grant CREATE TYPE to OWB$CLIENT;
--fix bug 3090158(to add the create proc,exe any proc and create type)
grant CREATE PROCEDURE to OWB$CLIENT;
--fix bug 5115009  
--grant DROP ANY PROCEDURE to OWB$CLIENT;
--grant CREATE PUBLIC SYNONYM to OWB$CLIENT;
--grant DROP PUBLIC SYNONYM to OWB$CLIENT;
--grant CREATE TABLE to OWB$CLIENT;


--create owbsys_audit to be used as default for JRT audit datasource
CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE &owbsys_tbs;
grant create session to owbsys_audit;
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
CREATE synonym owbsys_audit.wb_rt_ht_audit_sequence FOR OWBSYS.wb_rt_ht_audit_sequence;
CREATE synonym owbsys_audit.wb_rt_ht_audit_job FOR OWBSYS.wb_rt_ht_audit_job;
CREATE synonym owbsys_audit.wb_rt_ht_audit_phase FOR OWBSYS.wb_rt_ht_audit_phase;
CREATE synonym owbsys_audit.wb_rt_ht_audit_task FOR OWBSYS.wb_rt_ht_audit_task;
CREATE synonym owbsys_audit.wb_rt_ht_audit_step FOR OWBSYS.wb_rt_ht_audit_step;
CREATE synonym owbsys_audit.wb_rt_ht_audit_struct FOR OWBSYS.wb_rt_ht_audit_struct;
CREATE synonym owbsys_audit.wb_rt_ht_audit_error FOR OWBSYS.wb_rt_ht_audit_error;
CREATE synonym owbsys_audit.wb_rt_ht_audit_error_source FOR OWBSYS.wb_rt_ht_audit_error_source;
CREATE synonym owbsys_audit.wb_rt_ht_audit_trace FOR OWBSYS.wb_rt_ht_audit_trace;
CREATE synonym owbsys_audit.wb_rt_ht_audit_file FOR OWBSYS.wb_rt_ht_audit_file;
CREATE synonym owbsys_audit.wb_rt_ht_audit_param FOR OWBSYS.wb_rt_ht_audit_param;
CREATE synonym owbsys_audit.wb_rt_ht_audit_msg FOR OWBSYS.wb_rt_ht_audit_msg;


-- create owbrtps table to store the home value
create table OWBSYS.OWBRTPS (
KEY VARCHAR2(35) NOT NULL,
VALUE VARCHAR2(255) NOT NULL,
UNIQUE (KEY)
);

BEGIN
      IF dbms_registry.status('OWB') = 'VALID' THEN
        DBMS_OUTPUT.PUT_LINE('OWB is VALID');
      ELSE
        DBMS_OUTPUT.PUT_LINE('OWB finish dbms register ');
        dbms_registry.loaded('OWB');
        dbms_owb.validate;
      END IF;
END;
/
alter user OWBSYS account lock;
alter user owbsys_audit account lock;

prompt NOTE: To associate the Control Center with the correct OWB home, now run owb/UnifiedRepos/reset_owbcc_home.sql. You must run the script as a user with java admin privileges. For example, connect as sys as sysdba to run the script.

SPOOL OFF