[vault-plugin-database-oracle] Looking for recommendations to manage automated database migrations

[Shyam Sankaran]

Hi,

In my current project we manage all database migrations through Liquibase and we are in the process of using vault to manage all the application secrets which includes Oracle database passwords. Below is the scenario:

There is a java spring boot liquibase application which applies database changeset if the application is given the following configuration, for example:

----

application.yml

databaseUrl: jdbc:oracle:thin:@<oracle-url>:3521/<sid>

databaseUsername: DDL_USER

databasePassword: DDL_PASSWORD

------

run command: java -jar db-migrations.jar

------

After changesets are applied all the changes are made onto the DDL_USER schema. 

Now, we would like to integrate the vault database oracle backend and spring-cloud-vault-config-database for this application but because usernames are created randomly we don't exactly know how to apply the changesets to DDL_USER using the generated username. Please advice.

[bkas...@hashicorp.com]

Hi Shyam,

I'm not to familiar with the technology you mentioned, but would something like https://github.com/hashicorp/consul-template#consul-template help? Consul template can connect to vault and inject database credentials from vault into a template file. It's useful for connecting vault to services that can't know how to talk to vault (or ones you can't change the code for). 

Let me know if this helps, or if not could you share more information about the requirements?

Best,

Brian

[Fábio Maia]

Hi Brian,

Thanks for your reply.

Let me speak on behalf of Shyam as I'm pairing with him on this.

Shyam's question is not regarding to the integration between vault and the Liquibase application as this part is already done by using spring-cloud-vault-config-database. It's working as expected very similar to what you've described about consult-template.

The issue is related on how database migrations can be applied to a given database schema with a randomly database user created by vault-plugin-database-oracle. 

Given the scenario below and let's take out of the picture the liquibase application as it does not make a difference for this scenario:

(1) Enabled database backend:

vault mount database

(2) Register the plugin

vault write sys/plugins/catalog/vault-plugin-database-oracle \

    sha_256=<plugin-sha256> \

    command=vault-plugin-database-oracle

(3) Configure a connection

vault write database/config/oracle \

    plugin_name=vault-plugin-database-oracle \

    connection_url="system/Oracle@localhost:1521/OraDoc.localhost" \

    allowed_roles="*"

(2) Add role

vault write database/roles/oracle-role \

    db_name=oracle \

    creation_statements="CREATE USER {{name}} IDENTIFIED BY {{password}}; GRANT CONNECT TO {{name}}; GRANT CREATE SESSION TO {{name}};" \

    default_ttl="1h" \

    max_ttl="24h"

(3) Query credentials

vault read database/creds/oracle-role

Key             Value
---             -----
lease_id        database/creds/oracle-role/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6
lease_duration  1h0m0s
lease_renewable true
password        8cab931c-d62e-a73d-60d3-5ee85139cd66
username        v-root-e2978cd0

Now, let's say there is already a database schema created MY_SCHEMA and we want to create a table in that schema:

CREATE TABLE PERSON (ID NUMBER(5) PRIMARY KEY, NAME VARCHAR2(15) NOT NULL)

We want to execute that SQL with the v-root-e2978cd0 user that was created by vault. However, that user does not have permissions to create tables under the MY_SCHEMA schema. So we're looking for any recommendation about addressing this scenario properly with the vault-plugin-database-oracle. 

[Brian Kassouf]

Hi Fábio,

Thanks for the further explanation, is this something you can specify in SQL? If so you can extend the "creation_statement" to add additional permissions. So, for instance, you can add additional GRANT statements and template it with {{name}}. 

--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.
 
GitHub Issues: https://github.com/hashicorp/vault/issues
IRC: #vault-tool on Freenode
---
You received this message because you are subscribed to the Google Groups "Vault" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vault-tool+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/b5f0bd3e-1505-4d9e-afe5-c71d580233e8%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Sanjay Tiwari]

Hi, I am not able to execute the second step. getting the following error.

[root@ip-10-159-8-7 ~]# vault write sys/plugins/catalog/vault-plugin-database-oracle sha_256=69a3db19c83d5e7c5887d93955949e05b040788d0512f07a40df4dd5614f3f98 command=vault-plugin-database-oracle

Error writing data to sys/plugins/catalog/vault-plugin-database-oracle: Error making API request.

URL: PUT http://127.0.0.1:8200/v1/sys/plugins/catalog/vault-plugin-database-oracle

Code: 500. Errors:

* 1 error occurred:

* could not set plugin, plugin directory is not configured

[Matt]

Has anyone found a good solution for this?  I thought I had this working out with roles - but it's not exactly right.   The problem I've found is that, even in Oracle 18, you can't grant schema access to a user (you can grant access to objects in the schema - but that doesn't help when there's a yet-to-be-populated schema).