JDBC Authentication

Spring Security 的 JdbcDaoImpl`实现了 `UserDetailsService来提供对基于用户名和密码的身份验证的支持，该身份验证可通过使用 JDBC 检索。JdbcUserDetailsManager`扩展了 `JdbcDaoImpl，通过 `UserDetailsManager`接口提供 `UserDetails`的管理。当 Spring Security 配置为 accept a username/password进行身份验证时，会使用基于 `UserDetails`的身份验证。

Spring Security’s JdbcDaoImpl implements UserDetailsService to provide support for username-and-password-based authentication that is retrieved by using JDBC. JdbcUserDetailsManager extends JdbcDaoImpl to provide management of UserDetails through the UserDetailsManager interface. UserDetails-based authentication is used by Spring Security when it is configured to accept a username/password for authentication.

在以下部分，我们将讨论：

In the following sections, we discuss:

The Default Schema used by Spring Security JDBC Authentication
Setting up a DataSource
JdbcUserDetailsManager Bean

Default Schema

Spring Security 提供 JDBC 身份验证的默认查询。这部分提供了与默认查询一同使用的相应默认模式。你需要调整模式，以与查询的任何自定义项和你使用的数据库方言相匹配。

Spring Security provides default queries for JDBC-based authentication. This section provides the corresponding default schemas used with the default queries. You need to adjust the schema to match any customizations to the queries and the database dialect you use.

User Schema

JdbcDaoImpl 需要表来加载密码、帐户状态（已启用或已禁用）以及用户的权限列表（角色）。

JdbcDaoImpl requires tables to load the password, account status (enabled or disabled) and a list of authorities (roles) for the user.

默认模式也作为名为 org/springframework/security/core/userdetails/jdbc/users.ddl 的类路径资源公开。

The default schema is also exposed as a classpath resource named org/springframework/security/core/userdetails/jdbc/users.ddl.

Default User Schema

create table users(
	username varchar_ignorecase(50) not null primary key,
	password varchar_ignorecase(500) not null,
	enabled boolean not null
);

create table authorities (
	username varchar_ignorecase(50) not null,
	authority varchar_ignorecase(50) not null,
	constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

Oracle 是一个流行的数据库选择，但要求一个略有不同的模式：

Oracle is a popular database choice but requires a slightly different schema:

Default User Schema for Oracle Databases

CREATE TABLE USERS (
    USERNAME NVARCHAR2(128) PRIMARY KEY,
    PASSWORD NVARCHAR2(128) NOT NULL,
    ENABLED CHAR(1) CHECK (ENABLED IN ('Y','N') ) NOT NULL
);


CREATE TABLE AUTHORITIES (
    USERNAME NVARCHAR2(128) NOT NULL,
    AUTHORITY NVARCHAR2(128) NOT NULL
);
ALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_UNIQUE UNIQUE (USERNAME, AUTHORITY);
ALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_FK1 FOREIGN KEY (USERNAME) REFERENCES USERS (USERNAME) ENABLE;

Group Schema

如果你的应用程序使用组，你需要提供组模式：

If your application uses groups, you need to provide the groups schema:

Default Group Schema

create table groups (
	id bigint generated by default as identity(start with 0) primary key,
	group_name varchar_ignorecase(50) not null
);

create table group_authorities (
	group_id bigint not null,
	authority varchar(50) not null,
	constraint fk_group_authorities_group foreign key(group_id) references groups(id)
);

create table group_members (
	id bigint generated by default as identity(start with 0) primary key,
	username varchar(50) not null,
	group_id bigint not null,
	constraint fk_group_members_group foreign key(group_id) references groups(id)
);

Setting up a DataSource

在我们配置 JdbcUserDetailsManager 之前，我们必须创建一个 DataSource。在我们的示例中，我们设置了一个 embedded DataSource，它使用 default user schema 初始化。

Before we configure JdbcUserDetailsManager, we must create a DataSource. In our example, we set up an embedded DataSource that is initialized with the servlet-authentication-jdbc-schema.

Embedded Data Source

Java
XML
Kotlin

@Bean
DataSource dataSource() {
	return new EmbeddedDatabaseBuilder()
		.setType(H2)
		.addScript(JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION)
		.build();
}

<jdbc:embedded-database>
	<jdbc:script location="classpath:org/springframework/security/core/userdetails/jdbc/users.ddl"/>
</jdbc:embedded-database>

@Bean
fun dataSource(): DataSource {
    return EmbeddedDatabaseBuilder()
        .setType(H2)
        .addScript(JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION)
        .build()
}

在生产环境中，你应该确保建立到外部数据库的连接。

In a production environment, you want to ensure that you set up a connection to an external database.

JdbcUserDetailsManager Bean

在此示例中，我们使用 Spring Boot CLI对密码值 password`进行编码并获取编码后的密码 `{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW。有关如何存储密码的更多详细信息，请参阅 PasswordEncoder部分。

In this sample, we use Spring Boot CLI to encode a password value of password and get the encoded password of {bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW. See the PasswordEncoder section for more details about how to store passwords.

JdbcUserDetailsManager

Java
XML
Kotlin

@Bean
UserDetailsManager users(DataSource dataSource) {
	UserDetails user = User.builder()
		.username("user")
		.password("{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")
		.roles("USER")
		.build();
	UserDetails admin = User.builder()
		.username("admin")
		.password("{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")
		.roles("USER", "ADMIN")
		.build();
	JdbcUserDetailsManager users = new JdbcUserDetailsManager(dataSource);
	users.createUser(user);
	users.createUser(admin);
	return users;
}

<jdbc-user-service>
	<user name="user"
		password="{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW"
		authorities="ROLE_USER" />
	<user name="admin"
		password="{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW"
		authorities="ROLE_USER,ROLE_ADMIN" />
</jdbc-user-service>

@Bean
fun users(dataSource: DataSource): UserDetailsManager {
    val user = User.builder()
            .username("user")
            .password("{bcrypt}$2a$10\$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")
            .roles("USER")
            .build();
    val admin = User.builder()
            .username("admin")
            .password("{bcrypt}$2a$10\$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")
            .roles("USER", "ADMIN")
            .build();
    val users = JdbcUserDetailsManager(dataSource)
    users.createUser(user)
    users.createUser(admin)
    return users
}