The following mysqld options affect security:
Table 5.6. Security Option/Variable Summary
Name | Cmd-Line | Option file | System Var | Status Var | Var Scope | Dynamic |
---|---|---|---|---|---|---|
allow-suspicious-udfs | Yes | Yes | ||||
automatic_sp_privileges | Yes | Global | 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 | |||
secure-file-priv | Yes | Yes | Global | No | ||
- Variable: secure_file_priv | Yes | Global | No | |||
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 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. See
Section 22.3.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.3.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.3.2.3, “Password Hashing in MySQL”.
MySQL Enterprise. The MySQL Enterprise Monitor offers advice on the security implications of using this option. For subscription information, see http://www.mysql.com/products/enterprise/advisors.html.
--safe-show-database
(OBSOLETE)
In previous versions of MySQL, this option caused the
SHOW DATABASES
statement to
display the names of only those databases for which the user
had some kind of privilege. In MySQL 5.5, this
option is no longer available as this is now the default
behavior, and 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.3, “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 or any column in the
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.
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 limits the effect of the
LOAD_FILE()
function and the
LOAD DATA
and
SELECT ... INTO
OUTFILE
statements to work only with files in the
specified directory.
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 plugins and 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.
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.5.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.