Understanding MySQL password

Question

Probably I cant get more dumb, but seriously this is the first time I had to think about it(for the first time the single user app goes multi-user from multiple locations). For what purpose is a MySQL (or any other database for that matter) password? The password we see in connection strings, as if like this:

"SERVER=localhost;DATABASE=tree;UID=root;PASSWORD=branch;Min Pool Size = 0;Max Pool Size=200"

Is branch a code word for user root to access database tree? Or is it for root to access any MySQL database on a specific machine? Or is it a password for any user to access database tree? Or password for any user to access any MySQL database on that machine?

So my questions are basically?

1. Is the password specific for the MySQL, or the database, or the user? Or for that particular user for that particular database on that particular MySQL installation? Or any combination of these?

2. Can we have a password for MySQL itself (if thats not the case with branch), can we have a password for the db itself (if thats not the case with branch) etc?

Sorry for my ignorance ;)

Edit: I can run this query to drop my database:

query = "DROP DATABASE tree";

with the connection string:

"SERVER=localhost;UID=root;PASSWORD=branch;Min Pool Size = 0;Max Pool Size=200"

Doesn't that mean password is not per database, but per user?

Answer 1

1) Is the password specific for the MySQL, or the database, or the user? Or for that particular user for that particular database on that particular MySQL installation? Or any combination of these?

Let me start from scratch. First time when you install MySQL you are asked to enter Username(say root) and Password(say root). This is for the whole server. After you connect to the server with the above username and password, you can create user accounts for different reasons like- User 'abc' with password 'xxx' should connect to only one database, say Test. And, another user 'xyz' with password 'aaa' can connect to the server and access all the databases but with only SELECT privilege.

 CREATE USER 'abc'@'%' IDENTIFIED BY 'xxx'; 
 FLUSH PRIVILEGES; 
 GRANT ALL ON `test`.* TO 'abc'@'%' WITH GRANT OPTION;

Here user 'abc' with password 'xxx' can connect to the server but with minimal privileges- By only accessing Test database.

 CREATE USER 'xyz'@'%' IDENTIFIED BY 'aaa'; 
 FLUSH PRIVILEGES; 
 GRANT SELECT ON *.* TO 'xyz'@'%' ;

This user- 'xyz' with password 'aaa' connects to server with only SELECT privilege.

So, three users with different passwords can actually connect to the server with 'root' user with password 'root' has all the privileges whereas 'abc' and 'xyz' has minimal privileges.

I think I have answered your 2nd question also :-)