Notes on <High Performance MySQL> -- Ch12: Security

Chapter 12 Security

Terminology

-          Authentication

Who are you?

MySQL authenticates you with a username, a password, and the host from which you are connecting.

-          Authorization

What are you allowed to do?

In MySQL, authorization applies to global privileges, which aren’t associated with any specific schema objects (such as tables or databases).

-          Access Control

What data are you allowed seeing and/or manipulating?

When you try to read or modify data, MySQL checks to see that you’ve been granted permission to see or change the columns you are selecting.

Unlike global privileges, access controls apply to specific data, such as a particular database, table, or column.

-          Privileges and Permissions

These terms mean roughly the same thing – a privilege or permission is how MySQL represents an authorization or access right.

Account Basics

Username + Password + Location

Privileges

There are two kinds of privileges: those are associated with objects, and those that aren’t. --  Object-specific privileges and Global privileges

In general, the global privileges let you do things to the server, and the object-based privileges let you do things to the server’s contents.

MySQL’s privileges are also hierarchical, with a twist or two.

The Grant Tables

-          User

Each row contains a user account (the username, hostname, and encrypted password) and the user’s global privileges. MySQL 5.0 adds optional per-user limits, such as the number of connections the user is allowed to have.

-          Db

-          Host

The GRANT and REVOKE commands never modify the host table. You can only add and remove entries manually.

-          Tables_priv

-          Columns_priv

-          Procs_priv

How MySQL Checks Privileges

 

MySQL determines which privileges apply by issuing the equivalent of a SELECT statement against the cached grant tables.

 

Adding, Removing, and Viewing Grants

You can add a new user account or a privilege with GRANT, but REVOKE can remove only privileges, not accounts; you’ll need to use DROP USER to remove a user account.

You can use SHOW GRNATS to see a user’s grants.

 

If you do decide to manipulate the grant tables by hand rather than using the GRANT and REVOKE commands, you must tell MySQL that you’ve done so by issuing a FLUSH PRIVILEGES command, which refetches and reaches the accounts and privileges in the tables.

 

Setting Up MySQL Privileges

MySQL doesn’t support roles or groups, it supports only users.

Here’s an overview of the types of accounts you may need to create and the privileges you should set for them:

-          System administration account

Mysql-> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY ‘p4ssword’ WITH   GRANT OPTION;

-          Database administration accounts

-          Pre-employee accounts

-          Simulated groups

-          Logging, write-only access

-          Backups

-          Operations and monitoring

-           

 

Privilege Changes in MySQL 4.1

MySQL 4.1 introduced a new, much more secure password hashing scheme.

New-style passwords begin with an asterisk, so you can tell them apart readily with visual inspection.

Privilege Changes in MySQL 5.0

-          Stored routines

They can execute in two security contexts: as the definer (SQL SECURITY DEFINER) or the invoker (SQL SECURITY INVOKER).

Stored routines are commonly used as proxies to grant specific rights to tables where user don’t have any rights assigned directly. The usual idiom is to create a privileged user, then create the routines with that user as their definer, and give them the SQL SECURITY DEFINER characteristics.

-          Triggers

If the trigger isn’t created with the SQL SECURITY DEFINER characteristics the user who inserts into the table must have the SUPER privilege to execute the trigger.

 

-          Views

Like stored procedures and triggers, you can execute views with the privileges of either the definer or the invoker. Definer privileges let you give a user access to a view but not to the underlying tables.

This let you implemented row-level security, but also restrict access to columns. We believe this is a better solution than specifying column-level privileges with GRANT, as it is much easier to maintain. If you put the views in a separate database, you can simply grant database-level privileges to users, rather than having to maintain privileges on individual tables or views.

-          Privileges on the INFORMATION_SCHEMA tables

Privileges and Performance

-          Too many privileges

-          Privileges that are too fine-grained

-          Column privileges and the query cache

Queries that access a table with column privileges cannot be served from the query cache. We suggest using views instead of column privileges to avoid this and the other problems with column privileges.

 

By default, MySQL does both a forward and a reverse DNS lookup when authenticating users. Adding skip_name_resolve to your my.cnf will disable this. This can be good for both security and performance, because it speeds up connections, reduces reliance on DNS servers, and reduces susceptibility to denial-of-services attacks.

The side effect of this change is that it prevents you from defining uses with hostname in the HOST column. Instead, you must use IP addresses. You can also use special value localhost even when skip_name_resolve is enabled.

 

Common Problems and Solutions

-          Connecting through localhost versus 127.0.0.1

When you specify the hostname localhost as a connection parameter, it tries by default to connect through a Unix socket instead of via TCIP/IP. (localhost isn’t special on windows, but it means to connect via a named pipe)

 

If you want to connect via TCP/IP to the machine on which you’re running, you have two choices: specify an IP address instead of the hostname, or specify the protocol explicitly.

 

$mysql –host=127.0.0.1

$mysql – host=localshot – protocol=tcp

 

-          Using temporary tables safely

-          Disallowing passwordless access

In MySQL 5.0 and newer, you can set the server’s SQL mode to NO_AUTO_CREATE_USER to prevent from creating users without a password.

 

A user whose password in the mysql.user table is an empty string is a user without a password, not a user with an empty password.

-          Disabling anonymous users

-          Remember to quote hostnames separately

-          Don’t reuse usernames

-          Granting SELECT allows SHOW CREATE TABLE

Granting the SELECT privilege lets a user execute SHOW CREATE TABLE, which shows the SQL command that will recreate a table.

-          Don’t grant privileges on the mysql database

-          Don’t grant the SUPER privilege freely

-          Granting privileges on wildcarded databases

-          Revoking specific privileges

-          Users can connect even after REVOKE

-          When you can’t grant or revoke a privilege

-          Invisible privileges

SHOW GRANTS doesn’t really show all the privileges for a user: it merely shows the privileges explicitly assigned to that user. A user may have other permission, perhaps because of permission granted to anonymous users.

-          Obsolete privileges

 

MySQL will let you create database-level privileges for databases that don’t exist, but it won’t let you grant table-level privileges for tables that don’t exist. If you need to do this, you’ll have to insert rows directly into mysql.tables_priv.

Operating System Security

Guidelines

-          Don’t run MySQL from a privileged account

-          Keep your OS up-to-date

-          Restrict logins on the database host

-          Separate production from everything else

-          Have your server audited

-          Use the strongest means available

 

Keeping your backups on a different server is another important security measure.

Network Security

Localhost-Only Connections

The skip_networking option tells MySQL not to listen to any TCP socket, but it will allow connections on a Unix socket.

Firewalling

MySQL in a DMZ

Connection Encryption and Tunneling

TCP Wrappers

Automatic Host Blocking

Data Encryption

Hashing Passwords

MySQL provides three user functions for hashing passwords: ENCRYPT(), SHA1(), and MD5().

Encrypted Filesystems

Application-Level Encryption

Application-level encryption is often a good solution, but it does have some drawbacks. For example, it’s much harder for MySQL to effectively encrypted data, and it’s much harder to optimize MySQL’s performance when you’re working with encrypted data.

Source Code Modification

MySQL in a chrooted Environment