Linux Server Security (2nd Edition( [Electronic resources] نسخه متنی

8.4. Database Operation

Now that you've installed a reasonably secure
version of the server in a reasonably secure location,
let's look at how to run the thing securely.

8.4.1. MySQL Table Types

Many new developers of
MySQL-backed web sites have been
horrified to watch their database fall over and sink into the swamp
just as their site becomes popular. Although MySQL has a reputation
for speed, this is primarily in cases where database reads greatly
outnumber writes. Once the number of simultaneous writes crosses some
threshold, performance degrades most ungracefully.

This is a self-inflicted Denial of Service by the implementation of
the default MySQL table
type: MyISAM. It locks the whole table with
each write (INSERT, UPDATE, or DELETE), pushing back all other
requests. It's like closing all check-in lines but
one at a busy airport terminal. Waits lengthen until the
administrator must kill database threads or restart the database
server.

MySQL actually has multiple table types, each implementing a
different storage mechanism and behavior. You'll
usually deal with two: MyISAM and InnoDB. MyISAM is great for reads
and counts (such as COUNT * FROM TABLE), bad for heavy writes, and
lacking true transactionsthe ability to
perform multiple SQL statements as a unit and roll back to the
original state if there are problems.

InnoDB is more recent, with full transaction support (ACID
compliance, for the database folks), foreign-key constraints, and
finer-grained locking. It's preferred when there are
many writes or a need for transactions. People who are used to MyISAM
should be aware that COUNT(*) is much slower in InnoDB tables. InnoDB
is more complex and has many specialized options.

If you're just starting with MySQL, try MyISAM first
and move up to InnoDB later if you need the write performance or
transaction support. Luckily, you can do this with a single SQL
command:

alter table table_name type=innodb Many public MySQL-based sites such as
slashdot.org
have migrated from MyISAM to InnoDB.

8.4.2. Loading Datafiles

If you have FILE privileges, you can bulk
load data from a flat file to a
MySQL table. This has obvious
security implications.

The SQL LOAD DATA command reads a flat file
on the database machine into a MySQL table. This could be used to
load /etc/passwd into a table, then read it with
a SQL
SELECT statement. Since end users should not be stuffing files into
tables, it's best to restrict this to administrative
accounts. For example, if you need to load a flat file into a
particular table every day, create a MySQL account for that purpose
and grant it load privileges:

GRANT FILE ON database.table TO user @host identified by "password" The SQL LOAD DATA LOCAL command allows the
database server to read files from the client. This permits an evil
server to grab any file from the database client, or an evil client
to upload a file of its choice.

Recent versions of MySQL (3.23.49+ and 4.0.2+) are compiled to
include an explicit --enable-local-infile option
for backward compatibility. To disable this ability completely, they
can be compiled without this option. Local loads can also be disabled
at runtime by starting mysqld with the
--local-infile=0 option.

8.4.3. Writing Data to Files

The SQL command SELECT ... INTO OUTFILE dumps
the results of the select operation into an external file. This is
another good reason not to run the server as Unix
root. The FILE grant permission is needed to
write files. There doesn't seem to be a way to grant
read-only or write-only permissions.

8.4.4. Viewing Database Threads

Any user with
PROCESS privilege can view the
cleartext
of any currently executing database server threads (with
SQL SHOW PROCESSLIST or clients such as
mysqladmin processlist or
mytop). This includes threads containing password
changes, so the privilege should be confined to those who would
normally be permitted to view such things.

8.4.5. Killing Database Threads

A user can always
kill his own threads, but
with
SUPER privilege, he can kill
any thread. Confine this privilege to administrators.

8.4.6. Stopping the Server

Anyone with SHUTDOWN privilege may
stop the MySQL server by running mysqladmin
shutdown. The mysql user may
also stop the server at the operating system level with commands such
as service mysqld stop.

8.4.7. Backups

A database administrator should periodically dump tables to
files in case data becomes lost or corrupted and needs to be
recovered. The
mysqldump
client writes all the SQL commands needed to re-create the tables and
insert all the data rows. The backup file permissions should only
allow reading and writing by the mysql user and
group.

8.4.8. Logging

MySQL
writes logs to record errors,
queries,
slow queries, and updates. These are normally written to the same
data directory that contains the MySQL database. Besides protecting
these files from snooping, they should be rotated before they fill up
the disk. Red Hat includes a
mysql-log-rotate script as part of its
logrotate package.

8.4.9. Replication

To enhance speed and reliability,
MySQL
can be configured to replicate data in many
ways. This introduces many issues that are better explained in the
book, High Performance MySQL
(O'Reilly). In terms of security, you
want to protect the data streams among master(s) and slaves.

8.4.10. Queries

Database servers have some of the same
problems as web servers. Each has an
embedded language that can be abused or exploited.

If the database is suddenly running very slowly, the cause may be
benign (a slow query) or some attack. A good tool to view and kill
runaway queries is the Perl application mytop
(http://jeremy.zawodny.com/mysql/mytop/).

If the cause is a valid but slow query, database books describe the
art and science of query optimization, including building proper
indexes, using EXPLAIN to see how a query would be handled,
denormalizing, and so on. Some optimizations might include using the
appropriate MySQL table type. For example, Innodb tables handle high
write/read ratios better than MyISAM tables.

8.4.11. SQL Injection

Some queries are actual attempts to attack the server. Since SQL is a
language, it's susceptible to lexical, grammatical,
and logical errors. Exploiting SQL to crack a system is also called
SQL injection.

Let's say you have a web site where people register
to access your content. Somewhere you'll have a
table defining your users: ID, password, and so on. You have a script
(Perl, PHP, or whatever) that collects the ID and password from a
form and checks the database to see if that user exists. In PHP, you
might code:

$query = "SELECT * FROM USERS WHERE ID = '$id' and password = '$password'";

where $id and
$password are the values from the form.
(In Chapter 10I point out that we would actually
take a few steps before this to ensure that
$id and $password
actually came from the form.) If
$id were shrek and
$password were donkey,
the query would be:

SELECT * FROM USERS WHERE ID = 'shrek' and PASSWORD = 'donkey' A cunning SQL injector could use these values instead:

This results in:

SELECT * FROM USERS WHERE ID = '' OR ''='' and PASSWORD = '' OR ''='' This will select every row. If we had used SELECT COUNT(*) instead,
we would get a count of all the rows.

Chapter 10 includes more information on how to
guard against SQL injection in your Perl or PHP scripts. These
client-side safeguards include:

Checking all input variables Discarding illegal characters Checking maximum sizes Quoting
At the server level, you can use an intrusion
detection system such as
snort (see Chapter 13)
to detect SQL injection attempts.
This provides an extra layer of protection, since you
can't trust that all clients have been secured. A
good discussion of SQL injection is Detection of SQL
Injection and Cross-site Scripting Attacks (http://www.securityfocus.com/infocus/1768).