SET ECHO ON
SPOOL XCONNECT_PRELIMS.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 manager schema
DROP USER XCMANAGER CASCADE;
CREATE USER XCMANAGER IDENTIFIED BY SUTRON
DEFAULT TABLESPACE HDB_DATA QUOTA UNLIMITED ON HDB_DATA
TEMPORARY TABLESPACE HDB_TEMP QUOTA UNLIMITED ON HDB_TEMP;

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

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 HDB_DATA 
TEMPORARY TABLESPACE HDB_TEMP;

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

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


undefine tbl_hdb 
define tbl_hdb = &2


-- Grant rights to XCManagers
GRANT SELECT ON &&tbl_hdb..HDB_SITE TO XCMANAGERS;
GRANT INSERT ON &&tbl_hdb..HDB_SITE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_hdb..HDB_SITE TO XCMANAGERS;
GRANT DELETE ON &&tbl_hdb..HDB_SITE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_SITE TO XCMANAGER WITH GRANT OPTION;
GRANT INSERT ON &&tbl_hdb..HDB_SITE TO XCMANAGER;
GRANT UPDATE ON &&tbl_hdb..HDB_SITE TO XCMANAGER;
GRANT DELETE ON &&tbl_hdb..HDB_SITE TO XCMANAGER;
GRANT REFERENCES ON &&tbl_hdb..HDB_SITE to XCManager;

GRANT SELECT ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGERS;
GRANT INSERT ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGERS;
GRANT DELETE ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGER;
GRANT INSERT ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGER;
GRANT UPDATE ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGER;
GRANT DELETE ON &&tbl_hdb..HDB_SITE_DATATYPE TO XCMANAGER;
GRANT REFERENCES ON &&tbl_hdb..HDB_SITE_DATATYPE to XCManager;

GRANT SELECT ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGERS;
GRANT INSERT ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGERS;
GRANT DELETE ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGER;
GRANT INSERT ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGER;
GRANT UPDATE ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGER;
GRANT DELETE ON &&tbl_hdb..HDB_DATATYPE TO XCMANAGER;
GRANT REFERENCES ON &&tbl_hdb..HDB_DATATYPE to XCManager;

GRANT SELECT ON &&tbl_hdb..HDB_STATE TO XCMANAGERS;
GRANT INSERT ON &&tbl_hdb..HDB_STATE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_hdb..HDB_STATE TO XCMANAGERS;
GRANT DELETE ON &&tbl_hdb..HDB_STATE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_STATE TO XCMANAGER WITH GRANT OPTION;
GRANT INSERT ON &&tbl_hdb..HDB_STATE TO XCMANAGER;
GRANT UPDATE ON &&tbl_hdb..HDB_STATE TO XCMANAGER;
GRANT DELETE ON &&tbl_hdb..HDB_STATE TO XCMANAGER;
GRANT REFERENCES ON &&tbl_hdb..HDB_STATE to XCManager;

GRANT SELECT ON &&tbl_hdb..R_BASE TO XCMANAGERS;
GRANT INSERT ON &&tbl_hdb..R_BASE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_hdb..R_BASE TO XCMANAGERS;
GRANT DELETE ON &&tbl_hdb..R_BASE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..R_BASE TO XCMANAGER WITH GRANT OPTION;
GRANT INSERT ON &&tbl_hdb..R_BASE TO XCMANAGER;
GRANT UPDATE ON &&tbl_hdb..R_BASE TO XCMANAGER;
GRANT DELETE ON &&tbl_hdb..R_BASE TO XCMANAGER;

GRANT EXECUTE ON &&tbl_hdb..DENY_ACTION TO XCMANAGERS;
GRANT EXECUTE ON &&tbl_hdb..populate_pk TO XCMANAGERS;
GRANT EXECUTE ON &&tbl_hdb..modify_r_base_raw TO XCMANAGERS;
GRANT EXECUTE ON &&tbl_hdb..DENY_ACTION TO XCMANAGER;
GRANT EXECUTE ON &&tbl_hdb..populate_pk TO XCMANAGER;
GRANT EXECUTE ON &&tbl_hdb..modify_r_base_raw TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_METHOD TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_METHOD TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_OVERWRITE_FLAG TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_OVERWRITE_FLAG TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_LOADING_APPLICATION TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_LOADING_APPLICATION TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_INTERVAL TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_INTERVAL TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_VALIDATION TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_VALIDATION TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_COLLECTION_SYSTEM TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_COLLECTION_SYSTEM TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_COMPUTED_DATATYPE TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_COMPUTED_DATATYPE TO XCMANAGER;

GRANT SELECT ON &&tbl_hdb..HDB_AGEN TO XCMANAGERS;
GRANT SELECT ON &&tbl_hdb..HDB_AGEN TO XCMANAGER;

SPOOL OFF
