The passwords for mysql are a cpanel thing.
Basically, you CAN use the main account and it's password for all your databases, but it is not recommended.
If you go into cpanel and select the mysql section, you can create user accounts for your databases in a nice graphical environment. You can then assign these users to any of your databases.
And yes, once you have done that, you will have to edit the applications that use the DB to have the username/password that you created.
And yes, if you have used the main account and password in a cleartext config file, it would be good to create a DB user for it, change the app, then change the main account password to something else.
It is not enforced that way because quite frankly most of the users want complete control over their stuff, including being able to do things that I would otherwise not allow. If it isn't a blatant bug, or a security issue, it is generally allowed.
Principal Systems Administrator