Learning MySQL

Privileges and Performance

MySQL’s user and privilege management gives you fine-grain control over who has access to which parts of the server and its databases, as well as what that access allows. However, this fine-grained control comes at a price: when you implement complex user and privilege settings, checking these for each SQL statement you execute adds a performance penalty.

When you choose your users and their privileges, you should strive to balance control and performance. Here are some basic tips:

• Keep it simple. If you follow the “default deny” philosophy, you’ll create only the users you need; avoid creating users whom you only think you might want in the future.

• Grant the privilege as high up the hierarchy as possible. For example, if you want to grant a privilege for all tables in a database, grant it for the database instead. Avoid using column and table privileges unless you really need them.

• Minimize your use of the host table.

Remember, the more comparisons required to determine permissions, the slower each query will run on your server. However, don’t compromise your security policy for the sake of performance; a server that has been rendered unusable by an attack has zero performance!