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

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

CREATE USER OWBMETA IDENTIFIED BY OWBMETA DEFAULT TABLESPACE USERS;
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_owbmeta.validate','OWBMETA');
      END IF;
END;
/
GRANT EXECUTE ON DBMS_REGISTRY to OWBMETA;


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

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

--system privileges:

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

grant create public synonym to OWBMETA;
grant drop public synonym to OWBMETA;

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

--object privileges:

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


begin
  dbms_java.grant_permission(upper('OWBMETA'), 'java.util.PropertyPermission', '*', 'read,write');
  dbms_java.grant_permission(upper('OWBMETA'),'java.io.FilePermission', '<<ALL FILES>>', 'read,write,execute,delete'); 
  dbms_java.grant_permission(upper('OWBMETA'),'java.lang.RuntimePermission','*','writeFileDescriptor');
  dbms_java.grant_permission(upper('OWBMETA'), 'java.net.SocketPermission','*', 'connect');
end;
/


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


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


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


begin
  execute immediate('grant all on olapsys.xml_load_log to OWBMETA 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 OWBM$CLIENT IDENTIFIED by s;
GRANT OWBM$CLIENT to OWBMETA with ADMIN OPTION;

--fix bug 5560480
CREATE ROLE  OWBM_DESIGNCENTER_VIEW IDENTIFIED by s;
GRANT  OWBM_DESIGNCENTER_VIEW  to OWBMETA with ADMIN OPTION;
CREATE ROLE OWBM_USER;
GRANT OWBM_USER to OWBMETA with ADMIN OPTION;




--then grant the necessary sys obj privs 
grant select on sys.v_$system_parameter to OWBM$CLIENT;
grant select on sys.v_$session to OWBM$CLIENT;
grant select on sys.gv_$session to OWBM$CLIENT;
grant select on sys.v_$instance to OWBM$CLIENT;
grant select on sys.v_$mystat to OWBM$CLIENT;
grant execute on sys.dbms_lock to OWBM$CLIENT;
grant select on sys.dbms_lock_allocated to OWBM$CLIENT;
grant select on sys.v_$lock to OWBM$CLIENT;
grant execute on sys.dbms_snapshot to OWBM$CLIENT;
grant execute on sys.dbms_obfuscation_toolkit to OWBM$CLIENT;
grant execute on sys.dbms_stats to OWBM$CLIENT;
grant select on sys.dba_tablespaces to OWBM$CLIENT;
grant select on sys.dba_role_privs to OWBM$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 OWBM$CLIENT;
grant CREATE DATABASE LINK to OWBM$CLIENT;
--fix bug 4715583,add the following 2 back since find them in grantprivs.sql 
grant CREATE DIMENSION to OWBM$CLIENT;
grant CREATE INDEXTYPE to OWBM$CLIENT;
grant CREATE SEQUENCE to OWBM$CLIENT;
grant CREATE SESSION to OWBM$CLIENT;
grant ALTER SESSION to OWBM$CLIENT;
grant create snapshot to OWBM$CLIENT;
grant CREATE SYNONYM  to OWBM$CLIENT;
grant CREATE TYPE to OWBM$CLIENT;
--fix bug 3090158(to add the create proc,exe any proc and create type)
grant CREATE PROCEDURE to OWBM$CLIENT;
--fix bug 5115009 	
--grant DROP ANY PROCEDURE to OWBM$CLIENT;
--grant CREATE PUBLIC SYNONYM to OWBM$CLIENT;
--grant DROP PUBLIC SYNONYM to OWBM$CLIENT;
--grant CREATE TABLE to OWBM$CLIENT;
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_owbmeta.validate;
      END IF;
END;
/