Rem
Rem $Header: onerole.sql 16-jan-01.15:31:59 dthompso Exp $
Rem
Rem onerole.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onerole.sql
Rem
Rem    DESCRIPTION
Rem      Creates the role 'OLAP_DBA' which is granted to users who have
Rem      privileges to edit any user's metadata.
Rem
Rem    NOTES
Rem      Must be run as 'SYS'. Role is granted by default to 'DBA' role and
Rem      the metadata owner 'OLAPSYS'.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon         06/04/07 - Add CWM_USER, OLAP_USER roles
Rem    dthompso      01/16/01 - Add 'select any dictionary' privilege
Rem    pramarao      09/09/00 - .added privilages to olap_dba to create and drop views
Rem    dallan      08/15/00 - Add ANALYZE ANY role to OLAP_DBA.
Rem    dthompso    04/27/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

Rem add olap_dba role if needed
declare
  cursor onerole is select role from dba_roles where role = 'OLAP_DBA';
  onerolename varchar2(30);
begin
  if not  onerole%isopen then 
    open onerole;
    fetch onerole into onerolename;
    if onerole%notfound then
      execute immediate 'create role olap_dba';
    end if;
    close onerole;
  end if;
end;
/

Rem grant privileges needed in RDBMS 11.  Note that we do not revoke privileges
Rem that may no longer be needed.
grant CREATE ANY TABLE to OLAP_DBA
/
grant INSERT ANY TABLE to OLAP_DBA
/
grant UPDATE ANY TABLE to OLAP_DBA
/
grant DELETE ANY TABLE to OLAP_DBA
/
grant SELECT ANY TABLE to OLAP_DBA
/
grant DROP ANY TABLE to OLAP_DBA
/
grant CREATE ANY CUBE DIMENSION to OLAP_DBA
/
grant INSERT ANY CUBE DIMENSION to OLAP_DBA
/
grant UPDATE ANY CUBE DIMENSION to OLAP_DBA
/
grant DELETE ANY CUBE DIMENSION to OLAP_DBA
/
grant SELECT ANY CUBE DIMENSION to OLAP_DBA
/
grant DROP ANY CUBE DIMENSION to OLAP_DBA
/
grant CREATE ANY CUBE to OLAP_DBA
/
grant UPDATE ANY CUBE to OLAP_DBA
/
grant SELECT ANY CUBE to OLAP_DBA
/
grant DROP ANY CUBE to OLAP_DBA
/
grant CREATE ANY MEASURE FOLDER to OLAP_DBA
/
grant INSERT ANY MEASURE FOLDER to OLAP_DBA
/
grant DELETE ANY MEASURE FOLDER to OLAP_DBA
/
grant DROP ANY MEASURE FOLDER to OLAP_DBA
/
grant CREATE ANY CUBE BUILD PROCESS to OLAP_DBA
/
grant UPDATE ANY CUBE BUILD PROCESS to OLAP_DBA
/
grant DROP ANY CUBE BUILD PROCESS to OLAP_DBA
/
grant CREATE ANY VIEW to OLAP_DBA
/
grant DROP ANY VIEW to OLAP_DBA
/
grant CREATE JOB to OLAP_DBA
/
grant CREATE SEQUENCE to OLAP_DBA
/
grant OLAP_DBA to DBA
/
grant OLAP_DBA to OLAPSYS
/

Rem If needed, create 'marker role' CEM_USER that must be granted to any user
Rem to use CWM
declare
  cursor onerole is select role from dba_roles where role = 'CWM_USER';
  onerolename varchar2(30);
begin
  if not  onerole%isopen then 
    open onerole;
    fetch onerole into onerolename;
    if onerole%notfound then
      execute immediate 'create role cwm_user';
    end if;
    close onerole;
  end if;
end;
/

Rem add olap_user role if needed
declare
  cursor onerole is select role from dba_roles where role = 'OLAP_USER';
  onerolename varchar2(30);
begin
  if not  onerole%isopen then 
    open onerole;
    fetch onerole into onerolename;
    if onerole%notfound then
      execute immediate 'create role olap_user';
    end if;
    close onerole;
  end if;
end;
/