1. Overview
1.概述
In this short tutorial, we’ll explore the capabilities offered by Spring to perform JDBC Authentication using an existing DataSource configuration.
在这个简短的教程中，我们将探索Spring提供的功能，以使用现有的DataSource配置执行JDBC认证。
In our Authentication with a Database-backed UserDetailsService post, we analyzed one approach to achieve this, by implementing the UserDetailService interface ourselves.
在我们的使用数据库支持的UserDetailsService的认证文章中，我们分析了实现这一目标的一种方法，即通过自己实现UserDetailService接口。
This time, we’ll make use of the AuthenticationManagerBuilder#jdbcAuthentication directive to analyze the pros and cons of this simpler approach.
这一次，我们将利用AuthenticationManagerBuilder#jdbcAuthentication指令来分析这种更简单的方法的利弊。
2. Using an Embedded H2 Connection
2.使用嵌入式H2连接
First of all, we’ll analyze how we can achieve authentication using an embedded H2 database.
首先，我们将分析如何利用嵌入式H2数据库实现认证。
This is easy to achieve because most of the Spring Boot’s autoconfiguration are prepared out-of-the-box for this scenario.
这很容易实现，因为Spring Boot的大多数自动配置都是为这种情况准备的，开箱即用。
2.1. Dependencies and Database Configuration
2.1.依赖关系和数据库配置
Let’s start by following the instructions of our previous Spring Boot With H2 Database post to:
让我们先按照我们之前的Spring Boot With H2 Database帖子的指示来做。
- Include the corresponding spring-boot-starter-data-jpa and h2 dependencies
- Configure the database connection with application properties
- Enable the H2 console
2.2. Configuring JDBC Authentication
2.2.配置JDBC认证
We’ll use Spring Security’s AuthenticationManagerBuilder configuration helper to configure JDBC Authentication:
我们将使用Spring Security的AuthenticationManagerBuilder 配置帮助器来配置JDBC认证：。
@Autowired
private DataSource dataSource;
@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)
throws Exception {
auth.jdbcAuthentication()
.dataSource(dataSource)
.withDefaultSchema()
.withUser(User.withUsername("user")
.password(passwordEncoder().encode("pass"))
.roles("USER"));
}
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}As we can see, we’re using the autoconfigured DataSource. The withDefaultSchema directive adds a database script that will populate the default schema, allowing users and authorities to be stored.
正如我们所见，我们正在使用自动配置的DataSource。withDefaultSchema指令添加了一个数据库脚本，将填充默认模式，允许存储用户和权限。
This basic user schema is documented in the Spring Security Appendix.
这种基本的用户模式在Spring安全附录中有所记载。
Finally, we’re creating an entry in the database with a default user programmatically.
最后，我们以编程方式在数据库中创建一个带有默认用户的条目。
2.3. Verifying the Configuration
2.3.验证配置
Let’s create a very simple endpoint to retrieve the authenticated Principal information:
让我们创建一个非常简单的端点来检索认证的Principal信息。
@RestController
@RequestMapping("/principal")
public class UserController {
@GetMapping
public Principal retrievePrincipal(Principal principal) {
return principal;
}
}In addition, we’ll secure this endpoint, whilst permitting access to the H2 console:
此外，我们将保护这个端点，同时允许对H2控制台的访问。
@Configuration
public class SecurityConfiguration {
@Bean
public SecurityFilterChain filterChain(HttpSecurity httpSecurity)
throws Exception {
httpSecurity.authorizeRequests()
.antMatchers("/h2-console/**")
.permitAll()
.anyRequest()
.authenticated()
.and()
.formLogin();
httpSecurity.csrf()
.ignoringAntMatchers("/h2-console/**");
httpSecurity.headers()
.frameOptions()
.sameOrigin();
return http.build();
}
}Note: here we’re reproducing the former security configuration implemented by Spring Boot, but in a real-life scenario, we probably won’t enable the H2 console at all.
注意：在这里我们重现了Spring Boot实现的前安全配置，但是在现实生活中，我们可能根本不会启用H2控制台。。
Now we’ll run the application and browse the H2 console. We can verify that Spring is creating two tables in our embedded database: users and authorities.
现在我们运行该应用程序并浏览H2控制台。我们可以验证，Spring正在我们的嵌入式数据库中创建两个表。users和authorities。
Their structure corresponds to the structure defined in the Spring Security Appendix we mentioned before.
它们的结构与我们之前提到的Spring Security附录中定义的结构相一致。
Finally, let’s authenticate and request the /principal endpoint to see the related information, including the user details.
最后，让我们认证并请求/principal 端点来查看相关信息，包括用户的详细信息。
2.4. Under the Hood
2.4.引擎盖下
At the beginning of this post, we presented a link to a tutorial that explained how we can customize database-backed authentication implementing the UserDetailsService interface; we strongly recommend having a look at that post if we want to understand how things work under the hood.
在这篇文章的开头，我们提供了一个教程的链接，该教程解释了我们如何实现UserDetailsService界面的自定义数据库支持的身份验证；如果我们想了解事情在幕后的运作，我们强烈建议看一下那篇文章。
In this case, we’re relying on an implementation of this same interface provided by Spring Security; the JdbcDaoImpl.
在这种情况下，我们依赖于Spring Security提供的这个相同接口的实现；JdbcDaoImpl.。
If we explore this class, we’ll see the UserDetails implementation it uses, and the mechanisms to retrieve user information from the database.
如果我们探索这个类，我们会看到它使用的UserDetails实现，以及从数据库中检索用户信息的机制。
This works pretty well for this simple scenario, but it has some drawbacks if we want to customize the database schema, or even if we want to use a different database vendor.
这对这个简单的场景来说效果很好，但如果我们想定制数据库模式，甚至想使用不同的数据库供应商，它就有一些缺点。
Let’s see what happens if we change the configuration to use a different JDBC service.
让我们看看如果我们改变配置以使用不同的JDBC服务会发生什么。
3. Adapting the Schema for a Different Database
3.为不同的数据库调整模式
In this section, we’ll configure authentication on our project using a MySQL database.
在本节中，我们将使用MySQL数据库在我们的项目中配置认证。
As we’ll see next, in order to achieve this, we’ll need to avoid using the default schema and provide our own.
正如我们接下来要看到的，为了实现这一目标，我们需要避免使用默认模式，而提供我们自己的模式。
3.1. Dependencies and Database Configuration
3.1.依赖关系和数据库配置
For starters, let’s remove the h2 dependency and replace it for the corresponding MySQL library:
首先，让我们删除h2依赖，并将其替换为相应的MySQL库。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>As always, we can look up the latest version of the library in Maven Central.
一如既往，我们可以在Maven Central中查找该库的最新版本。
Now let’s re-set the application properties accordingly:
现在让我们相应地重新设置一下应用程序的属性。
spring.datasource.url=
jdbc:mysql://localhost:3306/jdbc_authentication
spring.datasource.username=root
spring.datasource.password=pass3.2. Running the Default Configuration
3.2.运行默认配置
Of course, these should be customized to connect to your running MySQL server. For testing purposes, here we’ll start a new instance using Docker:
当然，这些应该被定制，以连接到你正在运行的MySQL服务器。为测试目的，这里我们将使用Docker启动一个新的实例。
docker run -p 3306:3306
--name bael-mysql
-e MYSQL_ROOT_PASSWORD=pass
-e MYSQL_DATABASE=jdbc_authentication
mysql:latestLet’s run the project now to see if the default configuration is suitable for a MySQL database.
现在让我们运行该项目，看看默认配置是否适合MySQL数据库。
Actually, the application won’t be able to get started, because of an SQLSyntaxErrorException. This actually makes sense; as we said, most of the default autoconfiguration is suitable for an HSQLDB.
实际上，应用程序将无法启动，因为有一个SQL语法错误异常。这实际上是有道理的；正如我们所说的，大多数默认的自动配置都适合HSQLDB。
In this case, the DDL script provided with the withDefaultSchema directive uses a dialect not suitable for MySQL.
在这种情况下，随withDefaultSchema指令提供的DDL脚本使用了不适合MySQL的方言。
Therefore, we need to avoid using this schema and provide our own.
因此，我们需要避免使用这种模式，而提供我们自己的模式。
3.3. Adapting the Authentication Configuration
3.3.调整认证配置
As we don’t want to use the default schema, we’ll have to remove the proper statement from the AuthenticationManagerBuilder configuration.
由于我们不想使用默认模式，我们必须从AuthenticationManagerBuilder配置中删除适当的语句。
Also, since we’ll be providing our own SQL scripts, we can avoid trying to create the user programmatically:
此外，由于我们将提供我们自己的SQL脚本，我们可以避免尝试以编程方式创建用户。
@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)
throws Exception {
auth.jdbcAuthentication()
.dataSource(dataSource);
}Now let’s have a look at the database initialization scripts.
现在让我们来看看数据库初始化脚本。
First, our schema.sql:
首先，我们的schema.sql。
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
enabled TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY (username)
);
CREATE TABLE authorities (
username VARCHAR(50) NOT NULL,
authority VARCHAR(50) NOT NULL,
FOREIGN KEY (username) REFERENCES users(username)
);
CREATE UNIQUE INDEX ix_auth_username
on authorities (username,authority);And then, our data.sql:
然后，我们的data.sql。
-- User user/pass
INSERT INTO users (username, password, enabled)
values ('user',
'$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
1);
INSERT INTO authorities (username, authority)
values ('user', 'ROLE_USER');Finally, we should modify some other application properties:
最后，我们应该修改其他一些应用程序的属性。
- Since we’re not expecting Hibernate to create the schema now, we should disable the ddl-auto property
- By default, Spring Boot initializes the data source only for embedded databases, which is not the case here:
spring.sql.init.mode=always
spring.jpa.hibernate.ddl-auto=noneAs a result, we should now be able to start our application correctly, authenticating and retrieving the Principal data from the endpoint.
因此，我们现在应该能够正确地启动我们的应用程序，验证并从端点检索Principal数据。
Also, note that the spring.sql.init.mode property was introduced in Spring Boot 2.5.0; for earlier versions, we need to use spring.datasource.initialization-mode.
此外，请注意，spring.sql.init.mode属性是在Spring Boot 2.5.0中引入的；对于早期版本，我们需要使用spring.datasource.initialization-mode.。
4. Adapting the Queries for a Different Schema
4.为不同的模式调整查询方式
Let’s go a step further. Imagine the default schema is just not suitable for our needs.
让我们再往前走一步。想象一下，默认模式就是不适合我们的需要。
4.1. Changing the Default Schema
4.1.改变默认模式
Imagine, for example, that we already have a database with a structure that slightly differs from the default one:
例如，想象一下，我们已经有一个数据库，其结构与默认结构略有不同。
CREATE TABLE bael_users (
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
enabled TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY (email)
);
CREATE TABLE authorities (
email VARCHAR(50) NOT NULL,
authority VARCHAR(50) NOT NULL,
FOREIGN KEY (email) REFERENCES bael_users(email)
);
CREATE UNIQUE INDEX ix_auth_email on authorities (email,authority);Finally, our data.sql script will be adapted to this change too:
最后，我们的data.sql脚本也将适应这一变化。
-- User user@email.pass/pass
INSERT INTO bael_users (name, email, password, enabled)
values ('user',
'user@email.com',
'$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
1);
INSERT INTO authorities (email, authority)
values ('user@email.com', 'ROLE_USER');4.2. Running the Application with the New Schema
4.2.使用新模式运行应用程序
Let’s launch our application. It initializes correctly, which makes sense since our schema is correct.
让我们启动我们的应用程序。它初始化正确，这是有道理的，因为我们的模式是正确的。
Now, if we try to log in, we’ll find an error is prompted when presenting the credentials.
现在，如果我们试图登录，我们会发现在出示凭证时提示了一个错误。
Spring Security is still looking for a username field in the database. Lucky for us, the JDBC Authentication configuration offers the possibility of customizing the queries used to retrieve user details in the authentication process.
Spring Security仍然在寻找数据库中的username 字段。我们很幸运，JDBC认证配置提供了在认证过程中自定义用于检索用户详细信息的查询的可能性。
4.3. Customizing the Search Queries
4.3.定制搜索查询
Adapting the queries is quite easy. We simply have to provide our own SQL statements when configuring the AuthenticationManagerBuilder:
调整查询是非常容易的。我们只需在配置AuthenticationManagerBuilder时提供我们自己的SQL语句。
@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)
throws Exception {
auth.jdbcAuthentication()
.dataSource(dataSource)
.usersByUsernameQuery("select email,password,enabled "
+ "from bael_users "
+ "where email = ?")
.authoritiesByUsernameQuery("select email,authority "
+ "from authorities "
+ "where email = ?");
}We can launch the application once more, and access the /principal endpoint using the new credentials.
我们可以再次启动应用程序，并使用新的凭证访问/principal端点。
5. Conclusion
5.总结
As we can see, this approach is much simpler than having to create our own UserDetailService implementation, which implies an arduous process; creating entities and classes implementing the UserDetail interface and adding repositories to our project.
正如我们所看到的，这种方法比创建我们自己的UserDetailService实现要简单得多，后者意味着一个艰巨的过程；创建实现UserDetail接口的实体和类，并向我们的项目添加存储库。
The drawback is, of course, the little flexibility it offers when our database or our logic differs from the default strategy provided by the Spring Security solution.
当然，缺点是当我们的数据库或我们的逻辑与Spring Security解决方案提供的默认策略不同时，它提供的灵活性很小。
Lastly, we can have a look at the complete examples in our GitHub repository. We even included an example using PostgreSQL that we didn’t show in this tutorial, just to keep things simple.
最后，我们可以看看我们的GitHub资源库中的完整示例。我们甚至包括了一个使用PostgreSQL的例子，我们没有在本教程中展示，只是为了让事情变得简单。