8 RPAS Hybrid Storage Architecture Option

This chapter contains information on securing the RPAS Hybrid Storage Architecture (HSA).

RPAS Data Mart Construction and Security Model

RPAS includes an optional component known as HSA. RPAS HSA allows a configurable subset of RPAS data and metadata to be stored in a group of Oracle Database tables, collectively known as an RPAS Data Mart (RDM). Here we present details on the security model of the RPAS Server to Oracle Database connection.

In order to enforce the "least privileges" model of access control, the RDM installation process will create, in its standard configuration, eight Oracle schemas (a schema is the equivalent of an Oracle Database "user," with a particular set of privileges). Only one of these schemas, the RPAS Data Mart schema, will own persistent data tables. The remaining schemas have defined access rights, as needed by particular RPAS Server processes. Oracle login details for these schema/users will be stored in an Oracle Wallet, with default permissions allowing access only by the Unix account used for RPAS Server administration.

The following table shows all schemas and their corresponding role and connection alias. While the schema names can be customized to the customer's naming standard, all the role names and connection aliases are constants.

Table 8-1 Schemas

Schema (Default)	Role	DB Connection Alias
rpas_data_mart	NA	rpas_data_mart_conn
rpas_patch_user	rpas_patch_role	rpas_patch_conn
rpas_batch_user	rpas_batch_role	pas_batch_conn
rpas_dimload_user	rpas_dimload_role	rpas_dimload_conn
rpas_factload_user	rpas_factload_role	rpas_factload_conn
rpas_hiermgr_user	rpas_hiermgr_role	rpas_hiermgr_conn
rpas_wkbk_user	rpas_wkbk_role	rpas_wkbk_conn
rpas_etl_user	rpas_etl_role	rpas_etl_conn

The RPAS RDM creation process (a set of binary utilities and shell scripts) will create the required schemas and set their permissions. It will also construct the metadata and data tables required in the RPAS Data Mart schema. Options exist in the RDM creation process to allow a customer DBA to examine and customize the generated Oracle DDL/DML scripts before the installation process proceeds, both for database layout and efficiency concerns, as well as to verify that the Database constructs meet their corporate security standards. It is important to note, however, that the permissions for the various schemas (roles) have been set as restrictively as possible, and if they are modified, it will likely break RPAS functionality.

The following diagram gives an overview of an HSA-enabled RPAS installation, with particular attention to the supported security model, as described above.

Figure 8-1 RDM Schema Security Model

External Integration APIs

In addition to RPAS Server utilities, data may also flow to and from and RDM from other database applications. To facilitate this, we provide a set of External Integration APIs in the form of PL/SQL procedures. They allow a process from another RPAS application or any other customer application to load data into or extract data from an RDM in a controlled, secure manner. We provide specifications for staging tables (full details in the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client section on HSA features), and the external application may then call our External API procedures in PL/SQL, which will first validate the data and then load it into the appropriate RDM tables. External application code will not need (or be granted) access to the RDM data tables directly, but will only need to call our External API routines in PL/SQL.

SSL Connection

For best security practices in the connection between the RPAS Server utilities and the Oracle Database, we recommend enabling the optional SSL feature. The Oracle Database server natively supports SSL connections from clients, and the feature needs only to be enabled and configured for use with RPAS. Note that only one-way SSL is covered in this document. For two-way SSL setup, see the Oracle Database Security Guide at the following URL:

http://docs.oracle.com/database/121/DBSEG/asossl.htm

SSL Overview

The following diagram shows the components of the SSL configuration for RDM. The client/server connection will be protected by one-way SSL, which only authenticates the server on the client side. As a result, only the Oracle server wallet is required to have a private key. The client wallet will only need to contain the server CA (Certificate Authority) certificate.

The CA certificate can be either self-signed or issued by a third-party CA. In the case of third-party CA, more than one certificate may need to be imported into the client wallet (usually called CA certificate chain).

The server side configuration is mostly done manually by the DBA. A script is provided to create the wallet if self-signed certificate is used.

The client side configuration is done through scripts by the RPAS admin.

Figure 8-2 SSL Configuration for RDM

Set Up SSL on Oracle Server

This section contains information on setting up SSL on the Oracle server side. This should be done by a DBA who has the permission to modify the configuration files of the Oracle server.

Create Oracle Server Wallet

The Oracle server wallet can be created the same way as is the RPAS server wallet for one-way SSL. The process has been documented in great detail in the Oracle Retail Predictive Application Server Administration Guide for the Classic Client "SSL" chapter. On UNIX, the wallet must be created by the user account which starts the Oracle database processes and for security reason the wallet is only accessible by its creator.

If self-signed CA certificate is used, the shell script "createSSLWallets.sh" provided by RPAS can be used to create the root and server wallets, private keys and all related certificates.

When running the script "createSSLWallets.sh," choose option 3 to create wallets for Oracle database server, as shown in the following figure:

Figure 8-3 Create Wallet Select Option

The script asks for the root directory where the wallets will be created, your organization name (any name such as "QA"), and passwords for the root and server wallets. In the end of a successful run, the wallet location and the path to the CA certificate file will be displayed to the user (see the following figure).

The CA certificate file can be copied to a common location and will need to be imported into the Oracle client wallet later on.

Figure 8-4 Create Wallet Complete

Update Oracle Server Network Configuration

The network configuration file "sqlnet.ora" normally resides in $ORACLE_HOME/network/admin directory.

The following table lists the required settings for SSL.

Required SSL Settings in sqlnet.ora on Oracle server
SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = wallet_location) ) )

The SSL client authentication parameter must be set to FALSE for one-way SSL. The wallet_location is the absolute path to the Oracle server wallet as discussed in the previous section.

The following table lists the optional settings for SSL.

Optional SSL Settings in sqlnet.ora on Oracle server
SSL_CIPHER_SUITES= (SSL_cipher_suite1 [,SSL_cipher_suite2]) SSL_VERSION=1.0

For more information regarding these settings, refer to Oracle Database Security Guide at the following URL:

http://docs.oracle.com/database/121/DBSEG/asossl.htm">>http://docs.oracle.com/database/121/DBSEG/asossl.htm

Update Oracle Listener Configuration

The listener configuration file "listener.ora" normally resides in $ORACLE_HOME/network/admin directory. After modification, the Oracle listener must be restarted for the changes to take effect.

The following table shows the changes in bold.

Required SSL Settings in listener.ora on Oracle server
SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = wallet_location) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = odbserver-host)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = odbserver-host)(PORT = 2484)) ) )

The wallet location and SSL settings are the same as that in the network configuration file (must be in both files). A new listener endpoint with protocol TCPS must be added to the LISTENER setting. Port number 2484 is the typical port for TCPS but other number can also be used.

Set up SSL on Oracle Client

This section contains information on setting up SSL on the Oracle client side for use by RPAS or RDM processes.

Update Schema Info Configuration

The schema info configuration must have matching endpoint parameters to the Oracle listener. The protocol must be "tcps" and the port number must be the same as specified in the listener configuration.

TNS Parameters in schemaInfo.xml
<tns_parameters> <protocol>tcps</protocol> <host>odbserver-host</host> <port>2484</port> <server>dedicated</server> <service_name>myservice</service_name> <sid></sid> </tns_parameters>

Import Server CA Certificate

To import the server CA certificate:

1. The prepareRDM.ksh script in the RDM build process already creates an Oracle wallet to hold the login credentials for RDM. The same wallet should be used to hold the Oracle server CA certificate. If protocol "tcps" is specified in the schema info, this same script will ask for the path to the CA certificate file and import it into the wallet automatically.

2. The following figure shows the importing of CA certificate by the prepareRDM.ksh script.

   Figure 8-5 Import CA Certificate

   
   

If third-party CA is used and there is more than one file in the CA certificate chain, the user must specify the top certificate for the script to import and manually import the rest in the order of the chain using the following command. The client wallet location is the "wallet" subdirectory under the RDM repository.

orapki wallet add -wallet {client_wallet_directory} -trusted_cert -cert {ca_cert_chain_file} -pwd {client_wallet_password}

Example:

orapki wallet add -wallet C:/wallets/client -trusted_cert -cert C:/wallets/ca_chain2.txt -pwd clientpass1

Update Oracle Client Network Configuration

The network configuration file "sqlnet.ora" used by RPAS processes is created automatically by the RDM Manager during the RDM build process. This file resides under the "tns_admin" subdirectory of the RDM repository.

For your information, the following SSL settings are required.

Required SSL Settings in sqlnet.ora on Oracle Client
SSL_CLIENT_AUTHENTICATION = TRUE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = wallet_location) ) )

The SSL client authentication parameter must be set to TRUE for one way SSL. The wallet_location is the "wallet" subdirectory of the RDM repository.

Update Oracle Net Service Names

The Oracle Net Service Names configuration file "tnsnames.ora" is created automatically by the RDM Manager during the RDM build process under the "tns_admin" subdirectory of the RDM repository. All entries will use the endpoint parameters specified by the schema info configuration.

Net Service Names using SSL in tnsnames.ora on Oracle Client
rpas_data_mart_conn = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = odbserver-host)(PORT = 2484)) ) (CONNECT_DATA = (SERVER = dedicated) (SERVICE_NAME = myservice) ) ) . . .

Test and Confirm SSL Connection

After SSL configuration is done on both Oracle server and client and the Oracle listener has been restarted, the user can use RDM Manager to verify the connection. (There are a small delay, sometimes a few minutes, between the listener is restarted and the SSL connection is up.)

rdmMgr -rdm rdmPath -testConnection