Configuring the Db2 Big SQL Ranger plugin for SSL using public CA certificates

Use the following steps to configure the Db2® Big SQL Ranger plugin for SSL using public CA certificates.

Before you begin

Before proceeding, check that the following prerequisites are met:
  1. You have enabled the Big SQL Ranger plugin. For instructions, see Enabling and disabling Ranger security support for Db2 Big SQL.
  2. You have configured Ranger Admin for SSL. For instructions see Security.
  3. Root and intermediate certificates are installed on your hosts, or you have added root and intermediate certificates to the default Java™ keystore "cacerts" on the hosts for Ranger Admin and the Db2 Big SQL head node. For example:
    
keytool -import -file carootcert.der -alias carootcert 
  -keystore /usr/jdk64/jdk1.8.0_112/jre/lib/security/cacerts -storepass changeit

keytool -import -file caintermediatecert.der -alias caintermediatecert 
  -keystore /usr/jdk64/jdk1.8.0_112/jre/lib/security/cacerts -storepass changeit
  4. You have keystore and truststore files for Ranger Admin and other Ranger plugins. For example, you might have the following files:
    • server-keystore.jks - the keystore file that is used by Ranger Admin
    • agent-keystore.jks - the keystore file that is used to store agent (plugin) keys and the certificate
    • agent-truststore.jks - the plugin truststore that is used to store server certificates
  5. Optional: Although not necessary for enabling SSL for the Db2 Big SQL Ranger plugin, in order to have the Ranger user sync functions working properly in an SSL-enabled setup for Ranger, ensure that you have configured Ranger Usersync for SSL. For instructions, see Configuring Ranger Usersync in the Hortonworks documentation.
    Note: The documents linked above are for HDP 2.6.5. Refer to the version of HDP appropriate to your environment.

Procedure

In the following procedure, complete commands are provided for the setup. You can change some values such as file names and passwords.

  1. In Ambari, select IBM Big SQL > Service Actions > Stop to stop the Big SQL service.
  2. As the bigsql user, create credential files for the agent-keystore.jks and agent-truststore.jks passwords.
    1. su -bigsql
    2. Change directory to the desired path for the JCEKS files. For example:
      cd /home/bigsql/ranger
      Note that this path is used as the location for the JCEKS files in the remaining steps and must be updated in the example commands if using a different location.
    3. Set the IBM® JDK at the front of the PATH. This is required only for this step.
      export PATH=~bigsql/sqllib/java/jdk64/bin:$PATH
    4. java -cp "/usr/hdp/2.6.5.0-292/ranger-hive-plugin/install/lib/*" org.apache.ranger.credentialapi.buildks create sslKeyStore -value <'keyStorePassword'> -provider jceks://file/home/bigsql/ranger/bigsql_cred.jceks

      The keystore password must be enclosed by single quotation marks.

    5. java -cp "/usr/hdp/2.6.5.0-292/ranger-hive-plugin/install/lib/*" org.apache.ranger.credentialapi.buildks create sslTrustStore -value <'trustStorePassword'> -provider jceks://file/home/bigsql/ranger/bigsql_cred_trust.jceks

      The truststore password must be enclosed by single quotation marks.

    If you do not have an agent truststore and are using the keystore as a truststore, create both JCEKS files using the keystore password.
  3. Go to $BIGSQL_HOME/conf/ranger-policymgr-ssl.xml, set the following properties, and save the file:
    • xasecure.policymgr.clientssl.keystore = the path of agent-keystore.jks. The path is wherever you put the agent-keystore.jks file during the Before you begin steps.
    • xasecure.policymgr.clientssl.keystore.credential.file = the path of bigsql_cred.jceks (jceks://file/home/bigsql/ranger/bigsql_cred.jceks)
    • xasecure.policymgr.clientssl.truststore = the path of agent-truststore.jks. If you are using agent-keystore.jks as a truststore, use the same path provided for xasecure.policymgr.clientssl.keystore.
    • xasecure.policymgr.clientssl.truststore.credential.file = the path for bigsql_cred_trust.jceks (jceks://file/home/bigsql/ranger/bigsql_cred_trust.jceks)
    • xasecure.policymgr.clientssl.keystore.password = <keyStorePassword> (This should match the password specified in Step 3.d above.)
    • xasecure.policymgr.clientssl.truststore.password = <trustStorePassword> (This should match the password provided in Step 3.e above.)
    Note: If you re-enable the plugin, the content of ranger-policymgr-ssl.xml will be reset. Make sure that you have the correct values here.
  4. Go to $BIGSQL_HOME/conf/ranger-bigsql-security.xml, set the following property, and save the file:
    • ranger.plugin.bigsql.policy.rest.url = https://<ranger-admin-host>:<ranger-service-port>
  5. If Db2 Big SQL High Availability is enabled, repeat Steps 2 - 4 for each Db2 Big SQL head node.
  6. In Ambari, restart the Db2 Big SQL service.
  7. On the machine where Ranger Admin is located, add the agent CA-signed certificate to Ranger Admin trusted CAcerts (the Ranger Admin truststore).
    The following commands provide an example only. It is assumed that the signed certificate for the agent keystore is located in /etc/ranger/admin/conf/ranger-agent-ca-cert on the Ranger Admin host, and that the Ranger Admin truststore is located in /etc/ranger/admin/conf/ranger-admin-truststore.jks.
    • cd /etc/ranger/admin/conf
    • keytool -import -file ranger-agent-ca-cert -alias <ranger-agent-alias> -keystore /etc/ranger/admin/conf/ranger-admin-truststore.jks -storepass <ranger-admin-truststore password>
  8. Log in to the Ranger UI as admin (as keyadmin if you are configuring for Ranger KMS) and click the Edit button for the Db2 Big SQL repository.
  9. Provide the CN of the agent-keystore.jks as the value for Common Name For Certificate, and save your changes.
    Note: Ranger KMS does not have this configuration available by default so you must add it manually. In the Add New Configuration dialog, add the field commonNameForCertificate and provide your <commonName> value there.
  10. In Ambari, restart the Ranger service.
  11. Make or edit a policy and save it, and then navigate to Audit > Plugins and ensure that there is a recent entry for your Db2 Big SQL repository with HTTP Response Code 200.

    If there is no entry for your Db2 Big SQL repository, connect to the bigsql database and perform a simple operation which will trigger an authentication check. For example, create a simple table or attempt to query a user table which already exists in the Db2 Big SQL database. The authentication request should force Ranger to sync Db2 Big SQL policies.

    Note: It can take up to 30 seconds for the policy to sync. If it does not show up immediately, wait, and then click the refresh button on the page.

Example

This complete example shows how to set up SSL for Ranger Admin and the Db2 Big SQL plugin, including how to create keystores and truststores for a CA certificate. The example assumes that the Db2 Big SQL head is installed on the same node as Ranger Admin.

Prerequisite: Add root and intermediate certificates to the default Java keystore "cacerts"

Run the following commands on the hosts for Ranger Admin and the Big SQL head:

keytool -import -file carootcert.der -alias carootcert 
  -keystore /usr/jdk64/jdk1.8.0_112/jre/lib/security/cacerts -storepass changeit

keytool -import -file caintermediatecert.der -alias caintermediatecert 
  -keystore /usr/jdk64/jdk1.8.0_112/jre/lib/security/cacerts -storepass changeit
Step 1: Set up the Ranger Admin keystore
  1. Download the PKCS7b (.pem) certificate file.
  2. Secure copy (scp) the certificate file to the server.
  3. Run the following commands:
    
cp cert.pem cert.p7b

openssl pkcs7 -print_certs -in cert.p7b -out cert.cer

openssl pkcs12 -export -in cert.cer -inkey bigcentos1_fyre_ibm_com.key 
  -out rangeradmin.p12 -name rangeradmin
  4. Create a keystore for the Ranger Admin service from the certificate:

    
keytool -importkeystore -deststorepass temp4now -destkeypass temp4now 
  -srckeystore rangeradmin.p12 -srcstoretype PKCS12 
  -destkeystore ranger-admin-keystore.jks -deststoretype JKS -alias rangeradmin

    You should now have the generated ranger-admin-keystore.jks keystore file.

  5. Run the following commands:
    
cp ranger-admin-keystore.jks /etc/ranger/admin/conf/ 

cd /etc/ranger/admin/conf/
  6. Set permissions:

    
chown ranger:ranger ranger-admin-keystore.jks
 

chmod 400 ranger-admin-keystore.jks
Step 2: Set up the Db2 Big SQL plugin keystore
  1. Run the following command on the Db2 Big SQL head node host:

    
mkdir /etc/bigsql/conf
  2. Generate another keystore for the Db2 Big SQL plugin:
    
openssl pkcs12 -export -in cert.cer -inkey bigcentos1_fyre_ibm_com.key 
  -out bigsqlplugin.p12 -name bigsqlplugin
 

keytool -importkeystore 
  -deststorepass temp4now -destkeypass temp4now 
  -srckeystore bigsqlplugin.p12 -srcstoretype PKCS12 
  -destkeystore bigsql-plugin-keystore.jks -deststoretype JKS -alias bigsqlplugin


    You should now have the generated bigsql-plugin-keystore.jks keystore file.

  3. Run the following command:
    
cp bigsql-plugin-keystore.jks /etc/bigsql/conf/
  4. Set permissions:

    
cd /etc/bigsql/conf/
 

chown bigsql:hadoop bigsql-plugin-keystore.jks
 

chmod 400 bigsql-plugin-keystore.jks
Step 3: Set up the Db2 Big SQL plugin truststore
  1. Export the rangeradmin.cer file:

    
keytool -export -keystore ranger-admin-keystore.jks -alias rangeradmin 
  -file rangeradmin.cer -storepass temp4now
  2. Secure copy (scp) the rangeradmin.cer file from the Db2 Big SQL master node.
  3. Create the truststore for Db2 Big SQL:

    
keytool -import -file rangeradmin.cer -alias rangeradmin 
  -keystore /etc/bigsql/conf/bigsql-plugin-truststore.jks -storepass temp4now
Step 4: Set up the Ranger Admin truststore
  1. Export the bigsqlplugin.cer file:

    
keytool -export -keystore bigsql-plugin-keystore.jks -alias bigsqlplugin 
  -file bigsqlplugin.cer -storepass temp4now
  2. Secure copy (scp) the bigsqlplugin.cer file to the Ranger Admin host.
  3. Create the truststore for Ranger Admin:

    
keytool -import -file bigsqlplugin.cer -alias bigsqlplugin 
  -keystore /etc/ranger/admin/conf/ranger-admin-truststore.jks -storepass temp4now

Step 5: Configure Ranger Admin for SSL

For instructions, see the Configuring Ranger Admin page in the security section of the HDP documentation.

Step 6: Configure Db2 Big SQL for SSL

See the Procedure section on this page. In this example,
  • server-keystore.jks is /etc/ranger/admin/conf/ranger-admin-keystore.jks
  • agent-keystore.jks /etc/bigsql/conf/bigsql-plugin-keystore.jks
  • agent-truststore.jks is /etc/bigsql/conf/bigsql-plugin-truststore.jks

What to do next

In case you encounter any problems during this procedure, to help resolve the problems see Configuring the Db2 Big SQL Ranger plugin for SSL: Troubleshooting Tips.