MySQL Security

Security

Database security entails allowing or disallowing user actions on the database and the objects within it. When you will create an database application, the security policy is the first step. An application security policy is a list of application security requirements and rules that regulate user access to database objects. This chapter discusses aspects of application security and MySQL Database features which contains the following topics :

MySQL general security issues

Security Guidelines :

• Except MySQL root account does not permit anyone to access the user table in the MySQL database.
• Use the GRANT and REVOKE statements to control access to MySQL. Do not grant unnecessary privileges and never grant privileges to all hosts.
• Never store simple text passwords in your database. Store the hash value using  SHA2(), SHA1(), MD5() functions or other hashing function in a different way. Try to use a complex password.
• Try to use a firewall and put MySQL behind the firewall.
• 3306 is the default user port of MySQL and this port should not be accessible from untrusted hosts. You can scan the ports from Internet using a tool such as nmap. From a remote machine you can check whether the port is open or not with this command: shell> telnet server_host 3306. If telnet hangs or the connection is refused, the port is blocked. 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.
• Some applications access MySQL database for different a purpose. Never trust these input data entered by the user and must validate properly before access database.
• Do not transmit unencrypted data over the Internet. Use an encrypted protocol such as SSL (MySQL supports internal SSL connections) or SSH.
• Use tcpdump and strings utilities. By issuing this command shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings you can check whether MySQL data streams are unencrypted or not.

Keeping Passwords Secure:

• End-User Guidelines for Password Security
• Use the -p or --password option on the command line with no password value specified. Here is the command

  shell> mysql -u user_id -p database_name
  Enter password : ***********
  When you input the password it will not visible.

• Store your password in an option file. For example Unix you can list your password in [client] section of the .my.cnf file in your home directory and to keep password safe, set the file access mode to 400 or 600.
• Administrator Guidelines for Password Security : MySQL stores passwords for user accounts in the mysql.user table. Therefore this table should not be accessed by any nonadministrative accounts. User account password must reset time to time.
• Passwords and Logging : Passwords can be written as plain text in SQL statements such as CREATE USER, GRANT, and SET PASSWORD, or statements that invoke the PASSWORD() function. If these statements are logged by the MySQL server as written, such passwords become available to anyone with access to the logs. Beginning with MySQL 5.6.3, statement logging is modified so that passwords do not appear in plain text for the following statements:

  CREATE USER ... IDENTIFIED BY ...GRANT ... IDENTIFIED BY ...SET PASSWORD ...SLAVE START ... PASSWORD = ...  (as of 5.6.4)CREATE SERVER ... OPTIONS(... PASSWORD ...) (as of 5.6.9)ALTER SERVER ... OPTIONS(... PASSWORD ...)  (as of 5.6.9)
  

  Passwords in those statements are rewritten not to appear literally in statement text, for the general query log, slow query log, and binary log. Rewriting does not apply to other statements.
• Password Hashing in MySQL : MySQL lists user accounts in the user table of the MySQL database. Each MySQL account can be assigned a password, although the user table does not store the cleartext version of the password, but a hash value computed from it.
• Implications of Password Hashing Changes in MySQL 4.1 for Application Programs : An upgrade to MySQL version 4.1 or later can cause compatibility issues for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts.
• The validate_password plugin (available as of MySQL 5.6.6) can be used to test passwords and improve security.

Making MySQL Secure Against Attackers :

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

• Require all MySQL accounts to have a password.
• Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld.
• Never run the MySQL server as the Unix root user
• Do not grant the FILE privilege to nonadministrative users
• Do not permit the use of symlinks to tables.
• Stored programs and views should be written using the security guidelines
• If you do not trust your DNS, you should use IP addresses rather than hostnames in the grant tables.
• If you want to restrict the number of connections permitted to a single account, you can do so by setting the max_user_connections variable in mysqld.

Security-Related mysqld Options and Variables :

The following table shows mysqld options and system variables that affect security.

How to Run MySQL as a Normal User:

• On Windows, you can run the server as a Windows service using a normal user account.
• On Unix, the MySQL server mysqld can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons.

Security Issues with LOAD DATA LOCAL:

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 remote program being run by the user who connects to the Web server.

Client Programming Security Guidelines:

Applications that access MySQL should not trust any data entered by users, who 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. See the following guidelines :

• Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts.
• Try to enter single and double quotation marks (“'” and “"”) in all of your Web forms. If you get any kind of MySQL error, investigate the problem right away.
• Try to modify dynamic URLs by adding %22 (“"”), %23 (“#”), and %27 (“'”) to them.
• Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these 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 the size of data before passing it to MySQL.
• Do not give your applications any access privileges they do not need.

The MySQL Access Privilege System

Privileges Provided by MySQL :

MySQL provides privileges that apply in different contexts and at different levels of operation:

• Administrative privileges enable users to manage the operation of the MySQL server. These privileges are global because they are not specific to a particular database.
• Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.
• Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).

Permissible Privileges for GRANT and REVOKE:

Privilege System Grant Tables:

Normally, you manipulate the contents of the grant tables in the mysql database indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one.

These mysql database tables contain grant information:

• user: Contains user accounts, global privileges, and other non-privilege columns.
• db: Contains database-level privileges.
• host: Obsolete. New MySQL installations no longer create this table as of MySQL 5.6.7.
• tables_priv: Contains table-level privileges.
• columns_priv: Contains column-level privileges.
• procs_priv: Contains stored procedure and function privileges.
• proxies_priv: Contains proxy-user privileges.
  

Specifying Account Names:

MySQL account names consist of a username and a hostname. This enables the creation of accounts for users with the same name who can connect from different hosts. This section describes how to write account names, including special values and wildcard rules. In SQL statements such as CREATE USER, GRANT, and SET PASSWORD, write account names using the following rules:

• Syntax for account names is 'user_name'@'host_name'.
• An account name consisting only of a username is equivalent to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.
• The username and hostname need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, 'test-user'@'%.com'.
• Quote usernames and hostnames as identifiers or as strings, using either backtick (“`”), single quotation marks (“'”), or double quotation marks (“"”).
• The username and hostname parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is interpreted as 'me@localhost'@'%'.
• A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client's username and hostname literally.

Access Control, Stage 1: Connection Verification:

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests. Your identity is based on two pieces of information :

• The client host from which you connect.
• Your MySQL user name.

Access Control, Stage 2: Connection Verification:

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, tables_priv, columns_priv, or procs_priv tables.

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! It is wise to grant privileges in the user table only to people who need them, such as database administrators.

The db table grants database-specific privileges. Values in the scope columns of this table can take the following forms :

• A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in usernames.
• The wildcard characters “%” and “_” can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (“_”) as part of a database name, specify it as “\_” in the GRANT statement.
• A '%' or blank Host value means "any host."
• A '%' or blank Db value means "any database."

When Privilege Changes Take Effect :

When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point. If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Causes of Access-Denied Errors:

If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

• Make sure that the server is running. If it is not, clients cannot connect to it.
• It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To find out where the socket file is, you can use this command:

   shell> netstat -ln | grep mysql

• Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces.
• Check to make sure that there is no firewall blocking access to MySQL.
• The grant tables must be properly set up so that the server can use them for access control.
• After a fresh installation, you should connect to the server and set up your users and their access permissions :

  shell> mysql -u root mysql 

• If you have updated an existing MySQL installation to a newer version, run the mysql_upgrade script.
• If a client program receives the error message "Client does not support authentication protocol requested by server; consider upgrading MySQL client" it means that the server expects passwords in a newer format than the client is capable of generating.
• If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check any applicable option files and your environment.
• If you get the error message "Access denied for user 'root'@'localhost' (using password: YES)", it means that you are using an incorrect root password.
• If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work.
• localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly.
• The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password.
• If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table.
• If you get the error message "Host ... is not allowed to connect to this MySQL server", when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host.
• If you specify a hostname when trying to connect, but get an error message where the hostname is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:

MySQL User Account Management

UserNames and Passwords:

MySQL stores accounts in the user table of the mysql database. An account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account may also have a password

Adding and removing user accounts:

You can create MySQL accounts in two ways:

• By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.
• By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.

To remove an account, use the DROP USER statement,

Setting Account Resource Limits:

In MySQL 5.6, you can limit use of the following server resources for individual accounts:

• The number of queries that an account can issue per hour
• The number of updates that an account can issue per hour
• The number of times an account can connect to the server per hour
• The number of simultaneous connections to the server by an account

Assigning Account Passwords:

Required credentials for clients that connect to the MySQL server can include a password. In MySQL 5.6, it is also possible for clients to authenticate using plugins.

To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause :

mysql> CREATE USER 'user'@'localhost'    -> IDENTIFIED BY 'mypass';

To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement :

mysql> SET PASSWORD FOR -> 'user'@'localhost' = PASSWORD('mypass');

Reference: MySQL 5.6 Manual

Previous: MySQL Views
Next: MySQL Show Commands