SET ECHO ON
SPOOL XCPrelims.OUT

-- Create the role for the manager
DROP ROLE XCMANAGERS;
CREATE ROLE XCMANAGERS;
GRANT CREATE SESSION TO XCMANAGERS;
GRANT CREATE TABLE TO XCMANAGERS;
GRANT CREATE SEQUENCE TO XCMANAGERS;
GRANT CREATE VIEW TO XCMANAGERS;
GRANT CREATE SYNONYM TO XCMANAGERS;
GRANT CREATE PROCEDURE TO XCMANAGERS;

-- Create the XCMANAGER schema
DROP USER XCMANAGER CASCADE;
CREATE USER XCMANAGER IDENTIFIED BY SUTRON
  DEFAULT TABLESPACE XC_DATA QUOTA UNLIMITED ON XC_DATA
  TEMPORARY TABLESPACE XC_TEMP QUOTA UNLIMITED ON XC_TEMP;

-- Grant him the manager role
GRANT XCMANAGERS TO XCMANAGER WITH ADMIN OPTION;

GRANT CREATE SESSION TO XCMANAGER;
GRANT RESOURCE TO XCMANAGER;
GRANT CONNECT  TO XCMANAGER;

GRANT CREATE PUBLIC SYNONYM TO XCMANAGERS;
GRANT DROP PUBLIC SYNONYM TO XCMANAGERS;

-- Create the role for users
DROP ROLE XCUSERS;
CREATE ROLE XCUSERS;
GRANT XCUSERS TO PUBLIC;

DROP USER XCONNECT CASCADE;

CREATE USER XCONNECT IDENTIFIED BY XCONNECT
DEFAULT TABLESPACE XC_DATA 
TEMPORARY TABLESPACE XC_TEMP;

GRANT XCUSERS TO XCONNECT;
ALTER USER XCONNECT DEFAULT ROLE XCUSERS;

GRANT CREATE SESSION TO XCONNECT;
GRANT RESOURCE TO XCONNECT;
GRANT CONNECT  TO XCONNECT;

SPOOL OFF
