使用SSL安全链接Mysql数据库

一、使用SSL安全连接

To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL and your version of MySQL must be built with SSL support.

To make it easier to use secure connections, MySQL is bundled with yaSSL as of MySQL 5.0.10. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license.) yaSSL support initially was available only for a few platforms, but now it is available on all platforms supported by MySQL AB.

To get secure connections to work with MySQL and SSL, you must do the following:

  1. If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, and you are going to use OpenSSL rather than the bundled yaSSL library, install OpenSSL if it has not already been installed. We have tested MySQL with OpenSSL 0.9.6. To obtain OpenSSL, visit http://www.openssl.org.
  2. If you are not using a binary (precompiled) version of MySQL that has been built with SSL support, configure a MySQL source distribution to use SSL. When you configure MySQL, invoke the configure script with the appropriate option to select the SSL library that you want to use.

    For yaSSL:

    shell> ./configure --with-yassl

    For OpenSSL:

    shell> ./configure --with-openssl

    Before MySQL 5.0, it was also neccessary to use --with-vio, but that option is no longer required.

    Note that yaSSL support on Unix platforms requires that either /dev/urandom or /dev/random be available to retrieve true random numbers. For additional information (especially regarding yaSSL on Solaris versions prior to 2.8 and HP-UX)

  3. Make sure that you have upgraded your grant tables to include the SSL-related columns in the mysql.user table. This is necessary if your grant tables date from a version of MySQL older than 4.0.
  4. To check whether a server binary is compiled with SSL support, invoke it with the --ssl option. An error will occur if the server does not support SSL: 
    shell> mysqld --ssl --help060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'

    To check whether a running mysqld server supports SSL, examine the value of the have_openssl system variable:

    mysql> SHOW VARIABLES LIKE 'have_openssl';+---------------+-------+| Variable_name | Value |+---------------+-------+| have_openssl  | YES   |+---------------+-------+

    If the value is YES, the server supports SSL connections. If the value is DISABLED, the server supports SSL connections but was not started with the appropriate --ssl-xxx options (described later in this section). If the value is YES, the server supports SSL connections.

To start the MySQL server so that it allows clients to connect via SSL, use the options that identify the key and certificate files the server needs when establishing a secure connection:

shell> mysqld --ssl-ca=cacert.pem /--ssl-cert=server-cert.pem /--ssl-key=server-key.pem

一般情况下mysql服务器会随开机自启动，如果需要支持ssl，则修要修改配置文件/etc/mysql/my.cnf，设置ssl-ca,ssl-cert,ssl-key. 然后/etc/init.d/mysql restart
  • --ssl-ca identifies the Certificate Authority (CA) certificate.
  • --ssl-cert identifies the server public key. This can be sent to the client and authenticated against the CA certificate that it has.
  • --ssl-key identifies the server private key.

To establish a secure connection to a MySQL server with SSL support, the options that a client must specify depend on the SSL requirements of the user account that the client uses. 

If the account has no special SSL requirements or was created using a GRANT statement that includes the REQUIRE SSL option, a client can connect securely by using just the --ssl-ca option:

shell> mysql --ssl-ca=cacert.pem

To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=cacert.pem /--ssl-cert=client-cert.pem /--ssl-key=client-key.pem

In other words, the options are similar to those used for the server. Note that the Certificate Authority certificate has to be the same.

A client can determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher status variable. The value of Ssl_cipher is non-empty if SSL is used, and empty otherwise. For example:

mysql> SHOW STATUS LIKE 'Ssl_cipher';+---------------+--------------------+| Variable_name | Value              |+---------------+--------------------+| Ssl_cipher    | DHE-RSA-AES256-SHA |+---------------+--------------------+

For the mysql client, you can use the STATUS or /s command and check the SSL line:

mysql> /s...SSL:                    Not in use...

Or:

mysql> /s...SSL:                    Cipher in use is DHE-RSA-AES256-SHA...

To establish a secure connection from within an application program, use the mysql_ssl_set() C API function to set the appropriate certificate options before calling mysql_real_connect().

二、对数据库帐号设置不同的安全连接类型

There are a number of different possibilities for limiting connection types for a given account:

  • REQUIRE NONE indicates that the account has no SSL or X509 requirements. This is the default if no SSL-related REQUIRE options are specified. Unencrypted connections are allowed if the username and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files. That is, the client need not specify any SSL commmand options, in which case the connection will be unencrypted. To use an encrypted connection, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.
  • The REQUIRE SSL option tells the server to allow only SSL-encrypted connections for the account. 
    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret' REQUIRE SSL;

    To connect, the client must specify the --ssl-ca option, and may additionally specify the --ssl-key and --ssl-cert options.

  • REQUIRE X509 means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. 
    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret' REQUIRE X509;

    To connect, the client must specify the --ssl-ca, --ssl-key, and --ssl-cert options. This is also true for ISSUER and SUBJECT because those REQUIRE options imply X509.

  • REQUIRE ISSUER 'issuer' places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case. 
    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret'  REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';

    Note that the 'issuer' value should be entered as a single string.

  • REQUIRE SUBJECT 'subject' places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection. 
    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret'  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/    O=MySQL demo client certificate/    CN=Tonu Samuel/Email=tonu@example.com';

    Note that the 'subject' value should be entered as a single string.

  • REQUIRE CIPHER 'cipher' is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used. Using this option, you can ask that a specific cipher method is used to allow a connection. 
    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret'  REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'  IDENTIFIED BY 'goodsecret'  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/    O=MySQL demo client certificate/    CN=Tonu Samuel/Email=tonu@example.com'  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The AND keyword is optional between REQUIRE options.

三、为Mysql制作ssl证书

This section demonstrates how to set up SSL certificate and key files for use by MySQL servers and clients. The first example shows a simplified procedure such as you might use from the command line. The second shows a script that contains more detail. Both examples use the openssl command that is part of OpenSSL.

The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts by the openssl commands. For testing, you can press Enter to all prompts. For production use, you should provide non-empty responses.

# Create clean environmentshell> rm -rf newcertsshell> mkdir newcerts && cd newcerts

# Create CA certificateshell> openssl genrsa 2048 > ca-key.pemshell> openssl req -new -x509 -nodes -days 1000 /-key ca-key.pem > ca-cert.pem

# Create server certificateshell> openssl req -newkey rsa:2048 -days 1000 /-nodes -keyout server-key.pem > server-req.pemshell> openssl x509 -req -in server-req.pem -days 1000 /-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificateshell> openssl req -newkey rsa:2048 -days 1000 /-nodes -keyout client-key.pem > client-req.pemshell> openssl x509 -req -in client-req.pem -days 1000 /-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

 

 

时间： 2024-04-17 00:05:49

使用SSL安全链接Mysql数据库的相关文章

代码-java链接mysql数据库实现登陆验证

问题描述 java链接mysql数据库实现登陆验证 mysql[users]登陆用户表如下所示 没有连数据库的登陆界面代码如下所示 我想改一下代码,将它变成可以通过数据库验证的界面,链接数据库方式是用纯java jdbc驱动程序的方法,不用jdbc-odbc桥 解决方案 参考这篇博客的后半部分,虽然是jsp的,但是后面的jdbc可以参考.http://blog.csdn.net/chinacsharper/article/details/39777643

jap-jsp链接Mysql数据库，求理解

问题描述 jsp链接Mysql数据库,求理解 jsp链接Mysql数据库的步骤,以及要写的代码 希望能写的相信些,我就不会用java,jsp链接数据库,就是不想以后也不会,多多请教学长们!求学习,谢谢了 解决方案 看看JDBC连接数据库的几个步骤吧,很简单的. 解决方案二: jsp就是java+html要学jsp自然是要学好java,所以建议你还是应该好好去学下java,另外jsp里面链接数据库就是要用到JDBC链接,网站例子很多,一搜一大片.

java-JAVA链接MySQL数据库默认驱动位置修改 新人求助

问题描述 JAVA链接MySQL数据库默认驱动位置修改 新人求助 别人帮我给一个工程加在了MySQL数据库的驱动,我现在给其他工程加载MySQL驱动又回默认找到之前那一个怎样更改为其他的路径下的驱动呢 解决方案 你建一个libary,把数据库连接jar丢进去,以后做项目直接加载这个libary 解决方案二: 关于java通过JDBC访问mysql数据库,可以参考这里 网站:http://blog.csdn.net/hsd2012/article/details/50983902 关于java数据

c++-vs下链接mysql数据库出错

问题描述 vs下链接mysql数据库出错 数据库初始化没有问题,可以连接 使用mysql_query出现问题,无法查询 代码如下: MYSQL_RES * result = NULL; CString temp ( "select username from user_login where username = "); MYSQL_ROW row; CString douhao("'"); int res; CString i_query = temp + dou

mysql-jsp链接Mysql数据库,代码问题、求指导

问题描述 jsp链接Mysql数据库,代码问题.求指导 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName(

myeclipse链接mysql数据库报错

问题描述 myeclipse链接mysql数据库报错 Unknown system variable 'tx_read_only' Could not retrieve transation read-only status server --- The error occurred in sqlmaps/user/userSQL.xml. --- The error occurred while applying a parameter map. --- Check the user.logi

mysql-使用Entity Framework链接Mysql数据库所有浮点型字段无法显示问题

问题描述 使用Entity Framework链接Mysql数据库所有浮点型字段无法显示问题 如题,不知道为何只要是Mysql中使用了浮点型字段,在EF在项目中都无法生成实体字段,不知各位大神有没有遇到过的或是解决方案,十分感谢. 解决方案 看看是不是映射的问题,两边不对应.换成decmal看看

vb2012怎么链接mysql数据库

问题描述 vb2012怎么链接mysql数据库 vb2012链接mysql数据库时 为什么链接不上,程序中进行插入的语句无法插入,在工具中进行链接数据库时找不到mysql 数据库

Tomcat通过JNDI方式链接MySql数据库

原文:Tomcat通过JNDI方式链接MySql数据库 拷贝MySQL的JDBC驱动到Tomcat的lib路径下 配置全局数据源或者单个Web应用的局部数据源 局部数据源 在Tomcat的conf/Catalina/localhost下的 app.xml中添加 <Context path="/test" docBase="test" debug="5" reloadable="true"> <Resource