Working with MySQL
This is a short explanation of how accounts/authentication work when working with a MySQL database. This will not cover some of the more granular/complex things you can do permission wise. I am going to assume that you all know what MySQL is and how to connect to the MySQL server (mysqld). Basic sql knowledge is also assumed. The first thing you need to understand when working with MySQL is what their concept of an ‘account’ is; it’s probably somewhat different than your normal concept of a user account. In MySQL an account is comprised of two pieces of information: a user name and a ‘connecting domain.’ MySQL stores administrative information (user information as well as other things) in the database named mysql. Within the mysql db, there is a table called user whose schema includes but is not limited to:
mysql> DESCRIBE user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) | NO | PRI | | |
| User | varchar(16) | NO | PRI | | |
| Password | varchar(16) | NO | | | |
...
There are many other fields in this table corresponding to various permission sets, but for now its really only necessary to understand that this table contains a field for the user name, a field for the host (the connecting domain) and a field for the password (actually the password hash). Consider:
mysql> SELECT User,Host,Password FROM user WHERE User="hanse6";
+--------+-----------+-------------------+
| User | Host | Password |
+--------+-----------+-------------------+
| hanse6 | localhost | **password hash** |
| hanse6 | % | **password hash** |
+--------+-----------+-------------------+
2 rows in set (0.01 sec)`
From the previous select statement you can see that for my user name there are 2 MySQL accounts. One for the localhost domain, and one for the ‘%’ domain. The ‘%’ character is the wildcard character in MySQL, and so this domain would actually represent “any domain other than localhost.” As a quick example you could use ‘%.example.com’ to represent foo.example.com, bar.example.com, and so on. Depending on where I connect to the database from, the hash of the password I supply will be checked against one of these two hashes. If I log into sw, or am running a webapp that is stored on sw, the password I supply will get checked against the localhost hash, similarly if I connect to the MySQL server from home (or any machine other than sw) my password will get checked against the entry in the record where Host=’%’.
One reason MySQL ties an account to a particular domain is that its conceivable you would want different permissions for the same “user name” depending on how the entity (actual person, webapp, db application, and so on) is connecting to the database. Consider a MySQL user name called “accounting.” You (as astute administrators) might want the accounting staff to be able to connect locally (on a terminal on the actual server) to have full access to the accounting database, but webapps connecting from a webserver to ONLY have select privileges on the tables in the database. Strictly speaking within MySQL these would be two separate accounts, each with their own entry in the mysql.user table.
Much of the confusion over why people are typically denied access to their database stems from not understanding what is currently in the mysql.user table. MySQL provides a set of directives to change the password hash for a user. The format of this command is:
SET PASSWORD [FOR user] = {Password('some password') | 'encrypted-hash'}
If [FOR user] is not present, MySQL will attempt to set the password for the currently logged in user. You can choose to either specify the plain text password, or the actual hash as returned by MySQL’s PASSWORD( ) function. Normally I advise to change the password hash for each of the MySQL accounts at the same time unless you have specific reasons to want the password to be different among the various accounts.
It is perfectly legal to do the following:
mysql> SET PASSWORD = PASSWORD('Passw0rdz');
This updates the hash of the currently logged in user, sometimes it’s not always obvious which account that is. For reference you may execute the following command to see who you are currently logged in as:
mysql> SELECT CURRENT_USER();
+------------------+
| CURRENT_USER() |
+------------------+
| hanse6@localhost |
+------------------+
1 row in set (0.00 sec)
the following two commands will change both the local host and the everything else password. The first logs in to mysql as username@localhost, while the second logs on as username@%
echo "set password = PASSWORD('Passw0rdz');" | mysql -u $USERNAME -p
echo "set password = PASSWORD('Passw0rdz');" | mysql -u $USERNAME -h $HOSTNAME -p