spring-security/docs/modules/ROOT/pages/servlet/authentication/passwords/jdbc.adoc

[[servlet-authentication-jdbc]]
= JDBC Authentication

Spring Security's `JdbcDaoImpl` implements xref:servlet/authentication/passwords/user-details-service.adoc#servlet-authentication-userdetailsservice[UserDetailsService] to provide support for username/password based authentication that is retrieved 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 xref:servlet/authentication/passwords/index.adoc#servlet-authentication-unpwd-input[accept a username/password] for authentication.

In the following sections we will discuss:

* The <<servlet-authentication-jdbc-schema>> used by Spring Security JDBC Authentication
* <<servlet-authentication-jdbc-datasource>>
* <<servlet-authentication-jdbc-bean>>

[[servlet-authentication-jdbc-schema]]
== Default Schema

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

[[servlet-authentication-jdbc-schema-user]]
=== User Schema

`JdbcDaoImpl` requires tables to load the password, account status (enabled or disabled) and a list of authorities (roles) for the user.
The default schema required can be found below.

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

.Default User Schema
[source,sql]
----
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 is a popular database choice, but requires a slightly different schema.
You can find the default Oracle Schema for users below.

.Default User Schema for Oracle Databases
[source,sql]
----
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;
----

[[servlet-authentication-jdbc-schema-group]]
=== Group Schema

If your application is leveraging groups, you will need to provide the groups schema.
The default schema for groups can be found below.

.Default Group Schema
[source,sql]
----
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)
);
----

[[servlet-authentication-jdbc-datasource]]
== Setting up a DataSource

Before we configure `JdbcUserDetailsManager`, we must create a `DataSource`.
In our example, we will setup an https://docs.spring.io/spring-framework/docs/current/spring-framework-reference/data-access.html#jdbc-embedded-database-support[embedded DataSource] that is initialized with the <<servlet-authentication-jdbc-schema,default user schema>>.

.Embedded Data Source
[tabs]
======
Java::
+
[source,java,role="primary"]
----
@Bean
DataSource dataSource() {
	return new EmbeddedDatabaseBuilder()
		.setType(H2)
		.addScript(JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION)
		.build();
}
----

XML::
+
[source,xml,role="secondary"]
----
<jdbc:embedded-database>
	<jdbc:script location="classpath:org/springframework/security/core/userdetails/jdbc/users.ddl"/>
</jdbc:embedded-database>
----

Kotlin::
+
[source,kotlin,role="secondary"]
----
@Bean
fun dataSource(): DataSource {
    return EmbeddedDatabaseBuilder()
        .setType(H2)
        .addScript(JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION)
        .build()
}
----
======

In a production environment, you will want to ensure you setup a connection to an external database.

[[servlet-authentication-jdbc-bean]]
== JdbcUserDetailsManager Bean

In this sample we use xref:features/authentication/password-storage.adoc#authentication-password-storage-boot-cli[Spring Boot CLI] to encode the password of `password` and get the encoded password of `+{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW+`.
See the xref:features/authentication/password-storage.adoc#authentication-password-storage[PasswordEncoder] section for more details about how to store passwords.

.JdbcUserDetailsManager

[tabs]
======
Java::
+
[source,java,role="primary",attrs="-attributes"]
----
@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;
}
----

XML::
+
[source,xml,role="secondary",attrs="-attributes"]
----
<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>
----

Kotlin::
+
[source,kotlin,role="secondary",attrs="-attributes"]
----
@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
}
----
======