I am working on a solution where I am integrating Hashisocrp Vault with RDS Mysql to generate temporary credentials (username and password for 1 hour) and revoke and destroy the user after 1 hour. It is done through Dynamic Secrets in Hashicorp Vault. The solution is working as expected however there is an issue when the user remains connected with the same session, it still can perform the operation with the privileges given even after user expiry. I know the authentication only happens during first connection in Mysql that is causing the issue. Killing the session manually fixes the issue. But, I am not able to automate killing the session in Hashicorp Vault revocation statement.Is there anyway where I can kill the session from specific user in 1 command?
I tried below command, however it generates the statement it does not execute it. I need to run in a single command the execute command.
SELECT CONCAT('mysql.rds_kill(', id, ')') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE User = 'v-auth-d77kgpQzD'
Creation statements: CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON . TO '{{name}}'@'%' Revocation statements REVOKE SELECT ON . FROM '{{name}}'@'%';FLUSH PRIVILEGES; DROP USER '{{name}}'@'%';
