Lease Revoke does not happen

[andrefcpimentel2]

I'm using Vault 1.6.0 with Oracle SE2 (AWS RDS), and I successfully set up the plugin successfully.
My role:
vault write database/roles/my-role db_name=oracle creation_statements="CREATE USER {{name}} IDENTIFIED BY {{password}};GRANT SELECT ON session_privs TO {{name}};" default_ttl="1h" max_ttl="24h"
But when Revoking the lease either through the UI or using the CLI (vault lease revoke), nothing happens. Also after the TTL, nothing happens. The user is still listed in sqlplus.

[pcman312]

Hi @andrefcpimentel2! Thanks for bringing up this issue.

Unfortunately this plugin doesn't work well with AWS RDS. Before dropping a user, Oracle requires that there are no open connections from that user. In order to enforce this, it has a system table that we can query to list all open connections. We then use that list to forcibly disconnect that user from the database before dropping the user. RDS has unfortunately made it so that we cannot query that table and instead has a stored procedure for the operation. We don't have a workaround for this at the moment :(

[pcman312]

Apologies, I meant to comment without closing. I would like to keep the issue open for visibility into RDS.

[nouseforaname]

Finding this ticket just solved out Issue.

It would be nice if the Error would indicate, that additionally to the revocation statements the, code tries to access v$sessions. Our DB Team created stored procedures for Vault to Call (taking care of proper user de(provisioning) on the DB) and we had issues running the revocation statement via Vault. Directly connecting using sqlplus and running the statement did not produce any errors.

A note in the docs for this behaviour would be greatly appreaciated. Thanks!

[kpcraig]

While i don't think it will 'solve' the issue, it seems like the functionality from #137 and the doc update from hashicorp/vault#23517 (when it lands) should make the fix clearer?