Page tree
Skip to end of metadata
Go to start of metadata

This topic shows the contents of the create_users_and_tablespaces.sql database creation script.

SET DEFINE ON

-- Please modify following variables for role, user or tablespace name customisations
DEFINE ROLE_OWNER = 'BCO_OWN_ROLE'
DEFINE ROLE_DASHBOARD = 'BCO_DASH_ROLE'
DEFINE ROLE_REPORT = 'BCO_REP_ROLE'

DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_DASHBOARD = 'BCO_DASH'
DEFINE USER_REPORT = 'BCO_REP'

DEFINE TBS_CATALOG = 'BCO'
DEFINE TBS_CATALOG_IDX = 'BCO_IDX'
DEFINE TBS_DATA = 'BCO_DATA'
DEFINE TBS_DATA_IDX = 'BCO_DATA_IDX'
DEFINE TBS_DASH= 'BCO_DASH'

DEFINE TBS_TEMP = 'TEMP'

-- Tablespaces
CREATE TABLESPACE &TBS_CATALOG DATAFILE
SIZE 150M
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
/

CREATE TABLESPACE &TBS_CATALOG_IDX DATAFILE
SIZE 150M
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
/

CREATE TABLESPACE &TBS_DATA DATAFILE
SIZE 8000M,
SIZE 8000M,
SIZE 8000M
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M
/

CREATE TABLESPACE &TBS_DATA_IDX DATAFILE
SIZE 8000M,
SIZE 8000M,
SIZE 8000M
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M
/

CREATE TABLESPACE &TBS_DASH DATAFILE
SIZE 100M
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
/

-- Schema owner role
CREATE ROLE &ROLE_OWNER NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_OWNER
/
GRANT CREATE SYNONYM TO &ROLE_OWNER
/
GRANT CREATE TABLE TO &ROLE_OWNER
/
GRANT CREATE VIEW TO &ROLE_OWNER
/
GRANT CREATE SEQUENCE TO &ROLE_OWNER
/
GRANT CREATE TYPE TO &ROLE_OWNER
/
GRANT CREATE PROCEDURE TO &ROLE_OWNER
/
GRANT CREATE TRIGGER TO &ROLE_OWNER
/
GRANT CREATE OPERATOR TO &ROLE_OWNER
/
GRANT SELECT ON DBA_DATA_FILES TO &ROLE_OWNER
/

-- Reporting role
CREATE ROLE &ROLE_REPORT NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_REPORT
/
GRANT CREATE SYNONYM TO &ROLE_REPORT
/

-- Schema owner user
CREATE USER &USER_OWNER IDENTIFIED BY &USER_OWNER
DEFAULT TABLESPACE &TBS_CATALOG
TEMPORARY TABLESPACE &TBS_TEMP
QUOTA UNLIMITED ON &TBS_CATALOG
/
GRANT &ROLE_OWNER TO &USER_OWNER
/
GRANT &ROLE_REPORT TO &USER_OWNER
/
ALTER USER &USER_OWNER DEFAULT ROLE ALL
/

ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG_IDX
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA_IDX
/

-- Reporting user (limited set of grant)
CREATE USER &USER_REPORT IDENTIFIED BY &USER_REPORT
TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_REPORT TO &USER_REPORT
/
ALTER USER &USER_REPORT DEFAULT ROLE ALL
/

-- Dashboard schema role
CREATE ROLE &ROLE_DASHBOARD NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_DASHBOARD
/
GRANT CREATE SYNONYM TO &ROLE_DASHBOARD
/
GRANT CREATE TABLE TO &ROLE_DASHBOARD
/
GRANT CREATE VIEW TO &ROLE_DASHBOARD
/
GRANT CREATE SEQUENCE TO &ROLE_DASHBOARD
/
GRANT CREATE TYPE TO &ROLE_DASHBOARD
/
GRANT CREATE PROCEDURE TO &ROLE_DASHBOARD
/
GRANT CREATE TRIGGER TO &ROLE_DASHBOARD
/
GRANT CREATE OPERATOR TO &ROLE_DASHBOARD
/

-- Dashboard user
CREATE USER &USER_DASHBOARD
IDENTIFIED BY &USER_DASHBOARD
DEFAULT TABLESPACE &TBS_DASH
TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_DASHBOARD TO &USER_DASHBOARD
/
ALTER USER &USER_DASHBOARD DEFAULT ROLE ALL
/
ALTER USER &USER_DASHBOARD QUOTA UNLIMITED ON &TBS_DASH
/