"But... there's a root account... creating another account... which is typically a privileged action. What's protecting that? Is that root account being rotated, too?"
I'm talking about the account creating the database user. Let's take MSSQL, for example. The equivalent to a root account there is `sa`. So, Vault will have control of the `sa` account in order to create leased database users.
If I'm a malicious actor inside the environment, what's stopping me from compromising the `sa` account and mimicking dynamic secrets? I'd need to be comparing Vault logs with Database logs constantly to ensure it was legit.
In some cases, vault can change the password of the root account on the service, so that only vault is known to have the password. But you are correct, there is a problem here. There is nothing stopping you from attacking the DB or service directly.
You can do a lot to minimize that, many DB auth systems let you specify where connections for users are allowed to come from, only allow a single concurrent connection, etc. Plus you can encrypt and verify that connection with TLS, build up FW rules, etc.
Security is never "perfect" but to say dynamic secrets are wrong and crazy just because of this one problem isn't very well thought out. Your wordpress application will be hacked a LOT easier than your MSSQL sa account.
So dynamic secret people are saying, we all know our apps leak/get compromised all the time, minimize their leakage with a short lived, one-time use secret, that can only be used from X machine, etc, instead of a long-term, static secret that can be used anywhere, etc.
For sure, for high-value targets you best be auditing everything you care about, not just vault, but with a vault audit log, you can automate a lot of that comparison between MSSQL and vault, where before it was much harder to automate since the logs from every application using MSSQL will be different, etc. But if you are in a high-value org like this, you are almost certainly already auditing your MSSQL account creations, this doesn't change that.
I think the solution to this problem is to not run your apps with the root account in the database. Then the app db passwords can be automatically rotated by vault, and db admins can request temporary root access from vault if necessary (and those credentials should be short-lived).
That's protected by Shamir's Secret Sharing the root encryption key, which adds a fairly robust layer of security. It means that (unless you store the key parts in an HSM/Cloud KMS) there's a trade off of having a manual step to get the unencrypted key in to vault's memory.
You can also set up vault to encrypt each shard of the root key with the GPG pubkey for each administrator before dumping them to output. This way, plaintext shards of the root key never even touch the disk or otherwise come into view of anybody other than the intended recipient.