I'm assuming you mean a password which you give when you create a wallet in your local machine. The password has actually very little to do with Ethereum.
When you create a wallet (an account) a private key is generated. That private key is the most important secret you have - anyone with that private key has full access to your wallet and its assets. So the software you use stores the private key in your hard drive and encrypts it with a password. Whenever you want to use the private key for transactions you first have to provide the password to decrypt the private key.
If the password is leaked it's not a big issue as long as nobody gets access to your hard drive.