2 Installing the Oracle Health Sciences Cohort Explorer Schema

This chapter describes a secure installation of the Oracle Health Sciences Cohort Explorer (OHSCE) schema on either a Windows or Unix platform.

It includes the following sections:

• "Security Guidelines for Database Objects and Database Options"

• "Prerequisites for Creating the Schema"

• "Installation Files"

• "Installing the Schema"

The Master Install script is provided in the media pack.

A successful installation of the OHSCE schema creates the OHSCE data mart and seed data for OHSCE.

Security Guidelines for Database Objects and Database Options

This section describes Oracle Health Sciences Cohort Explorer database objects and database options.

About Oracle Health Sciences Cohort Explorer Database Objects

The Oracle Health Sciences Cohort Explorer contains database objects. Use DDL scripts and PL/SQL procedures and functions to create database objects; and DML scripts to create seed data.

While installing and configuring Oracle Database Server, follow the guidelines in Oracle® Database 2 Day + Security Guide 11g Release 2 (11.2).

Oracle Database Options

The Oracle Database has options that provide additional security features. Oracle Health Sciences Cohort Explorer may include data that falls under HIPAA guidelines in the United States and similar guidelines elsewhere. These features can help comply with those guidelines.

Database Vault

Oracle Health Sciences Cohort Explorer includes data that may fall under HIPAA or other regulations outside the United States. These data are highly sensitive and only those with a need to know should have access to it. To prevent DBAs and others from seeing the data, it is recommended that Oracle Database Vault be used to limit access to the HDWF schema to the HDWF user to prevent DBAs and other "superuser" accounts from accessing the data. Note that Database Vault requires a separate license.

Oracle Audit Vault

Oracle Audit Vault automates the audit collection, monitoring, and reporting process, turning audit data into a key security resource for detecting unauthorized activity. Consider using this feature to satisfy compliance regulations such as SOX, PCI, and HIPAA, and to mitigate security risks. Note that Oracle Audit Vault requires a separate license.

Transparent Data Encryption

Transparent Data Encryption is one of the three components of the Oracle Advanced Security option for Oracle Database 11g Release 2 Enterprise Edition. It provides transparent encryption of stored data to support your compliance efforts. If you employ Transparent Data Encryption, applications do not have to be modified and continue to work seamlessly as before. Data is automatically encrypted when it is written to disk and automatically decrypted when accessed by the application. Key management is built in, eliminating the complex task of creating, managing and securing encryption keys. Note that Transparent Data Encryption requires a separate license.

Prerequisites for Creating the Schema

You must ensure the following:

• Installation of the Oracle Health Sciences Clinical Development Center (CDC) 3.1 SP1 database.

  Refer to the document CDC-SCE_3.1_SP1_Database_Server_Installation_Qualification_for_Windows for details on a CDC database installation on MS Windows.

  Refer to the document CDC-SCE_3.1_SP1_Database_Server_Installation_Qualification_for_UNIX_using_Windows for details on a CDC database installation on UNIX.

  Note:

  Install OID 11.1.1.5.0 if you need to synchronize your user accounts using LDAP.

• Installation of the Oracle Health Sciences Clinical Development Center (CDC) 3.1 SP1 client.

  Refer to the appropriate document for details:

  CDC-SCE 3.1 SP1 Client Installation Qualification for Citrix

  CDC-SCE 3.1 SP1 Client_Installation Qualification for Windows

  CDC-SCE 3.1 SP1 Database Server Installation Qualification for UNIX using Windows

• Installation of the HDWF schema (created during the installation of Oracle Healthcare Data Warehouse Foundation 3.1).

• Creation of the main OHSCE data model user account with CREATE SESSION, CREATE TABLE and CREATE SYNONYM privileges.

  Important:

  You must install the OHSCE schema on the same instance as the Oracle Health Sciences CDC database.

• No user is connected to the database.

• The user has passwords to the user accounts for the following:

  • OHSCE Data Model (OHSCE)

  • HDWF

  • GTMETA

  • SCESCHEMA

• The user knows the description of each parameter that is passed to the install_cxe.sql file. The install_cxe.sql script validates each of the nine expected parameters at the start of the script and exits if any of the parameters are incorrect.

  Provide the required parameters in the following sequence during the execution of the script:

  1. The name of the OHSCE schema user. This is not case-sensitive.

  2. The password of the OHSCE schema user. This is case-sensitive.

  3. The net manager configuration to connect to the OHSCE database instance.

  4. The GTMETA user password. The CDC database must be installed on the same instance as GTMETA. This is set to GTMETA after the installation of CDC. This parameter is case-sensitive.

  5. The SCESCHEMA user password. This is set to SCESCHEMA after the installation of CDC. This parameter is case-sensitive.

  6. The name of the HDWF schema user. This is not case-sensitive.

  7. The password of the HDWF schema user. This is case-sensitive.

  8. The net manager configuration to connect to the HDWF database instance. This is most likely a different instance than the OHSCE database instance.

  9. The name of the tablespace used to create indexes for the OHSCE schema. This parameter is used to specify the index tablespace.

Installation Files

Installation requires the following files:

• install_cxe.sql

• cohort_data_model.sql

• cohort_index_sequence.sql

• cohort_related_ddl.sql

• cohort_drop_indexes.sql

• load_seed_data.sql

• cohort_create_indexes.sql

• hdm_cd_repository_hier_v.sql

• load_cdc_data.sql

• c_load_param.sql

• c_load_de_identify.sql

• cohort_protocol_util.pks

• cohort_protocol_util.pkb

• cohort_revoke_grants.sql

• c_cohort_procedure_type.sql

Installing the Schema

Install and verify the installation of the schema:

1. Copy all the files listed in Installation Files to the database server. Ensure that the files are placed in the same folder.

   Important:

   You must install OHSCE on the same instance as the Oracle Health Sciences CDC database.

2. Start SQLPLUS® in /nolog mode.

3. Execute the install_cxe.sql script using following command.

     SQL>@install_cxe.sql  <CDM schema user name>
   < CDM schema user password>
   < Database Instance>
   < GTMETA user password >
   < SCESCHEMA user password > 
   < HDM schema user  name>
   < HDM schema user password>
   < Database Instance>
   < Index tablespace name>
   

   The script installs the OHSCE database, creates a view in the HDWF schema and then integrates OHSCE with the CDC schema.

4. Review the install_cxe.log file that is created in the same folder after the installation.

5. Log into each schema as the schema owner to verify that all packages, stored procedures, functions, triggers, and views are valid in each of the OHSCE,HDWF, SCESCHEMA and GTMETA schemas.

6. Log in to SQL Developer with the OHSCE schema and verify the following:

   • All objects are created in the schema.

   • All records are correctly inserted into the C_LOAD_PARAM, C_LOAD_DATES, C_LOAD_DE_IDENTIFY and C_COHORT_PROCEDURE_TYPE tables.

7. Log in to the HDWF schema and verify that the HDM_CD_REPOSITORY_HIER_V view is created there.