Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      amdu111.sql - migration script for upgrading olap catalog component
Rem
Rem    DESCRIPTION
Rem      Upgrade OLAP Catalog from 11.1.0.
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       08/03/09 - move revokes of grant all on <various> from amdu102.sql
Rem    dbardwel    01/20/09 - drop the obsolete view olapsys.all$olap2_aws as the view
Rem                           has been moved to sys.all$olap2_aws for 11.2. Note that the
Rem                           running of amdrelod.sql installs the sys.all$olap2_aws view now.
Rem                           Move the revoke select on sys.user$ from olapsys to here.
Rem    glyon       08/06/08 - Creation

Rem old releases granted unnecessary privileges
begin
  execute immediate('revoke all on olapsys.olap_session_dims from public');
  execute immediate('revoke all on olapsys.olap_session_cubes from public');
exception
  when others then
    null;
end;
/

begin
  execute immediate('revoke all on olapsys.cwm2$awcubecreateaccess from public');
  execute immediate('revoke all on olapsys.cwm2$awdimcreateaccess from public');
exception
  when others then
    null;
end;
/

begin
  execute immediate('revoke all on olapsys.mrac_olap2_aws_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_attributes_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cubes_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_agg_lvl_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_agg_meas_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_agg_op_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_agg_specs_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_dim_uses_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_cube_measures_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_dimensions_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_dim_levels_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_hier_lvl_ord_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_map_attr_use_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_map_dim_use_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_map_meas_use_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_phys_obj_prop_v from public');
  execute immediate('revoke all on olapsys.mrac_olap2_aw_phys_obj_v from public');
exception
  when others then
    null;
end;
/

@@amdrelod.sql
drop view olapsys.all$olap2_aws;

Rem revoke access to sys.user$, and grant access to necessary views
begin
  execute immediate('revoke select on sys.user$ from olapsys');
exception
  when others then
    null;
end;
/

execute sys.dbms_registry.upgraded('AMD');

alter session set current_schema = sys;

execute sys.cwm2_olap_installer.Validate_CWM2_Install;