/* OWB 9.0.4 and OWB 9.2 compliant.

Assmption: user dump has been created using all defaults (i.e. grants
have also been exported). Username and password of user that was
exported will be the same as username that will be used for import. If
this prerequisite is not fulfilled, then table OWB_ROLE_INFO will need
to be updated with encrypted password.

*/

sqlplus /nolog

connect sys/<pw>@<db name> as sysdba

/* see comment in assumption!!! */
create user <owb_repos_user> identified by <owb_repos_user_pw>
default tablespace <default_tablespace>
temporary tablespace <temporary_tablespace>
/

grant select on sys.v_$system_parameter to <owb_repos_user>; 
grant select on sys.v_$session to <owb_repos_user> with grant option; 
grant select on sys.v_$mystat to <owb_repos_user> with grant option;
grant select on sys.dba_role_privs to <owb_repos_user>;
grant execute on sys.dbms_lock to <owb_repos_user>; 
grant select on sys.dbms_lock_allocated to <owb_repos_user>;
grant select on sys.v_$lock to <owb_repos_user>;
grant execute on sys.dbms_snapshot to <owb_repos_user>; 
grant execute on sys.dbms_obfuscation_toolkit to <owb_repos_user> with grant option;
grant CREATE DATABASE LINK to <owb_repos_user>;
grant CREATE DIMENSION to <owb_repos_user>;
grant CREATE INDEXTYPE to <owb_repos_user>;
grant CREATE SEQUENCE to <owb_repos_user>;
grant CREATE SESSION to <owb_repos_user> with admin option;
grant ALTER SESSION to <owb_repos_user>;
grant create snapshot to <owb_repos_user>; 
grant CREATE SYNONYM  to <owb_repos_user>;
grant CREATE PROCEDURE to <owb_repos_user>;
grant CREATE ANY PROCEDURE to <owb_repos_user>;
grant DROP ANY PROCEDURE to <owb_repos_user>;
grant EXECUTE any PROCEDURE to <owb_repos_user>;
grant CREATE PUBLIC SYNONYM to <owb_repos_user>;
grant DROP PUBLIC SYNONYM to <owb_repos_user>;
grant CREATE TABLE to <owb_repos_user>;
grant CREATE TYPE to <owb_repos_user>;
grant CREATE VIEW to <owb_repos_user>;
grant CREATE MATERIALIZED VIEW to <owb_repos_user>;
grant CREATE TRIGGER to <owb_repos_user>;
grant global query rewrite to <owb_repos_user>;
grant unlimited tablespace to <owb_repos_user>;
grant CREATE ROLE to <owb_repos_user>;	 

/* Following statement fails if other OWB repository onwer was created before. */
create role cwmaccess;
/* If create role failed, then execute:
grant cwmaccess to <owb_repos_user> with admin option ;
*/

CREATE ROLE OWB_<owb_repos_user> IDENTIFIED by <owb_repos_user_pw> ;
/* i.e. if <owb_repos_user> = OWB904 (password welcome) then this statement will be: create role owb_owb904 identified by welcome */
CREATE ROLE OWBR_<owb_repos_user>;
/* i.e. if <owb_repos_user> = OWB904 then this statement will be: create role owbr_owb904 */
grant OWB_<owb_repos_user> to <owb_repos_user> with admin option ;
grant OWBR_<owb_repos_user> to <owb_repos_user> with admin option ;

alter user <owb_repos_user> default role OWB_<owb_repos_user>, OWBR_<owb_repos_user> ;

grant select on sys.v_$system_parameter to OWB_<owb_repos_user>; 
grant select on sys.v_$session to OWB_<owb_repos_user>; 
grant select on sys.v_$mystat to OWB_<owb_repos_user>; 
grant execute on sys.dbms_lock to OWB_<owb_repos_user>; 
grant select on sys.dbms_lock_allocated to OWB_<owb_repos_user>;
grant select on sys.v_$lock to OWB_<owb_repos_user>;
grant execute on sys.dbms_snapshot to OWB_<owb_repos_user>; 
grant execute on sys.dbms_obfuscation_toolkit to OWB_<owb_repos_user>;

grant CREATE DATABASE LINK to OWB_<owb_repos_user>;
grant CREATE SEQUENCE to OWB_<owb_repos_user>;
grant CREATE SESSION to OWB_<owb_repos_user>;
grant ALTER SESSION to OWB_<owb_repos_user>;
grant create snapshot to OWB_<owb_repos_user>; 
grant CREATE SYNONYM  to OWB_<owb_repos_user>;

/* exit SQL plus */

/* Import previously created export file. */
imp <owb_repos_user>/<owb_repos_user_pw>@<db name>

/* Import will generate a number of compilation warnings on ALL_IV views. */

sqlplus system@<system pw>@<db name>

exec dbms_utility.compile_schema('<owb_repos_user>',true)

connect <owb_repos_user>/<owb_repos_user_pw>@<db name>

/* compile all invalid objects, using alter <object type> <object name> compile */