This is precisely the difference. By having a passphrase which is selected by the user, having access to the api key and secret key (database dump or otherwise), will not allow the attacker to create phony API requests. The API still generates a strong secret key for signatures which is not user selected.
Doesn't this also mean that if we want, users can require typing in the passphrase whenever starting up our custom apps? That way the "keys to the kingdom" are not simply laying around on my computer's hard drive.
Correct. How you choose to handle passphrase storage and usage is completely up to you. If your programs operate in such a way that you can enter the passphrase only during startup then you will further prevent tampering or use of your trading programs without your authorization.