35.11. Managing MySQL Users
Your MySQL database server requires all clients to authenticate themselves with a username and password before they can execute SQL commands. It has its own tables of users, passwords, and permissions that are consulted when a client tries to log in, rather than the UNIX user files /etc/passwd and /etc/shadow. Detailed permissions can be defined for each user to limit the kinds of SQL statements that he can use, the client hosts he can connect from, and the databases, tables, and fields that he can modify.
Typically after MySQL has been first installed, only the root user be able to log in. This user will have permissions to access all databases and tables and perform all actions, and so is generally used for administration purposes only. If you want to write an application that uses a database, it is a good idea to create another user for that purpose and set up the application to log in as that user.
The standard MySQL install also creates an Anonymous user with no password and access to databases starting with test. This special user is used for any login attempt for which no other matching user is found. Anonymous users are explained in more detail later in this chapter.
To add a user, follow these steps:
1. | On the module's main page, click on the User Permissions icon. This will take you to a page listing existing users, as shown in Figure 35.4. |
2. | Click on the Create a new user link above or below the table to go to the user creation form. |
3. | In the Username field, select the second radio button and enter a name for this user. Even though it is possible to create multiple user entries with the same name (as explained later), this new one should be unique. |
4. | Assuming you want the user to have a password, change the Password field to Set to and enter it in the adjacent field. If you choose None, no password needs to be given and attempts to log in with a password will be rejected. |
5. | To allow this user to log in only from a specific host, select the second radio button in the Hosts field and enter a hostname into the text box. The hostname must be the same as the one returned by a reverse lookup of the client's IP address, which will almost always be a complete hostname like pc.example.com instead of just server. You can enter an IP address instead, or a hostname or IP address SQL pattern like %.example.com. To allow a user to connect only from the same system as the database server is running on, enter localhost as the host. If Any is selected, this user will be able to connect from any host. Be careful when creating a user who has a host specified. If he tries to connect from somewhere else and an Anonymous user exists with a matching host, he will be logged in as the Anonymous user instead! |
6. | Select the entries for the actions that you want the user to be able to perform in the Permissions list. For an application user, being able to select, insert, update, and delete records is usually enough. Untrusted users should never be given permissions beyond Drop tables, as that would allow him to harm the database, access arbitrary files, or enhance his own permissions. If a user does not have any permissions at all, he will be unable to connect unless some have been granted for a specific database or host (as explained in Section 35.12 “Managing Database, Host, Table, and Field Permissions”). |
7. | To create the user, click the Save button at the bottom of the page. The new MySQL login will be usable immediately and will have access to all databases and tables with the permissions specified in Step 6. See Section 35.12 “Managing Database, Host, Table, and Field Permissions” for information on how to restrict a user to only certain databases or tables. |
When a client tries to log in, MySQL searches for the first matching user and host in the list of users. The server always checks entries with specific hostnames before those that allow any host and Anonymous user entries before those for a specific user. This means that a user may end up with the Anonymous permissions even though he is in the user list with greater privileges. Due to the confusion this can cause, I recommend deleting all Anonymous user entries unless you fully understand their effects.
It is possible and even useful to have multiple entries for the same user in the list, as long as they have different hostnames. For example, if you want to allow the user fred to log in from only server.example.com and www.foo.com clients, you would need to create two entries from fred with the Host field set differently. They should have the same password and permissions, however, unless you want to require a different password or grant different permissions depending on the host from which the user is connecting.
New and existing users can be edited by clicking on their names in the list, which brings up an editing form almost identical to the one used for creating a user. The only difference is that the Password field has a Don't change option that is selected if the user has a password and that tells Webmin to leave the password unchanged when the user is saved. After making changes, click the Save button at the bottom of the form to update the user in the database. Or, to delete it, hit the Delete button. If there are multiple entries for the same user, you will have to update them all individually when changing the password or permissions.
Unless you have already created another administration user with full privileges, the root user should not be deleted. Because this Webmin module normally logs in as root itself, modifying or removing this user may force you to log in to MySQL again, as explained in the introduction to the module earlier in the chapter. By deleting the root user or removing its privileges, it is possible to deny yourself access to the database, which can only be fixed using command-line programs like mysqladmin.
Like many other modules, the MySQL Database Server module can be configured to automatically create, update, or delete a MySQL user when the same thing happens to a corresponding UNIX user. This can be useful if you allow some of the UNIX users on your system to access databases and want to keep their passwords and usernames synchronized.
To set up synchronization, follow these steps:
1. | On the module's main page, click on the User Permissions icon. Scroll down to the form below the list of existing MySQL users. |
2. | If you want a new MySQL user to be created for each new UNIX user, check the Add a new MySQL user when a UNIX user is added box. Then, select the permissions that should be granted to the user from the list to the right. When a MySQL user is automatically added, it will be allowed to log in from any host. |
3. | If you want MySQL users to be renamed or have their passwords changed when the same thing happens to matching UNIX users, check the Update a MySQL user when the matching UNIX user is modified box. If more than one entry exists for the same user, they will all be affected. |
4. | To have a MySQL user deleted at the same time as the UNIX user of the same name, check the Delete a MySQL user when the matching UNIX user is deleted box. If more than one entry exists for the same user, they will all be deleted. |
5. | Click the Save button to make the new synchronization settings active. |