I might not store an access token, I might use user auth and get a temporary token for a script that then calls to a secret manager. The access token is cached locally in plain text. Is tbis more secure since it's a token with expiry?
There's no single proper way. You can't always go with "maximum" protection for everything, because then you'll be doing multiparty ceremonies all the time and it would be mighty impractical https://www.cloudflare.com/dns/dnssec/root-signing-ceremony/
Windows implements DPAPI, which is used to encrypt stored secrets on disk keyed with the users password sha1’d (and previous sha1 keys encrypted with the newest password). That way a file system compromise isnt a password compromise, at least directly.
More hardcore is hardware based modules, TPM and yubikeys are in this space, they use pub/private keys so the secret is no longer pre shared. Other hardware security modules also exist which are more full fledged. The former ultimately guarantee authentication but not session security, the latter can potentially do both afaik.
The main security need this is addressing is terminating access to ex-employees/contractors given a requirement for shared credentials. If the shared credentials are not short term, you have to rotate credentials every time the team changes. If you don't have a system to do it automatically it's pretty easy to forget to do it or do it incompletely. So better just to do it very frequently and automatically so you don't even need to bother with a revocation process.
You might be thinking that you don't have any shared credentials, but effectively any credential that a server has access to is shared, because there's almost certainly at least two people with access to any given server.
Anyone who compromises the short term credential only has a short window to abuse it, and then they have to get another one.
Yes, maybe its easy for them to get it again, but maybe it's not. Security is about defence in depth and risk management, not perfection.