We’re using Vault short lived credentials with custom revocation statements configured hitting an Oracle database. We have the revocation setup, with it calling a procedure to do any session terminates along with dropping the database user. However, what we’re noticing is that default revocation statements are still executing which essentially is:
ALTER SYSTEM KILL SESSION ‘760,1308,@1’ IMMEDIATE
drop user vaulttest67iVrZal3LDLD4gwYU81
Our revocation statement looks like the following (de-identifying certain components for security reasons):
revocation_statements=[“declare\n v_user varchar2(128) := ‘{{username}}’;\n begin\n ..drop_user(p_username => v_user);\n end;”]
Everything with the package call works by hand including executing directly as the database account integrated into our Vault instance. There are no errors in the logs other than the errors pertaining to the default revocation statements attempting to fire from the vault db user directly.
Thanks,
chad
Could you please share what your Vault role configuration is where the custom revocation statement is not working?
It has been a while, but when I tested the database secrets engine with Oracle (on AWS RDS) with HCP Vault it was working for me.
So within the Revocation Statement box, here is what we have.
[“declare\n v_user varchar2(128) := ‘{{username}}’;\n begin\n xxxx_owner.xxxx_pkg.drop_user(p_username => v_user);\n end;”]
The procedure above will issue a session kill for each session it finds, as well does some proprietary cleanup steps and then issues the drop user.
Our TTL is 10 minutes with maxTTL is 15 minutes.
So what I’ve noticed from having database session tracing on is that the custom revocation isn’t always firing but what does get executed everytime is the default revocation of below, which we don’t want/need:
SELECT inst_id, sid, serial#, username FROM gv$session WHERE username = UPPER(‘yyyy’)
drop user yyyy
The only thing we want to have happen following the TTL OR as part of a subsequent execution following a failure of some sorts, is the execution of the procedure.
Given where you are at with troubleshooting this, I would suggest a support ticket if you have support, or raising a GitHub issue about this. Not sure I will be able to reproduce why the custom revocation statement is working only some of the time via public forums/limited insight into your environment.
So what we discovered with support is that you can set disconnect_sessions to false which will disable the default disconnect statements.
Some other info to share:
On versions greater than 1.8 retries after failures will occur 6 times with an exponential backoff. Once it tries and fails for a 6th time, it will mark the lease as irrevocable.
Irrevocable leases can be identified with the vault read sys/leases/count type=irrevocable command.
For any leases that were configured with the default disconnect statements still in place, and subsequent failures occurred, even with the custom revocation statements succeeding these will each need to go through the 6 iterations of failures. Hence this is why we still see revocation attempts occurring for users that have been dropped for days via the custom revocation commands.
vault write database/config/oracle disconnect_sessions=false
Thank you so much for sharing what you found - really appreciate that. I will take this and make a story for our docs backlog.