3 Post Installation of Retail Infrastructure in Database

Oracle Retail applications use the Oracle database as the backend data store for applications. In order to ensure complete environment security the database should be secured.

This chapter describes the post installation steps for secured setup of Retail infrastructure in the Database.

The following topics are covered in this chapter:

• Configuring SSL Connections for Database Communications

• Configuring SSL on the Database Server

• Configuring SSL on an Oracle Database Client

• Configuring SSL on a Java Database Connectivity (JDBC) Thin Client

• Configuring the Password Stores for Database User Accounts

• Configuring the Database Password Policies

• Creating an Encrypted Tablespace in Oracle 12c Container Database

• Additional Information

Configuring SSL Connections for Database Communications

Secure Sockets Layer (SSL) is the standard protocol for secure communications, providing mechanisms for data integrity and encryption. This can protect the messages sent and received by the database to applications or other clients, supporting secure authentication and messaging. Configuring SSL for databases requires configuration on both the server and clients, which include application servers.

This section covers the steps for securing Oracle Retail Application Clusters (RAC) database. Similar steps can be followed for single node installations also.

Configuring SSL on the Database Server

The following steps are one way to configure SSL communications on the database server:

1. Obtain an identity (private key and digital certificate) and trust (certificates of trusted certificate authorities) for the database server from a Certificate Authority.

2. Create a folder containing the wallet for storing the certificate information. For Real Application Cluster (RAC) systems, this directory can be shared by all nodes in the cluster for easier maintenance.

   mkdir-p/oracle/secure_wallet
   

3. Create a wallet in the path. For example:

   orapki wallet create -wallet /oracle/secure_wallet -auto_login
   

4. Import each trust chain certificate into the wallet as shown in the following example:

   orapki wallet add -wallet /oracle/secure_wallet -trusted_cert -cert <trust chain certificate>
   

5. Import the user certificate into the wallet, as shown in the following example:

   orapki wallet add -wallet /oracle/secure_wallet -user_cert -cert <certificate file location>
   

6. Update the listener.ora by adding a TCPS protocol end-point first in the list of end points.

   LISTENER1=
     (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcps)(HOST=<dbserver>)(PORT=2484))
       (ADDRESS=(PROTOCOL=tcp)(HOST=<dbserver>)(PORT=1521)))
   

7. Update the listener.ora by adding the wallet location and disabling SSL authentication.

   WALLET_LOCATION = (SOURCE= (METHOD=File) (METHOD_DATA=
     (DIRECTORY=wallet_location)))
   SSL_CLIENT_AUTHENTICATION=FALSE
   

8. Update the sqlnet.ora with the same wallet location information and disabling SSL authentication.

   WALLET_LOCATION = (SOURCE= (METHOD=File) (METHOD_DATA=
     (DIRECTORY=wallet_location)))
   SSL_CLIENT_AUTHENTICATION=FALSE
   

9. Update the tnsnames.ora to configure a database alias using TCPS protocol for connections.

   <dbname>_secure=
     (DESCRIPTION= (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=TCPS)(HOST=<dbserver>)(PORT=2484))) 
       (CONNECT_DATA=(SERVICE_NAME=<dbname>)))
   

10. Restart the database listener to pick up listener.ora changes.

11. Verify the connections are successful to the new <dbname>_secure alias.

12. At this point either the new secure alias can be used to connect to the database, or the regular alias can be modified to use TCPS protocol.

13. Export the identity certificate so that it can be imported on the client systems:

    orapki wallet export -wallet /oracle/secure_wallet -dn <full dn of identity certificate> -cert <filename_to_create>
    

Configuring SSL on an Oracle Database Client

The following steps are one way to configure SSL communications on the database client:

1. Create a folder containing the wallet for storing the certificate information.

   mkdir-p /oracle/secure_wallet
   

2. Create a wallet in the path. For example:

   orapki wallet create -wallet /oracle/secure_wallet -auto_login
   

3. Import each trust chain certificate into the wallet as shown in the following example:

   orapki wallet add -wallet /oracle/secure_wallet -trusted_cert -cert <trust chain certificate
   

4. Import the identity certificate into the wallet, as shown in the following example:

   orapki wallet add -wallet /oracle/secure_wallet -trusted_cert -cert <certificate file location>
   

5. Update the sqlnet.ora with the wallet location information and disabling SSL authentication.

   WALLET_LOCATION 
     = (SOURCE= 
     (METHOD=File) 
     (METHOD_DATA=
       (DIRECTORY=wallet_location)))
   SSL_CLIENT_AUTHENTICATION=FALSE
   

6. Update the tnsnames.ora to configure a database alias using TCPS protocol for connections.

   <dbname>_secure=
   (DESCRIPTION= 
     (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCPS)(HOST=<dbserver>)(PORT=2484)))
     (CONNECT_DATA=(SERVICE_NAME=<dbname>)))
   

7. Verify the connections are successful to the new <dbname>_secure alias.

At this point either the new secure alias can be used to connect to the database, or the regular alias can be modified to use TCPS protocol.

Configuring SSL on a Java Database Connectivity (JDBC) Thin Client

The following steps are one way to configure SSL communications for a Java Database Connectivity (JDBC) thin client:

1. Create a folder containing the keystore with the certificate information.

   mkdir-p /oracle/secure_jdbc
   

2. Create a keystore in the path. For example:

   keytool -genkey -alias jdbcwallet -keyalg RSA -keystore /oracle/secure_ jdbc/truststore.jks -keysize 2048
   

3. Import the database certificate into the trust store as shown in the following example:

   keytool -import -alias db_cert -keystore /oracle/secure_jdbc/truststore.jks -file <db certificate file>
   

4. JDBC clients can use the following URL format for JDBC connections:

   jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=tcps) (HOST=<dbserver>) (PORT=2484)) (CONNECT_DATA= (SERVICE_NAME=<dbname>)))
   

   
   

   Note: The <dbname> would be replaced with service name in case of multitenant database (12c).

   
   

5. You need to set the properties as shown in Table 2-1, either as system properties or as JDBC connection properties.

   Table 3-1 Setting the Properties

   Property	Value
   javax.net.ssl.trustStore	Path and file name of truststore. For example: /oracle/secure_jdbc/truststore.jks
   javax.net.ssl.trustStoreType	JKS
   javax.net.ssl.trustStorePassword	Password for trust store

   
   

Configuring the Password Stores for Database User Accounts

Wallets can be used to protect sensitive information, including usernames and passwords for database connections. The Oracle Database client libraries have built-in support for retrieving credential information when connecting to databases. Oracle Retail applications utilize this functionality for non-interactive jobs such as batch programs, so that they are able to connect to the database without exposing user and password information to other users on the same system.

For information on configuring wallets for database access, see ”Setting Up Password Stores with Oracle Wallet” in the product installation guide.

Configuring the Database Password Policies

Oracle Database includes robust functionality to enforce policies related to passwords such as minimum length, complexity, when it expires, number of invalid attempts, and so on. Oracle Retail recommends these policies are used to strengthen passwords and lock out accounts after failed attempts.

For example, to modify the default user profile to lock accounts after five failed login attempts, run the following commands as a database administrator:

1. Query the current settings of the default profile.

   select resource_name,limit,resource_type from dba_profiles where profile='DEFAULT';
   

2. Alter the profile, if failed_login_attempts is set to unlimited:

   alter profile default limit FAILED_LOGIN_ATTEMPTS 5;
   

   
   

   Note: Many other profile settings are available for increased security. For more information, see the Oracle Database Security Guide.

   
   

Creating an Encrypted Tablespace in Oracle 12c Container Database

The retail tablespaces can be encrypted in container databases using the following method:

1. Update the sqlnet.ora file with the following encryption details:

   1. Configure the sqlnet.ora file for the software keystore location.

      ENCRYPTION_WALLET_LOCATION= 
      (SOURCE=
      (METHOD=FILE)
        (METHOD_DATA=
        (DIRECTORY=path_to_keystore)))
      

   2. Restart the listener.

2. Set up the Tablespace Encryption in the Container Database.

   1. Create Software Keystores as follows:

      SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u03/wallet_cdb' IDENTIFIED BY "val1ue#";
      

      Keystore altered.

   2. Create an Auto-Login Software Keystore as follows:

      SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u03/wallet_cdb' identified by "val1ue#';
      

      Keystore altered.

      
      

      Note: The auto-login software keystore can be opened from different computers from the computer where this keystore resides. However, the [local] auto-login software keystore can only be opened from the computer on which it was created.

      
      

   3. Open the Software Keystore as follows:

      SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "val1ue#" Container=ALL;
      

      Keystore altered.

   4. Set the Software TDE Master Encryption Key as follows:

      SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "val1ue#" WITH BACKUP USING 'TDE_ENCRYPTION' Container=all;
      

      Keystore altered.

      
      

      Note: One can set the Encryption KEY only for particular PDB if required, by specifying the CONTAINER=<PDB>.

      
      

   5. Create the ENCRYPTED TABLESPACE in PDB as follows:

      SQL> conn sys/D0ccafe1@QOLRP01APP as sysdba
      

      Connected.

      SQL> create tablespace test datafile '+DATA1' size 100m ENCRYPTION DEFAULT STORAGE (ENCRYPT);
      

      Tablespace created.

   6. Verify the Encryption:

      SQL> select * from v$encryption_wallet
      

      WRL_TYPE	WRL_PARAMETER	STATUS	WALLET_TYPE	WALLET OR	FULLY BAC	CON ID
      FILE	/u03/wallet_cdb	OPEN	PASSWORD	SINGLE	NO	0

      
      

3. For more information on Configuring Transparent Data Encryption (TDE), see:

   http://docs.oracle.com/database/121/ASOAG/asotrans_config.htm#ASOAG9529
   

4. Other information may be useful during maintenance activity.

   1. Close the Encryption Wallet as follows:

      SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE Close IDENTIFIED BY "val1ue#" Container=ALL;
      

Additional Information

For more information on the subjects covered in this section, as well as information on other options that are available to strengthen database security, see the Oracle Database Security Guide 12c Release 1.

The Oracle Advanced Security Option provides industry-standards-based solutions to solve enterprise computing security problems, including data encryption and strong authentication. Some of the capabilities discussed in this guide require licensing the Advanced Security Option.

For more information, see the Oracle Database Advanced Security Administrator's Guide 12c Release 1.