The following mysqld options affect security:
Table 5.5. Security Option/Variable Summary
Name | Cmd-Line | Option file | System Var | Status Var | Var Scope | Dynamic |
---|---|---|---|---|---|---|
allow-suspicious-udfs | Yes | Yes | ||||
chroot | Yes | Yes | ||||
des-key-file | Yes | Yes | ||||
local_infile | Yes | Global | Yes | |||
local-infile | Yes | Yes | ||||
- Variable: local_infile | ||||||
old-passwords | Yes | Yes | Both | Yes | ||
- Variable: old_passwords | Yes | Both | Yes | |||
safe-show-database | Yes | Yes | Yes | Global | Yes | |
safe-user-create | Yes | Yes | ||||
secure-auth | Yes | Yes | Global | Yes | ||
- Variable: secure_auth | Yes | Global | Yes | |||
skip-grant-tables | Yes | Yes | ||||
skip-name-resolve | Yes | Yes | ||||
skip-networking | Yes | Yes | Global | No | ||
- Variable: skip_networking | Yes | Global | No | |||
skip-show-database | Yes | Yes | Global | No | ||
- Variable: skip_show_database | Yes | Global | No |
This option controls whether user-defined functions that have
only an xxx
symbol for the main function
can be loaded. By default, the option is turned off and only
UDFs that have at least one auxiliary symbol can be loaded;
this prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. This option
was added in MySQL 4.0.24 and 4.1.10a. See
Section 18.2.2.6, “User-Defined Function Security Precautions”.
If you start the server with
--local-infile=0
, clients
cannot use LOCAL
in
LOAD DATA
statements. See
Section 5.4.5, “Security Issues with LOAD
DATA LOCAL
”.
Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section 5.4.2.3, “Password Hashing in MySQL”.
With this option, the SHOW
DATABASES
statement displays the names of only those
databases for which the user has some kind of privilege. As of
MySQL 4.0.2, this option is deprecated and does not do
anything (it is enabled by default), because there is a
SHOW DATABASES
privilege that
can be used to control access to database names on a
per-account basis. See Section 12.4.1.2, “GRANT
Syntax”.
If this option is enabled, a user cannot create new MySQL
users by using the GRANT
statement unless the user has the
INSERT
privilege for the
mysql.user
table. If you want a user to
have the ability to create new users that have those
privileges that the user has the right to grant, you should
grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name
'@'host_name
';
This ensures that the user cannot change any privilege columns
directly, but has to use the
GRANT
statement to give
privileges to other users.
Disallow authentication for accounts that have old (pre-4.1) passwords. This option is available as of MySQL 4.1.1.
The mysql client also has a
--secure-auth
option, which
prevents connections to a server if the server requires a
password in old format for the client account.
This option causes the server not to use the privilege system
at all. This gives anyone with access to the server
unrestricted access to all
databases. You can cause a running server to start
using the grant tables again by executing mysqladmin
flush-privileges or mysqladmin
reload command from a system shell, or by issuing a
MySQL FLUSH
PRIVILEGES
statement. This option also suppresses
loading of user-defined functions (UDFs).
Host names are not resolved. All Host
column values in the grant tables must be IP numbers or
localhost
.
Do not allow TCP/IP connections over the network. All connections to mysqld must be made via Unix socket files. This option is unsuitable when using a MySQL version prior to 3.23.27 with the MIT-pthreads package, because Unix socket files were not supported by MIT-pthreads at that time.
With this option, the SHOW
DATABASES
statement is allowed only to users who
have the SHOW DATABASES
privilege, and the statement displays all database names.
Without this option, SHOW
DATABASES
is allowed to all users, but displays each
database name only if the user has the
SHOW DATABASES
privilege or
some privilege for the database. Note that any global
privilege is a privilege for the database.
Options that begin with --ssl
specify whether to allow clients to connect via SSL and
indicate where to find SSL keys and certificates. See
Section 5.6.6.3, “SSL Command Options”.
User Comments
A simple security enhancement missing in
this list would be for:
[mysqld]
bind-address=127.0.0.1
This means localhost can only connect, and
that's enough for small scale projects. They
won't see port 3306 in portscans anymore.
If your application, web server and database all run on the same machine, then you probably don't need networking enabled at all. Use of the "--skip-networking" flag is highly recommended.
So is there nothing equivalent to the suggested "bind-address=127.0.0.1". I really would find this feature useful because I'm running chrooted daemons. I either have to make hard links to the mysql socket file at startup time or I have to use tcp/ip ... tcp/ip would be much less problematic.
If the feature isn't supported I guess I'll resort to firewalling for now.
For setting these options read
http://www.faqs.org/docs/apache-compile/prereqs.html
2.4.4. Securing MySQL
or
http://www.linuxsecurity.com/tips/tip-24.html
It's important to note that the SHOW DATABASES restriction only works if NO global privs are present at all. I performed an upgrade to 4.0.x from 3.x.x and found I couldn't restrict the display of databases. Turns out MySQL adds 'Create_tmp_table_priv' and 'Lock_tables_priv' by default as 'Y', meaning that suddenly, this doesn't work again. It would be assumed as this was insignificant considering the nature of these options, but it seems not.
Just save someone else 16 hours of fiddling.
Cross-reference for the "bind-address" MySQL server option:
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
You can only specify a single address! This should generally be sufficent, but not if you want to be able to connect to both <external address>:3306 and also 127.0.0.1:3306
Add your own comment.