Secure Oracle Database Connection using Hashicorp Vault and Consul
This is an overview of how to secure your Oracle database connection using the Hashicorp Vault PKI engine with an Oracle database secured listener. This pattern can be used for Dev/Test environments where you would want to use your own certificate to save the cost of buying third party certs. Also, this will help you automate your certificate rotation without requesting a signed certificate from your organisation CA. This pattern is based on the following assumptions:
- AWS is the cloud provider
- Oracle database is installed on EC2
- IAM Role is attached to EC2 instance
- Hashicorp Vault is already setup
- Ansible installed on EC2 instance
- Terraform used to deploy Vault and Infrastructure
I am not writing all the detailed steps myself but putting all the reference links on one page here for you to follow and understand the procedure in the simplest way. So, here it goes.
On a high level, the procedure below consists of high-level steps:
- Enable Hashicorp Vault PKI Engine
- Create Vault Role to grant PKI engine access and map it to EC2 IAM Role
- Deploy consul template to fetch updated cert from PKI engine
- Create wallet using certs fetched from PKI engine
- Register secured listener using above created wallet
Enable Hashicorp Vault PKI Engine
Here is an example of how to setup Root CA and Intermediate CA certs for your Vault namespace:
Create Vault Role to grant PKI engine access and map it to EC2 IAM Role
Below is a snippet of how you can create a vault role to grant access to PKI and map this role to the IAM Role using terraform. (Make sure IAM role is added to EC2 instance profile for it to assume)
# Vault policy document
data "vault_policy_document" "role_vault_cert_policy_document" {
rule {
path = "pki*"
capabilities = ["create", "read", "update", "delete", "list", "sudo"]
}
}# Vault policy
resource "vault_policy" "role_vault_cert_policy" {
name = "${var.service}_cert_policy"
policy = data.vault_policy_document.role_vault_cert_policy_document.hcl
}# Vault AWS Auth backend role with attached above policy
resource "vault_aws_auth_backend_role" "role" {
auth_type = "iam"
bound_iam_principal_arns = [<EC2 IAM Role ARN>]
role = <EC2 IAM Role Name>
token_max_ttl = 2764800
token_policies = ["default", vault_policy.role_vault_cert_policy.name]
}
Deploy consul template to fetch updated cert from PKI engine
Thanks to Ned Shawa, here is a very good overview of how you can deploy consul template your VM to integrate it with Vault:
Once you have configured the consul template to generate certs in a location on your VM, you have achieved 70% of the job. Now, let's move on from DevOps to DBA bits.
Create wallet using certs fetched from PKI engine
You can use the below commands to create a wallet using PEM cert files fetched by the consul template from the Vault PKI engine.
# Convert PEM to PKCS12
openssl pkcs12 -export -in <Consul Template Target Location>/certificate.crt -inkey <Consul Template Target Location>/private_key.rsa -certfile <Consul Template Target Location>/ca_chain.crt -out ${ORACLE_HOME}/wallet/ewallet.p12 -password pass:<WALLET PASSWORD># Convert PKCS12 to JKS
${ORACLE_HOME}/bin/orapki wallet pkcs12_to_jks -wallet ${ORACLE_HOME}/wallet/ewallet.p12 -jksKeyStoreLoc ${ORACLE_HOME}/wallet/ewallet.jks -jksKeyStorepwd <JKS KEY PASSWORD> -pwd <WALLET PASSWORD># Create wallet
${ORACLE_HOME}/bin/orapki wallet create -wallet ${ORACLE_HOME}/ssl_wallet -auto_login -pwd <WALLET PASSWORD># Add JKS Cert to wallet
${ORACLE_HOME}/bin/orapki wallet jks_to_pkcs12 -wallet ${ORACLE_HOME}/ssl_wallet -pwd <WALLET PASSWORD> -keystore ${ORACLE_HOME}/wallet/ewallet.jks -jkspwd <JKS KEY PASSWORD># Show certificate details from wallet
${ORACLE_HOME}/bin/orapki wallet display -wallet "${ORACLE_HOME}/ssl_wallet" -pwd <WALLET PASSWORD>
Register secured listener using wallet
You can follow the below link to configure secured listener in SQLNet.ora and TNSNames.ora
ORACLE-BASE — Configuration of TCP/IP with SSL and TLS for Database Connections
Conclusion
The above procedure is very useful when there is a scenario where your organisation wants to use the Hashicorp Vault PKI engine to generate cert and want internal applications to connect using a secured listener. Consul template is very handy to automate the cert generating and fetching the certs from the Vault PKI engine. I have a client where we have used this procedure to automatically refresh the certs in the secured listener wallet scheduled on every alternate Sunday as a cron job on Dev/Test databases. Remember you can set the expiry of your certs in the consul configuration file, hence you can control the frequency of your cron job. This helped us maintain short-lived certs on Dev/Test databases and automate the refresh so we didn’t have to think about remembering certificate expiry for each environment ever again. Hope this helps you too if you have a similar use case as above.
About The Author
Sourish Banerjee is a Senior DevOps & Cloud Consultant at Version 1.
