Unable to kill session before dropping user on RDS Oracle

Vault
1

Hi All,

I wonder if any of you is successfully creating and dropping credentials on AWS RDS for Oracle.
I’m unable to find the correct syntax or procedure to place in the revocation statement to kill active sessions of the user to drop, which is a prerequisite step for dropping a user in Oracle.
Following, a more detailed explanation.

The issue is related with disconnecting the user session before dropping the user. Oracle refuses to drop a user if it’s still connected.

Please note, in order to disconnect or kill a session on RDS it’s necessary to use a procedure provided by AWS, rdsadmin.rdsadmin_util.disconnect, since we do not have direct access to SYS.

The code I’m trying to run as revocation statements (just to test the disconnection) is the following one. Note also that I generate a JSON out of it, and base64 encode it

DECLARE
       CURSOR get_sessions
       IS
          SELECT s.sid sid, s.serial# serial, s.username username
              FROM v$session s, v$process p
              WHERE s.username = '"{{name}}"'
                AND p.addr(+) = s.paddr ;
    BEGIN
       FOR session_rec IN get_sessions
       LOOP
          BEGIN
              rdsadmin.rdsadmin_util.disconnect(
                  sid    => session_rec.sid,
                  serial => session_rec.serial);
          END;
       END LOOP;
    END;

If I place an existing user in the code above (instead of the “{{name}}”) and run it from an admin sqlplus session, I disconnect the user if it is connected. No issues

However, when Vault runs the above revocation statement when the TTL expires for a connected user, the database connection hangs, that user is still connected, I cannot even see anything on the Vault log. The only thing I can do is reset the connection, but the user is still there and its session is well alive.

Anyone can shed some light on this? I can’t believe I’m the first one to hit into this

Thanks in advance