How to setup JDBC authentication with Spring Security from scratch - Java Brains

Let’s learn how to create a new Spring Security app that uses JDBC to connect to a database for user information. You’ll learn how to configure Spring Security to query your database to get users and authorities in order to authenticate your users, and you’ll also learn how to make it work with different schemas and data sources.
Default User Schema from documentation:
docs.spring.io/spring-securit...
Java Brains website: javabrains.io
#JavaBrains #BrainBytes #HowTo #SpringSecurity #Spring #SpringBoot #Java #Tutorial

[@piters101]

It will be great if you will talk about authentication using jwt for rest, and oauth

[@apoorvnigam2282]

Yes , we always wait for Koushik's video..... Thanks for your wonderful teaching

[@najisolayman]

I recently watched your playlist on Spring Security, and I must say, it was one of the best educational resources I've come across on the topic. Your explanations were clear, concise, and easy to understand, and I appreciated the way you broke down the concepts into bite-sized pieces. I just wanted to take a moment to thank you for creating such an informative and well-structured playlist. It's evident that you put a lot of time and effort into producing these videos, and it's greatly appreciated. Keep up the fantastic work!

[@bekirus1]

One of the best java teachers on world wide web :) Your explantations are easy to understand ! Awesome!

[@togax5]

Thank you so much for these amazing tutorials. You are one of my favorite teachers!

[@maheshdhavalshankh5974]

You explain very well sir!!! Thanks for being on KZbin 🙏🏻🙏🏻🙏🏻

[@ebenjs228]

Simple, clean and concise.

[@indranilgoswami1500]

Thanks Kuashik sir ..You are outstanding Teacher .. Thanks a lot .

[@CaracalServal]

This series is awesome!

[@sivanov23]

Great tutorial, with it and the use of Chat GPT I was able to generate and run the app you created on Spring Boot 3.3.0. There, we don't extend the WebSecurityConfigurerAdapter, but rather add each configuration as a separate Bean to make it available for the Spring container to recognize and include in the app.

[@ChristianAltamiranoAyala]

Great video Koushik greetings from peru.

[@francogb]

Thank you, but would be great if you can explain the usage with JWT. Keep it up!

[@jackbenimble999]

Great videos so far!

[@ilhamAhmedzada]

thanks for all videos ,they are really gereat (y)

[@lewispil3529]

thanks for the video, i'm watching video by video, well explained 🤗

[@AhamedKabeer-wn1jb]

Thank You.. Well Explained..

[@friendaanil]

amazing content everytime

[@YouTuber-lb9ot]

hope you have gone through each of them..

[@SuperWhatusername]

Thank you for this tutorial

[@mostafaalsabagh1660]

thank you so much , great work

[@cedhyuga91]

Thank you so muuuch !!! You the best !!

[@OlexiyNas]

Greate video, thanks!

[@mediss3720]

Very nice tutorial

[@okwuchukwuokwara1522]

Great tutorials

[@birdofhermes6152]

Thanks a lot for the video

[@mamoonalshamali8922]

thank you very much

[@kumarsaroj18]

Well, the tutorial is very clear and explained with very simple examples which make even complicated concepts very easy to grasp. However, you didn't mention/explained following 2 things: 1. schema.sql and data.sql are standard file names, and are automatically picked by springboot from resources folder. 2. From above two sql files, we can get our custom schema (instead of default one). You kept the table and column name same as present in the default schema. So table and columns with different name are allowed ? a. If not allowed, why did you mention that we can create our own schema? b. If allowed, how do we tell springboot, which column to look into for username and which column for password, and same goes to authorities/roles as well.

[@tanko.spirit7754]

he actually explained to you that you can use a custom schema and later on clarified that you would specify the columns/table name etc. in the.usersByUsernameQuery() and .authoritiesByUsernameQuery() queries! and i suppose the column names have to be "username", "password", "authority" and "enabled".. if your custom schema has different names for these columns, lets say, instead of "password" you have "pass", then all you would do in the query is: "select username, pass as 'password', ...." etc. i.e. using aliases if you don't know about it, check this out: www.w3schools.com/sql/sql_alias.asp

[@biswajitmondal7807]

@@tanko.spirit7754 is it mandatory to keep the fields authority,username password...if i want to change it to email column and password column and also delete the authority and enabled columns then?

[@nithinvarghese7765]

Wow..superb

[@SagirAnsari-bn7oj]

Super make it weekend while see notification bell...

[@SagirAnsari-bn7oj]

@revking You can simply create a data.sql file in your src/main/resources folder and it will be automatically executed on startup. In this file you just add some insert statements,...Similarly, you can create a schema.sql file (or schema-h2.sql) as well to create your schema

[@SagirAnsari-bn7oj]

@revking Yup

[@tomknud]

Neat video, thanks

[@umeshchoubey]

It's really great vedio. Can you upload video recording microservice transaction management

[@rajendrakumarsahoo5479]

First of all tons of thanks for these wonderful tutorials.But is it mandatory to have user and authority table relation as per spring boot standard ?

[@MrNSundar]

Thank you..it would be great if you can come up with a tutorial for ldap authentication.

[@PramodKumar-hu7mt]

Please do tutorials on Docker and Kubernetes

[@gigel008]

Hello Koushik. Maybe you already planned this, but could you show us how to get rid of the default html templates that spring security provides? Especially if you're building a rest service and don't want to use html at all.

[@waheedkhan5337]

Hi Koushik, by any chance could you do a mini tutorial on integrating Spring Boot, Spring Security and Angular 7+ with typical real-world login, logout authentication flows and maybe some commentary about session\cookie management between the front-end and back-end?

[@gautamtyagi8846]

@waheed khan try this url, I found this helpful. www.javainuse.com/spring/ang7-jwt

[@108vicky]

Nice.. please make vdo on spring spring oauth2 with jwt token

[@anshumandas1062]

Please create the next video on Spring Boot jwt JPA authentication

[@AbhishekAnandDas]

WebSecurityConfigurerAdapter has been deprecated in latest version of spring security. Can you make a video on latest version?

[@thannasip8001]

"Role means group of authorities" this is what I understood from your previous one,but here authorities table holds roles(admin, user) so authority means role right?

[@gormatikyan9663]

it is a very good tutorial but I have 2 questions. 1)In my example there is no difference between lowercase and uppercase for username 2)and there is authentication for url localhost:8080/user but there is not needed authentication for url localhost:8080/user/

[@bunthaideng2492]

Hello sir, I really appreciate with your tutorials. I have 2 questions. 1) what if I add and().httpBasic()? What does it mean here? 2) if user has different table, what is this design called?

[@kose25]

You really helped me, thank you

[@piyushbhatnagar2543]

Thanks for the above video. In my scenario I want to change the user roles based on the kind of data user is checking. The user has a search bar from which he can search different data. In that case are these queries dynamic in changing roles everytime? Or should I follow someother approach towards above problem

[@narimo2773]

Why in the configuration we have roles `ADMIN` & `USER`. However, in `data.sql` we have `ROLE_USER` & `ROLE_ADMIN`? And everything keeps working fine.

[@andrei-un3yr]

It's because spring adds a "ROLE_" prefix to the role you specify in configuration stackoverflow.com/questions/33205236/spring-security-added-prefix-role-to-all-roles-name

[@rizkyjayusman9567]

@@andrei-un3yr could i customize a default role's prefix?

[@arunkumarjaiswal4562]

@@andrei-un3yr thankyou

[@arunkumarjaiswal4562]

good question , I was also confused

[@aneckumar5701]

so since spring adds ROLE as prefix so we add in database with field as ROLE_USER etc , however we check without prefix?

[@amanrai8010]

The Saviour of Century Kuashik

[@sparaseth]

Thanks Koushik! Just wanted to know, how the username parameter being passed to the select query and the password equality being checked?

[@mikecmw8492]

Yes! How do you actually use this code?

[@sreyasaha151]

please upload videos spring security using oAuth2 ,okta,ldap etc.

[@kunaldas9284]

@Kaushik - I have a question after going through this video. Is it possible that we could have different external databases, one which stores the authentication and authorisation data and other one which stores the business/application data. If it is possible, then how can we configure these two different datasources to be used separately by spring security and our business logic.

[@stevegathara8885]

Do you have a tutorial for this specific using the latest spring security documentation? i.e using version 3.1 and above

[@snowdenfu]

thanks so much about security with database, but do u have code samples for this?

[@monsterhuntergo]

small caps on my data.sql worked for me. I think should be case sensitive though im using Spring Tool Suite 4 as IDE: insert into users (username, password, enabled) values ('user', 'pass', true); insert into users (username, password, enabled) values ('admin', 'pass', true); insert into authorities (username, authority) values ('user', 'ROLE_USER'); insert into authorities (username, authority) values ('admin', 'ROLE_ADMIN');

[@ek19751]

I would second the request for LDAP - a real quick one at least if you could please. Thanks again Koushik

[@Java.Brains]

LDAP video coming after the JPA one that I'm working on

[@ek19751]

@@Java.Brains Thank you so much Koushik! The quality of your lessons is of a different caliber than what I'd generally find on the internet, so that's why :-)

[@seanduignan294]

@@Java.Brains Yes please :)

[@ambersingh1493]

How can we check the h2-console and what will be the default url and credentials if we want to see the tables practically in h2-db?

[@biswajitmondal7807]

Hi kaushik thanks for this tutorial.I have one question is it possible to Change the query fields...like we querying on username,enabled,authority...if i want to make my custom login using email,password only?

[@merajsyed6987]

Sir could you please make a series on migrating spring security code to latest spring boot. As a lot of things have been updated in the latest spring security. Same code will break with latest spring boot.

[@vihangakanchana1211]

Hi, Usually SecurityConfiguration we are doing like .antMatchers("/api/public/test1").hasAuthority("ACCESS_TEST1") .antMatchers("/api/public/test2").hasAuthority("ACCESS_TEST2") But I want to get this endpoints and required authority to property file or DB. can I do it? and how can I do it?

[@yatri6329]

Can we see in the h2 dabase those created table

[@ek19751]

@Java Brains, do you have a video explaining OOP concepts in Java with examples? This is one subject that's hard to get some solid examples

[@Java.Brains]

No OOP videos yet. I'll add that to the list :)

[@ek19751]

@@Java.Brains Thank you again. Normally I'd find inheritance or polymorphism explained using an 'Animal' class, but I'd also like to see examples of stuff that I do on a daily basis. Input data could be from a database, webservice or flat file - I could create an interface and implement it, and then bring polymorphism into play... stuff like that. Thank you again!

[@akron1986]

Nice video, I just think it isn't good the default way that spring security create the tables. Using the column name with the same value is not good, should use Id to do that

[@Emanuel-kl2dt]

For my first app, would you recommend to use the jdbc approach shown in this video or to use JPA?

[@MI982]

For your first app whatever you now how to implement is perfect. If you think you can use JPA - go for it!

[@SamChaneyProductions]

Thanks for this tutorial. Mine is almost working but for some reason it only displays "Welcome" regardless of which user I login as. It never displays the "Welcome User" or "Welcome Admin" strings we defined in HomeResource.

[@kamalale7976]

i also got the same issue :S

[@ajaydhiman2368]

How we can use our own form instead of default one because if we need to modify then we can't do in this case

[@bhabanishankarjena9990]

Nice video , jdbc authentication program has not included in git hub. Would you please add that.

[@tirupatirao7521]

By default jdbc implementation will have h2 data source implemented/initialised?

[@ajaydhiman2368]

how to pass value for '?' in this example

[@sandeeptengale7021]

Thank you sir, one question in data.sql, the roles are ROLE_USER and ROLE_ADMIN. But in authorization antMatchers() it is just USER and ADMIN. How mapping is done here?

[@bhushanchaudhari3109]

Spring security automatically appends 'Role_'

[@sandeeptengale7021]

@@bhushanchaudhari3109 Thank you

[@varuntandon4465]

@Java Brains, why do we need to insert roles in authorities table like "ROLE_USER" or "ROLE_ADMIN", why can't we store it like "USER" and "ADMIN"?

[@shridharhmujumdar3568]

Hi Koushik Sir.. Why are you able to access the "/" api without any authentication ? Ideally, permitAll() shud permit all the authenticated users, but what i see is it is permitting everyone without any authentication.. please explain

[@content_Tube74]

hello how can i solve this? Parameter 0 of constructor in com.spring.security.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found.

[@PerpetualLearner8934]

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);

[@Daxbedivlogs]

can any of u help me resolve this error ?? Field dataSource in com.bedi.springsecurityjdbch2.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found. The injection point has the following annotations: - @org.springframework.beans.factory.annotation.Autowired(required=true) The following candidates were found but could not be injected: - Bean method 'dataSource' in 'JndiDataSourceAutoConfiguration' not loaded because @ConditionalOnClass did not find required class 'org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType' - Bean method 'dataSource' in 'XADataSourceAutoConfiguration' not loaded because @ConditionalOnClass did not find required class 'javax.transaction.TransactionManager' Action: Consider revisiting the entries above or defining a bean of type 'javax.sql.DataSource' in your configuration.

[@arsalalam5998]

the authority has string 'ROLE_USER' but in configure method we say 'USER' . Did not get that ?

[@ajaydhiman2368]

how we will pass userid in this method to configure() ?

[@adityamaurya8092]

In query why there is no matching of pass word

[@Alessandro281991]

How can i do it without a formlogin? I need to expose only a login api without a form login .

[@shabarishkumarelluru6214]

Hi Koushik, I have one doubt. Is it possible to give permit all access to the post method? I am trying as shown below protected void configure(HttpSecurity http) throws Exception { http .authorizeRequests() .antMatchers("/CreateOrder","/").hasRole("admin") .antMatchers("/getOrderById").hasAnyRole("clerk","admin","supervisor") .antMatchers("/createMyUser","/getMyUserByID","/", "/getAllOrders","/*").permitAll() .and().formLogin();// @formatter:off // @formatter:on // more lines } here createMyUser is a post method,as shown below @RequestMapping(value = "/createMyUser", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE, consumes = MediaType.APPLICATION_JSON_VALUE) public MyUser createMyUser(MyUser MyUser) throws Exception { MyUserRepository.save(MyUser); return MyUser; } Its working fie for getMethods, but not for post Methods. Please help me to clarify this query. Thanks!

[@runindiatraveller6634]

Hi sir this was wonderful tutorial,but i don't know why but my antmatcher is not working it is throwing error ,idk is it because i am using java se 17

[@bhabanishankarjena9990]

data.sql ,how getting stored to DB.

[@gpr1983]

is anybody getting the below? I tried the same as in the video, not getting how to fix the error. PreparedStatementCallback; SQL [select username, authority from authorities where username = ?]; Invalid value "3" for parameter "columnIndex" [90008-200]; nested exception is org.h2.jdbc.JdbcSQLDataException

[@HimanshuSingh2009]

Getting error : Field dataSource in com.security.jdbc.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found.

[@balaifarara]

At pom.xml check if scope of h2database is runtime or not

[@deryamilas2593]

how can i get user's username without hard coding it in config method?

[@thomassun3046]

one question here: why that place is ROLE_USER, ROLE_ADMIN, instead of USER,ADMIN,it is straightforwad!

[@arunkumarjaiswal4562]

It's because spring adds a "ROLE_" prefix to the role you specify in configuration stackoverflow.com/questions/33205236/spring-security-added-prefix-role-to-all-roles-name

[@kulashaker30]

Who the hell are these 20 haters that didn't like this in detail approach of Spring Security authentication via JDBC?????

[@henrymart6102]

Hello. Instead of creating new project in SpringBoot, I've just tried include in previous project Dependency "h2" (did synchro and update), and the program did not accepted expression ".dataSource(dataSource)" Why so? and how to correct it?

[@henrymart6102]

Ok. fixed by replacing "import javax.activation.DataSource;" with "import javax.sql.DataSource;" , but received another problem with table creation: "You have an error in your SQL syntax" (on video it is 11:30 launch)

[@coffecoding]

I am getting an error for DataSource Action: Consider revisiting the entries above or defining a bean of type 'javax.sql.DataSource' in your configuration.

[@coffecoding]

Oh god It was my bad, I forgot to add the jdbc-api, anyway I understood why it is added.

[@Daxbedivlogs]

i have same error Field dataSource in com.bedi.springsecurityjdbch2.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found. The injection point has the following annotations: - @org.springframework.beans.factory.annotation.Autowired(required=true) The following candidates were found but could not be injected: - Bean method 'dataSource' in 'JndiDataSourceAutoConfiguration' not loaded because @ConditionalOnClass did not find required class 'org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType' - Bean method 'dataSource' in 'XADataSourceAutoConfiguration' not loaded because @ConditionalOnClass did not find required class 'javax.transaction.TransactionManager' Action: Consider revisiting the entries above or defining a bean of type 'javax.sql.DataSource' in your configuration.

[@varunraj1862]

In data.sql role is having prefix as Role_User but in Authorize method of Configure role used is only user. How is it getting matched to be authorised. Kindly clarify.

[@1309CV]

Hi varun raj, Spring Security expects you to specify roles as "ROLE_", where "" can be: ' ADMIN', 'USER', etc. The reason you don't explicitly have to do this when manually creating (and configuring) users with the #roles(), #hasRoles(), #hasAnyRole(), etc methods, is because Spring Security is 'smart' enough to do this for you. The method #hasRole() calls an underlying method: public ExpressionInterceptUrlRegistry hasRole(String role) { return access(ExpressionUrlAuthorizationConfigurer.hasRole(role)); } The call to "hasRole(role)" checks if the String role starts with "ROLE_". private static String hasRole(String role) { Assert.notNull(role, "role cannot be null"); Assert.isTrue(!role.startsWith("ROLE_"), () -> "role should not start with 'ROLE_' since it is automatically inserted. Got '" + role + "'"); return "hasRole('ROLE_" + role + "')"; } If it doesn't, it just returns the String with that "ROLE_" prefix. This way, Spring Security can continue to do its job. However, when creating users through schema (.sql) files, this type of behavior is not supported. I'm not 100% sure what methods are called instead. The other 'role' type methods have similar characteristics, in that they call to check if the role starts with "ROLE_". They do this differently in their own respective ways. Hopefully, this helped answer your question. Cheers, Ares.

[@varunraj1862]

@@1309CV Thanks for your detailed explanation.

[@parvezahmed5287]

after ruuning this app the server stoped beacusw there is no bean found for datasource but why i have let spring to use defaultschema but still it show Field dataSource in security.jdbc.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found. why this error?? will i make a bean for datasource

[@olo1045]

I had the same problem, for me the solution was to add in application.properties the following lines: spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password= spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.h2.console.enabled=true spring.h2.console.path=/h2 and also in the pom.xml file add com.h2database h2 1.4.200 compile Without it wouldnt want to initiate the h2 driver xD

[@jackbenimble999]

Any code base?

[@thomassun3046]

IDEA shows me that:No data sources are configured to run this SQL

[@sourabhsharma6113]

You might have missed to add "JDBC API" dependency. Or if you're referring to the other case where "Spring Boot" configuration of IDE is failing to start the application, I am also facing the same. Please let know if you're able to find the solution to it. Though, the application runs fine from command line with the mvn cli - mvn spring-boot:run

[@amsfuy]

I always get the error: "Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "USERS" already exists; SQL statement" when starting the application. Does anybody know why or how to solve it?

[@amsfuy]

I was able to solve it the following way: User only a data.sql which contains the schema and data. Before creating the tables, I preprended the statements DROP TABLE IF EXISTS AUTHORITIES; DROP TABLE IF EXISTS USERS; In my application.properties file I added the lines spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.jpa.database-platform=org.hibernate.dialect.H2Dialect Maybe that helps anyone having the same problem (maybe my future self?!).

[@myharsh271]

u didnt specified the github link for code

[@DevPlayGame]

How to use Security without Spring boot? Thanks.

[@sug_madic7683]

use spring for security and use nodejs for mvc simple as that you ass

[@rnages1921]

Can someone help me I am stuck with below error; Referential integrity constraint violation: "FK_AUTHORITIES_USERS: PUBLIC.AUTHORITIES FOREIGN KEY(USERNAME) REFERENCES PUBLIC.USERS(USERNAME) (CAST('user' AS VARCHAR_IGNORECASE))"; SQL statement: INSERT INTO authorities (username, authority) values ('user', 'ROLE_USER') [23506-200]

[@smurfaccount9192]

I'm facing the same issues

[@hyperborean72]

rather complicate still amazing tutorial

[@Pyta321]

[@anil2009]

please upload password hashing videos sir🙏🙏🙏🙏

[@andrelong7230]

Where is your source code?

[@digitopotato1653]

tutorial is great but the theme is dark im having hard time to view clearly

[@aniketbharsakale2561]

@Java Brains, Thanks for these clips & I am following & executing them. I have followed till 11:47 "as is", but I see the spring-security is generating its own password with the default user as "user". Due to this, my app behaves like it only has web & security dependencies with no overridden login. (but, a matter of fact till here, I have my class SecConfig which extends WebSecurityConfigurerAdapter & overrides the 2 respective methods & the PasswordEncoder is there) But when I run the previous project with -- auth.inMemoryAuthentication() it runs as expected. while this project with -- auth.jdbcAuthentication() does not. Any clue? Or has anything got updated overtime? Any pointers/directions are appreciated. here's my GitHub link for the current state: github.com/aniketrb-github/spring-security/tree/main/spring-sec-jdbc-auth Thanks.

[@seanduignan294]

Hi Koushik, by any chance could you do a mini tutorial on integrating Spring Boot, Spring Security and Angular 7+ with typical real-world login, logout authentication flows and maybe some commentary about session\cookie management between the front-end and back-end?

[@mycasper9000]

That will be super amazing !