I know I can keep master account details in Secrets Manager.
Is there a way to keep "standard" users' details in SM (and rotate them on a regular basis)?
Yes, the procedure is the same as for the master account. In the end, when a secret is created, you can edit the secret and change values for username and password.
For the rotation, you're using the Lambda function (code and deployment instructions can be found in AWS documentation), and it can work the same way with any DB user, not just a master user.