Creating Oracle roles, users and tablespaces for BPM 75 Advanced

Example...


CREATE ROLE BPMN_GOLD_OWNER_ROLE;
GRANT CREATE CLUSTER to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE INDEXTYPE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE MATERIALIZED VIEW to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE PROCEDURE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SEQUENCE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SESSION to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE SYNONYM to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TABLE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TRIGGER to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE TYPE to BPMN_GOLD_OWNER_ROLE;
GRANT CREATE VIEW to BPMN_GOLD_OWNER_ROLE;
GRANT JAVAUSERPRIV to BPMN_GOLD_OWNER_ROLE;

--===============================================================

CREATE ROLE BPMN_GOLD_PDWOWNER_ROLE;
GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLD_PDWOWNER_ROLE;
GRANT EXECUTE ON SYS.DBMS_LOCK TO BPMN_GOLD_PDWOWNER_ROLE;

--===============================================================

CREATE ROLE BPMN_GOLD_XAOWNER_ROLE;
GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON SYS.PENDING_TRANS$ to BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON DBA_2PC_PENDING to BPMN_GOLD_XAOWNER_ROLE;
GRANT SELECT ON DBA_PENDING_TRANSACTIONS to BPMN_GOLD_XAOWNER_ROLE;
GRANT EXECUTE ON DBMS_XA to BPMN_GOLD_XAOWNER_ROLE;

--===============================================================
-- Common DB user / role / tablespace
--===============================================================

CREATE TABLESPACE "BPMN_GOLDCOM_DATA01"
DATAFILE '+DATA/BPM/BPMN_GOLDCOM_DATA01.dbf'
SIZE 2000M reuse
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- Creating schema for CommonDB
CREATE USER BPMN_GOLDCOM_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDCOM_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDCOM_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDCOM_OWNER;


--===============================================================
-- ProcessChoreograpther DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPCH_2_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPCH_2_DATA01.dbf'
SIZE 16000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDPCH_2_OWNER IDENTIFIED BY <some password>
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE BPMN_GOLDPCH_2_DATA01
QUOTA UNLIMITED ON BPMN_GOLDPCH_2_DATA01 ;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPCH_2_OWNER;

--===============================================================
-- ProcessServer DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPSS_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPSS_DATA01.dbf'
SIZE 15000M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

-- Create schema owner if the user does not already exist --
CREATE USER BPMN_GOLDPSS_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDPSS_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDPSS_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPSS_OWNER;

--===============================================================
-- PerformanceDW DB user / role / tablespace
--===============================================================

CREATE TABLESPACE BPMN_GOLDPDW_DATA01
DATAFILE '+DATA/BPM/BPMN_GOLDPDW_DATA01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDPDW_OWNER IDENTIFIED BY <some password>
DEFAULT TABLESPACE BPMN_GOLDPDW_DATA01
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON BPMN_GOLDPDW_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPDW_OWNER;
GRANT BPMN_GOLD_PDWOWNER_ROLE TO BPMN_GOLDPDW_OWNER;

--===============================================================

-- BusinessSpace DB user / role / tablespace

--===============================================================

CREATE TABLESPACE BPMN_GOLDBSP_DATA01

DATAFILE '+DATA/BPM/BPMN_GOLDBSP_DATA01.dbf'

SIZE 2000M reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING;

CREATE USER BPMN_GOLDBSP_OWNER IDENTIFIED BY <some password>

DEFAULT TABLESPACE BPMN_GOLDBSP_DATA01

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON BPMN_GOLDBSP_DATA01;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDBSP_OWNER;

--===============================================================

-- Messaging Enginer DB users / roles / tablespaces

--===============================================================

-- Creating Tablespace for Messaging

CREATE TABLESPACE "BPMN_GOLDAPPM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDAPPM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDAPPM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDAPPM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDAPPM_2_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDBPCM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDBPCM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDBPCM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDBPCM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDBPCM_2_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDCEIM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDCEIM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDCEIM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDCEIM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDCEIM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDPDWM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDPDWM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDPDWM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDPDWM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDPDWM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDPSSM_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDPSSM_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDPSSM_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDPSSM_DATA01

QUOTA UNLIMITED ON BPMN_GOLDPSSM_DATA01 ;

CREATE TABLESPACE "BPMN_GOLDSYSM_2_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDSYSM_2_DATA01.dbf'

SIZE 3000M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

CREATE USER BPMN_GOLDSYSM_2_OWNER IDENTIFIED BY <some password>

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE BPMN_GOLDSYSM_2_DATA01

QUOTA UNLIMITED ON BPMN_GOLDSYSM_2_DATA01 ;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDAPPM_2_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDBPCM_2_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDCEIM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPDWM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDPSSM_OWNER;

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDSYSM_2_OWNER;

--===============================================================

-- XA Recovery DB user / role / tablespace

--===============================================================

CREATE TABLESPACE "BPMN_GOLDXAR_DATA01"

DATAFILE '+DATA/BPM/BPMN_GOLDXAR_DATA01.dbf'

SIZE 400M reuse

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO;

-- Creating schema for CommonDB XA

CREATE USER BPMN_GOLDXAR_OWNER IDENTIFIED BY <some password>

DEFAULT TABLESPACE BPMN_GOLDXAR_DATA01

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON BPMN_GOLDXAR_DATA01;

--Gran Role BPM_COMMON

GRANT BPMN_GOLD_OWNER_ROLE TO BPMN_GOLDXAR_OWNER;

GRANT BPMN_GOLD_XAOWNER_ROLE TO BPMN_GOLDXAR_OWNER;