How to setup JDBC authentication with Spring Security from scratch - Java Brains
HTML-код
- Опубликовано: 19 май 2024
- 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
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!
It will be great if you will talk about authentication using jwt for rest, and oauth
Yes , we always wait for Koushik's video..... Thanks for your wonderful teaching
One of the best java teachers on world wide web :) Your explantations are easy to understand ! Awesome!
You explain very well sir!!!
Thanks for being on RUclips 🙏🏻🙏🏻🙏🏻
Thank you so much for these amazing tutorials. You are one of my favorite teachers!
Simple, clean and concise.
Thank you, but would be great if you can explain the usage with JWT. Keep it up!
This series is awesome!
Thanks Kuashik sir ..You are outstanding Teacher .. Thanks a lot .
Great video Koushik greetings from peru.
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 ?
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.
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
@@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?
thanks for the video, i'm watching video by video, well explained 🤗
thanks for all videos ,they are really gereat (y)
Great videos so far!
Thank you for this tutorial
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.
amazing content everytime
hope you have gone through each of them..
Thank You.. Well Explained..
thank you so much , great work
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
Thank you so muuuch !!! You the best !!
"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?
Very nice tutorial
WebSecurityConfigurerAdapter has been deprecated in latest version of spring security. Can you make a video on latest version?
@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.
Thank you..it would be great if you can come up with a tutorial for ldap authentication.
Wow..superb
It's really great vedio. Can you upload video recording microservice transaction management
Great tutorials
Super make it weekend while see notification bell...
@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
@revking Yup
Greate video, thanks!
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?
@waheed khan try this url, I found this helpful.
www.javainuse.com/spring/ang7-jwt
Thanks a lot for the video
thanks so much about security with database, but do u have code samples for this?
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?
Please do tutorials on Docker and Kubernetes
Neat video, thanks
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?
Do you have a tutorial for this specific using the latest spring security documentation? i.e using version 3.1 and above
Thanks Koushik!
Just wanted to know, how the username parameter being passed to the select query and the password equality being checked?
Yes! How do you actually use this code?
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/
thank you very much
Please create the next video on Spring Boot jwt JPA authentication
Nice.. please make vdo on spring spring oauth2 with jwt token
For my first app, would you recommend to use the jdbc approach shown in this video or to use JPA?
For your first app whatever you now how to implement is perfect. If you think you can use JPA - go for it!
By default jdbc implementation will have h2 data source implemented/initialised?
@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
No OOP videos yet. I'll add that to the list :)
@@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!
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?
Why in the configuration we have roles `ADMIN` & `USER`.
However, in `data.sql` we have `ROLE_USER` & `ROLE_ADMIN`?
And everything keeps working fine.
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
@@andrei-un3yr could i customize a default role's prefix?
@@andrei-un3yr thankyou
good question , I was also confused
so since spring adds ROLE as prefix so we add in database with field as ROLE_USER etc , however we check without prefix?
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?
please upload videos spring security using oAuth2 ,okta,ldap etc.
I would second the request for LDAP - a real quick one at least if you could please. Thanks again Koushik
LDAP video coming after the JPA one that I'm working on
@@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 :-)
@@Java.Brains Yes please :)
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?
Spring security automatically appends 'Role_'
@@bhushanchaudhari3109 Thank you
Nice video , jdbc authentication program has not included in git hub. Would you please add that.
Can we see in the h2 dabase those created table
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!
The Saviour of Century Kuashik
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');
@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"?
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
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
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
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.
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.
i also got the same issue :S
You really helped me, thank you
the authority has string 'ROLE_USER' but in configure method we say 'USER' . Did not get that ?
How can i do it without a formlogin? I need to expose only a login api without a form login .
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
how to pass value for '?' in this example
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.
how we will pass userid in this method to configure() ?
In query why there is no matching of pass word
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
data.sql ,how getting stored to DB.
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.
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.
@@1309CV Thanks for your detailed explanation.
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
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
how can i get user's username without hard coding it in config method?
Any code base?
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?
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)
Getting error : Field dataSource in com.security.jdbc.SecurityConfiguration required a bean of type 'javax.sql.DataSource' that could not be found.
At pom.xml check if scope of h2database is runtime or not
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.
Oh god It was my bad, I forgot to add the jdbc-api, anyway I understood why it is added.
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.
one question here: why that place is ROLE_USER, ROLE_ADMIN, instead of USER,ADMIN,it is straightforwad!
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
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?
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?!).
IDEA shows me that:No data sources are configured to run this SQL
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
u didnt specified the github link for code
How to use Security without Spring boot? Thanks.
use spring for security and use nodejs for mvc simple as that you ass
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]
I'm facing the same issues
please upload password hashing videos sir🙏🙏🙏🙏
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);
Who the hell are these 20 haters that didn't like this in detail approach of Spring Security authentication via JDBC?????
rather complicate still amazing tutorial
Where is your source code?
tutorial is great but the theme is dark im having hard time to view clearly
@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.
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?
That will be super amazing !