Security and More in MySQL Databases

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss system and other variables, then begin to look at general security issues. The second of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator’s Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

4.2.3.1 System Variables

Starting from MySQL 4.0.3, we provide better access to a lot of system and connection variables. Many variables can be changed dynamically while the server is running. This allows you to modify server operation without having to stop and restart it.

The mysqld server maintains two kinds of variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections.

When the server starts, it initializes all global variables to their default values. These defaults may be changed by options specified in option files or on the command line. After the server starts, those global variables that are dynamic can be changed by connecting to the server and issuing a SET GLOBAL var_name statement. To change a global variable, you must have the SUPER privilege.

The server also maintains a set of session variables for each client that connects. The client’s session variables are initialized at connect time using the current values of the corresponding global variables. For those session variables that are dynamic, the client can change them by issuing a SET SESSION var_name statement. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.

A change to a global variable is visible to any client that accesses that global variable. However, it affects the corresponding session variable that is intialized from the global variable only for clients that connect after the change. It does not affect the session variable for any client that is already connected (not even that of the client that issues the SET GLOBAL statement).

Global or session variables may be set or retrieved using several syntax forms. The following examples use sort_buffer_size as a sample variable name.

To set the value of a GLOBAL variable, use one of the following syntaxes:

mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;

To set the value of a SESSION variable, use one of the following syntaxes:

mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;

LOCAL is a synonym for SESSION.

If you don’t specify GLOBAL, SESSION, or LOCAL when setting a variable, SESSION is the default.

To retrieve the value of a GLOBAL variable, use one of the following statements:

mysql> SELECT @@global.sort_buffer_size;
mysql> SHOW GLOBAL VARIABLES like
'sort_buffer_size';

To retrieve the value of a SESSION variable, use one of the following statements:

mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like
'sort_buffer_size';

Here, too, LOCAL is a synonym for SESSION.

When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local., MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.

For SHOW VARIABLES, if you do not specify GLOBAL, SESSION, or LOCAL, MySQL returns the SESSION value.

The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future. If we remove a SESSION variable with the same name as a SESSION variable, a client with the SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection. If we add a SESSION variable with the same name as a SESSION variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.

4.2.3.1.1 Structured System Variables

Structured system variables are supported beginning with MySQL 4.1.1. A structured variable differs from a regular system variable in two respects:

  • Its value is a structure with components that specify server parameters considered to be closely related.

  • There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

Currently, MySQL supports one structured variable type. It specifies parameters that govern the operation of key caches. A key cache structured variable has these components:

  • key_buffer_size

  • key_cache_block_size

  • key_cache_division_limit

  • key_cache_age_threshold

The purpose of this section is to describe the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 6.4.6, “The MyISAM Key Cache.”

To refer to a component of a structured variable instance, you can use a compound name in instance_name.component_name format. Examples:

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable.

The naming rules for structured variable instances and components are as follows:

  • For a given type of structured variable, each instance must have a name that is unique within variables of that type. However, instance names need not be unique across structured variable types. For example, each structured variable will have an instance named default, so default is not unique across variable types.

  • The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name.

  • If a structured variable instance name is not legal as an unquoted identifier, refer to it as a quoted identifier using backticks. For example, hot-cache is not legal, but ┬┤hot-cache┬┤ is.

  • global, session, and local are not legal instance names. This avoids a conflict with notation such as @@global.var_name for referring to non-structured system variables.

At the moment, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.

With one exception, it is allowable to refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:

shell> mysqld --hot_cache.key_buffer_size=64K

In an option file, do this:

[mysqld]
hot_cache.key_buffer_size=64K

If you start the server with such an option, it creates a key cache named hot_cache with a size of 64KB in addition to the default key cache that has a default size of 8MB.

Suppose that you start the server as follows:

shell> mysqld --key_buffer_size=256K 
--extra_cache.key_buffer_size=128K
--extra_cache.key_cache_block_size=2048

In this case, the server sets the size of the default key cache to 256KB. (You could also have written --default.key_buffer_size=256K.) In addition, the server creates a second key cache named extra_cache that has a size of 128KB, with the size of block buffers for caching table index blocks set to 2048 bytes.

The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:

shell> mysqld --key_buffer_size=6M 
--hot_cache.key_buffer_size=2M
--cold_cache.key_buffer_size=2M

Structured variable values may be set and retrieved at runtime as well. For example, to set a key cache named hot_cache to a size of 10MB, use either of these statements:

mysql> SET GLOBAL hot_cache.key_buffer_size =
10*1024*1024;
mysql> SET @@global.hot_cache.key_buffer_size =
10*1024*1024;

To retrieve the cache size, do this:

mysql> SELECT @@global.hot_cache.key_buffer_size;

However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE pattern-matching operation:

mysql> SHOW GLOBAL VARIABLES LIKE
'hot_cache.key_buffer_size';

This is the exception to being able to use structured variable names anywhere a simple variable name may occur.

{mospagebreak title=4.2.3.1.2 Dynamic System Variables}

Beginning with MySQL 4.0.3, many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also select their values using SELECT. See Section 4.2.3.1, “System Variables.”

The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply.

Variable Name

Value Type

Type

autocommit

boolean

SESSION

big_tables

boolean

SESSION

binlog_cache_size

numeric

GLOBAL

bulk_insert_buffer_size

numeric

GLOBAL | SESSION

character_set_client

string

GLOBAL | SESSION

character_set_connection

string

GLOBAL | SESSION

character_set_results

string

GLOBAL | SESSION

character_set_server

string

GLOBAL | SESSION

collation_connection

string

GLOBAL | SESSION

collation_server

string

GLOBAL | SESSION

concurrent_insert

boolean

GLOBAL

connect_timeout

numeric

GLOBAL

convert_character_set

string

GLOBAL | SESSION

default_week_format

numeric

GLOBAL | SESSION

delay_key_write

OFF | ON | ALL

GLOBAL

delayed_insert_limit

numeric

GLOBAL

delayed_insert_timeout

numeric

GLOBAL

delayed_queue_size

numeric

GLOBAL

error_count

numeric

SESSION

flush

boolean

GLOBAL

flush_time

numeric

GLOBAL

foreign_key_checks

boolean

SESSION

ft_boolean_syntax

numeric

GLOBAL

group_concat_max_len

numeric

GLOBAL | SESSION

identity

numeric

SESSION

insert_id

boolean

SESSION

interactive_timeout

numeric

GLOBAL | SESSION

join_buffer_size

numeric

GLOBAL | SESSION

key_buffer_size

numeric

GLOBAL

last_insert_id

numeric

SESSION

local_infile

boolean

GLOBAL

log_warnings

boolean

GLOBAL

long_query_time

numeric

GLOBAL | SESSION

low_priority_updates

boolean

GLOBAL | SESSION

max_allowed_packet

numeric

GLOBAL | SESSION

max_binlog_cache_size

numeric

GLOBAL

max_binlog_size

numeric

GLOBAL

max_connect_errors

numeric

GLOBAL

max_connections

numeric

GLOBAL

max_delayed_threads

numeric

GLOBAL

max_error_count

numeric

GLOBAL | SESSION

max_heap_table_size

numeric

GLOBAL | SESSION

max_insert_delayed_threads

numeric

GLOBAL

max_join_size

numeric

GLOBAL | SESSION

max_relay_log_size

numeric

GLOBAL

max_seeks_for_key

numeric

GLOBAL | SESSION

max_sort_length

numeric

GLOBAL | SESSION

max_tmp_tables

numeric

GLOBAL

max_user_connections

numeric

GLOBAL

max_write_lock_count

numeric

GLOBAL

myisam_max_extra_sort_file_size

numeric

GLOBAL | SESSION

myisam_max_sort_file_size

numeric

GLOBAL | SESSION

myisam_repair_threads

numeric

GLOBAL | SESSION

myisam_sort_buffer_size

numeric

GLOBAL | SESSION

net_buffer_length

numeric

GLOBAL | SESSION

net_read_timeout

numeric

GLOBAL | SESSION

net_retry_count

numeric

GLOBAL | SESSION

net_write_timeout

numeric

GLOBAL | SESSION

query_alloc_block_size

numeric

GLOBAL | SESSION

query_cache_limit

numeric

GLOBAL

query_cache_size

numeric

GLOBAL

query_cache_type

enumeration

GLOBAL | SESSION

query_cache_wlock_invalidate

boolean

GLOBAL | SESSION

query_prealloc_size

numeric

GLOBAL | SESSION

range_alloc_block_size

numeric

GLOBAL | SESSION

read_buffer_size

numeric

GLOBAL | SESSION

read_only

numeric

GLOBAL

read_rnd_buffer_size

numeric

GLOBAL | SESSION

rpl_recovery_rank

numeric

GLOBAL

safe_show_database

boolean

GLOBAL

server_id

numeric

GLOBAL

slave_compressed_protocol

boolean

GLOBAL

slave_net_timeout

numeric

GLOBAL

slow_launch_time

numeric

GLOBAL

sort_buffer_size

numeric

GLOBAL | SESSION

sql_auto_is_null

boolean

SESSION

sql_big_selects

boolean

SESSION

sql_big_tables

boolean

SESSION

sql_buffer_result

boolean

SESSION

sql_log_bin

boolean

SESSION

sql_log_off

boolean

SESSION

sql_log_update

boolean

SESSION

sql_low_priority_updates

boolean

GLOBAL | SESSION

sql_max_join_size

numeric

GLOBAL | SESSION

sql_quote_show_create

boolean

SESSION

sql_safe_updates

boolean

SESSION

sql_select_limit

numeric

SESSION

sql_slave_skip_counter

numeric

GLOBAL

sql_warnings

boolean

SESSION

storage_engine

enumeration

GLOBAL | SESSION

table_cache

numeric

GLOBAL

table_type

enumeration

GLOBAL | SESSION

thread_cache_size

numeric

GLOBAL

timestamp

boolean

SESSION

tmp_table_size

enumeration

GLOBAL | SESSION

transaction_alloc_block_size

numeric

GLOBAL | SESSION

transaction_prealloc_size

numeric

GLOBAL | SESSION

tx_isolation

enumeration

GLOBAL | SESSION

unique_checks

boolean

SESSION

wait_timeout

numeric

GLOBAL | SESSION

warning_count

numeric

SESSION


Variables that are marked as “string” take a string value. Variables that are marked as “numeric” take a numeric value. Variables that are marked as “boolean” can be set to 0, 1, ON or OFF. Variables that are marked as “enumeration” normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumeration-valued system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

{mospagebreak title=4.2.4 Server Status Variables}

The server maintains many status variables that provide information about its operations. You can view these variables and their values by using the SHOW STATUS statement:

mysql> SHOW STATUS;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_files        | 60         |
| Created_tmp_tables       | 8340       |
| Delayed_errors           | 0          |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 462604     |
| Handler_read_first       | 105881     |
| Handler_read_key         | 27820558   |
| Handler_read_next        | 390681754  |
| Handler_read_prev        | 6022500    |
| Handler_read_rnd         | 30546748   |
| Handler_read_rnd_next    | 246216530  |
| Handler_update           | 16945404   |
| Handler_write            | 60356676   |
| Key_blocks_used          | 14955      |
| Key_read_requests        | 96854827   |
| Key_reads                | 162040     |
| Key_write_requests       | 7589728    |
| Key_writes               | 3813196    |
| Max_used_connections     | 0          |
| Not_flushed_delayed_rows | 0          |
| Not_flushed_key_blocks   | 0          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Open_tables              | 1          |
| Opened_tables            | 44600      |
| Qcache_free_blocks       | 36         |
| Qcache_free_memory       | 138488     |
| Qcache_hits              | 79570      |
| Qcache_inserts           | 27087      |
| Qcache_lowmem_prunes     | 3114       |
| Qcache_not_cached        | 22989      |
| Qcache_queries_in_cache  | 415        |
| Qcache_total_blocks      | 912        |
| Questions                | 2026873    |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 99646      |
| Select_range_check       | 0          |
| Select_scan              | 30802      |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 0          |
| Slow_queries             | 0          |
| Sort_merge_passes        | 30         |
| Sort_range               | 500        |
| Sort_rows                | 30296250   |
| Sort_scan                | 4650       |
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_connected        | 1          |
| Threads_created          | 30022      |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

Many status variables are reset to 0 by the FLUSH STATUS statement.

The status variables have the following meanings. The Com_xxx statement counter variables were added beginning with MySQL 3.23.47. The Qcache_xxx query cache variables were added beginning with MySQL 4.0.1. Otherwise, variables with no version indicated have been present since at least MySQL 3.22.

  • Aborted_clients

    The number of connections that were aborted because the client died without closing the connection properly. See Section A.2.10, “Communication Errors and Aborted Connections.”

  • Aborted_connects

    The number of tries to connect to the MySQL server that failed. See Section A.2.10, “Communication Errors and Aborted Connections.”

  • Binlog_cache_use

    The number of transactions that used the temporary binary log cache. This variable was added in MySQL 4.1.2.

  • Binlog_cache_disk_use

    The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction. This variable was added in MySQL 4.1.2.

  • Bytes_received

    The number of bytes received from all clients. This variable was added in MySQL 3.23.7.

  • Bytes_sent

    The number of bytes sent to all clients. This variable was added in MySQL 3.23.7.

  • Com_xxx

    The number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_insert count DELETE and INSERT statements.

  • Connections

    The number of connection attempts (successful or not) to the MySQL server.

  • Created_tmp_disk_tables

    The number of temporary tables on disk created automatically by the server while executing statements. This variable was added in MySQL 3.23.24.

  • Created_tmp_files

    How many temporary files mysqld has created. This variable was added in MySQL 3.23.28.

  • Created_tmp_tables

    The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

  • Delayed_errors

    The number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).

  • Delayed_insert_threads

    The number of INSERT DELAYED handler threads in use.

  • Delayed_writes

    The number of INSERT DELAYED rows written.

  • Flush_commands

    The number of executed FLUSH statements.

  • Handler_commit

    The number of internal COMMIT statements. This variable was added in MySQL 4.0.2.

  • Handler_delete

    The number of times a row was deleted from a table.

  • Handler_read_first

    The number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed.

  • Handler_read_key

    The number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed.

  • Handler_read_next

    The number of requests to read the next row in key order. This will be incremented if you are querying an index column with a range constraint or if you are doing an index scan.

  • Handler_read_prev

    The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC. This variable was added in MySQL 3.23.6.

  • Handler_read_rnd

    The number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.

  • Handler_read_rnd_next

    The number of requests to read the next row in the data file. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

  • Handler_rollback

    The number of internal ROLLBACK statements. This variable was added in MySQL 4.0.2.

  • Handler_update

    The number of requests to update a row in a table.

  • Handler_write

    The number of requests to insert a row in a table.

  • Key_blocks_used

    The number of used blocks in the key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size in Section 4.2.3, “Server System Variables.”

  • Key_read_requests

    The number of requests to read a key block from the cache.

  • Key_reads

    The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

  • Key_write_requests

    The number of requests to write a key block to the cache.

  • Key_writes

    The number of physical writes of a key block to disk.

  • Max_used_connections

    The maximum number of connections that have been in use simultaneously since the server started.

  • Not_flushed_delayed_rows

    The number of rows waiting to be written in INSERT DELAY queues.

  • Not_flushed_key_blocks

    The number of key blocks in the key cache that have changed but haven’t yet been flushed to disk.

  • Open_files

    The number of files that are open.

  • Open_streams

    The number of streams that are open (used mainly for logging).

  • Open_tables

    The number of tables that are open.

  • Opened_tables

    The number of tables that have been opened. If Opened_tables is big, your table_cache value is probably too small.

  • Qcache_free_blocks

    The number of free memory blocks in query cache.

  • Qcache_free_memory

    The amount of free memory for query cache.

  • Qcache_hits

    The number of cache hits.

  • Qcache_inserts

    The number of queries added to the cache.

  • Qcache_lowmem_prunes

    The number of queries that were deleted from the cache because of low memory.

  • Qcache_not_cached

    The number of non-cached queries (not cachable, or due to query_cache_type).

  • Qcache_queries_in_cache

    The number of queries registered in the cache.

  • Qcache_total_blocks

    The total number of blocks in the query cache.

  • Questions

    The number of queries that have been sent to the server.

  • Rpl_status

    The status of failsafe replication (not yet implemented).

  • Select_full_join

    The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. This variable was added in MySQL 3.23.25.

  • Select_full_range_join

    The number of joins that used a range search on a reference table. This variable was added in MySQL 3.23.25.

  • Select_range

    The number of joins that used ranges on the first table. (It’s normally not critical even if this is big.) This variable was added in MySQL 3.23.25.

  • Select_range_check

    The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.) This variable was added in MySQL 3.23.25.

  • Select_scan

    The number of joins that did a full scan of the first table. This variable was added in MySQL 3.23.25.

  • Slave_open_temp_tables

    The number of temporary tables currently open by the slave SQL thread. This variable was added in MySQL 3.23.29.

  • Slave_running

    This is ON if the server is a slave that is connected to a master. This variable was added in MySQL 3.23.16.

  • Slow_launch_threads

    The number of threads that have taken more than slow_launch_time seconds to create. This variable was added in MySQL 3.23.15.

  • Slow_queries

    The number of queries that have taken more than long_query_time seconds. See Section 4.8.5, “The Slow Query Log.”

  • Sort_merge_passes

    The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable. This variable was added in MySQL 3.23.28.

  • Sort_range

    The number of sorts that were done with ranges. This variable was added in MySQL 3.23.25.

  • Sort_rows

    The number of sorted rows. This variable was added in MySQL 3.23.25.

  • Sort_scan

    The number of sorts that were done by scanning the table. This variable was added in MySQL 3.23.25.

  • Ssl_xxx

    Variables used for SSL connections. These variables were added in MySQL 4.0.0.

  • Table_locks_immediate

    The number of times that a table lock was acquired immediately. This variable was added as of MySQL 3.23.33.

  • Table_locks_waited

    The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. This variable was added as of MySQL 3.23.33.

  • Threads_cached

    The number of threads in the thread cache. This variable was added in MySQL 3.23.17.

  • Threads_connected

    The number of currently open connections.

  • Threads_created

    The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache hit rate can be calculated as Threads_created/Connections. This variable was added in MySQL 3.23.31.

  • Threads_running

    The number of threads that are not sleeping.

  • Uptime

    The number of seconds the server has been up.

{mospagebreak title=4.3 General Security Issues}

This section describes some general security issues to be aware of and what you can do to make your MySQL installation more secure against attack or misuse. For information specifically about the access control system that MySQL uses for setting up user accounts and checking database access, see Section 4.4, “The MySQL Access Privilege System.”

4.3.1 General Security Guidelines

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

  • Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical. The encrypted password is the real password in MySQL. Anyone who knows the password that is listed in the user table and has access to the host listed for the account can easily log in as that user.

  • Learn the MySQL access privilege system. The GRANT and REVOKE statements are used for controlling access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts.

    Checklist:

    • Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, you have problems. Anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the information about setting a root password. See Section 2.4.5, “Securing the Initial MySQL Accounts.”

    • Use the SHOW GRANTS statement and check to see who has access to what. Then use the REVOKE statement to remove those privileges that are not necessary.

  • Do not store any plain-text passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use MD5(), SHA1(), or some other one-way hashing function.

  • Do not choose passwords from dictionaries. There are special programs to break them. Even passwords like “xfish98″ are very bad. Much better is “duag98″ which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard. Another method is to use “Mhall” which is taken from the first characters of each word in the sentence “Mary had a little lamb.” This is easy to remember and type, but difficult to guess for someone who does not know it.

  • Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).

    Checklist:

    • Try to scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should not be accessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to try the following command from some remote machine, where server_host is the host on which your MySQL server runs:

      shell> telnet server_host 3306
  • If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet just hangs or the connection is refused, everything is OK; the port is blocked.

  • Do not trust any data entered by users of your applications. They can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like “; DROP DATABASE mysql;“. This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them.

    A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every record in the table. This exposes every record and causes excessive server load. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

    Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is allowable to display any record in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.

    Checklist:

    • Try to enter ‘'‘ and ‘"‘ in all your Web forms. If you get any kind of MySQL error, investigate the problem right away.

    • Try to modify any dynamic URLs by adding %22 (‘"‘), %23 (‘#‘), and %27 (‘'‘) in the URL.

    • Try to modify data types in dynamic URLs from numeric ones to character ones containing characters from previous examples. Your application should be safe against this and similar attacks.

    • Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!

    • Check data sizes before passing them to MySQL.

  • Consider having your application connect to the database using a different username than the one you use for administrative purposes. Do not give your applications any access privileges they do not need.

  • Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:

    • MySQL C API: Use the mysql_real_escape_string() API call.

    • MySQL++: Use the escape and quote modifiers for query streams.

    • PHP: Use the mysql_escape_string() function, which is based on the function of the same name in the MySQL C API. Prior to PHP 4.0.3, use addslashes() instead.

    • Perl DBI: Use the quote() method or use placeholders.

    • Java JDBC: Use a PreparedStatement object and placeholders.

    Other programming interfaces might have similar capabilities.

  • Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections as of Version 4.0.0. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.

  • Learn to use the tcpdump and strings utilities. For most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

    shell> tcpdump -l -i eth0 -w - src or dst
    port 3306 | strings
  • (This works under Linux and should work with small modifications under other systems.) Warning: If you do not see plaintext data, this doesn’t always mean that the information actually is encrypted. If you need high security, you should consult with a security expert.

{mospagebreak title=4.3.2 Making MySQL Secure Against Attackers}

When you connect to a MySQL server, you should use a password. The password is not transmitted in clear text over the connection. Password handling during the client connection sequence was upgraded in MySQL 4.1.1 to be very secure. If you are using an older version of MySQL, or are still using pre-4.1.1-style passwords, the encryption algorithm is less strong and with some effort a clever attacker who can sniff the traffic between the client and the server can crack the password. (See Section 4.4.9, “Password Hashing in MySQL 4.1,” for a discussion of the different password handling methods.) If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.

All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL 3.22 and above) to make traffic much more difficult to decipher. To make the connection even more secure, you should use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client. You can find an Open Source SSH client at http://www.openssh.org/, and a commercial SSH client at http://www.ssh.com/.

If you are using MySQL 4.0 or newer, you can also use internal OpenSSL support. See Section 4.5.7, “Using Secure Connections.”

To make a MySQL system secure, you should strongly consider the following suggestions:

  • Use passwords for all MySQL users. A client program does not necessarily know the identity of the person running it. It is common for client/server applications that the user can specify any username to the client program. For example, anyone can use the mysql program to connect as any other person simply by invoking it as mysql -u other_user db_name if other_user has no password. If all users have a password, connecting using another user’s account becomes much more difficult.

    To change the password for a user, use the SET PASSWORD statement. It is also possible to update the user table in the mysql database directly. For example, to change the password of all MySQL accounts that have a username of root, do this:

    shell> mysql -u root
    mysql> UPDATE mysql.user SET
    Password=PASSWORD('
    newpwd')
    -> WHERE User='root'; mysql> FLUSH PRIVILEGES;
  • Don’t run the MySQL server as the Unix root user. This is very dangerous, because any user with the FILE privilege will be able to create files as root (for example, ~root/.bashrc). To prevent this, mysqld refuses to run as root unless that is specified explicitly using a --user=root option.

    mysqld can be run as an ordinary unprivileged user instead. You can also create a separate Unix account named mysql to make everything even more secure. Use the account only for administering MySQL. To start mysqld as another Unix user, add a user option that specifies the username to the [mysqld] group of the /etc/my.cnf option file or the my.cnf option file in the server’s data directory. For example:

    [mysqld]
    user=mysql
  • This causes the server to start as the designated user whether you start it manually or by using mysqld_safe or mysql.server. For more details, see Section A.3.2, “How to Run MySQL as a Normal User.”

    Running mysql as a Unix user other than root does not mean that you need to change the root username in the user table. Usernames for MySQL accounts have nothing to do with usernames for Unix accounts.

  • Don’t allow the use of symlinks to tables. (This can be disabled with the --skip-symbolic-links option.) This is especially important if you run mysqld as root, because anyone that has write access to the server’s data directory then could delete any file in the system! See Section 6.6.1.2, “Using Symbolic Links for Tables on Unix.”

  • Make sure that the only Unix user with read or write privileges in the database directories is the user that mysqld runs as.

  • Don’t grant the PROCESS or SUPER privilege to non-administrative users. The output of mysqladmin processlist shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an UPDATE user SET password=PASSWORD('not_secure') query.

    mysqld reserves an extra connection for users who have the SUPER privilege (PROCESS before MySQL 4.0.2), so that a MySQL root user can log in and check server activity even if all normal connections are in use.

    The SUPER privilege can be used to terminate client connections, change server operation by changing the value of system variables, and control replication servers.

  • Don’t grant the FILE privilege to non-administrative users. Any user that has this privilege can write a file anywhere in the filesystem with the privileges of the mysqld daemon! To make this a bit safer, files generated with SELECT ... INTO OUTFILE will not overwrite existing files and are writable by everyone.

    The FILE privilege may also be used to read any file that is world-readable or accessible to the Unix user that the server runs as. With this privilege, you can read any file into a database table. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which then can be displayed with SELECT.

  • If you don’t trust your DNS, you should use IP numbers rather than hostnames in the grant tables. In any case, you should be very careful about creating grant table entries using hostname values that contain wildcards!

  • If you want to restrict the number of connections allowed to a single account, you can do so by setting the max_user_connections variable in mysqld. The GRANT statement also supports resource control options for limiting the extent of server use allowed to an account.

4.3.3 Startup Options for mysqld Concerning Security

The following mysqld options affect security:

  • --local-infile[={0|1}]

    If you start the server with --local-infile=0, clients cannot use LOCAL in LOAD DATA statements. See Section 4.3.4, “Security Issues with LOAD DATA LOCAL.”

  • --safe-show-database

    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 doesn’t do anything (it is enabled by default), because there is now a SHOW DATABASES privilege that can be used to control access to database names on a per-account basis.

  • --safe-user-create

    If this is enabled, a user cannot create new users with 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 with those privileges that the user has right to grant, you should grant the user the following privilege:

    mysql> GRANT INSERT(user) ON mysql.user TO
    '
    user_name'@'host_name';
  • This will ensure that the user can’t change any privilege columns directly, but has to use the GRANT statement to give privileges to other users.

  • --secure-auth

    Disallow authentication for accounts that have old (pre-4.1) passwords. This option is available as of MySQL 4.1.1.

  • --skip-grant-tables

    This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing a mysqladmin flush-privileges or mysqladmin reload command, or by issuing a FLUSH PRIVILEGES statement.)

  • --skip-name-resolve

    Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

  • --skip-networking

    Don’t 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.

  • --skip-show-database

    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.

{mospagebreak title=4.3.4 Security Issues with LOAD DATA LOCAL}

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified.

There are two potential security issues with supporting the LOCAL version of LOAD DATA statements:

  • The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.

  • In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the program being run by the user connecting to the Web server.

To deal with these problems, we changed how LOAD DATA LOCAL is handled as of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows):

  • By default, all MySQL clients and libraries in binary distributions are compiled with the --enable-local-infile option, to be compatible with MySQL 3.23.48 and before.

  • If you build MySQL from source but don’t use the --enable-local-infile option to configure, LOAD DATA LOCAL cannot be used by any client unless it is written explicitly to invoke mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0).

  • You can disable all LOAD DATA LOCAL commands from the server side by starting mysqld with the --local-infile=0 option.

  • For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the --local-infile[=1] option, or disabled with the --local-infile=0 option. Similarly, for mysqlimport, the --local or -L option enables local data file loading. In any case, successful use of a local loading operation requires that the server is enabled to allow it.

  • If LOAD DATA LOCAL INFILE is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

    ERROR 1148: The used command is not allowed
    with this MySQL version
Please check back next week for the continuation of this article.
[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan