Database Secure Deployment

This page describes secure deployment for MS SQL 2008 and Oracle 11g databases.

<tabber>

Secure Deployment for MS SQL Server 2008=

For MS SQL Server 2008 secure deployment, Genesys recommends using MS SQL Server Transparent Data Encryption (TDE) which performs a real-time I/O encryption and decryption of the data and log files. This method has only a minor impact on performance, which is critical for the Advisors Suite.

It is important to mention that TDE is available only for MS SQL Server Enterprise edition. The data cannot be encrypted using TDE if any other MS SQL Server edition is used.

Advisors Suite MS SQL databases do not have any properties that can prevent the application of TDE. The databases do not contain any READ-ONLY file groups, full text indexes, or filestreams. Users must follow the standard Microsoft documentation related to this topic.

The Advisors Suite does not support MS SQL Server cell-level encryption.

|-| Secure Deployment for Oracle 11g=

Oracle 11g offers:

• Transparent Database Encryption (TDE) introduced in Oracle 10g, which allows the encryption of individual column content on the data file level.
• Tablespace encryption introduced in Oracle 11g, which allows the encryption of the entire content of a tablespace.

To verify that databases are secured with TDE encryption, do the following:

1. Run the following query and all your tables should be using the ENCRYPTED_TS tablespace:
   select * from user_tables
2. Run the following query and check if the ENCRYPTED_TS table space shows Yes:
   select tablespace_name,encrypted from user_tablespaces

The following specifics of Advisors database deployment must be considered if the above Oracle features are used.

[+] Platform, Metric Graphing, and Genesys Adapter Metrics Databases

Initial Platform, Metric Graphing, and Genesys Adapter Metrics database scripts contain tablespace names in the form of variables in each create SQL statement for tables, primary keys, and indexes. The tables and indexes are distributed among several groupings based on Genesys’ recommendations related to the data update patterns and its usage characteristics.

The Platform deployment script replaces the variables dynamically with the values you provide in the deployment script dialog. The deployment script generates a new runObjCre.sql script with the substituted variables. The deployment script executes runObjCre.sql and other SQL scripts in a certain order.

It is important to make a decision about what objects need encryption and what objects should go to what tablespace before the deployment script execution.

If you decide to place all objects into one single encrypted tablespace, specify the tablespace as a user default data tablespace, and then read the script dialog prompts to insure this tablespace is used for all objects (that is, on all prompts, specify the name of this tablespace, or simply press Enter). If you want to use different encrypted tablespaces for different groups of objects predefined in the scripts, you must specify the tablespace names you have chosen for this purpose on the corresponding prompts. Review the Readme.txt file supplied with the scripts to find out how the objects are grouped in the scripts.

If a more granular customization is necessary (for instance change table/index grouping or encrypt the data on the column level), you will need to implement the following steps:

1. Run the deployment script from SQL*plus to generate runObjCre.sql.
2. Drop the previously created user.
3. Customize the generated runObjCre.sql.
4. Save it and then execute the scripts in the following order:
   1. Platform schema:
      runUsrCre.sql
      runObjCre.sql
      version_ROUTINE.sql
      version_FA_ROUTINE.sql
      version_INIT_DATA.sql
      version_CUSTOM_ROUTINE.sql
      exec spCompileInvalid();
   2. Metrics Graphing schema:
      runMgUsrCre.sql
      runObjCre.sql
      version_INIT_DATA.sql
      version_ROUTINE.sql
      exec spCompileInvalid();
   3. Genesys Adapter Metrics schema:
      runMetricsUsrCre.sql
      runObjCre.sql
      gc_metrics_new_version_ROUTINE.sql
      exec spCompileInvalid();

List of Function-Based Indexes

TDE limitations related to the column-based encryption of the content with function-based indexes are applicable to the Advisors Suite. The Advisors schema contains a number of function-based indexes that need to be modified or dropped if the column-based encryption of the related columns is chosen. See the following Table.

Platform Schema

Index	Table	Column expression
IX_APPLICATION_NAME	APPLICATION – Contains application group metadata	UPPER("NAME")
IX_CALL_APP_UP	CALL_APPLICATION – Contains metadata for queues, call types, services, interaction queues	UPPER("NAME")
IX_CALL_CENTER_NAME	CALL_CENTER – Contains contact center metadata	UPPER("NAME")
IX_CALL_CREGION_NAME	REGIONS – Contains metadata for geographic regions, reporting regions and operating units	UPPER("NAME"), UPPER("TYPE")
IX_CG_UP	CONTACT_GROUP – Contains metadata for workforce contact groups	UPPER("NAME")
IX_CG_ORIGIN	CONTACT_GROUP	UPPER("WFM_EQUIVALENT_ID"), UPPER("SOURCE_SYSTEM")
IX_CONTACT	CONTACT – Contains Advisors users contact data	UPPER(“EMAIL”)
IX_PG_NAME	PG – Contains metadata for peripheral gateways	UPPER("PG_NAME")
IX_USERS_USERNAME	USERS – Contains the list of Advisor users	UPPER("USERNAME")
IX_KEY_ACTION_NAME	KEY_ACTION	UPPER("NAME")
IX_ADAPTER_INST_HOST_PORT	ADAPTER_INSTANCES	UPPER(“HOST”)