Mysql Administration - Security

Security

General Security Issues  

• General factors that affect security. These include choosing good passwords, not granting unnecessary privileges to users, ensuring application security by preventing SQL injections and data corruption, 
• Security of the installation itself. The data files, log files, and the all the application files of your installation should be protected to ensure that they are not readable or writable by unauthorized parties. 
• Access control and security within the database system itself, including the users and databases granted with access to the databases, views and stored programs in use within the database. For more information, 
• The features offered by security-related plugins. See Section 6.5, “Security Plugins”. 
• Network security of MySQL and your system. The security is related to the grants for individual users, but you may also wish to restrict MySQL so that it is available only locally on the MySQL server host, or to a limited set of other hosts. 
• Ensure that you have adequate and appropriate backups of your database files, configuration and log files. Also be sure that you have a recovery solution in place and test that you are able to successfully recover the information from your backups.

Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! 

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 support for SSL-encrypted connections between MySQL clients and servers.

Checklist

1) Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! 

2) Learn how the MySQL access privilege system works,Use the GRANT and REVOKE statements to control access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts

• Try mysql -u root. If you are able to connect successfully to the server without being asked for a password
• Use the SHOW GRANTS statement to check which accounts have access to what. 

3) Do not store cleartext passwords in your database. - 

use SHA2(), SHA1(), MD5(), or some other one-way hashing function and store the hash value

4) 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).

• 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
• shell> telnet server_host 3306

5) Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques 

See Section 6.1.7, “Client Programming Security Guidelines”.

6) 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.

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

How to Run MySQL as a Normal User

1. Stop the server if it is running (use mysqladmin shutdown). 
2. Change the database directories and files so that user_name has privileges to read and write files in them (you might need to do this as the Unix root user):
   shell> chown -R user_name /path/to/mysql/datadir              If you do not do this, the server will not be able to access databases or tables when it runs as user_name. 
3. If directories or files within the MySQL data directory are symbolic links, chown -R might not follow symbolic links for you. If it does not, you will also need to follow those links and change the directories and files they point to. 
4. Start the server as user user_name. Another alternative is to start mysqld as the Unix root user and use the --user=user_name option. mysqld starts up, then switches to run as the Unix user user_name before accepting any connections. 
5. To start the server as the given user automatically at system startup time, specify the user name by adding a user option 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=user_name

Privileges Provided by MySQL

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

• Administrative privileges enable users to manage 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).

Table 6.2 Permissible Privileges for GRANT and REVOKE

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
LOCK TABLES	Lock_tables_priv	databases
REFERENCES	References_priv	databases or tables
EVENT	Event_priv	databases
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
INDEX	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
TRIGGER	Trigger_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	Alter_routine_priv	stored routines
CREATE ROUTINE	Create_routine_priv	stored routines
EXECUTE	Execute_priv	stored routines
FILE	File_priv	file access on server host
CREATE TABLESPACE	Create_tablespace_priv	server administration
CREATE USER	Create_user_priv	server administration
PROCESS	Process_priv	server administration
PROXY	see proxies_priv table	server administration
RELOAD	Reload_priv	server administration
REPLICATION CLIENT	Repl_client_priv	server administration
REPLICATION SLAVE	Repl_slave_priv	server administration
SHOW DATABASES	Show_db_priv	server administration
SHUTDOWN	Shutdown_priv	server administration
SUPER	Super_priv	server administration
ALL [PRIVILEGES]		server administration
USAGE		server administration

Privilege

Column

Context

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'

    ->     WITH GRANT OPTION;

mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'

    ->     WITH GRANT OPTION;

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> CREATE USER 'dummy'@'localhost';

mysql> SHOW GRANTS FOR 'admin'@'localhost';

+-----------------------------------------------------+

| Grants for admin@localhost                          |

+-----------------------------------------------------+

| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |

+-----------------------------------------------------+

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

    ->     ON bankaccount.*

    ->     TO 'custom'@'localhost';

mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

    ->     ON expenses.*

    ->     TO 'custom'@'host47.example.com';

mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

    ->     ON customer.*

    ->     TO 'custom'@'%.example.com';

To address such concerns, MySQL permits limits for individual accounts on use of these server resources:

• The number of queries an account can issue per hour 
• The number of updates 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

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

    ->     WITH MAX_QUERIES_PER_HOUR 20

    ->          MAX_UPDATES_PER_HOUR 10

    ->          MAX_CONNECTIONS_PER_HOUR 5

    ->          MAX_USER_CONNECTIONS 2;

To expire an account password, use the ALTER USER statement. For example:

ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE;

Pluggable Authentication

When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the appropriate account row from the mysql.user table. The server then authenticates the client, determining from the account row which authentication plugin applies for the client:

• If the account row specifies a plugin, the server invokes it to authenticate the user. If the server cannot find the plugin, an error occurs. 
• If the account row specifies no plugin name, the server authenticates the account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. Clients must match the password in the Password column of the account row.

Security Plugins

6.5.1 Authentication Plugins

6.5.2 The Password Validation Plugin

6.5.3 MySQL Enterprise Audit

6.5.4 MySQL Enterprise Firewall

Figure 6.1 MySQL Enterprise Firewall Operation