使用Hibernate，Spring和JDBC配置SSL证书 [英] Configure SSL certificates with Hibernate, Spring and JDBC

问题描述

我试图从未加密的JDBC连接中使用用户名和密码登录到我的MySQL数据库服务器，转移到使用SSL和基于证书的身份验证的连接。我在Spring MVC中使用Hibernate。我的WebAppConfig文件如下所示：

  package com。****。PolicyManager.init; 
 
 import java.util.Properties; 
 
 import javax.annotation.Resource; 
 import javax.sql.DataSource; 
 
 import org.springframework.context.annotation.Bean; 
 import org.springframework.context.annotation.ComponentScan; 
 import org.springframework.context.annotation.Configuration; 
 import org.springframework.context.annotation.PropertySource; 
 import org.springframework.core.env.Environment; 
 import org.springframework.jdbc.datasource.DriverManagerDataSource; 
 import org.springframework.orm.hibernate4.HibernateTransactionManager; 
 import org.springframework.orm.hibernate4.LocalSessionFactoryBean; 
 import org.springframework.transaction.annotation.EnableTransactionManagement; 
 import org.springframework.web.servlet.config.annotation.EnableWebMvc; 
 import org.springframework.web.servlet.view.JstlView; 
导入org.springframework.web.servlet.view.UrlBasedViewResolver; 
 
 @Configuration 
 @ComponentScan（com.sprhib）
 @EnableWebMvc 
 @EnableTransactionManagement 
 @PropertySource（classpath：application.properties） 
 public class WebAppConfig {
 
 
 private static final String PROPERTY_NAME_DATABASE_DRIVER =db.driver; 
 private static final String PROPERTY_NAME_DATABASE_PASSWORD =db.password; 
 private static final String PROPERTY_NAME_DATABASE_URL =db.urlSSL; 
 private static final String PROPERTY_NAME_DATABASE_USERNAME =db.username; 
 
 private static final String PROPERTY_NAME_HIBERNATE_DIALECT =hibernate.dialect; 
 private static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL =hibernate.show_sql; 
 private static final String PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN =entitymanager.packages.to.scan; 
 
 @Resource 
 private环境env; 
 
 @Bean 
 public DataSource dataSource（）{
 DriverManagerDataSource dataSource = new DriverManagerDataSource（）; 
 
 dataSource.setDriverClassName（env.getRequiredProperty（PROPERTY_NAME_DATABASE_DRIVER））; 
 dataSource.setUrl（env.getRequiredProperty（PROPERTY_NAME_DATABASE_URL））; 
 dataSource.setUsername（env.getRequiredProperty（PROPERTY_NAME_DATABASE_USERNAME））; 
 dataSource.setPassword（env.getRequiredProperty（PROPERTY_NAME_DATABASE_PASSWORD））; 
 
返回dataSource; 
 
 
 $Be 
 public LocalSessionFactoryBean sessionFactory（）{
 LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean（）; 
 sessionFactoryBean.setDataSource（dataSource（））; 
 sessionFactoryBean.setPackagesToScan（env.getRequiredProperty（
 PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN））; 
 sessionFactoryBean.setHibernateProperties（hibProperties（））; 
 return sessionFactoryBean; 
 
 
私有属性hibProperties（）{
属性properties = new Properties（）; 
 properties.put（PROPERTY_NAME_HIBERNATE_DIALECT，
 env.getRequiredProperty（PROPERTY_NAME_HIBERNATE_DIALECT））; 
 properties.put（PROPERTY_NAME_HIBERNATE_SHOW_SQL，
 env.getRequiredProperty（PROPERTY_NAME_HIBERNATE_SHOW_SQL））; 
返回属性; 
 
 $ b $Be 
 public HibernateTransactionManager transactionManager（）{
 HibernateTransactionManager transactionManager = $ b $ new HibernateTransactionManager（）; 
 transactionManager.setSessionFactory（sessionFactory（）。getObject（））; 
返回transactionManager; 
 
 
 $Be 
 public UrlBasedViewResolver setupViewResolver（）{
 UrlBasedViewResolver resolver = new UrlBasedViewResolver（）; 
 resolver.setPrefix（/ WEB-INF / pages /）; 
 resolver.setSuffix（。jsp）; 
 resolver.setViewClass（JstlView.class）; 
返回解析器; 
} 
 
} 
  

我的属性配置文件.properties），如下所示：

  #DB属性：
 db.driver = com.mysql.jdbc.Driver 
 db.url = jdbc：mysql：// localhost：3306 / PolicyManager 
 db.urlSSL = jdbc：mysql：// localhost：3306 / PolicyManager？autoReconnect = true& amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; useSSL = true< requireSSL = true 
 db.username = myuser 
 db.password = .dialect.MySQL5InnoDBDialect 
 hibernate.show_sql = true 
 entitymanager.packages.to.scan = com。****。PolicyManager.model 
  

我在/ etc / mysql / certs里面生成了正确的证书，并且编辑了my.cnf指向那个，但是在网上找不到任何关于如何配置我的数据库初始化的特定方法，以使用基于证书的身份验证来删除存储我的数据库用户名和密码的需要ord在服务器上以纯文本格式。

任何人都可以推荐一个解决方案，或者指向一个使用此WebAppConfig.java文件（hib属性，DriverManagerDataSource和LocalSessionFactoryBean）进行配置的教程吗？

解决方案

MySQL指南 information 关于在客户端做什么，这个bug 也有一些详细的信息。

它基本上是按照以下步骤完成的：

1. 创建密钥库和信任库使用您的客户端证书
   
2. 配置环境（或MysqlDataSource）以使用这些密钥库和信任库


3. 正确配置连接URL（这就是您显然已经完成了）。

就是这样。关键是在客户端拥有正确的证书。

更多信息：

1. 从GlassFish安全地连接MySQL到MySQL


2. 通过Java保护JDBC与MySQL的连接


3. MySQL SSL文档

I'm trying to move from an unencrypted JDBC connection using a username and password to log in to my MySQL database server, to a connection using SSL and certificate-based authentication. I'm using Hibernate with Spring MVC. My WebAppConfig file looks like this:

package com.****.PolicyManager.init;

import java.util.Properties;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateTransactionManager;
import org.springframework.orm.hibernate4.LocalSessionFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.JstlView;
import org.springframework.web.servlet.view.UrlBasedViewResolver;

@Configuration
@ComponentScan("com.sprhib")
@EnableWebMvc
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class WebAppConfig {


    private static final String PROPERTY_NAME_DATABASE_DRIVER = "db.driver";
    private static final String PROPERTY_NAME_DATABASE_PASSWORD = "db.password";
    private static final String PROPERTY_NAME_DATABASE_URL = "db.urlSSL";
    private static final String PROPERTY_NAME_DATABASE_USERNAME = "db.username";

    private static final String PROPERTY_NAME_HIBERNATE_DIALECT = "hibernate.dialect";
    private static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";
    private static final String PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN = "entitymanager.packages.to.scan";

    @Resource
    private Environment env;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(env.getRequiredProperty(PROPERTY_NAME_DATABASE_DRIVER));
        dataSource.setUrl(env.getRequiredProperty(PROPERTY_NAME_DATABASE_URL));
        dataSource.setUsername(env.getRequiredProperty(PROPERTY_NAME_DATABASE_USERNAME));
        dataSource.setPassword(env.getRequiredProperty(PROPERTY_NAME_DATABASE_PASSWORD));

        return dataSource;
    }

    @Bean
    public LocalSessionFactoryBean sessionFactory() {
        LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource());
        sessionFactoryBean.setPackagesToScan(env.getRequiredProperty(
                PROPERTY_NAME_ENTITYMANAGER_PACKAGES_TO_SCAN));
        sessionFactoryBean.setHibernateProperties(hibProperties());
        return sessionFactoryBean;
    }

    private Properties hibProperties() {
        Properties properties = new Properties();
        properties.put(PROPERTY_NAME_HIBERNATE_DIALECT, 
                env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_DIALECT));
        properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL, 
                env.getRequiredProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));
        return properties;  
    }

    @Bean
    public HibernateTransactionManager transactionManager() {
        HibernateTransactionManager transactionManager = 
                new HibernateTransactionManager();
        transactionManager.setSessionFactory(sessionFactory().getObject());
        return transactionManager;
    }

    @Bean
    public UrlBasedViewResolver setupViewResolver() {
        UrlBasedViewResolver resolver = new UrlBasedViewResolver();
        resolver.setPrefix("/WEB-INF/pages/");
        resolver.setSuffix(".jsp");
        resolver.setViewClass(JstlView.class);
        return resolver;
    }

}

And my properties config file (application.properties) as follows:

#DB properties:
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/PolicyManager
db.urlSSL=jdbc:mysql://localhost:3306/PolicyManager?autoReconnect=true&verifyServerCertificate=false&useSSL=true&requireSSL=true
db.username=myuser
db.password=mypass

#Hibernate Configuration:
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.show_sql=true
entitymanager.packages.to.scan=com.****.PolicyManager.model

I've generated the right certificates inside /etc/mysql/certs and have edited my.cnf to point to then, but can't find any info online about how to configure my specific method of database initialisation to use certificate-based authentication to remove the need to store my database username and password in plain text on the server.

Can anyone suggest a solution or point me to a tutorial that uses this WebAppConfig.java file (hib properties, DriverManagerDataSource and LocalSessionFactoryBean) for it's configuration?

解决方案

The MySQL guide has information on what to do on the client side, this bug also has some detailed information.

It basically comes done to the following steps

1. Create a keystore and truststore with your clients certificate
2. Configure your environment (or a MysqlDataSource) to use these keystore and truststore
3. Configure the connection URL properly (which is what you apparently already have done).

And that should be it. The key is to have the correct certificates on the client side.

More information:

1. Secure JDBC connection to MySQL from GlassFish
2. Secure JDBC connection to MySQL from Java
3. MySQL SSL Documentation

这篇关于使用Hibernate，Spring和JDBC配置SSL证书的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！