35.12. Managing Database, Host, Table, and Field Permissions
Users created by following the instructions in Section 35.11 “Managing MySQL Users” have access to all databases on the server with the same permissions. It is possible, however, to give a user access to only specific databases by following these steps:
1. | Make sure the user does not have any permissions on the user permissions page. Any that he has set here will apply to all databases, which is not what you want. |
2. | On the module's main page, click on the Database Permissions icon. This will bring up a list of users and the privileges they have for specific databases. |
3. | |
4. | In the form that appears, the Databases field controls which databases he will have access to. You can either select the Any radio button to grant permissions for all databases, select the second radio button to grant access to the database selected from the menu, or choose the final button to grant access to databases whose names match the SQL pattern entered into the adjacent field. Typically, the second option is the one that you want to select so you can grant access to a single database. If the user should have access to more than one, you will need to add multiple database permissions entries. |
5. | |
6. | The Hosts field allows you to choose from which client host(s) the user will be able to connect to the database. You should normally select Any, which gives him access from anywhere unless the user himself is prevented from connecting from some hosts, as explained in Section 35.11 “Managing MySQL Users”. |
7. | From the Permissions list, select the privileges that the user should have for the chosen database. These will be added to any that are set for the user on the user permissions page. |
8. | Click the Save button to add and activate the new permissions. You will be returned to the database permissions list. |
You can edit database permissions by clicking on a database name from the list. This will take you to an editing form identical to the creation form in which the database, username, hosts, or permissions can be changed. The Save button saves and activates any changes, while the Delete button removes the permissions from the database.
When MySQL is first installed, database permissions for the Anonymous user in the test and test_% databases will be created automatically. Assuming the Anonymous user exists on the user permissions page, these give anyone who can connect to MySQL access to records in those databases. Unless you are making use of anonymous logins, these database permissions can be safely deleted.
MySQL also allows permissions to be granted on databases to all users connecting from certain client hosts. This can be useful if you want to increase the privileges that a particular client system has, such as a web server connecting to your database server.
To add host permissions, follow these steps:
1. | On the module's main page, click on the Host Permissions icon. This will take you to a page listing existing permissions granted to client hosts, if any. When MySQL is installed, no permissions of this type are initially defined. |
2. | |
3. | If the permissions should apply to all databases, select the Any radio button in the Databases field. If they are for only a specific database, select the second radio button and choose a database from the menu next to it. If you want to grant permissions to databases whose names match a SQL pattern, select the final radio button and enter the pattern into the adjacent text field. |
4. | In the Hosts field, select the second radio button and enter a hostname, IP address, or hostname or IP pattern (like %.example.com or 192.168.1.%) into the field next to it. Selecting the Any button isn't particularly useful. |
5. | From the Permissions menu, choose those privileges that will be granted to all users connecting to the chosen database from the specified host. These will be added to any other permissions that are granted on the user permissions or database permissions pages. |
6. | Click the Save button to activate the new client host permissions. |
As usual, you can edit existing an host permissions entry by clicking on the database name from the list, editing fields, and clicking Save. You can also remove it with the Delete button.
MySQL also supports the granting of permissions to specific tables and fields to users connecting from certain hosts. Webmin allows you to set these up by clicking on the Table Permissions and Field Permissions icons on the main page. Because they are quite complex and rarely used, however, they are not covered in this chapter.