Rem
Rem $Header: cwmlite/admin/amdsys.sql /main/10 2008/07/28 13:25:49 glyon Exp $
Rem
Rem amdsys.sql
Rem
Rem Copyright (c) 2000, 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      amdsys.sql
Rem
Rem    DESCRIPTION
Rem      Create metadata owner schema 'OLAPSYS' with default password 'MANAGER'
Rem
Rem    NOTES
Rem      Must be run as 'SYS' and requires paramaters for default and
Rem      temporary tablespace.  
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    clei        02/13/08 - remove update statement on user$
Rem    glyon       06/04/07 - Make it impossible to connect as olapsys
Rem    jcarey      10/07/04 - Remove unnecessary rights
Rem    cdalessi    04/23/04 - Remove references to all_aw_numbers 
Rem    awesley     04/12/04 - add grant to sys.view$, create/drop any synonym
Rem    dbardwel    03/29/04 - 
Rem    ckearney    11/20/03 - add grant to all_aw_numbers & aw_prop$
Rem    ckearney    11/20/03 - add grant to all_aw_numbers & aw_prop$
Rem    awesley     11/12/03 - add grant select on dba_tables, dba_constraints, dba_objects, create/drop dimension
Rem    cdalessi    07/29/03 - 
Rem    cdalessi    10/21/02 - cdalessi_txn104879
Rem    cdalessi    10/10/02 - Renamed this file from oneuser.sql to amdsys.sql
Rem    dthompso    04/11/01 - B1716330: Add parameter for default temporary tab
Rem    dallan      06/23/00 - Add parameter for tablespace, created in DBCA.
Rem    dthompso    04/27/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

create user olapsys identified by no_password
password expire account lock 
default tablespace &1 
temporary tablespace &2  
quota unlimited on &1
/

grant create table, create view, create procedure, create sequence, create public synonym, drop public synonym to olapsys
/

grant resource to olapsys
/

grant select on sys.obj$ to olapsys
/

grant select on sys.dim$ to olapsys
/

grant select on sys.con$ to olapsys
/

grant select on sys.col$ to olapsys
/

grant select on sys.ccol$ to olapsys
/

grant select on sys.cdef$ to olapsys
/

grant select on dba_users to olapsys
/

grant select on dba_roles to olapsys
/

grant select on dba_role_privs to olapsys
/

grant select on sys.dimlevel$ to olapsys
/

grant select on sys.dimlevelkey$ to olapsys
/

grant select on sys.dimjoinkey$ to olapsys
/

grant select on sys.dimattr$ to olapsys
/

grant select on sys.hier$ to olapsys
/

grant select on sys.hierlevel$ to olapsys
/

grant select on sys.dependency$ to olapsys
/

grant select on sys.objauth$ to olapsys
/

grant select on sys.sysauth$ to olapsys
/

grant select on sys.v_$enabledprivs to olapsys
/

grant select on sys.aw_prop$ to olapsys
/

grant select on sys.all_aws to olapsys
/

grant select on sys.all_aw_prop_name to olapsys
/

grant execute on sys.dbms_lock to olapsys
/

grant execute on sys.dbms_aw_lib to olapsys
/

grant create public synonym to olapsys
/

grant drop public synonym to olapsys
/

grant select on sys.dba_tables to olapsys
/

grant select on sys.dba_constraints to olapsys
/

grant select on sys.dba_objects to olapsys
/

grant create any dimension to olapsys
/

grant drop any dimension to olapsys
/

grant select on sys.view$ to olapsys
/

grant select on sys.syn$ to olapsys
/

grant create any synonym to olapsys
/

grant drop any synonym to olapsys
/

Rem needed to get views on data dictionary tables to work for unprivileged
Rem users
grant select any dictionary to olapsys
/