Improving MySQL security via privileges on Shared UW Hosting
IT Connect > Web > Publishing > Using MySQL > MySQL Security via Privileges
- On This Page
- Hosts
- Users
- Privileges
Rename this and phrase it in a different light.
The way you set up privileges in your MySQL database depends on what applications you will be connecting to the database with, where these applications run, who will be administering the database, and how they choose to do this. In light of this diversity, we cannot give exact instructions for how to set up your MySQL privileges. We'll give some general advice regarding the tools available and ways they can be used to accomplish common goals.
Two sections of the MySQL Reference Manual are particularly pertinent to these instructions. The MySQL Privilege System and MySQL User Account Management are good resources for understanding these recommendations, and MySQL in general. Please refer to this for answers before calling or emailing us.
Hosts
Configure your users to only accept connections from hosts that you know they should be connecting from.
The MySQL setup instructions allow the 'root' user to connect from localhost (ovid or vergil) and any machine with a hostname ending in ".u.washington.edu" This is done using an unlimited wildcard character (%) which stands in for any other characters that might appear in the hostname of a machine attempting to make a connection to your MySQL server. The host value looks like this:
%.u.washington.edu
Most likely your users will only be connecting from a small number of hosts, so you probably don't need to allow all machines named something ending in ".u.washington.edu" to connect.
A common case is a user that only connects from the Web servers. This would happen if you had a Web application as the only entity connecting with a user account. Here are the simplest host strings which would match each of our Web server clusters:
| hostname |
students.washington.edu |
depts.washington.edu staff.washington.edu |
courses.washington.edu faculty.washington.edu |
|---|---|---|---|
| host string |
students0_.u.washington.edu |
depts1_.u.washington.edu |
courses0_.u.washington.edu |
The underscore character is a single-character wildcard in this context, so these host strings should match hostnames with any other single character in place of the underscore.
Using 'localhost' in the host field of a user in MySQL allows that user to connect from the host that MySQL is running on, using a socket, rather than a numbered port. It can improve security to only allow a user with granting privileges, or other wide-ranging privileges to connect only from localhost.
Users
Only give your users privileges to operate on the database(s) that they need to operate on.
You could do this by creating a user for each application that will connect to your database or each database you are hosting. You might even create multiple users for a single application. One user may only have select privileges and another may only have update or delete privileges. It's up to you to decide how much separation of privileges is appropriate for your applications.
When you create a user for a specific application to connect to a specific database, do not give that user any privileges on the MySQL instance as a whole, or any granting privileges.
Below is an example of how to give a user 'SELECT' privileges on a specific database table:
GRANT SELECT ON database.table TO 'username'@'hostname';
If you wanted to give this user all privileges on any table in a specific database:
GRANT SELECT ON database.* TO 'username'@'hostname';
The first of these examples, privileges on a specific table, is likely too fine-grained for more applications, but demonstrates how specific permissions can be. The second is probably a good middle ground in permission complexity and safety.
Privileges
Each user can be given any of a long list of Privileges Provided by MySQL.
The only user that should need all of these privileges is the administrative or 'root' user. This user does not have to be named 'root,' but will be named that by default. You should not use this user to connect to MySQL databases from Web applications unless perhaps a well-secured phpMyAdmin application or similar.
Each of your other users should be given privileges necessary to the queries that user will need to run, and no more. If you're writing your own application, this should be easy to determine, as you will likely be writing the database queries as well. If you're using an application you did not write this can be more difficult. Hopefully the application has documentation that explains what privileges it needs on the database.
