(window.webpackJsonp=window.webpackJsonp||[]).push([[241],{667:function(e,a,t){"use strict";t.r(a);varr=t(56),s=Object(r.a)({},(function(){vare=this,a=e.$createElement,t=e._self._c||a;returnt("ContentSlotsDistributor",{attrs:{"slot-key":e.$parent.slotKey}},[t("h1",{attrs:{id:"jdbc-authentication"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#jdbc-authentication"}},[e._v("#")]),e._v(" JDBC Authentication")]),e._v(""),t("p",[e._v("Spring Security’s "),t("code",[e._v("JdbcDaoImpl")]),e._v(" implements "),t("RouterLink",{attrs:{to:"/en/spring-security/user-details-service.html#servlet-authentication-userdetailsservice"}},[e._v("UserDetailsService")]),e._v(" to provide support for username/password based authentication that is retrieved using JDBC."),t("code",[e._v("JdbcUserDetailsManager")]),e._v(" extends "),t("code",[e._v("JdbcDaoImpl")]),e._v(" to provide management of "),t("code",[e._v("UserDetails")]),e._v(" through the "),t("code",[e._v("UserDetailsManager")]),e._v(" interface."),t("code",[e._v("UserDetails")]),e._v(" based authentication is used by Spring Security when it is configured to "),t("RouterLink",{attrs:{to:"/en/spring-security/index.html#servlet-authentication-unpwd-input"}},[e._v("accept a username/password")]),e._v(" for authentication.")],1),e._v(""),t("p",[e._v("In the following sections we will discuss:")]),e._v(""),t("ul",[t("li",[t("p",[e._v("The "),t("a",{attrs:{href:"#servlet-authentication-jdbc-schema"}},[e._v("Default Schema")]),e._v(" used by Spring Security JDBC Authentication")])]),e._v(""),t("li",[t("p",[t("a",{attrs:{href:"#servlet-authentication-jdbc-datasource"}},[e._v("Setting up a DataSource")])])]),e._v(""),t("li",[t("p",[t("a",{attrs:{href:"#servlet-authentication-jdbc-bean"}},[e._v("JdbcUserDetailsManager Bean")])])])]),e._v(""),t("h2",{attrs:{id:"default-schema"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#default-schema"}},[e._v("#")]),e._v(" Default Schema")]),e._v(""),t("p",[e._v("Spring Security provides default queries for JDBC based authentication.\nThis section provides the corresponding default schemas used with the default queries.\nYou will need to adjust the schema to match any customizations to the queries and the database dialect you are using.")]),e._v(""),t("h3",{attrs:{id:"user-schema"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#user-schema"}},[e._v("#")]),e._v(" User Schema")]),e._v(""),t("p",[t("code",[e._v("JdbcDaoImpl")]),e._v(" requires tables to load the password, account status (enabled or disabled) and a list of authorities (roles) for the user.\nThe default schema required can be found below.")]),e._v(""),t("table",[t("thead",[t("tr",[t("th"),e._v(""),t("th",[e._v("The default schema is also exposed as a classpath resource named "),t("code",[e._v("org/springframework/security/core/userdetails/jdbc/users.ddl")]),e._v(".")])])]),e._v(""),t("tbody")]),e._v(""),t("p",[e._v("Example 1. Default User Schema")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v("create table users(\n\tusername varchar_ignorecase(50) not null primary key,\n\tpassword varchar_ignorecase(500) not null,\n\tenabled boolean not null\n);\n\ncreate table authorities (\n\tusername varchar_ignorecase(50) not null,\n\tauthority varchar_ignorecase(50) not null,\n\tconstraint fk_authorities_users foreign key(username) references users(username)\n);\ncreate unique index ix_auth_username on authorities (username,authority);\n")])])]),t("p",[e._v("Oracle is a popular database choice, but requires a slightly different schema.\nYou can find the default Oracle Schema for users below.")]),e._v(""),t("p",[e._v("Example 2. Default User Schema for Oracle Databases")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v("CREATE TABLE USERS (\n USERNAME NVARCHAR2(128) PRIMARY KEY,\n PASSWORD NVARCHAR2(128) NOT NULL,\n ENABLED CHAR(1) CHECK (ENABLED IN ('Y','N') ) NOT NULL\n);\n\nCREATE TABLE AUTHORITIES (\n USERNAME NVARCHAR2(128) NOT NULL,\n AUTHORITY NVARCHAR2(128) NOT NULL\n);\nALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_UNIQUE UNIQUE (USERNAME, AUTHORITY);\nALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_FK1 FOREIGN KEY (USERNAME) REFERENCES USERS (USERNAME) ENABLE;\n")])])]),t("h3",{attrs:{id:"group-schema"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#group-schema"}},[e._v("#")]),e._v(" Group Schema")]),e._v(""),t("p",[e._v("If your application is leveraging groups, you will need to provide the groups schema.\nThe default schema for groups can be found below.")]),e._v(""),t("p",[e._v("Example 3. Default Group Schema")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v("create table groups (\n\tid bigint generated by default as identity(start with 0) primary key,\n\tgroup_name varchar_ignorecase(50) not null\n);\n\ncreate table group_authorities (\n\tgroup_id bigint not null,\n\tauthority varchar(50) not null,\n\tconstraint fk_group_authorities_group foreign key(group_id) references groups(id)\n);\n\ncreate table group_members (\n\tid bigint generated by default as identity(start with 0) primary key,\n\tusername varchar(50) not null,\n\tgroup_id bigint not null,\n\tconstraint fk_group_members_group foreign key(group_id) references groups(id)\n);\n")])])]),t("h2",{attrs:{id:"setting-up-a-datasource"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#setting-up-a-datasource"}},[e._v("#")]),e._v(" Setting up a DataSource")]),e._v(""),t("p",[e._v("Before we configure "),t("code",[e._v("JdbcUserDetailsManager")]),e._v(", we must create a "),t("code",[e._v("DataSource")]),e._v(".\nIn our example, we will setup an "),t("a",{attrs:{href:"https://docs.spring.io/spring-framework/docs/current/spring-framework-reference/data-access.html#jdbc-embedded-database-support",target:"_blank",rel:"noopener noreferrer"}},[e._v("embedded DataSource"),t("OutboundLink")],1),e._v(" that is initialized with the "),t("a",{attrs:{href:"#servlet-authentication-jdbc-schema"}},[e._v("default user schema")]),e._v(".")]),e._v(""),t("p",[e._v("Example 4. Embedded Data Source")]),e._v(""),t("p",[e._v("Java")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('@Bean\nDataSource dataSource() {\n\treturn new EmbeddedDatabaseBuilder()\n\t\t.setType(H2)\n\t\t.addScript("classpath:org/springframework/security/core/userdetails/jdbc/users.ddl")\n\t\t.build();\n}\n')])])]),t("p",[e._v("XML")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('<jdbc:embedded-database>\n\t<jdbc:script location="classpath:org/springframework/security/core/userdetails/jdbc/users.ddl"/>\n</jdbc:embedded-database>\n')])])]),t("p",[e._v("Kotlin")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('@Bean\nfun dataSource(): DataSource {\n return EmbeddedDatabaseBuilder()\n .setType(H2)\n .addScript("classpath:org/springframework/security/core/userdetails/jdbc/users.ddl")\n .build()\n}\n')])])]),t("p",[e._v("In a production environment, you will want to ensure you setup a connection to an external database.")]),e._v(""),t("h2",{attrs:{id:"jdbcuserdetailsmanager-bean"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#jdbcuserdetailsmanager-bean"}},[e._v("#")]),e._v(" JdbcUserDetailsManager Bean")]),e._v(""),t("p",[e._v("In this sample we use "),t("RouterLink",{attrs:{to:"/features/authentication/password-storage.html#authentication-password-storage-boot-cli"}},[e._v("Spring Boot CLI")]),e._v(" to encode the password of "),t("code",[e._v("password")]),e._v(" and get the encoded password of "),t("code",[e._v("{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")]),e._v(".\nSee the "),t("RouterLink",{attrs:{to:"/features/authentication/password-storage.html#authentication-password-storage"}},[e._v("PasswordEncoder")]),e._v(" section for more details about how to store passwords.")],1),e._v(""),t("p",[e._v("Example 5. JdbcUserDetailsManager")]),e._v(""),t("p",[e._v("Java")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('@Bean\nUserDetailsManager users(DataSource dataSource) {\n\tUserDetails user = User.builder()\n\t\t.username("user")\n\t\t.password("{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")\n\t\t.roles("USER")\n\t\t.build();\n\tUserDetails admin = User.builder()\n\t\t.username("admin")\n\t\t.password("{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")\n\t\t.roles("USER", "ADMIN")\n\t\t.build();\n\tJdbcUserDetailsManager users = new JdbcUserDetailsManager(dataSource);\n\tusers.createUser(user);\n\tusers.createUser(admin);\n\treturn users;\n}\n')])])]),t("p",[e._v("XML")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('<jdbc-user-service>\n\t<user name="user"\n\t\tpassword="{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW"\n\t\tauthorities="ROLE_USER" />\n\t<user name="admin"\n\t\tpassword="{bcrypt}$2a$10$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW"\n\t\tauthorities="ROLE_USER,ROLE_ADMIN" />\n</jdbc-user-service>\n')])])]),t("p",[e._v("Kotlin")]),e._v(""),t("div",{staticClass:"language- extra-class"},[t("pre",{pre:!0,attrs:{class:"language-text"}},[t("code",[e._v('@Bean\nfun users(dataSource: DataSource): UserDetailsManager {\n val user = User.builder()\n .username("user")\n .password("{bcrypt}$2a$10\\$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")\n .roles("USER")\n .build();\n val admin = User.builder()\n .username("admin")\n .password("{bcrypt}$2a$10\\$GRLdNijSQMUvl/au9ofL.eDwmoohzzS7.rmNSJZ.0FxO/BTk76klW")\n .roles("USER", "ADMIN")\n .build();\n val users = JdbcUserDetailsManager(dataSource)\n users.createUser(user)\n users.createUser(admin)\n return users\n}\n')])])]),t("p",[t("RouterLink",{attrs:{to:"/en/spring-security/in-memory.html"}},[e._v("In Memory")]),t("RouterLink",{attrs:{to:"/en/spring-security/user-details.html"}},[e._v("UserDetails")])],1)])}),[],!1,null,null,null);a.default=s.exports}}]);
